H2GIS plugin

H2Gis plugin¶

Hawkore's H2Gis plugin for Apache Ignite registers H2Gis v1.5.0 spatial extension and other interesting functions into Apache Ignite's distributed SQL database.

Add repository and the Maven dependency below to your pom.xml file to make sure that the module is included into your application:

Maven dependency

<dependency>
    <groupId>com.hawkore.libs.ignite</groupId>
    <artifactId>hk-ignite-plugin-h2gis</artifactId>
    <version>2.8.1-hk</version>
</dependency>

Hawkore's Repository

<repositories>
    <repository>
        <id>Hawkore Repository</id>
        <url>https://repository.hawkore.com/maven2/</url>
    </repository>
</repositories>

Functions for H2¶

String functions for H2¶

Some interesting functions to work with Strings using SQL.

SCHEMA	NAME	DESCRIPTION
PUBLIC	SU_ABBREVIATEMIDDLE	Abbreviates a String to the length passed, replacing the middle characters with the supplied replacement String.
PUBLIC	SU_ABBREVIATE	Abbreviates a String using ellipses. This will turn 'Now is the time for all good men' into 'Now is the time for...'
PUBLIC	SU_COMMONPREFIX	Compares all Strings in an array and returns the initial sequence of characters that is common to all of them.
PUBLIC	SU_DIFFERENCE	Compares two Strings, and returns the portion where they differ. More precisely, return the remainder of the second String, starting from where it's different from the first. This means that the difference between 'abc' and 'ab' is the empty String and not 'c'.
PUBLIC	SU_FUZZYDISTANCE	Finds the Fuzzy Distance which indicates the similarity score between two Strings. Pass localeLanguageTag as third param to convert strings to lowercase, if null default Locale.ENGLISH will be used
PUBLIC	SU_JAROWINKLERDISTANCE	Finds the Jaro-Winkler Distance which indicates the similarity score between two Strings.
PUBLIC	SU_LEVENSHTEINDISTANCE	Finds the Levenshtein distance between two Strings if it's less than or equal to a given threshold.

Geo-spatial functions for H2¶

Some interesting spatial functions to work with geometries using SQL.

SCHEMA	NAME	DESCRIPTION
PUBLIC	ST_DISTANCE_SPHERE	Calculates the geographical distance (Great-circle distance) in meters between two geographic points
PUBLIC	ST_GEOCIRCLE	Generates a circle, also known as a point-radius since that is what it is comprised. Generated from POINT ( longitude latitude ) with radius in meters.

SU_ABBREVIATEMIDDLE¶

Abbreviates a String to the length passed, replacing the middle characters with the supplied replacement String.

PARAMETER	TYPE	DESCRIPTION
P1	VARCHAR	String to abbreviate, must not be null
P2	VARCHAR	Replacement String, must not be null
P3	INTEGER	Max length, must not be null

Example:

SELECT ID,COUNTRYCODE, USER, BODY, PUBLIC.SU_ABBREVIATEMIDDLE(body ,' [...] ', 20) as BODY_ABBREVIATED 
FROM "tweets".tweet
limit 2;

Result:

+------+-------------+------------+------------------------------------+---------------------+
|   ID | COUNTRYCODE |       USER |                               BODY |     BODY_ABBREVIATED|
+======+=============+============+====================================+=====================+
|98302 |          ES | user_98302 | big data gives organizations 98302 | big dat [...]  98302|
+------+-------------+------------+------------------------------------+---------------------+
|90367 |          ES | user_90367 | big data gives organizations 90367 | big dat [...]  90367|
+------+-------------+------------+------------------------------------+---------------------+

SU_ABBREVIATE¶

Abbreviates a String using ellipses. This will turn 'Now is the time for all good men' into 'Now is the time for...'.

PARAMETER	TYPE	DESCRIPTION
P1	VARCHAR	String to abbreviate, must not be null
P2	INTEGER	Max length, must not be null
P3	INTEGER	Offset, must not be null

Example 1: Abbreviates a string with offset = 0

SELECT ID,COUNTRYCODE, USER, BODY, PUBLIC.SU_ABBREVIATE(body, 20, 0) as BODY_ABBREVIATED 
FROM "tweets".tweet
limit 2;

Result:

+-------+-------------+-------------+-------------------------------------+---------------------+
|    ID | COUNTRYCODE |        USER |                                BODY |     BODY_ABBREVIATED|
+=======+=============+=============+=====================================+=====================+
|223582 |          FR | user_223582 | big data gives organizations 223582 | big data gives or...|
+-------+-------------+-------------+-------------------------------------+---------------------+
|215647 |          FR | user_215647 | big data gives organizations 215647 | big data gives or...|
+-------+-------------+-------------+-------------------------------------+---------------------+

Example 2: Abbreviates a string with offset = 10

SELECT ID,COUNTRYCODE, USER, BODY, PUBLIC.SU_ABBREVIATE(body, 20, 10) as BODY_ABBREVIATED 
FROM "tweets".tweet
limit 2;

Result:

+-------+-------------+-------------+-------------------------------------+---------------------+
|    ID | COUNTRYCODE |        USER |                                BODY |     BODY_ABBREVIATED|
+=======+=============+=============+=====================================+=====================+
|223582 |          FR | user_223582 | big data gives organizations 223582 | ...ives organizat...|
+-------+-------------+-------------+-------------------------------------+---------------------+
|215647 |          FR | user_215647 | big data gives organizations 215647 | ...ives organizat...|
+-------+-------------+-------------+-------------------------------------+---------------------+

SU_COMMONPREFIX¶

Compares all Strings in an array and returns the initial sequence of characters that is common to all of them.

PARAMETER	TYPE	DESCRIPTION
P1	ARRAY	Array of Strings, must not be null

Example:

SELECT PUBLIC.SU_COMMONPREFIX('i am a machine', 'i am a robot') as COMMON_PREFIX
FROM DUAL;

Result:

+-------------+
|COMMON_PREFIX|
+=============+
|      i am a |
+-------------+

SU_DIFFERENCE¶

Compares two Strings, and returns the portion where they differ. More precisely, return the remainder of the second String, starting from where it's different from the first. This means that the difference between 'abc' and 'ab' is the empty String and not 'c'.

