Using Geospatial Objects

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

When using GEO fields, you must set the SRID to define the reference system to use. Starting with release 4.1, MapD supports both 4326 and 900913. Unless a point is assigned an SRID, it remains unassigned, and you need to set the SRID in your SQL code.

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.

Starting with release 4.1, MapD supports SRIDs 4326 and 900913. You can specify the SRID of your geometry field when creating the table:

create table simple_geo (name text encoding dict(32), pt geometry(point,4326));

If you did not set the SRID of your geo field in the table, you need to set it in your SQL code. The following query uses ST _DISTANCE, sets the SRID to 4326, and casts the geospatial point field as a geography to return the distance in meters:

select a.name,
st_distance{(cast(st_setsrid(a.pt,4326) as geography),cast(st_setsrid(b.pt.4326) as geography)} as dist_meters 
from starting_point a, destination_points b;

If the SRID for both fields and tables in ST _DISTANCE is set, you do not need to use ST_ SETSRID, thereby simplifying the SQL code for returning distance:

select a.name,
st_distance(cast(a.pt as geography),cast(b.pt as geography)) as dist_meters 
from starting_point a, destination_points b;

You create geospatial objects as geometries (planar spatial data types), which are supported by the planar geometry engine at run time. When you call ST_DISTANCE on two geometry objects, the engine returns the shortest straight-line planar distance, in degrees, 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. To get geographic distance, cast the point as a geography from the default geometry. You can use 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_SetSRID`	Set 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_NRings`	Returns 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 the shortest planar distance between geometries, in degrees. For example: ST_Distance(poly1, ST_GeomFromText('POINT(0 0)')) select a.name, st_distance(st_setsrid(a.pt,4326),st_setsrid(b.pt,4326)) as distance from starting_point a, destination_points b; When cast as a geography, returns the shortest geodesic distance between two points, in meters. Point geographies can be specified as literals or as casts from point geometries. For example: ST_Distance(CastToGeography(p2), ST_GeogFromText('POINT(2.5559 49.0083)', 4326)) select a.name, st_distance(cast(a.pt as geography),cast(b.pt as geography)) as dist_meters from starting_point a, destination_points b;
`ST_Contains`	Returns true if the first geometry object contains the second object. For example: ST_Contains(poly1, ST_GeomFromText('POINT(0 0)'))
`ST_Area`	Returns the area of planar areas covered by POLYGON and MULTIPOLYGON geometries. For example: SELECT ST_Area('POLYGON((1 0, 0 1, -1 0, 0 -1, 1 0),(0.1 0, 0 0.1, -0.1 0, 0 -0.1, 0.1 0))') from tbl;
`ST_Perimeter`	Returns the cartesian perimeter of POLYGON and MULTIPOLYGON geometries. For example: SELECT ST_Perimeter('POLYGON((1 0, 0 1, -1 0, 0 -1, 1 0), (0.1 0, 0 0.1, -0.1 0, 0 -0.1, 0.1 0))') from tbl; It will also return the geodesic perimeter of POLYGON and MULTIPOLYGON geometries. For example: SELECT ST_Perimeter(ST_GeogFromText('POLYGON( (-76.6168198439371 39.9703199555959, -80.5189990254673 40.6493554919257, -82.5189990254673 42.6493554919257, -76.6168198439371 39.9703199555959))', 4326)) from tbl;
`ST_Length`	Returns the cartesian length of LINESTRING geometries. For example: SELECT ST_Length('LINESTRING(1 0, 0 1, -1 0, 0 -1, 1 0)') from tbl; It also returns the geodesic length of LINESTRING geometries. For example: SELECT ST_Length(ST_GeogFromText('LINESTRING( -76.6168198439371 39.9703199555959, -80.5189990254673 40.6493554919257, -82.5189990254673 42.6493554919257)', 4326)) from tbl;

Additional Notes

You can use SQL code similar to the examples in this topic as global filters in Immerse.
CREATE TABLE AS SELECT is not currently supported for geo data types.

You can use \d table_name to determine if the SRID is set for the geo field: