Geospatial

Spatial support

MonetDB/SQL comes with an interface to the Simple Feature Specification of the Open Geospatial Consortium(formerly known asOpen GIS Consortium)which opens the route to develop GIS .

The MonetDB/SQL/GISmodule supports all objects and functions specified in the OGC "Simple Features for SQL" specification. Spatial objects can, however, for the time being only be expressed in the Well-Known Text (WKT) format. WKT includes information about the type of the object and the object's coordinates.

Installation

The GIS functionality is packaged as a separate MonetDB module called geom. To benefit from the geometry functionality you first have to download and install geos. It is a well-known and sound library to built upon. The next step is to (re-)build MonetDB with the --enable-geom configure argument. This will build the necessary extension modules and activate them upon the first start of the server. Note that databases created before you configured with support for geom will lack geom functions in SQL. We recommend you to start on a new database.

Get Going

The spatial extension of MonetDB requires the user to simply use geom data types from SQL.

Example The script below creates and populates a 'forest' table and a 'buildings' table followed by a spatial query in this fictive landscape.

CREATE TABLE forests(id INT,name TEXT,shape MULTIPOLYGON);
CREATE TABLE buildings(id INT,name TEXT,location POINT,outline POLYGON);

INSERT INTO forests VALUES(109, 'Green Forest',
'MULTIPOLYGON( ((28 26,28 0,84 0,84 42,28 26), (52 18,66 23,73 9,48 6,52 18)), ((59 18,67 18,67 13,59 13,59 18)))');

INSERT INTO buildings VALUES(113, '123 Main Street',
	'POINT( 52 30 )',
	'POLYGON( ( 50 31, 54 31, 54 29, 50 29, 50 31) )');
INSERT INTO buildings VALUES(114, '215 Main Street',
	'POINT( 64 33 )',
	'POLYGON( ( 66 34, 62 34, 62 32, 66 32, 66 34) )');

SELECT forests.name,buildings.name
FROM forests,buildings
WHERE forests.name = 'Green Forest' and
    Overlaps(forests.shape, buildings.outline) = true;

Acceleration Spatial Operations

There are no special accelerators to speed up access to Spatial Objects yet. However, we can use the Minimum Bounding Rectangle (mbr) datatype to accelerate operations considerably. This requires a small query rewrite. In the example above the performance of the query can be improved in the following manner:

ALTER TABLE forests ADD bbox mbr;
UPDATE forests SET bbox = mbr(shape);
ALTER TABLE buildings ADD bbox mbr;
UPDATE buildings SET bbox = mbr(outline);

SELECT forests.name,buildings.name
FROM forests,buildings
WHERE forests.name = 'Green Forest' AND
    mbroverlaps(forests.bbox,buildings.bbox) = TRUE AND
    Overlaps(forests.shape, buildings.outline) = TRUE;

In this way the mbr operation acts as a filter. Upon request, and availabilty of resources, we will develop MAL optimizers to automate this process.

Limitations

This is the first implementation of OGC functionality in MonetDB. It is based on libgeos 3.3.0. Further development will be based on concrete external requests and availability of manpower. The shortlist of open issues is:

  • development of a JDBC extension to map the geometry datatypes to their Java counterparts. (UDFs/type mapping)
  • support for 3D types.
  • spatial optimizers in the MAL optimizer toolkit to exploit spatial search predicates