PARAMETER	TYPE	DESCRIPTION
P1	VARCHAR	First String, must not be null
P2	VARCHAR	Second String, must not be null

Example:

SELECT PUBLIC.SU_DIFFERENCE('i am a machine', 'i am a robot') as DIFFERENCE
FROM DUAL;

Result:

+------------+
| DIFFERENCE |
+============+
|      robot |
+------------+

SU_FUZZYDISTANCE¶

Finds the Fuzzy Distance which indicates the similarity score between two Strings. Pass locale as third param to normalize both Strings to lower case, if null default Locale.ENGLISH ('en') will be used.

This string matching algorithm is similar to the algorithms of editors such as Sublime Text, TextMate, Atom and others. One point is given for every matched character. Subsequent matches yield two bonus points. A higher score indicates a higher similarity.

PARAMETER	TYPE	DESCRIPTION
P1	VARCHAR	A full term that should be matched against, must not be null
P2	VARCHAR	The query that will be matched against a term, must not be null
P3	VARCHAR	A locale is necessary to normalize both Strings to lower case. This string matching logic is case insensitive.

Example:

SELECT ID,COUNTRYCODE, USER, BODY, PUBLIC.SU_FUZZYDISTANCE(body, 'data', null) as FUZZYDISTANCE 
FROM "tweets".tweet
limit 2;

Result:

+------+-------------+------------+------------------------------------+--------------+
|   ID | COUNTRYCODE |       USER |                               BODY | FUZZYDISTANCE|
+======+=============+============+====================================+==============+
|98302 |          ES | user_98302 | big data gives organizations 98302 |            10|
+------+-------------+------------+------------------------------------+--------------+
|90367 |          ES | user_90367 | big data gives organizations 90367 |            10|
+------+-------------+------------+------------------------------------+--------------+

SU_JAROWINKLERDISTANCE¶

Finds the Jaro-Winkler Distance which indicates the similarity score between two Strings.

Returns the score, normalized between 0 = no similarity and 1 = exact match.

PARAMETER	TYPE	DESCRIPTION
P1	VARCHAR	First String, must not be null
P2	VARCHAR	Second String, must not be null

SELECT ID,COUNTRYCODE, USER, BODY, PUBLIC.SU_JAROWINKLERDISTANCE(body, 'data') as JAROWINKLERDISTANCE 
FROM "tweets".tweet
limit 2;

Result:

+-------+-------------+-------------+-------------------------------------+--------------------+
|    ID | COUNTRYCODE |        USER |                                BODY | JAROWINKLERDISTANCE|
+=======+=============+=============+=====================================+====================+
|223582 |          FR | user_223582 | big data gives organizations 223582 |                 0.7|
+-------+-------------+-------------+-------------------------------------+--------------------+
|215647 |          FR | user_215647 | big data gives organizations 215647 |                 0.7|
+-------+-------------+-------------+-------------------------------------+--------------------+

SU_LEVENSHTEINDISTANCE¶

Finds the Levenshtein distance between two Strings.

Returns the number of changes needed to change one String into another, where each change is a single character modification (deletion, insertion or substitution).

If threshold is provided, it will return -1 if distance if greater than given threshold.

PARAMETER	TYPE	DESCRIPTION
P1	VARCHAR	First String, must not be null
P2	VARCHAR	Second String, must not be null
P3	INTEGER	the target threshold, must not be negative. Could be null.

Example 1: Without threshold

SELECT ID,COUNTRYCODE, USER, BODY, PUBLIC.SU_LEVENSHTEINDISTANCE(body, 'data', null) as LEVENSHTEINDISTANCE 
FROM "tweets".tweet
limit 2;

Result:

+------+-------------+------------+------------------------------------+--------------------+
|   ID | COUNTRYCODE |       USER |                               BODY | LEVENSHTEINDISTANCE|
+======+=============+============+====================================+====================+
|98302 |          ES | user_98302 | big data gives organizations 98302 |                  30|
+------+-------------+------------+------------------------------------+--------------------+
|90367 |          ES | user_90367 | big data gives organizations 90367 |                  30|
+------+-------------+------------+------------------------------------+--------------------+

Example 2: With threshold = 10

SELECT ID,COUNTRYCODE, USER, BODY, PUBLIC.SU_LEVENSHTEINDISTANCE(body, 'data', 10) as LEVENSHTEINDISTANCE 
FROM "tweets".tweet
limit 2;

Result:

+------+-------------+------------+------------------------------------+--------------------+
|   ID | COUNTRYCODE |       USER |                               BODY | LEVENSHTEINDISTANCE|
+======+=============+============+====================================+====================+
|98302 |          ES | user_98302 | big data gives organizations 98302 |                  -1|
+------+-------------+------------+------------------------------------+--------------------+
|90367 |          ES | user_90367 | big data gives organizations 90367 |                  -1|
+------+-------------+------------+------------------------------------+--------------------+

ST_DISTANCE_SPHERE¶

Calculates the geographical distance (Great-circle distance) in meters between two geographic points.

Main different with ST_DISTANCESPHERE is that it does not require that geometry parameters define the SRID.

Only works with WGS84 coordinates - latitude, longitude - SRID = EPSG:4326.

PARAMETER	TYPE	DESCRIPTION
P1	OTHER	Point A (“longitude, latitude”), must not be null
P2	OTHER	Point B (“longitude, latitude”), must not be null

Example: Search for tweets within a buffer of 6Km around POINT (-3.703790 40.416775)

SELECT ID, BODY, PLACE,
PUBLIC.ST_DISTANCE_SPHERE(place, 'POINT (-3.703790 40.416775)')/1000 as distance_km 
FROM "tweets".tweet USE INDEX (tweet_place_idx)
WHERE
place && public.ST_BUFFER('POINT (-3.703790 40.416775)', 6*0.0111)
and countryCode = 'ES' 
order by distance_km desc
limit 10;

Result:

