Geospatial Data

Geospatial data is information that identifies geographic features, locations, and boundaries on Earth. By using geospatial data, you can store and analyze geographical information. Points, LineStrings, and areas are defined through coordinates and saved in Exasol as GEOMETRY objects. GEOMETRY columns can optionally have an SRID (Spatial reference system identifier, see EXA_SPATIAL_REF_SYS for more information) a reference coordinate system, which is a kind of constraint.

The GEOMETRY columns are filled with strings (for example, 'POINT(2 5)'). If you read this data externally by the drivers, this data is automatically converted to strings. The same applies to commands IMPORT and EXPORT.

For geospatial objects, a multitude of functions are provided to execute calculations and operations.

Geospatial Objects

The following table provides you with a list of different types of geospatial objects in Exasol:

 

Geospatial Objects

 

Constructor Description
GEOMETRY General abstraction of any geospatial objects
POINT (X Y) Point within the two-dimensional area
LINESTRING (X Y, X Y,...) LineString which connects several two-dimensional Points
LINEARRING (X Y, X Y,...) A linear ring is a LineString whose start and end points are identical
POLYGON ((X Y,...)[,(X Y,...),...]) Area which is defined by a linear ring and an optional list of holes within this area.
GEOMETRYCOLLECTION (geometry,...) A collection of any geospatial objects
MULTIPOINT (X Y,...) Set of Points
MULTILINESTRING ((X Y,...),...) Set of LineStrings
MULTIPOLYGON ((X Y,...),...) Set of Polygons

Instead of numerical arguments, you can also use the keyword EMPTY for creating an empty set of an object (for example, POLYGON EMPTY)

Example

 

POINT(2 5)                -- PT
LINESTRING(11 1, 15 2, 15 10)        -- L
POLYGON((5 1, 5 5, 9 7, 10 1, 5 1),
(6 2, 6 3, 7 3, 7 2, 6 2))        -- PG


MULTIPOINT(0.1 1.4, 2.2 3, 1 6.4)
MULTILINESTRING((0 1, 2 3, 1 6), (4 4, 5 5))
MULTIPOLYGON(((0 0, 0 2, 2 2, 3 1, 0 0)),
((4 6, 8 9, 12 5, 4 6), (8 6, 9 6, 9 7, 8 7, 8 6)))
GEOMETRYCOLLECTION(POINT(2 5), LINESTRING(1 1, 15 2, 15 10))

Where geometry data types include

  • POINT - p
  • LINESTRING - ls
  • MULTILINESTRING - mls
  • POLYGON - pg
  • MULTIPOLYGON - mp
  • GEOMETRY - g
  • GEOMETRYCOLLECTION - gc
  • INTEGER - n

Geospatial Functions

Exasol provides many functions to execute operations and mathematical calculations on geospatial objects. The arguments of the functions mainly consist of geospatial objects as described previous section.

Function Description
Point Functions
ST_X(P) X coordinate of a Point
ST_Y(P) Y coordinate of a Point
(Multi-)LineString Functions
ST_ENDPOINT(ls) End point of a LineString
ST_ISCLOSED(mls) Defines whether all contained LineStrings are rings, that is, whether their start and end points are identical.
ST_ISRING(ls) Defines whether a LineString is a closed ring, that is, start and end points are identical.
ST_LENGTH(mls) Length of a LineString or the sum of lengths of all objects of a MultiLineString
ST_NUMPOINTS(ls) Number of Points within the LineString
ST_POINTN(ls,n) The nth point of a LineString, starting with 1. Returns NULL if ST_NUMPOINTS (ls)<n
ST_STARTPOINT(ls) Start point of a LineString
(Multi-)Polygon Functions  
ST_AREA(mp) Area of a polygon or sum of areas of all objects of a MultiPolygon
ST_EXTERIORRING(pg) Outer ring of the object
ST_INTERIORRINGN(pg,n) The nth hole of a polygon, starting with 1. Returns NULL if ST_NUMINTERIORRINGS(pg)<n
ST_NUMINTERIORRINGS(pg) Number of holes within a Polygon
GeometryCollection Functions
ST_GEOMETRYN(gc,n) The nth object of a GeometryCollection, starting with 1. Returns NULL if ST_NUMGEOMETRIES(gc)<n.
ST_NUMGEOMETRIES(gc) Number of objects within a collection of geometry objects
General Functions
ST_BOUNDARY(g) Geometric boundary of a geospatial object (for example, the end points of a LineString or the outer LinearRing of a Polygon).
ST_BUFFER(g,n) Returns a geospatial object, whose points have maximal distance n to the first argument. This is similar to an extension of the borders of an object. Around the edges, some sort of divided circle is created which is approximated by a number of points.
ST_CENTROID(g) Geometric center of mass of an object
ST_CONTAINS(g,g) Defines whether the first object fully contains the second one
ST_CONVEXHULL(g) Convex hull of a geospatial object
ST_CROSSES(g,g) Defines whether the two objects cross each other. This is the case if,
  • The intersection is not empty and does not equal to one of the objects
  • The dimension of the intersection is smaller than the maximal dimension of both arguments
