GeoSpatial

GeoSpatial mk Sun, 03/28/2010 - 23:35

Spatial support

MonetDB/SQL comes with an interface to the Simple Feature Specification of the Open Geospatial Consortium (formerly known as Open GIS Consortium) 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.

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

 

Spatial data types

Spatial data types mk Sat, 01/14/2012 - 15:17

Spatial Types

MonetDB supports the Open Geospatial Consortium (OGC, formerly OpenGIS) types: Point, Curve, LineString, Surface, Polygon, MultiPoint, MultiCurve, MultiLineString, MultiSurface, MultiPolygon, Geometry and GeomCollection. One non-OpenGIS type for fast access using pre-filtering is used. This type 'mbr' is used for storing a 2D box. Functions to create these boxes are specified in following sections.

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
CREATE FUNCTION PointFromText(wkt string, srid SMALLINT) RETURNS Point
CREATE FUNCTION LineFromText(wkt string, srid SMALLINT) RETURNS LineString
CREATE FUNCTION PolyFromText(wkt string, srid SMALLINT) RETURNS Polygon
CREATE FUNCTION MPointFromText(wkt string, srid SMALLINT) RETURNS MultiPoint
CREATE FUNCTION MLineFromText(wkt string, srid SMALLINT) RETURNS MultiLineString
CREATE FUNCTION MPolyFromText(wkt string, srid SMALLINT) RETURNS MultiPolygon
CREATE FUNCTION GeomCollectionFromText(wkt string, srid SMALLINT) RETURNS MultiPolygon
-- alias
CREATE FUNCTION PolygonFromText(wkt string, srid SMALLINT) RETURNS Polygon
 
CREATE FUNCTION AsText(p Point) RETURNS STRING
CREATE FUNCTION AsText(c Curve) RETURNS STRING
CREATE FUNCTION AsText(l LineString) RETURNS STRING
CREATE FUNCTION AsText(s Surface) RETURNS STRING
CREATE FUNCTION AsText(p Polygon) RETURNS STRING
CREATE FUNCTION AsText(p MultiPoint) RETURNS STRING
CREATE FUNCTION AsText(c MultiCurve) RETURNS STRING
CREATE FUNCTION AsText(l MultiLineString) RETURNS STRING
CREATE FUNCTION AsText(s MultiSurface) RETURNS STRING
CREATE FUNCTION AsText(p MultiPolygon) RETURNS STRING
CREATE FUNCTION AsText(g Geometry) RETURNS STRING

Analysis functions on Geometry

The following functions perform analysis operations on geometries:

CREATE FUNCTION Area(g Geometry) RETURNS FLOAT
CREATE FUNCTION Length(g Geometry) RETURNS FLOAT
CREATE FUNCTION Distance(a Geometry, b Geometry) RETURNS FLOAT
CREATE FUNCTION Buffer(a Geometry, distance FLOAT) RETURNS Geometry
CREATE FUNCTION ConvexHull(a Geometry) RETURNS Geometry
CREATE FUNCTION Intersection(a Geometry, b Geometry) RETURNS Geometry
CREATE FUNCTION "Union"(a Geometry, b Geometry) RETURNS Geometry
CREATE FUNCTION Difference(a Geometry, b Geometry) RETURNS Geometry
CREATE FUNCTION SymDifference(a Geometry, b Geometry) RETURNS Geometry
 
CREATE FUNCTION Dimension(g Geometry) RETURNS integer
CREATE FUNCTION GeometryTypeId(g Geometry) RETURNS integer
CREATE FUNCTION SRID(g Geometry) RETURNS integer
CREATE FUNCTION Envelope(g Geometry) RETURNS Geometry
CREATE FUNCTION IsEmpty(g Geometry) RETURNS BOOLEAN
CREATE FUNCTION IsSimple(g Geometry) RETURNS BOOLEAN
CREATE FUNCTION Boundary(g Geometry) RETURNS Geometry
 
CREATE FUNCTION Equals(a Geometry, b Geometry) RETURNS BOOLEAN
CREATE FUNCTION Disjoint(a Geometry, b Geometry) RETURNS BOOLEAN
CREATE FUNCTION "Intersect"(a Geometry, b Geometry) RETURNS BOOLEAN
CREATE FUNCTION Touches(a Geometry, b Geometry) RETURNS BOOLEAN
CREATE FUNCTION Crosses(a Geometry, b Geometry) RETURNS BOOLEAN
CREATE FUNCTION Within(a Geometry, b Geometry) RETURNS BOOLEAN
CREATE FUNCTION Contains(a Geometry, b Geometry) RETURNS BOOLEAN
CREATE FUNCTION Overlaps(a Geometry, b Geometry) RETURNS BOOLEAN
CREATE FUNCTION Relate(a Geometry, b Geometry, pattern STRING) RETURNS BOOLEAN
SQL functions on Point
CREATE FUNCTION X(g Geometry) RETURNS double
CREATE FUNCTION Y(g Geometry) RETURNS double
 
CREATE FUNCTION Point(x double,y double) RETURNS Point
 
SQL functions on Curve
CREATE FUNCTION IsRing(l LineString) RETURNS BOOLEAN
CREATE FUNCTION StartPoint(l LineString) RETURNS Point -- not yet implemented
CREATE FUNCTION EndPoint(l LineString) RETURNS Point -- not yet implemented
 
SQL functions on LineString
CREATE FUNCTION NumPoints(l LineString) RETURNS integer -- not yet implemented
CREATE FUNCTION PointN(l LineString,i integer) RETURNS Point -- not yet implemented
 
SQL functions on Surface
CREATE FUNCTION PointOnSurface(s Surface) RETURNS Point -- not yet implemented
CREATE FUNCTION Centroid(s Surface) RETURNS Point -- not yet implemented
 
SQL functions on Polygon
CREATE FUNCTION ExteriorRing(s Surface) RETURNS LineString -- not yet implemented
CREATE FUNCTION NumInteriorRing(s Surface) RETURNS integer -- not yet implemented
CREATE FUNCTION InteriorRingN(s Surface,n integer) RETURNS LineString -- not yet implemented
 
SQL functions on GeomCollection
-- Unimplemented Documentation
CREATE FUNCTION NumGeometries(GeomCollection c) RETURNS integer -- not yet implemented
CREATE FUNCTION GeometryN(GeomCollection c,n integer) RETURNS Geometry -- not yet implemented

SQL functions on spatial objects

The following functions return the minimum bounded rectangle (or boolean) of a given geometry:

CREATE FUNCTION mbr (p Point) RETURNS mbr
CREATE FUNCTION mbr (c Curve) RETURNS mbr
CREATE FUNCTION mbr (l LineString) RETURNS mbr
CREATE FUNCTION mbr (s Surface) RETURNS mbr
CREATE FUNCTION mbr (p Polygon) RETURNS mbr
CREATE FUNCTION mbr (m multipoint) RETURNS mbr
CREATE FUNCTION mbr (m multicurve) RETURNS mbr
CREATE FUNCTION mbr (m multilinestring) RETURNS mbr
CREATE FUNCTION mbr (m multisurface) RETURNS mbr
CREATE FUNCTION mbr (m multipolygon) RETURNS mbr
CREATE FUNCTION mbr (g Geometry) RETURNS mbr
CREATE FUNCTION mbr (g GeomCollection) RETURNS mbr
CREATE FUNCTION mbroverlaps(a mbr, b mbr) RETURNS BOOLEAN