OmniSci supports a subset of object types and functions for storing and writing queries for geospatial definitions.
||Variable||A sequence of 2 or more points and the lines that connect them.
||Variable||A set of one or more polygons.
||Variable||A point described by two coordinates. When the coordinates are longitude and latitude, OmniSci stores longitude first, and then latitude.
||Variable||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).
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
|Note||When representing longitude and latitude, the first coordinate is assumed to be longitude in OmniSci geospatial primitives.|
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
p1 and the polygon(s) in
SELECT ST_DISTANCE(p1, poly1) from geo1;
For information about importing data, see Importing Geospatial Data.
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;
Support for Geography
OmniSci provides support for geography objects and geodesic distance calculations, with some limitations.
Exporting Coordinates from Immerse
OmniSciDB supports import from any coordinate system supported by the Geospatial Data Abstraction Library (GDAL). On import, OmniSciDB will convert to and store in WGS84 encoding, and rendering is accurate in Immerse.
However, no built-in way to reference the original coordinates currently exists in Immerse, and coordinates exported from Immerse will be WGS84 coordinates. You can work around this limitation by adding to the dataset a column or columns in non-geo format that could be included for display in Immerse (for exampole, in a popup) or on export.
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.
OmniSci supports the functions listed below.
||Return a specified geometry value from Well-known Text representation.|
||Return a specified geometry value from Well-known Text representation and an SRID.|
||Return a specified geography value from Well-known Text representation.|
||Return a specified geography value from Well-known Text representation and an SRID.|
Return a point constructed on the fly from the provided coordinate values. Constant coordinates result in construction of a POINT literal.
ST_Contains(poly4326, ST_SetSRID(ST_Point(lon, lat), 4326))
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) )
Set the SRID
to a specific integer value. For example:
ST_TRANSFORM( ST_SETSRID(ST_GeomFromText('POINT(-71.064544 42.28787)'), 4326), 900913 )