Geospatial Capabilities
OmniSci supports a subset of object types and functions for storing and writing queries for geospatial definitions.
Type  Size  Example 
LINESTRING  Variable  A sequence of 2 or more points and the lines that connect them. For example: LINESTRING(0 0,1 1,1 2) 
MULTIPOLYGON  Variable  A set of one or more polygons. For example: 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))) 
POINT  Variable  A point described by two coordinates. When the coordinates are longitude and latitude, OmniSci stores longitude first, and then latitude. For example: POINT(0 0) 
POLYGON  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 example: POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1)) 
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),
location 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)
.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 straightline 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 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;
OmniSci provides support for geography objects and geodesic distance calculations, with some limitations.
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 builtin 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 nongeo format that could be included for display in Immerse (for example, 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.
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;
OmniSci supports the functions listed.
Function  Description 
ST_Centroid  Computes the geometric center of a geometry as a POINT. 
ST_GeomFromText(WKT)  Return a specified geometry value from Wellknown Text representation. 
ST_GeomFromText(WKT, SRID)  Return a specified geometry value from Wellknown Text representation and an SRID. 
ST_GeogFromText(WKT)  Return a specified geography value from Wellknown Text representation. 
ST_GeogFromText(WKT, SRID)  Return a specified geography value from Wellknown Text representation and an SRID. 
ST_Point(double lon, double lat)  Return a point constructed on the fly from the provided coordinate values. Constant coordinates result in construction of a POINT literal. Example: ST_Contains(poly4326, ST_SetSRID(ST_Point(lon, lat), 4326)) 
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_TRANSFORM is not currently supported in projections. It can be used only to transform geo inputs to other functions, such as ST_DISTANCE. 
ST_SETSRID  ST_TRANSFORM( ST_SETSRID(ST_GeomFromText('POINT(71.064544 42.28787)'), 4326), 900913 ) 
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. 
Function  Description 
ST_DISTANCE  Returns 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)
) 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 can also calculate the distance between a POLYGON and a POINT. If both fields use SRID 4326, then the calculated distance is in 4326 units (degrees). If both fields use SRID 4326, and both are transformed into 900913, then the results are in 900913 units (meters).
The following SQL code returns the names of polygons where the distance between the point and polygon is less than 1,000 meters. SELECT a.poly_name FROM poly a, point b WHERE ST_DISTANCE(
ST_TRANSFORM(b.location,900913),
ST_TRANSFORM(a.omnisci_geo,900913)
) < 1000; 
ST_MAXDISTANCE  Returns longest planar distance between geometries. In effect, this is the diameter of a circle that encloses both geometries.For example: Currently supported variants: 
ST_CONTAINS  Returns true if the first geometry object contains the second object. For example: You can also use ST_CONTAINS to:

ST_INTERSECTS  Returns true if two geometries intersect spatially, false if they do not share space. For example: SELECT ST_INTERSECTS(
'POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))',
'POINT(1 1)'
) FROM tbl; 
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_AREA does not support calculation of geographic areas, but rather uses planar coordinates. Geographies must first be projected in order to use ST_AREA . You can do this ahead of time before import or at runtime, ideally using an equal area projection (for example, a national equalarea Lambert projection). The area is calculated in the projection's units. For example, you might use Web Mercator runtime projection to get the area of a polygon in square meters:ST_AREA(
ST_TRANSFORM(
ST_GeomFromText(
'POLYGON((76.6168198439371 39.9703199555959,
80.5189990254673 40.6493554919257,
82.5189990254673 42.6493554919257,
76.6168198439371 39.9703199555959)
)', 4326
),
900913)
) <code></code> Web Mercator is not an equal area projection, however. Unless compensated by a scaling factor, Web Mercator areas can vary considerably by latitude. 
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 geographies. For example:SELECT ST_LENGTH(
ST_GeogFromText('LINESTRING(
76.6168198439371 39.9703199555959,
80.5189990254673 40.6493554919257,
82.5189990254673 42.6493554919257)',
4326)
) FROM tbl; 
ST_WITHIN  Returns true if geometry A is completely within geometry B. For example the following SELECT statement returns true:SELECT ST_WITHIN(
'POLYGON ((1 1, 1 2, 2 2, 2 1))',
'POLYGON ((0 0, 0 3, 3 3, 3 0))'
) FROM tbl; 
ST_DWITHIN  Returns true if the geometries are within the specified distance of each one another. Distance is specified in units defined by the spatial reference system of the geometries. For example:
SELECT ST_DWITHIN(
'POINT(1 1)',
'LINESTRING (1 2,10 10,3 3)', 2.0
) FROM tbl;
ST_DWITHIN supports geodesic distances between geographies, currently limited to geographic points. For example, you can check whether Los Angeles and Paris, specified as WGS84 geographic point literals, are within 10,000km of one another.SELECT ST_DWITHIN( ST_GeogFromText(
'POINT(118.4079 33.9434)', 4326),
ST_GeogFromText('POINT(2.5559 49.0083)',
4326 ),
10000000.0) FROM tbl; 
ST_DFULLYWITHIN  Returns true if the geometries are fully within the specified distance of one another. Distance is specified in units defined by the spatial reference system of the geometries. For example:
SELECT ST_DFULLYWITHIN(
'POINT(1 1)',
'LINESTRING (1 2,10 10,3 3)',
10.0) FROM tbl;
This function supports:ST_DFULLYWITHIN(POINT, LINESTRING, distance)
ST_DFULLYWITHIN(LINESTRING, POINT, distance) 
ST_DISJOINT  Returns true if the geometries are spatially disjoint (that is, the geometries do not overlap or touch. For example: SELECT ST_DISJOINT(
'POINT(1 1)',
'LINESTRING (0 0,3 3)'
) FROM tbl; 
 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 in distributed mode.GROUP BY
is not supported forPOINT
,LINESTRING
,POLYGON
, orMULTIPOLYGON
. You can use
\d table_name
to determine if the SRID is set for the geo field:omnisql> \d starting_pointCREATE TABLE starting_point (name TEXT ENCODING DICT(32),myPoint GEOMETRY(POINT, 4326) ENCODING COMPRESSED(32))If no SRID is returned, you can set the SRID usingST_SETSRID(column_name, SRID)
. For example,ST_SETSRID(myPoint, 4326)
.
Last modified 1yr ago