25 July 2016
25 July 2016,
 Off

London, July 25, 2016

In versions prior to 2.2, OrientDB had minimal support for storing and retrieving GeoSpatial data. The support was limited to a pair of coordinates (latitude, longitude) stored as double in an OrientDB class, with the possibility to create a spatial index against those 2 coordinates in order to speed up a geo spatial query. So the support was limited to Point.
In OrientDB v.2.2 we created a brand new Spatial Module with support for different types of Geometry objects stored as embedded objects in a user defined class

  • Point (OPoint)
  • Line (OLine)
  • Polygon (OPolygon)
  • MultiPoint (OMultiPoint)
  • MultiLine (OMultiline)
  • MultiPolygon (OMultiPlygon)
  • Geometry Collections

Along with those data types, the module extends OrientDB SQL with a subset of SQL-MM functions in order to support spatial data.The module only supports EPSG:4326 as Spatial Reference System. This blog post is an introduction to the OrientDB spatial Module, with some examples of its new capabilities. You can find the installation guide here.

Let’s start by loading some data into OrientDB. The dataset is about points of interest in Italy taken from here. Since the format is ShapeFile we used QGis to export the dataset in CSV format (geometry format in WKT) and import the CSV into OrientDB with the ETL in the class Points and the type geometry field is OPoint.

image08

Since the WKT field is in string format we have to create the geometry property in the Points class

create property Points.location EMBEDDED OPoint

 
And update it in order to insert the geometry field by using the Function ST_GeomFromText.

update Points set location = ST_GeomFromText(WKT)

 
In this way, we store the position in the field location stored as an Embedded object with an OPoint class and field coordinates. If we want to insert another point of interest we can use an SQL statement in the following way:

INSERT INTO Points SET name = “Some Name”, location = {"@class": "OPoint","coordinates" : [lon,lat]}

 
The order of lon/lat here in order to be GeoJSON compliant.

Alternetively, we may use the function to create the Point object from the WKT format:

INSERT INTO Points SET name = “Some Name”, location = St_GeomFromText("POINT (lon lat)")

 
Now let’s suppose that we are in the middle of Rome near Santa Maria in Trastevere, thirsty, and looking for something to drink. We could use the function ST_Distance_Sphere to find points that are near us within a max distance of, let’s say, 200 meters.

select *,ST_Distance_Sphere(location,ST_GeomFromText('POINT(12.4696635 41.8894657)')) as distance from Points where ST_Distance_Sphere(location,ST_GeomFromText('POINT(12.4696635 41.8894657)')) < 200 order by distance

 
image07

We can see that at ~113 meters distance we have a drinking water fountain. By using the location field in the result set, we can easily use the Google Maps API to create a map from that data.

image04

Now we want to know the same information but faster than before. Just create a spatial index on the location property:

create index Points.location on Points(location) SPATIAL ENGINE LUCENE

 
And execute the query again.

image05

Let’s complicate things a little bit. We said the new spatial module is able to handle different type of Geometry objects. With the same procedure as before (ShapeFile + QGis + CSV + ETL) we import the dataset of states and provinces boundaries of countries into OrientDB.
In this case the target class is ‘Provinces’ and the type geometry field is OMultipolygon, created with SQL

create property Provinces.geometry EMBEDDED OMultipolygon

 
Then we inspect some provinces of Italy just to check the imported data.

Once we have the provinces of Italy, we can use a spatial function to execute a nice query and extract interesting information.

For example we could use the ST_Contains function if we want know which is the Province of Calcata, a tiny little town near Rome.

select iso_3166_2,gn_name,region,geonunit from Provinces where ST_Contains(geometry, ST_GeomFromText('POINT(12.42617 42.21952)')) = true

 
image06

Or we could use the ST_Intersects function to get all the provinces that the Tiber river crosses. For this query, we extracted the WKT geometry data of the Tiber (MultiLineString) from here.

select iso_3166_2,gn_name,region,geonunit from Provinces where ST_Intersects(geometry, ST_GeomFromText(<MultiLineString>) = true order by region

 
where <MultiLineString> is the WKT notation of the Tiber

At this point, it is also possible to create a map with the provincial boundaries.

There are other implemented functions bundled with the module. You can find the full documentation here. The Spatial Module is a new feature in OrientDB v.2.2, if you want to see it improved with new functionalities, please drop us a line using OrientDB’s issue tracker.

Hoping this comes in handy,

Enrico Risa
OrientDB LTD

Comments are closed.