+------+------------------------------------+------------------------------------------------+------------------+
|   ID |                               BODY |                                          PLACE |       DISTANCE_KM|
+======+====================================+================================================+==================+
|49035 | big data gives organizations 49035 |  POINT (-3.7499845156288227 40.35407079004236) | 7.995156633130086|
+------+------------------------------------+------------------------------------------------+------------------+
|71290 | big data gives organizations 71290 | POINT (-3.6576400382479055 40.474523811017136) | 7.515683589843303|
+------+------------------------------------+------------------------------------------------+------------------+
|60870 | big data gives organizations 60870 |  POINT (-3.6855143416830183 40.35108883795703) | 7.466204579746087|
+------+------------------------------------+------------------------------------------------+------------------+
|73191 | big data gives organizations 73191 |  POINT (-3.6442361407780948 40.37040139512792) | 7.212915443266619|
+------+------------------------------------+------------------------------------------------+------------------+
|63858 | big data gives organizations 63858 |   POINT (-3.643352725561062 40.46116642472653) |  7.10820463443938|
+------+------------------------------------+------------------------------------------------+------------------+
|29843 | big data gives organizations 29843 | POINT (-3.7053850457329642 40.354753500637706) | 6.897808643187005|
+------+------------------------------------+------------------------------------------------+------------------+
|96152 | big data gives organizations 96152 |  POINT (-3.6654897719289026 40.36390665055403) | 6.714217715121237|
+------+------------------------------------+------------------------------------------------+------------------+
|65159 | big data gives organizations 65159 | POINT (-3.7640315124351558 40.446409673786384) | 6.070951684524529|
+------+------------------------------------+------------------------------------------------+------------------+
|37155 | big data gives organizations 37155 |  POINT (-3.6380660941530323 40.42141155416504) | 5.587711868859234|
+------+------------------------------------+------------------------------------------------+------------------+
|58808 | big data gives organizations 58808 |   POINT (-3.729606286597199 40.37173141817499) | 5.464997075063737|
+------+------------------------------------+------------------------------------------------+------------------+

ST_GEOCIRCLE¶

A circle, also known as a point-radius since that is what it is comprised.

Generated from a center POINT ( longitude latitude ) and radius in meters.

Only works with WGS84 coordinates - latitude, longitude - SRID = EPSG:4326.

PARAMETER	TYPE	DESCRIPTION
P1	OTHER	Center Point (“longitude,latitude”), must not be null
P2	INTEGER	Radius in meters, must not be null

Example: Generate a geo-circle with 6Km of radius

select public.ST_GEOCIRCLE('POINT (-3.703790 40.416775)', 6000) as circle from dual;

+----------------------------------------------------------------------------------------------------+
|                                                                                              CIRCLE|
+====================================================================================================+
|POLYGON ((-3.6330964425376986 40.416775, -3.633 [...] 1339546132657, -3.6330964425376986 40.416775))|
+----------------------------------------------------------------------------------------------------+

geo circle

H2Gis spatial extension¶

H2Gis v1.5.0 spatial extension for H2 will be registered by Hawkore's H2Gis plugin for Apache Ignite.

You could find full H2Gis documentation and samples for below functions at h2gis.org.