ST_DIFFERENCE(g,g) Difference set of two geospatial objects
ST_DIMENSION(g) Dimension of a geospatial object (for example, 0 for Points, 1 for LineStrings and 2 for Polygons).
ST_DISJOINT(g,g) Defines whether two geospatial objects are disjoint, i.e. their intersection is empty
ST_DISTANCE(g,g) Minimal distance between two geospatial objects
ST_ENVELOPE(g) Smallest rectangle which contains the geospatial object
ST_EQUALS(g,g) Defines whether two geospatial objects describe the same geometric object. For two objects g1 and g2 this is the case if ST_WITHIN(g1,g2) and ST_WITHIN(g2,g1)
ST_FORCE2D(g) Makes a 2D object out of a 3D object, which means that it ignores the third coordinate. This function can be necessary since Exasol does not support 3D objects
ST_GEOMETRYTYPE(g) Type of the geospatial object as string (for example, 'POINT' or 'MULTIPOLYGON')
ST_INTERSECTION(g,g) Intersection of two geospatial objects
ST_INTERSECTION(g) Intersection of two geospatial objects (aggregated)
ST_INTERSECTS(g,g) Defines whether an intersection of two geospatial objects exists
ST_ISEMPTY(g) Defines whether the geospatial object is an empty geometry. See Examples for an example of usage.
ST_ISSIMPLE(g)

Defines whether a geospatial object is simple:

POINT Always simple
MULTIPOINT Simple if no points are identical
LINESTRING Simple if does not pass through the same Point twice (except start and end points)
MULTILINESTRING Simple if all LineStrings are simple and the intersections are only in the boundaries of both objects
POLYGON Always simple
MULTIPOLYGIN Always simple
ST_OVERLAPS(g,g) Defines whether two geospatial objects overlap. This is the case if the objects are not identical, their intersection is not empty and has the same dimension as the two objects.
ST_SETSRID(g,srid) Sets the SRID for a geometry object (the coordinate system, see EXA_SPATIAL_REF_SYS for more information)
ST_SYMDIFFERENCE(g,g) Symmetric difference set of two geospatial objects
ST_TOUCHES(g,g) Defines whether two geospatial objects touch each other. This is the case if the intersection is not empty and is only located on the boundaries of the objects (see ST_BOUNDARY for more information)
ST_TRANSFORM(g,srid) Converts a geospatial object into the given reference coordinate system (see EXA_SPATIAL_REF_SYS for more information)
ST_UNION(g,g) Union set of two geospatial objects
ST_UNION(g) Union set of two geospatial objects (aggregated)
ST_WITHIN(g,g) Defines whether the first object is fully contained by the second one (opposite of ST_CONTAINS)

When converting between GEOMETRY types and VARCHAR/CHAR data types, the maximum significant digits used when casting is defined with the ST_MAX_DECIMAL_DIGITS parameter and can be changed using the ALTER SYSTEM or ALTER SESSION statements.

Examples
SELECT ST_Y('POINT (1 2)');
SELECT ST_ENDPOINT('LINESTRING (0 0, 0 1, 1 1)');
SELECT ST_ISRING('LINESTRING (0 0, 0 1, 1 1, 0 0)');
SELECT ST_LENGTH('LINESTRING (0 0, 0 1, 1 1)');
SELECT ST_BOUNDARY('LINESTRING (0 0, 1 1, 2 2)');
SELECT ST_AREA('POLYGON ((0 0, 0 4, 4 4, 4 0, 0 0),
             (1 1, 1 2, 2 2, 2 1, 1 1))');
SELECT ST_DISTANCE('POLYGON ((0 0, 0 4, 4 4, 4 0, 0 0))',
            'POINT(12 10)');
SELECT ST_ISEMPTY('GEOMETRYCOLLECTION EMPTY');
// This is an example of how ST_ISEMPTY can be used to list empty objects:  
CREATE TABLE t1 (id INTEGER, geo GEOMETRY(4326));
INSERT INTO t1 VALUES (1, 'POINT (13.36963 52.52493)'),(2, 'POINT EMPTY'), (3, 'POINT (-0.1233 51.5309)'); 

SELECT id, ST_ISEMPTY(geo) Empty_Geometry from t1;
// Results: 
ID EMPTY_GEOMETRY 
-- -------------- 
1  false          
2  true           
3  false