Using Geospatial Objects

MapD supports a subset of object types and functions for storing and writing queries for geospatial definitions.

See also:
Importing Geospatial Data

Geospatial Primitives

Geospatial Primitive Types
Type Description Example
POINT A point described by two coordinates. POINT(0 0)
LINESTRING A sequence of 2 or more points and the lines that connect them. LINESTRING(0 0,1 1,1 2)
POLYGON A set of one or more rings (closed line strings), with the first representing the shape (external ring) and the rest representing holes in that shape (internal rings). POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1))
MULTIPOLYGON A set of one or more polygons. MULTIPOLYGON(((0 0,4 0,4 4,0 4,0 0),(1 1,2 1,2 2,1 2,1 1)), ((-1 -1,-1 -2,-2 -2,-2 -1,-1 -1)))

For more information on WKT primitives, see Wikipedia: Well-known Text: Geometric objects.

You create geospatial objects as geometries (planar spatial data types), which are supported by the planar geometry engine at runtime. When you call ST_DISTANCE on two geometry objects, the engine returns the shortest straight-line planar distance between those points. For example, the following query returns the shortest distance between the point(s) in p1 and the polygon(s) in poly1: 

SELECT ST_DISTANCE(p1, poly1) from geo1;

Calling ST_CONTAINS returns whether the second geometry is contained in the first.

SELECT count(*) from geo1 where ST_CONTAINS(poly1, ‘POINT(0 0)’);

Geospatial Literals

Geospatial functions that expect geospatial object arguments accept geospatial columns, geospatial objects returned by other functions, or string literals containing WKT representations of geospatial objects. Supplying a WKT string is equivalent to calling a geometry constructor. For example, these two queries are identical:

SELECT count(*) from geo1 where ST_Distance(p1, ‘POINT(1 2)’) < 1.0;
SELECT count(*) from geo1 where ST_Distance(p1, ST_GeomFromText(‘POINT(1 2)’));

Geospatial literals can be created with a specific SRID. For example:

SELECT ST_Contains(mpoly2, ST_GeomFromText(‘POINT(-71.064544 42.28787)’, 4326)) from geo2;

Limited Support for Geography

MapD has limited support for geography objects and geodesic distance calculations. Currently, MapD supports spheroidal distance calculation between two geography points. The ST_DISTANCE function accepts two point geographies and returns distance in meters. You can specify a point geography as a cast from a point geometry using either a special function or a geography literal. For example:

ST_Distance(CastToGeography(p2),
              ST_GeogFromText('POINT(2.5559 49.0083)', 4326))

Geospatial Functions

MapD supports the functions listed below.

Geometry Constructors
Function Description
ST_GeomFromText(WKT) Return a specified geometry value from Well-known Text representation.
ST_GeogFromText(WKT) Return a specified geography value from Well-known Text representation.
Geometry Editors
Function Description
ST_Transform Returns a geometry with its coordinates transformed to a different spatial reference. Currently, WGS84 to Web Mercator transform is supported. For example: 
  ST_Distance(
    ST_Transform(ST_GeomFromText('POINT(-71.064544 42.28787)', 4326),
                 900913),
    ST_GeomFromText('POINT(-13189665.9329505 3960189.38265416)', 900913)
  )
ST_SetSRIDSet the SRID to a specific integer value. For example: 
ST_Transform(ST_SetSRID(ST_GeomFromText('POINT(-71.064544 42.28787)'), 4326),900913)
Geometry Accessors
Function Description
ST_X Returns the X value from a POINT column.
ST_Y Returns the Y value from a POINT column.
ST_XMin Returns X minima of a geometry.
ST_XMax Returns X maxima of a geometry.
ST_YMin Returns Y minima of a geometry.
ST_YMax Returns Y maxima of a geometry.
ST_StartPoint Returns the first point of a LINESTRING as a POINT.
ST_EndPoint Returns the last point of a LINESTRING as a POINT.
ST_PointN Return the Nth point of a LINESTRING as a POINT.
ST_NPoints Returns the number of points in a geometry.
ST_NRingsReturns the number of rings in a POLYGON or a MULTIPOLYGON.
ST_SRID Returns the spatial reference identifier for the underlying object.
Spatial Relationships and Measurements
Function Description
ST_Distance

Returns shortest shortest planar distance between geometries. For example:

ST_Distance(poly1, ST_GeomFromText('POINT(0 0)'))

Returns shortest geodesic distance between two points, in meters, if given two point geographies. Point geographies can be specified through casts from point geometries or as literals. For example: 

ST_Distance(CastToGeography(p2),
  ST_GeogFromText('POINT(2.5559 49.0083)', 4326))
ST_Contains

Returns true if the first geometry object contains the second object. For example:

ST_Contains(poly1, ST_GeomFromText('POINT(0 0)'))