SCHEMA	NAME	DESCRIPTION
PUBLIC	DBFREAD	Read a DBase III file and copy the content into a new table in the database
PUBLIC	DBFWRITE	Transfer the content of a table into a DBFCALL DBFWRITE('FILENAME', 'TABLE'[,'ENCODING'])
PUBLIC	DOUBLERANGE	Return an array of doubles within the range [start-end). The default step increment is 1 but the user can set another one.
PUBLIC	FILE_TABLE	Use the appropriate driver to open a specified file path.
PUBLIC	GEOJSONREAD	Import a GeoJSON 1.0 file.
PUBLIC	GEOJSONWRITE	Export a spatial table to a GeoJSON 1.0 file.
PUBLIC	GPXREAD	Read a GPX file and copy the content in the specified tables.The user can set a prefix name for all GPX tables and specify if the existing GPX tables must be dropped.
PUBLIC	H2GISVERSION	Returns H2GISGIS version number
PUBLIC	HEXTOVARBINARY	Convert Hexadecimal string into an array of byte.
PUBLIC	INTEGERRANGE	Return an array of integers within the range [start-end). The default step increment is 1 but the user can set another one.
PUBLIC	KMLWRITE	Export a spatial table to a KML or KMZ file.
PUBLIC	OSMREAD	Read a OSM file and copy the content in the specified tables.The user can set a prefix name for all OSM tables and specify if the existing OSM tables must be dropped.Here a sample in order to extract buildings polygons using way nodes:create index on MAP_WAY_NODE(ID_WAY,ID_NODE);drop table if exists MAP_BUILDINGS,MAP_WAY_GEOM;create table MAP_BUILDINGS(ID_WAY bigint primary key) as SELECT DISTINCT ID_WAY FROM MAP_WAY_TAG WT, MAP_TAG T WHERE WT.ID_TAG = T.ID_TAG AND T.TAG_KEY IN ('building');create table MAP_WAY_GEOM(ID_WAY BIGINT PRIMARY KEY, THE_GEOM POLYGON) AS SELECT ID_WAY, ST_MAKEPOLYGON(ST_MAKELINE(THE_GEOM)) THE_GEOM FROM (SELECT (SELECT ST_ACCUM(THE_GEOM) THE_GEOM FROM (SELECT N.ID_NODE, N.THE_GEOM,WN.ID_WAY IDWAY FROM MAP_NODE N,MAP_WAY_NODE WN WHERE N.ID_NODE = WN.ID_NODE ORDER BY WN.NODE_ORDER) WHERE IDWAY = W.ID_WAY) THE_GEOM ,W.ID_WAY FROM MAP_WAY W,MAP_BUILDINGS B WHERE W.ID_WAY = B.ID_WAY) GEOM_TABLE WHERE ST_GEOMETRYN(THE_GEOM,1) = ST_GEOMETRYN(THE_GEOM, ST_NUMGEOMETRIES(THE_GEOM)) AND ST_NUMGEOMETRIES(THE_GEOM) > 2;
PUBLIC	SHPREAD	Read a shape file and copy the content in the specified table.
PUBLIC	SHPWRITE	Transfer the content of a table into a new shape fileCALL SHPWRITE('FILENAME', 'TABLE'[,'ENCODING'])
PUBLIC	ST_3DAREA	Compute the 3D area of a polygon or a multipolygon derived from a 3D triangular decomposition.Distance units are those of the geometry spatial reference system.
PUBLIC	ST_3DLENGTH	Returns the 3D length (of a LineString) or the 3D perimeter (of a Polygon).Note : For 2D geometries, returns the 2D length.
PUBLIC	ST_3DPERIMETER	Returns the 3D length measurement of the boundary of a Polygon or a MultiPolygon. Note : For 2D geometries, returns the 2D length.
PUBLIC	ST_ACCUM	Construct an array of Geometries
PUBLIC	ST_ADDPOINT	Adds a point to a geometry. A tolerance could be set to snap the point to the geometry.
PUBLIC	ST_ADDZ	This function do a sum with the z value of (each vertex of) the geometric parameter to the corresponding value given by a field.
PUBLIC	ST_AREA	Compute geometry area.
PUBLIC	ST_ASBINARY	Convert a geometry into Well Known Binary.
PUBLIC	ST_ASGEOJSON	Return the geometry as a Geometry Javascript Object Notation (GeoJSON 1.0) element.2D and 3D Geometries are both supported.GeoJSON only supports SFS 1.1 geometry types (POINT, LINESTRING, POLYGON and COLLECTION).
PUBLIC	ST_ASGML	Store a geometry as a GML representation.It supports OGC GML standard 2.1.2
PUBLIC	ST_ASKML	Return the geometry as a Keyhole Markup Language (KML) element.Note this function supports two arguments : extrude (boolean) and altitude mode (integer).Available extrude values are true, false or none.Supported altitude mode :For KML profil : CLAMPTOGROUND = 1; RELATIVETOGROUND = 2; ABSOLUTE = 4;For GX profil : CLAMPTOSEAFLOOR = 8; RELATIVETOSEAFLOOR = 16; No altitude : NONE = 0;
PUBLIC	ST_ASTEXT	Convert a geometry into WKT, a text representation of the geometry.
PUBLIC	ST_ASWKT	Convert a geometry into WKT, a text representation of the geometry.
PUBLIC	ST_AZIMUTH	Returns the azimuth of the segment defined by the given Point geometries, or Null if the two points are coincident. Return value is in radians. Angle is computed clockwise from the north equals to 0.
PUBLIC	ST_BOUNDARY	Get geometry boundary as geometry.
PUBLIC	ST_BOUNDINGCIRCLE	Compute the minimum bounding circle of a geometry
PUBLIC	ST_BOUNDINGCIRCLECENTER	Compute the minimum bounding circle center of a geometry.This function is more precise than the conjunction of ST_CENTROID and ST_BoundingCircle
PUBLIC	ST_BUFFER	Compute a buffer around a Geometry.The optional third parameter can either specify number of segments used to approximate a quarter circle (integer case, defaults to 8) or a list of blank-separated key=value pairs (string case) to manage buffer style parameters :'quad_segs=8' endcap=round\|flat\|square' 'join=round\|mitre\|bevel' 'mitre_limit=5'
PUBLIC	ST_CENTROID	Computes the centroid of this Geometry. The centroid is equal to the centroid of the set of component Geometries of highest dimension (since the lower-dimension geometries contribute zero "weight" to the centroid) .
PUBLIC	ST_CLOSESTCOORDINATE	Computes the closest coordinate(s) contained in the given geometry starting from the given point, using the 2D distance.
PUBLIC	ST_CLOSESTPOINT	Returns the 2D point on geometry A that is closest to geometry B.
PUBLIC	ST_COLLECT	Construct an array of Geometries. Same as ST_ACCUM.
PUBLIC	ST_COLLECTIONEXTRACT	Given a (multi)geometry, returns a (multi)geometry consisting only of elements of the specified dimension.Dimension numbers are 1 == POINT, 2 == LINESTRING, 3 == POLYGON
PUBLIC	ST_COMPACTNESSRATIO	Returns the compactness ratio of the given polygon, defined to be the the perimeter of a circle whose area is equal to the given geometry's area divided by the given polygon's perimeter.
PUBLIC	ST_CONSTRAINEDDELAUNAY	Returns polygons that represent a Constrained Delaunay Triangulation from a geometry.Output is a COLLECTION of polygons, for flag=0 (default flag) or a MULTILINESTRING for flag=1.If the input geometry does not contain any lines, a delaunay triangulation will be computed.
PUBLIC	ST_CONTAINS	Return true if Geometry A contains Geometry B
PUBLIC	ST_CONVEXHULL	Computes the smallest convex POLYGON that contains all the points in the Geometry
PUBLIC	ST_COORDDIM	Returns the dimension of the coordinates of the given geometry.
PUBLIC	ST_COVERS	Returns true if no point in geometry B is outside geometry A.
PUBLIC	ST_CROSSES	Return true if Geometry A crosses Geometry B
PUBLIC	ST_DELAUNAY	Returns polygons that represent a Delaunay Triangulation from a geometry.Output is a COLLECTION of polygons, for flag=0 (default flag) or a MULTILINESTRING for flag=1
PUBLIC	ST_DENSIFY	Densifies a geometry using the given distance tolerance
PUBLIC	ST_DIFFERENCE	Compute the difference between two Geometries
PUBLIC	ST_DIMENSION	Get dimension of a geometry 0 for a Point, 1 for a line and 2 for a polygon.
PUBLIC	ST_DISJOINT	Return true if the two Geometries are disjoint
PUBLIC	ST_DISTANCE	For geometry type returns the 2-dimensional minimum Cartesian distance between two geometries in projected units (spatial ref units).
PUBLIC	ST_DISTANCESPHERE	Returns minimum distance in meters between two lon/lat points. Uses a spherical earth and radius derived from the spheroid defined by the SRID
PUBLIC	ST_DWITHIN	Returns true if the geometries are withinthe specified distance of one another.
PUBLIC	ST_ENDPOINT	Returns the last coordinate of a Geometry as a POINT, given that the Geometry is a LINESTRING or a MULTILINESTRING containing only one LINESTRING. Returns NULL for all other Geometries.
PUBLIC	ST_ENVELOPE	Get geometry envelope as geometry.
PUBLIC	ST_ENVELOPESINTERSECT	Return true if the envelope of Geometry A intersects the envelope of Geometry B
PUBLIC	ST_EQUALS	Return true if Geometry A is equal to Geometry B
PUBLIC	ST_EXPAND	Expands a geometry's envelope in both X and or Y directions. Both positive and negative distances are supported.
PUBLIC	ST_EXPLODE	Explode Geometry Collection into multiple geometries.Note : This function supports select query as the first arfument.
PUBLIC	ST_EXTENT	Return the minimum bounding box of a GEOMETRYCOLLECTION
PUBLIC	ST_EXTERIORRING	Returns a LinearRing instance or Null if parameter is not a Polygon.
PUBLIC	ST_EXTRUDE	ST_Extrude takes a LINESTRING or POLYGON as input and extends it to a 3D representation, returning a geometry collection containing floor, ceiling and wall geometries.Note: the NaN z value of the input geometry are replaced by a zero.
PUBLIC	ST_FLIPCOORDINATES	Returns a version of the given geometry with X and Y axis flipped. Useful for people who have builtlatitude/longitude features and need to fix them.
PUBLIC	ST_FORCE2D	Forces the geometries into a "2-dimensional mode" so that all output representations will only have the X and Y coordinates.
PUBLIC	ST_FORCE3D	Forces the geometries into XYZ mode. This is an alias for ST_Force_3DZ. If a geometry has no Z component, then a 0 Z coordinate is tacked on.
PUBLIC	ST_FURTHESTCOORDINATE	Computes the furthest coordinate(s) contained in the given geometry starting from the given point, using the 2D distance.
PUBLIC	ST_GEOMETRYN	Returns Geometry number n from a GeometryCollection. Use ST_NumGeometries to retrieve the total number of Geometries.
PUBLIC	ST_GEOMETRYSHADOW	This function computes the shadow footprint as a polygon(s) for a LINE and a POLYGON or LINE for a POINT.Avalaible arguments are :(1) The geometry.(2 and 3) The position of the sun is specified with two parameters in radians : azimuth and altitude.(4) The height value is used to extrude the facades of geometry.(5) Optional parameter to unified or not the shadow polygons. True is the default value.Note 1: The z of the output geometry is set to 0.Note 2: The azimuth is a direction along the horizon, measured from north to east.The altitude is expressed above the horizon in radians, e.g. 0 at the horizon and PI/2 at the zenith.The user can set the azimut and the altitude using a point see ST_SunPosition function,the folowing signature must be used ST_GeometryShadow(INPUT_GEOM,ST_SUNPosition(), HEIGHT).
PUBLIC	ST_GEOMETRYTYPE	Return the type of geometry : POINT, LINESTRING, POLYGON..
PUBLIC	ST_GEOMETRYTYPECODE	Returns the OGC SFS geometry type code from a Geometry
PUBLIC	ST_GEOMFROMGEOJSON	Convert a geojson representation of a geometry to a geometry object.
PUBLIC	ST_GEOMFROMGML	Convert an input GML representation of geometry to a geometry. An optional argument is used to set a SRID.This function supports only GML 2.1.2
PUBLIC	ST_GEOMFROMTEXT	Convert a Well Known Text geometry string into a geometry instance.
PUBLIC	ST_GEOMFROMWKB	Convert a binary large object to a geometry object.An optional integer parameter could be used to specify the SRID.
PUBLIC	ST_GOOGLEMAPLINK	Generate a Google Map link URL based on the center of the bounding box of the input geometry.Optional arguments : (1) specify the layer type m (normal map) , k (satellite), h (hybrid), p (terrain). (2) set a zoom level between 1 and 19. Default values are m and 19.
PUBLIC	ST_GRAPH	ST_Graph produces two tables (nodes and edges) from an input table containing`LINESTRING`s or `MULTILINESTRING`s in the given column and using the giventolerance, and potentially orienting edges by slope. If the input table hasname `input`, then the output tables are named `input_nodes` and `input_edges`.The nodes table consists of an integer `node_id` and a `POINT` geometryrepresenting each node. The edges table is a copy of the input table with threeextra columns: `edge_id`, `start_node`, and `end_node`. The `start_node` and`end_node` correspond to the `node_id`s in the nodes table.If the specified geometry column of the input table contains geometries otherthan `LINESTRING`s, the operation will fail.A tolerance value may be given to specify the side length of a square envelopearound each node used to snap together other nodes within the same envelope.Note, however, that edge geometries are left untouched. Note also thatcoordinates within a given tolerance of each other are not necessarily snappedtogether. Only the first and last coordinates of a geometry are considered tobe potential nodes, and only nodes within a given tolerance of each other aresnapped together. The tolerance works only in metric units.A boolean value may be set to true to specify that edges should be oriented bythe z-value of their first and last coordinates (decreasing).
PUBLIC	ST_HOLES	Returns the given geometry's holes as a GeometryCollection.
PUBLIC	ST_INTERIORRINGN	Returns interior ring number n from a Polygon. Use ST_NumInteriorRings to retrieve the total number of interior rings.
PUBLIC	ST_INTERPOLATE3DLINE	Interpolate the z values of a linestring or multilinestring based onthe start and the end z values. If the z values are equal to NaN return the input geometry.
PUBLIC	ST_INTERSECTION	Compute the intersection of two Geometries
PUBLIC	ST_INTERSECTS	Return true if the geometry A intersects the geometry B.
PUBLIC	ST_IS3D	Returns 1 if a geometry has a z-coordinate, otherwise 0.
PUBLIC	ST_ISCLOSED	Return TRUE if the provided geometry is a closed LINESTRING or MULTILINESTRING, null otherwise.
PUBLIC	ST_ISEMPTY	Check if the provided geometry is empty.
PUBLIC	ST_ISRECTANGLE	Returns true if the given geometry is a rectangle.
PUBLIC	ST_ISRING	Return TRUE if the provided geometry is a closed and simple LINESTRING or MULTILINESTRING; NULL otherwise.
PUBLIC	ST_ISSIMPLE	Test if the provided geometry is simple.
PUBLIC	ST_ISVALID	Returns true if the given geometry is valid.
PUBLIC	ST_ISVALIDDETAIL	Returns a valid_detail as an array of objects [0] = isvalid,[1] = reason, [2] = error locationThe second argument is optional. It can have the following values (0 or 1)1 = It will validate inverted shells and exverted holes according the ESRI SDE model.0 = It will based on the OGC geometry model.
PUBLIC	ST_ISVALIDREASON	Returns text stating if a geometry is valid or not and if not valid, a reason why.The second argument is optional. It can have the following values (0 or 1)1 = It will validate inverted shells and exverted holes according the ESRI SDE model.0 = It will based on the OGC geometry model.
PUBLIC	ST_LENGTH	Returns the 2D length of the geometry if it is a LineString or MultiLineString. 0 is returned for other geometries
PUBLIC	ST_LINEFROMTEXT	Convert a WKT String into a LINESTRING. If an SRID is not specified, it defaults to 0.
PUBLIC	ST_LINEFROMWKB	Convert Well Known Binary into a LINESTRING. If an SRID is not specified, it defaults to 0.
PUBLIC	ST_LINEINTERSECTOR	Split an input geometry by another geometry. This function uses a more robust intersection algorithm than the ST_Split function.It computes the intersections between the line segments of the input geometries.A collection of LineString is returned.
PUBLIC	ST_LINEMERGE	Merges a collection of LineString elements in order to make create a new collection of maximal-length linestrings. If you provide something else than (multi)linestrings it returns an empty multilinestring
PUBLIC	ST_LOCATEALONG	Returns a MULTIPOINT containing points along the line segments of the given geometry matching the specified segment length fraction and offset distance. A positive offset places the point to the left of the segment (with the ordering given by Coordinate traversal); a negative offset to the right. For areal elements, only exterior rings are supported.
PUBLIC	ST_LONGESTLINE	Returns the 2-dimensional longest line between the points of two geometries.If the geometry 1 and geometry 2 is the same geometry the function will return the longest line between the two vertices most far from each other in that geometry.
PUBLIC	ST_MAKEELLIPSE	Constructs an elliptical POLYGON with the given width and height centered at the given point. Each ellipse contains 100 line segments.
PUBLIC	ST_MAKEENVELOPE	Creates a rectangular POLYGON formed from the given x and y minima. The user may specify an SRID; if no SRID is specified the unknown spatial reference system is assumed.
PUBLIC	ST_MAKEGRID	Calculate a regular grid.The first argument is either a geometry or a table.The delta X and Y cell grid are expressed in a cartesian plane.Note :The geometry could be expressed using a subquery as (SELECT the_geom from myTable)
PUBLIC	ST_MAKEGRIDPOINTS	Calculate a regular grid of points.The first argument is either a geometry or a table.The delta X and Y cell grid are expressed in a cartesian plane.Note :The geometry could be expressed using a subquery as (SELECT the_geom from myTable)
PUBLIC	ST_MAKELINE	Constructs a LINESTRING from two POINT geometries.
PUBLIC	ST_MAKEPOINT	Constructs POINT from two or three doubles
PUBLIC	ST_MAKEPOLYGON	Creates a Polygon formed by the given shell and optionally holes.Input geometries must be closed Linestrings
PUBLIC	ST_MAKEVALID	Repair an invalid geometry. If preserveGeomDim is true, makeValid will remove degenerated geometries from the result, i.e geometries which dimension is lower than the input geometryA multi-geometry will always produce a multi-geometry (eventually empty or made of a single component).A simple geometry may produce a multi-geometry (ex. polygon with self-intersection will generally produce a multi-polygon). In this case, it is up to the client to explode multi-geometries if he needs to.If preserveGeomDim is off, it is up to the client to filter degenerate geometries. WARNING : for geometries of dimension 1 (linear), duplicate coordinates are preserved as much as possible. For geometries of dimension 2 (areal), duplicate coordinates are generally removed due to the use of overlay operations.
PUBLIC	ST_MAXDISTANCE	Returns the 2-dimensional largest distance between two geometries in projected units.If the geometry 1 and geometry 2 is the same geometry the function will return the distance between the two vertices most far from each other in that geometry.
PUBLIC	ST_MINIMUMBOUNDINGCIRCLE	Compute the minimum bounding circle of a geometry. This is an alias for ST_BoundingCircle
PUBLIC	ST_MINIMUMDIAMETER	Compute the minimum diameter for a given geometrywhich is a linestring.
PUBLIC	ST_MINIMUMRECTANGLE	Gets the minimum rectangular POLYGON which encloses the input geometry.
PUBLIC	ST_MLINEFROMTEXT	Convert a WKT String into a MULTILINESTRING. If an SRID is not specified, it defaults to 0.
PUBLIC	ST_MPOINTFROMTEXT	Convert a WKT String into a MULTIPOINT. If an SRID is not specified, it defaults to 0.
PUBLIC	ST_MPOLYFROMTEXT	Convert a WKT String into a MULTIPOLYGON. If an SRID is not specified, it defaults to 0.
PUBLIC	ST_MULTIPLYZ	This function do a multiplication with the z value of (each vertex of) the geometric parameter to the corresponding value given by a field.
PUBLIC	ST_NORMALIZE	Converts this Geometry to normal form (canonical form).
PUBLIC	ST_NPOINTS	Return the number of points (vertexes) in a geometry.
PUBLIC	ST_NUMGEOMETRIES	Get the number of geometries inside a geometry collection.
PUBLIC	ST_NUMINTERIORRING	Return the number of interior rings of the first polygon in the geometry. This will work with both POLYGON and MULTIPOLYGON. Return NULL if there is no polygon in the geometry.
PUBLIC	ST_NUMINTERIORRINGS	Return the number of interior rings of the first polygon in the geometry. This will work with both POLYGON and MULTIPOLYGON. Return NULL if there is no polygon in the geometry.
PUBLIC	ST_NUMPOINTS	Return the number of points in an LineString.
PUBLIC	ST_OCTOGONALENVELOPE	Computes the octogonal envelope of a geometry
PUBLIC	ST_OFFSETCURVE	Return an offset line or collection of lines at a given distance and side from an input geometry.The optional third parameter can either specify number of segments used to approximate a quarter circle (integer case, defaults to 8) or a list of blank-separated key=value pairs (string case) to manage line style parameters :'quad_segs=8' endcap=round\|flat\|square' 'join=round\|mitre\|bevel' 'mitre_limit=5'
PUBLIC	ST_ORDERINGEQUALS	Returns true if the given geometries represent the same geometry and points are in the same directional order.
PUBLIC	ST_OSMDOWNLOADER	Extract an OSM XML file from the OSM api server using a the bounding box of a given geometry.A path must be set to specified where the OSM file will be stored./nSet true to delete the XML file if exists. Default behaviour is false.
PUBLIC	ST_OSMMAPLINK	Generate an OSM map link URL based on the bounding box of the input geometry.An optional argument could be used to place a marker on the center of the bounding box.
PUBLIC	ST_OVERLAPS	Return true if the geometry A overlaps the geometry B.
PUBLIC	ST_PERIMETER	Returns the length measurement of the boundary of a Polygon or a MultiPolygon. Distance units are those of the geometry spatial reference system.
PUBLIC	ST_POINTFROMTEXT	Convert a WKT String into a POINT. If an SRID is not specified, it defaults to 0.
PUBLIC	ST_POINTFROMWKB	Convert Well Known Binary into a POINT. If an SRID is not specified, it defaults to 0.
PUBLIC	ST_POINTN	Returns the nth point of a LINESTRING or a MULTILINESTRING containing exactly one LINESTRING; NULL otherwise. As the OGC specifies, ST_PointN is 1-N based.
PUBLIC	ST_POINTONSURFACE	Get a Point that lie on the surface of a Surface Geometry. The returned point is always the same for the same geometry.
PUBLIC	ST_POLYFROMTEXT	Convert a WKT String into a POLYGON. If an SRID is not specified, it defaults to 0.
PUBLIC	ST_POLYFROMWKB	Convert Well Known Binary into Geometry then check that it is a POLYGON. If an SRID is not specified, it defaults to 0.
PUBLIC	ST_POLYGONIZE	Polygonizes a set of Geometry which contain linework that represents the edges of a planar graph
PUBLIC	ST_PRECISIONREDUCER	Reduce the geometry precision. Decimal_Place is the number of decimals to keep.
PUBLIC	ST_PROJECTPOINT	Projet a point along a linestring. If the point projected is out of line the first or last point on the line will be returned otherwise the input point.
PUBLIC	ST_RELATE	This function is used to compute the relation between two geometries, as described in the SFS specification. It can be used in two ways. First, if it is given two geometries,it returns a 9-character String representation of the 2 geometries IntersectionMatrix. If it is given two geometries and an IntersectionMatrix representation, it will return a boolean : true it the two geometries' IntersectionMatrix match the given one, false otherwise.
PUBLIC	ST_REMOVEDUPLICATEDCOORDINATES	Returns a version of the given geometry without duplicated coordinates.
PUBLIC	ST_REMOVEHOLES	Remove all holes in a polygon or a multipolygon. If the geometry doesn't contain any hole return the input geometry. If the input geometry is not a polygon or multipolygon return null.
PUBLIC	ST_REMOVEPOINTS	Remove all points on a geometry that are located within a polygon.
PUBLIC	ST_REMOVEREPEATEDPOINTS	Returns a version of the given geometry with duplicated points removed.If the tolerance parameter is provided, vertices within the tolerance of one another will be considered the same for the purposes of removal.
PUBLIC	ST_REVERSE	Returns the geometry with vertex order reversed.
PUBLIC	ST_REVERSE3DLINE	Returns a 1 dimension geometry with vertex order reversed according the ascending z values. The z of the first point must be lower than the z of the end point. If the z values are equal to NaN return the input geometry.
PUBLIC	ST_RINGBUFFER	Compute a ring buffer around a geometry.Avalaible arguments are : (1) the geometry, (2) the size of each ring, (3) the number of rings, (4) optional - the end cap style (square, round) Default is rounda list of blank-separated key=value pairs (string case) iso used t manage line style parameters. Please read the ST_Buffer documention. (5) optional - createHole True if you want to keep only difference between buffers Default is true.Note : Holes are not supported by this function.
PUBLIC	ST_RINGSIDEBUFFER	Return a ring buffer at a given distance on only one side of each input lines of the geometry.Avalaible arguments are : (1) the geometry, (2) the size of each ring, (3) the number of rings, (4) optional - a list of blank-separated key=value pairs (string case) iso used t manage line style parameters. The end cap style for single-sided buffers is always ignored, and forced to the equivalent of flat.Please read the ST_Buffer documention. (5) optional - createHole True if you want to keep only difference between buffers Default is true.Note : Holes are not supported by this function.
PUBLIC	ST_ROTATE	Rotates a geometry by a given angle (inradians) about the geometry's center.
PUBLIC	ST_SCALE	Scales the given geometry by multiplying the coordinates by the indicated scale factors
PUBLIC	ST_SETSRID	Return a new geometry with a replaced spatial reference id. Warning, use ST_Transform if you want to change the coordinate reference system as this method does not update the coordinates. This function can take at first argument an instance of Geometry or Envelope
PUBLIC	ST_SIDEBUFFER	Return a buffer at a given distance on only one side of each input lines of the geometry.The optional third parameter can either specify number of segments used to approximate a quarter circle (integer case, defaults to 8) or a list of blank-separated key=value pairs (string case) to manage line style parameters :'quad_segs=8' 'join=round\|mitre\|bevel' 'mitre_limit=5'The end cap style for single-sided buffers is always ignored, and forced to the equivalent of flat.
PUBLIC	ST_SIMPLIFY	Returns a simplified version of the given geometry using the Douglas-Peuker algorithm.
PUBLIC	ST_SIMPLIFYPRESERVETOPOLOGY	Simplifies a geometry and ensures that the result is a valid geometry.
PUBLIC	ST_SNAP	Snaps two geometries together with a given tolerance
PUBLIC	ST_SPLIT	Returns a collection of geometries resulting by splitting a geometry.Supported operations are : - split a polygon or a multipolygon by a linestring,- split a linestring or a multilinestring by a linestring,- split a linestring or a multilinestring by a point. At this stage a double tolerancecan be used to snap the point.
PUBLIC	ST_SRID	Retrieve the SRID from an EWKB encoded geometry.
PUBLIC	ST_STARTPOINT	Returns the first coordinate of a Geometry as a POINT, given that the Geometry is a LINESTRING or a MULTILINESTRING containing only one LINESTRING. Returns NULL for all other Geometries.
PUBLIC	ST_SUNPOSITION	Return the sun position (horizontal coordinate system) as a Point where : x = sun azimuth in radians (direction along the horizon, measured from north toeast).y = sun altitude above the horizon in radians, e.g. 0 at thehorizon and PI/2 at the zenith.
PUBLIC	ST_SYMDIFFERENCE	Compute the symmetric difference between two Geometries
PUBLIC	ST_TESSELLATE	Return the tessellation of a (multi)polygon surface with adaptive trianglesEx:`SELECT ST_TESSELLATE('POLYGON ((-6 -2, -8 2, 0 8, -8 -7, -10 -1, -6 -2))') the_geom`
PUBLIC	ST_TOMULTILINE	Constructs a MultiLineString from the given geometry's coordinates.
PUBLIC	ST_TOMULTIPOINT	Constructs a MultiPoint from the given geometry's coordinates.
PUBLIC	ST_TOMULTISEGMENTS	Converts a geometry into a set of distinct segments stored in a MultiLineString.
PUBLIC	ST_TOUCHES	Return true if the geometry A touches the geometry B.
PUBLIC	ST_TRANSFORM	Transform a geometry from one CRS to another using integer codes from the SPATIAL_REF_SYS table.
PUBLIC	ST_TRANSLATE	Translates a geometry using X, Y (and possibly Z) offsets.
PUBLIC	ST_TRIANGLEASPECT	Compute the aspect of steepest downhill slope for a triangle. The aspect value is expressed in degrees compared to the north direction.
PUBLIC	ST_TRIANGLECONTOURING	Split triangle into polygons within the specified range of values.Iso contouring using Z:select * from ST_TRIANGLECONTOURING('input_table',10,20,30,40)Iso contouring using table columnsSELECT * FROM ST_TRIANGLECONTOURING('input_table','m1','m2','m3',10,20,30,40)
PUBLIC	ST_TRIANGLEDIRECTION	Compute the steepest vector director for a triangleand represent it as a linestring
PUBLIC	ST_TRIANGLESLOPE	Compute the slope of a triangle expressed in percents.
PUBLIC	ST_UNION	Compute the union of two or more Geometries
PUBLIC	ST_UPDATEZ	This function replace the z value of (each vertex of) the geometric parameter to the corresponding value given by a field.The first argument is used to replace all existing z values.The second argument is a int value. Set 1 to replace all z values.Set 2 to replace all z values excepted the NaN values.Set 3 to replace only the NaN z values.
PUBLIC	ST_VORONOI	Construct a voronoi diagram from a delaunay triangulation or a set of points. ST_VORONOI(THE_GEOM MULTIPOLYGON) ST_VORONOI(THE_GEOM MULTIPOLYGON,OUT_DIMENSION INTEGER) ST_VORONOI(THE_GEOM MULTIPOLYGON,OUT_DIMENSION INTEGER,ENVELOPE POLYGON) ST_VORONOI(THE_GEOM MULTIPOINTS) ST_VORONOI(THE_GEOM MULTIPOINTS,OUT_DIMENSION INTEGER) ST_VORONOI(THE_GEOM MULTIPOINTS,OUT_DIMENSION INTEGER,ENVELOPE POLYGON) Ex: SELECT ST_VORONOI(ST_DELAUNAY('MULTIPOINT(2 2 0,6 3 0,4 7 0,2 8 0,1 6 0,3 5 0)')) the_geom; SELECT ST_VORONOI(ST_DELAUNAY('MULTIPOINT(2 2 0,6 3 0,4 7 0,2 8 0,1 6 0,3 5 0)'), 1) SELECT ST_VORONOI(ST_DELAUNAY('MULTIPOINT(2 2 0,6 3 0,4 7 0,2 8 0,1 6 0,3 5 0)'), 1, ST_EXPAND('POINT(3 5)', 10, 10))
PUBLIC	ST_WITHIN	Return true if the geometry A is within the geometry B.
PUBLIC	ST_X	Get the first X coordinate.
PUBLIC	ST_XMAX	Returns the maximal x-value of the given geometry.
PUBLIC	ST_XMIN	Returns the minimal x-value of the given geometry.
PUBLIC	ST_Y	Get the first Y coordinate.
PUBLIC	ST_YMAX	Returns the maximal y-value of the given geometry.
PUBLIC	ST_YMIN	Returns the minimal y-value of the given geometry.
PUBLIC	ST_Z	Get the first Z coordinate.
PUBLIC	ST_ZMAX	Returns the maximal z-value of the given geometry.
PUBLIC	ST_ZMIN	Returns the minimal z-value of the given geometry.
PUBLIC	ST_ZUPDATELINEEXTREMITIES	Replace the start and end z values of a linestring or multilinestring.By default the other z values are interpolated according the length of the line.Set false if you want to update only the start and end z values.
PUBLIC	TSVREAD	Read a Tab-separated values file.
PUBLIC	TSVWRITE	Write a Tab-separated values file.
PUBLIC	_COLUMNSRID	Get the column SRID from constraints and data.
PUBLIC	_DIMENSIONFROMCONSTRAINT	Check column constraint for Z constraint.
PUBLIC	_GEOMETRYTYPEFROMCONSTRAINT	Convert H2 constraint string into a OGC geometry type index.
PUBLIC	_GEOMETRYTYPENAMEFROMCONSTRAINT	Parse the constraint and return the Geometry type name

Keys	Action
`?`	Open this help
`n`	Next page
`p`	Previous page
`s`	Search