4.4.1 Spatial support

This section is under development

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.

4.4.2 Get Going

We assume you have an sql client connected to a MonetDB server with the spatial extension being loaded. How to do this is described in the section 'Installation' below.

An illustrative example script is given below. It 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;

4.4.3 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.

4.4.4 Installation

The GIS functionality is packaged as a separate MonetDB module called geom. It requires installation of MonetDB/SQL version 2.20 and up.

To benefit from the geometry functionality you first have to download and install GEOS from http://geos.refractions.net/. It is a well-known and sound library to built upon.

The next step is to check out the MonetDB/SQL geom module from our Sourceforge location and install it following the guidelines for MonetDB/SQL.

Once the geom module has been installed you can start the MonetDB/SQL server with geometry support using the following command:

mserver5 --dbinit="include geom;include sql;sql.start();"

Now on a client run the geom initialization script once:

mclient -lsql < [PATH]/share/MonetDB/sql/geom.sql

Please note that running this script changes the database in such a way that from now on the server should always be started with the geom module included.

4.4.5 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 has been tested with OpenJUMP, an open-source Geographic Information System application available at http://openjump.org/wiki/show/HomePage.

4.4.6 Limitations

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.
• support for 3D types.
• spatial optimizers in the MAL optimizer toolkit.

4.4.7 Spatial Types

MonetDB supports the OpenGIS types: Point, Curve, LineString, Surface, Polygon, MultiPoint, MultiCurve, MultiLineString, MultiSurface, MultiPolygon, Geometry and GeomCollection.

One non-OpenGIS type for fast access is used. This type 'mbr' is used for storing a 2D box. Functions to create these boxes are specified in following sections.

4.4.8 SQL functions on spatial objects

4.4.8.1 Functions on mbr's

The following functions return the mbr of a given geometry:

CREATE FUNCTION mbr (p Point) RETURNS mbr external name geom.mbr;
CREATE FUNCTION mbr (c Curve) RETURNS mbr external name geom.mbr;
CREATE FUNCTION mbr (l LineString) RETURNS mbr external name geom.mbr;
CREATE FUNCTION mbr (s Surface) RETURNS mbr external name geom.mbr;
CREATE FUNCTION mbr (p Polygon) RETURNS mbr external name geom.mbr;
CREATE FUNCTION mbr (m multipoint) RETURNS mbr external name geom.mbr;
CREATE FUNCTION mbr (m multicurve) RETURNS mbr external name geom.mbr;
CREATE FUNCTION mbr (m multilinestring) RETURNS mbr external name geom.mbr;
CREATE FUNCTION mbr (m multisurface) RETURNS mbr external name geom.mbr;
CREATE FUNCTION mbr (m multipolygon) RETURNS mbr external name geom.mbr;
CREATE FUNCTION mbr (g Geometry) RETURNS mbr external name geom.mbr;
CREATE FUNCTION mbr (g GeomCollection) RETURNS mbr external name geom.mbr;

This function returns true iff two mbrs overlap:

CREATE FUNCTION mbroverlaps(a mbr, b mbr) RETURNS BOOLEAN external name geom."mbroverlaps";

4.4.8.2 Conversion from and to Well-known Text

Convert a Well-Known-Text string to a spatial object. The SRID parameter is a reference to the Spatial Reference System in which the coordinates are expressed.

CREATE FUNCTION GeomFromText(wkt string, srid SMALLINT) RETURNS Geometry external name geom."GeomFromText";
CREATE FUNCTION PointFromText(wkt string, srid SMALLINT) RETURNS Point external name geom."PointFromText";
CREATE FUNCTION LineFromText(wkt string, srid SMALLINT) RETURNS LineString external name geom."LineFromText";
CREATE FUNCTION PolyFromText(wkt string, srid SMALLINT) RETURNS Polygon external name geom."PolyFromText";
CREATE FUNCTION MPointFromText(wkt string, srid SMALLINT) RETURNS MultiPoint external name geom."MultiPointFromText";
CREATE FUNCTION MLineFromText(wkt string, srid SMALLINT) RETURNS MultiLineString external name geom."MultiLineFromText";
CREATE FUNCTION MPolyFromText(wkt string, srid SMALLINT) RETURNS MultiPolygon external name geom."MultiPolyFromText";
CREATE FUNCTION GeomCollectionFromText(wkt string, srid SMALLINT) RETURNS MultiPolygon external name geom."GeomCollectionFromText";
-- alias
CREATE FUNCTION PolygonFromText(wkt string, srid SMALLINT) RETURNS Polygon external name geom."PolyFromText";

Return Well-know Text representation of spatial objects.

CREATE FUNCTION AsText(p Point) RETURNS STRING external name geom."AsText";
CREATE FUNCTION AsText(c Curve) RETURNS STRING external name geom."AsText";
CREATE FUNCTION AsText(l LineString) RETURNS STRING external name geom."AsText";
CREATE FUNCTION AsText(s Surface) RETURNS STRING external name geom."AsText";
CREATE FUNCTION AsText(p Polygon) RETURNS STRING external name geom."AsText";
CREATE FUNCTION AsText(p MultiPoint) RETURNS STRING external name geom."AsText";
CREATE FUNCTION AsText(c MultiCurve) RETURNS STRING external name geom."AsText";
CREATE FUNCTION AsText(l MultiLineString) RETURNS STRING external name geom."AsText";
CREATE FUNCTION AsText(s MultiSurface) RETURNS STRING external name geom."AsText";
CREATE FUNCTION AsText(p MultiPolygon) RETURNS STRING external name geom."AsText";
CREATE FUNCTION AsText(g Geometry) RETURNS STRING external name geom."AsText";

