Geospatial Capabilities

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

Geospatial Data Types

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

OmniSci 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), 
                          point GEOMETRY(POINT,4326)
                         );

If you do not set the SRID of your geo field in the table, you can set it in your SQL code.

When using GEO fields, you set the SRID to define the reference system to use. OmniSci supports SRID 4326 (WGS 84) and 900913 (Google Web Mercator). Unless a point is assigned an SRID, it remains unassigned.

Note	When representing longitude and latitude, the first coordinate is assumed to be longitude in OmniSci geospatial primitives.

See also:
Importing Geospatial Data

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)'));

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 on 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 ST_DISTANCE function accepts two point geographies and returns distance in meters. Points are stored as geometry, so they must be cast as geography before being passed to ST_DISTANCE.

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;

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.

ST_DISTANCE(
  ST_TRANSFORM(
    ST_GeomFromText(
                     'POINT(-71.064544 42.28787)', 4326), 900913
                    ),
    ST_GeomFromText(
                     'POINT(-13189665.9329505 3960189.38265416)', 900913
                    )
  )
)

ST_TRANSFORM(
  ST_SETSRID(
    ST_GeomFromText('POINT(-71.064544 42.28787)'), 4326
  ), 900913
)