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:
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
- pLINESTRING
- lsMULTILINESTRING
- mlsPOLYGON
- pgMULTIPOLYGON
- mpGEOMETRY
- gGEOMETRYCOLLECTION
- gcINTEGER
- 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,
|
||||||||||||
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:
|
||||||||||||
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
// 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