4.4.8.3 Analysis functions on Geometry

The following functions perform analysis operations on geometries:

CREATE FUNCTION Area(g Geometry) RETURNS FLOAT external name geom."Area";
CREATE FUNCTION Length(g Geometry) RETURNS FLOAT external name geom."Length";
CREATE FUNCTION Distance(a Geometry, b Geometry) RETURNS FLOAT external name geom."Distance";
CREATE FUNCTION Buffer(a Geometry, distance FLOAT) RETURNS Geometry external name geom."Buffer";
CREATE FUNCTION ConvexHull(a Geometry) RETURNS Geometry external name geom."ConvexHull";
CREATE FUNCTION Intersection(a Geometry, b Geometry) RETURNS Geometry external name geom."Intersection";
CREATE FUNCTION "Union"(a Geometry, b Geometry) RETURNS Geometry external name geom."Union";
CREATE FUNCTION Difference(a Geometry, b Geometry) RETURNS Geometry external name geom."Difference";
CREATE FUNCTION SymDifference(a Geometry, b Geometry) RETURNS Geometry external name geom."SymDifference";

CREATE FUNCTION Dimension(g Geometry) RETURNS integer external name geom."Dimension";
CREATE FUNCTION GeometryTypeId(g Geometry) RETURNS integer external name geom."GeometryTypeId";
CREATE FUNCTION SRID(g Geometry) RETURNS integer external name geom."SRID";
CREATE FUNCTION Envelope(g Geometry) RETURNS Geometry external name geom."Envelope";
CREATE FUNCTION IsEmpty(g Geometry) RETURNS BOOLEAN external name geom."IsEmpty";
CREATE FUNCTION IsSimple(g Geometry) RETURNS BOOLEAN external name geom."IsSimple";
CREATE FUNCTION Boundary(g Geometry) RETURNS Geometry external name geom."Boundary";

CREATE FUNCTION Equals(a Geometry, b Geometry) RETURNS BOOLEAN external name geom."Equals";
CREATE FUNCTION Disjoint(a Geometry, b Geometry) RETURNS BOOLEAN external name geom."Disjoint";
CREATE FUNCTION "Intersect"(a Geometry, b Geometry) RETURNS BOOLEAN external name geom."Intersect";
CREATE FUNCTION Touches(a Geometry, b Geometry) RETURNS BOOLEAN external name geom."Touches";
CREATE FUNCTION Crosses(a Geometry, b Geometry) RETURNS BOOLEAN external name geom."Crosses";
CREATE FUNCTION Within(a Geometry, b Geometry) RETURNS BOOLEAN external name geom."Within";
CREATE FUNCTION Contains(a Geometry, b Geometry) RETURNS BOOLEAN external name geom."Contains";
CREATE FUNCTION Overlaps(a Geometry, b Geometry) RETURNS BOOLEAN external name geom."Overlaps";
CREATE FUNCTION Relate(a Geometry, b Geometry, pattern STRING) RETURNS BOOLEAN external name geom."Relate";

4.4.8.4 SQL functions on Point

CREATE FUNCTION X(g Geometry) RETURNS double external name geom."X";
CREATE FUNCTION Y(g Geometry) RETURNS double external name geom."Y";

CREATE FUNCTION Point(x double,y double) RETURNS Point external name geom.point;

4.4.8.5 SQL functions on Curve

CREATE FUNCTION IsRing(l LineString) RETURNS BOOLEAN external name geom."IsRing";
CREATE FUNCTION StartPoint(l LineString) RETURNS Point external name geom."StartPoint"; -- not yet implemented
CREATE FUNCTION EndPoint(l LineString) RETURNS Point external name geom."EndPoint"; -- not yet implemented

4.4.8.6 SQL functions on LineString

CREATE FUNCTION NumPoints(l LineString) RETURNS integer external name geom."NumPoints"; -- not yet implemented
CREATE FUNCTION PointN(l LineString,i integer) RETURNS Point external name geom."PointN"; -- not yet implemented

4.4.8.7 SQL functions on Surface

CREATE FUNCTION PointOnSurface(s Surface) RETURNS Point external name geom."PointOnSurface"; -- not yet implemented
CREATE FUNCTION Centroid(s Surface) RETURNS Point external name geom."Centroid"; -- not yet implemented

4.4.8.8 SQL functions on Polygon

CREATE FUNCTION ExteriorRing(s Surface) RETURNS LineString external name geom."ExteriorRing"; -- not yet implemented
CREATE FUNCTION NumInteriorRing(s Surface) RETURNS integer external name geom."NumInteriorRing"; -- not yet implemented
CREATE FUNCTION InteriorRingN(s Surface,n integer) RETURNS LineString external name geom."InteriorRingN"; -- not yet implemented

4.4.8.9 SQL functions on GeomCollection

-- Unimplemented Documentation
CREATE FUNCTION NumGeometries(GeomCollection c) RETURNS integer external name geom."NumGeometries"; -- not yet implemented
CREATE FUNCTION GeometryN(GeomCollection c,n integer) RETURNS Geometry external name geom."GeometryN"; -- not yet implemented