Geospatial Capabilities
OmniSci supports a subset of object types and functions for storing and writing queries for geospatial definitions.
Geospatial Data 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.
OmniSci supports SRID 4326 (WGS 84) and 900913 (Google Web Mercator). When using geospatial fields, you set the SRID to determine which reference system to use. OmniSci does not assign a default SRID.
create table simple_geo ( name TEXT ENCODING DICT(32), point GEOMETRY(POINT,4326) );
If you do not set the SRID of the geo field in the table, you can
set it in a SQL query using ST_SETSRID(column_name,
SRID)
.
For example, ST_SETSRID(a.pt,4326)
.
Note | When representing longitude and latitude, the first coordinate is assumed to be longitude in OmniSci geospatial primitives. |
See also Importing Geospatial Data.
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;
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)')) < 1.0;
You can create geospatial literals with a specific SRID. For example:
SELECT ST_CONTAINS( mpoly2, ST_GeomFromText('POINT(-71.064544 42.28787)', 4326) ) FROM geo2;
Limited Support for Geography
OmniSci has limited support for geography objects and geodesic distance calculations. Currently, OmniSci supports spheroidal distance calculation between:
- Two points using either SRID 4326 or 900913.
- A point and a polygon/multipolygon using SRID 900913.
Note | Using SRID 900913 results in variance compared to SRID 4326 as polygons approach the North and South Poles. |
The following query returns the points and polygons within 1,000 meters of each other:
SELECT a.poly_name, b.pt_name FROM poly a, pt b WHERE ST_Distance (ST_Transform(b.omnisci_geo, 900913), (ST_Transform(b.location, 900913))<1000;
See the tables in Geospatial Functions below for examples.
Geospatial Functions
OmniSci supports the functions listed below.
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. |
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 ) |