MonetDB/SQL comes with an interface to the Simple Feature Specification of OpenGIS which opens the route to develop GIS applications
The MonetDB/SQL/GIS module supports all objects and functions specified in the OGC "Simple Features for SQL" specification. Spatial objects can, however, for the time being only expressed in the Well-Known Text (WKT) format. WKT includes information about the type of the object and the object's coordinates.
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. You best start on a new database.
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.
Bulk loading (currently not included)
The MonetDB distribution includes the program 'shp2monetdb' to convert a shapefile into SQL insert statements. This is a port from the shp2pgsql program that is shipped with PostGIS.
The functionality of the functionality has been tested with OpenJUMP, an open-source Geographic Information System.
This is the first implementation of OpenGIS functionality in MonetDB. Many issues require our attention, but priority will be derived from 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.