This topic describes the processing functions that are supported by Lindorm GanosBase.
Applicable engines and versions
The processing functions described in this topic are applicable only to all versions of LindormTable.
Functions
The following table describes the processing functions supported by Lindorm GanosBase.
Function | Description |
Function | Description |
Returns a buffer geometry object consisting of all points whose 2D distance to a specified source geometry object is equal to the specified radius. | |
Returns a buffer geometry object consisting of all points whose spherical distance to a specified source geometry object is equal to the specified radius. | |
Combines two or more geometry objects into a MultiPoint, MultiLineString, MultiPolygon, or GeometryCollection object. | |
Returns a geometry object representing the part that the first geometry object that you specify does not intersect with the second geometry object that you specify. | |
Returns a geometry object representing the part that the first geometry object that you specify intersects with the second geometry object that you specify. | |
Returns the smallest circle that can fully contain the specified geometry object. | |
Returns the radius of the smallest circle that can fully contain the specified geometry object. | |
Converts an invalid geometry object to a valid geometry object. | |
Returns a geometry object representing the union of multiple specified geometry objects. |
ST_Buffer
The ST_Buffer function returns a buffer geometry object consisting of all points whose 2D distance to a specified source geometry object is equal to the specified radius.
Syntax
ST_Buffer(geometry g, double radius)
Parameters
Parameter | Description |
Parameter | Description |
g | The source geometry object based on which the 2D distance of points is calculated. |
radius | The 2D distance from the source geometry object to the points of the buffer geometry object. Unit: degree. |
You can specify a geometry object of the following types: Point, LineString, Polygon, MultiPoint, MultiLineString, MultiPolygon, and GeometryCollection.
If you specify a positive value for radius, the buffer geometry object rings away from the center of the source geometry object. If you specify a negative value for radius, the buffer geometry object rings towards the center of the source geometry object. The returned geometry may be empty.
Only the default buffer style is supported. By default, the value of quadrantSegments is 8 and the value of endCapStyle is round.
Examples
Example 1:
SELECT ST_AsText(ST_Buffer(ST_GeomFromText('POINT(120.18 30.25)'), 0.05)) AS buffer;
The following result is returned:
+--------------------------------+ | buffer | +--------------------------------+ | POLYGON ((120.23 30.25, | | 120.22903926402017 | | 30.240245483899194, | | 120.22619397662557 | | 30.230865828381745, | | 120.22157348061513 | | 30.22222148834902, | | 120.21535533905933 | | 30.21464466094067, | | 120.20777851165099 | | 30.208426519384872, | | 120.19913417161825 | | 30.203806023374437, | | 120.18975451610082 | | 30.200960735979837, 120.18 | | 30.2, 120.1702454838992 | | 30.200960735979837, | | 120.16086582838176 | | 30.203806023374437, | | 120.15222148834903 | | 30.208426519384872, | | 120.14464466094068 | | 30.21464466094067, | | 120.13842651938488 | | 30.22222148834902, | | 120.13380602337445 | | 30.230865828381745, | | 120.13096073597984 | | 30.240245483899194, | | 120.13000000000001 | | 30.25, 120.13096073597984 | | 30.259754516100806, | | 120.13380602337445 | | 30.269134171618255, | | 120.13842651938488 | | 30.27777851165098, | | 120.14464466094068 | | 30.28535533905933, | | 120.15222148834903 | | 30.291573480615128, | | 120.16086582838176 | | 30.296193976625563, | | 120.1702454838992 | | 30.299039264020163, 120.18 | | 30.3, 120.18975451610082 | | 30.299039264020163, | | 120.19913417161825 | | 30.296193976625563, | | 120.20777851165099 | | 30.291573480615128, | | 120.21535533905933 | | 30.28535533905933, | | 120.22157348061513 | | 30.27777851165098, | | 120.22619397662557 | | 30.269134171618255, | | 120.22903926402017 | | 30.259754516100806, 120.23 | | 30.25)) | +--------------------------------+
Example 2:
SELECT ST_AsText(ST_Buffer(ST_GeomFromText('LINESTRING (120.18 30.25, 120.16 30.23)'), 0.01)) AS buffer;
The following result is returned:
+--------------------------------+ | buffer | +--------------------------------+ | POLYGON ((120.16707106781186 | | 30.222928932188132, | | 120.1655557023302 | | 30.221685303876974, | | 120.16382683432364 | | 30.220761204674886, | | 120.16195090322016 | | 30.220192147195966, 120.16 | | 30.22, 120.15804909677983 | | 30.22019214719597, | | 120.15617316567635 | | 30.22076120467489, | | 120.1544442976698 | | 30.221685303876978, | | 120.15292893218813 | | 30.222928932188136, | | 120.15168530387697 | | 30.224444297669805, | | 120.15076120467488 | | 30.226173165676354, | | 120.15019214719597 | | 30.22804909677984, | | 120.14999999999999 | | 30.230000000000004, | | 120.15019214719597 | | 30.231950903220163, | | 120.15076120467488 | | 30.233826834323654, | | 120.15168530387697 | | 30.2355557023302, | | 120.15292893218813 | | 30.23707106781187, | | 120.17292893218814 | | 30.257071067811868, | | 120.17444429766981 | | 30.258314696123026, | | 120.17617316567636 | | 30.259238795325114, | | 120.17804909677984 | | 30.259807852804034, 120.18 | | 30.26, 120.18195090322017 | | 30.25980785280403, | | 120.18382683432365 | | 30.25923879532511, | | 120.1855557023302 | | 30.258314696123023, | | 120.18707106781187 | | 30.257071067811864, | | 120.18831469612303 | | 30.255555702330195, | | 120.18923879532512 | | 30.253826834323647, | | 120.18980785280404 | | 30.25195090322016, | | 120.19000000000001 | | 30.249999999999996, | | 120.18980785280404 | | 30.248049096779837, | | 120.18923879532512 | | 30.246173165676346, | | 120.18831469612303 | | 30.2444442976698, | | 120.18707106781187 | | 30.242928932188132, | | 120.16707106781186 | | 30.222928932188132)) | +--------------------------------+
ST_BufferSphere
The ST_BufferSphere function returns a buffer geometry object consisting of all points whose spherical distance to a specified source geometry object is equal to the specified radius.
Syntax
ST_BufferSphere(geometry g, double radius)
Parameters
Parameter | Description |
Parameter | Description |
g | The source geometry object based on which the spherical distance of points is calculated. |
radius | The spherical distance from the source geometry object to the points of the buffer geometry object. Unit: meter. |
You can specify a geometry object of the following types: Point, LineString, Polygon, MultiPoint, MultiLineString, MultiPolygon, and GeometryCollection.
If you specify a positive value for radius, the buffer geometry object rings away from the center of the source geometry object. If you specify a negative value for radius, the buffer geometry object rings towards the center of the source geometry object. The returned geometry may be empty.
Only the default buffer style is supported. By default, the value of quadrantSegments is 8 and the value of endCapStyle is round.
Examples
Example 1:
SELECT ST_AsText(ST_BufferSphere(ST_GeomFromText('POINT(120.18 30.25)'), 500)) AS buffer;
The following result is returned:
+--------------------------------+ | buffer | +--------------------------------+ | POLYGON ((120.1851909310331 | | 30.25011173053024, | | 120.18511627294788 | | 30.249230378090658, | | 120.18484500297895 | | 30.248378605521545, | | 120.18438755060062 | | 30.247589145091563, | | 120.1837614989031 | | 30.246892333708224, | | 120.18299090835872 | | 30.24631494733322, | | 120.18210539193977 | | 30.245879172318283, | | 120.18113897716789 | | 30.24560175314354, | | 120.18012879881775 | | 30.245493349257533, | | 120.17911367245745 | | 30.245558125686234, | | 120.17813260355221 | | 30.245793593106626, | | 120.17722328930826 | | 30.246190703510113, | | 120.17642067070908 | | 30.24673419777976, | | 120.17575559027851 | | 30.247403191844143, | | 120.17525360707266 | | 30.248171978919647, | | 120.1749340144007 | | 30.249011017060198, | | 120.17480909803247 | | 30.249888064116504, | | 120.17488366345407 | | 30.250769416537622, | | 120.17515485043153 | | 30.251621204446096, | | 120.17561224212514 | | 30.252410693237966, | | 120.17623826468355 | | 30.253107541687907, | | 120.17700886207045 | | 30.253684968191145, | | 120.17789442027193 | | 30.254120780286797, | | 120.1788609054053 | | 30.25439822784955, | | 120.17987117198402 | | 30.25450664710903, | | 120.18088639100795 | | 30.254441870698788, | | 120.18186754290468 | | 30.254206387939035, | | 120.1827769178333 | | 30.25380924917365, | | 120.1835795655716 | | 30.253265717837415, | | 120.1842446391596 | | 30.252596683644782, | | 120.18474658058295 | | 30.251827859488568, | | 120.18506610289342 | | 30.250988792960012, | | 120.1851909310331 | | 30.25011173053024)) | +--------------------------------+
Example 2:
SELECT ST_AsText(ST_BufferSphere(ST_GeomFromText('LINESTRING (120.18 30.25, 120.16 30.23)'), 20)) AS buffer;
The following result is returned:
+--------------------------------+ | buffer | +--------------------------------+ | POLYGON ((120.1601568095326 | | 30.229881789491696, | | 120.16012723865646 | | 30.22985749603038, | | 120.16009277809007 | | 30.229838678911875, | | 120.16005475213065 | | 30.229826061263992, | | 120.16001462208855 | | 30.22982012797305, | | 120.15997393013107 | | 30.229821107050387, | | 120.15993424001891 | | 30.22982896087028, | | 120.15989707701289 | | 30.229843387615915, | | 120.15986386926043 | | 30.229863832877875, | | 120.15983589291346 | | 30.229889510959428, | | 120.15981422308717 | | 30.229919435069952, | | 120.15979969254388 | | 30.22995245524614, | | 120.15979285969011 | | 30.22998730254394, | | 120.15979398711634 | | 30.23002263780298, | | 120.15980303150468 | | 30.230057103109367, | | 120.15981964529222 | | 30.230089373979652, | | 120.15984319002649 | | 30.230118210260056, | | 120.17984317814214 | | 30.250118233425205, | | 120.17987275990819 | | 30.250142523059253, | | 120.17990723145967 | | 30.250161335607235, | | 120.17994526807108 | | 30.250173948109982, | | 120.17998540801094 | | 30.250179875873307, | | 120.18002610871608 | | 30.25017889109486, | | 120.18006580607275 | | 30.25017103161881, | | 120.18010297452594 | | 30.250156599481485, | | 120.18013618570674 | | 30.25013614930418, | | 120.18016416332479 | | 30.25011046697896, | | 120.18018583221566 | | 30.250080539466826, | | 120.18020035965924 | | 30.250047516868772, | | 120.18020718737982 | | 30.25001266822753, | | 120.18020605299972 | | 30.249977332758547, | | 120.18019700012098 | | 30.2499428683844, | | 120.18018037664854 | | 30.249910599550603, | | 120.1801568214192 | | 30.249881766327974, | | 120.1601568095326 | | 30.229881789491696)) | +--------------------------------+
ST_Collect
The ST_Collect function combines two or more geometry objects into a MultiPoint, MultiLineString, MultiPolygon, or GeometryCollection object.
Syntax
ST_Collect(geometry geomA, geometry geomB)
ST_Collect(geometry geomA, geometry geomB, ...,geometry geomN)
Parameters
Parameter | Description |
Parameter | Description |
geomA | The first geometry object that you want to specify. |
geomB | The second geometry object that you want to specify. |
geomN | The Nth geometry object that you want to specify. |
The
ST_Collect
function returns a MultiPoint, MultiLineString, MultiPolygon, or GeometryCollection object. These types of objects can only be read but cannot be written by Lindorm GanosBase SQL.The
ST_Collect
function andST_Union
can be interchanged in usage. However, the two functions have the following differences:The
ST_Collect
function returns a MultiPoint, MultiLineString, MultiPolygon, or GeometryCollection object. TheST_Union
may return a single geometry object.The
ST_Union
function splits a LineString object at the intersection of nodes while theST_Collect
function returns a MultiLineString object.In general, the computing performance of
ST_Collect
is superior toST_Union
. This is becauseST_Collect
does not split the boundary of the specified geometry objects or check whether the specified MultiPolygon objects overlap. ST_Collect combines multiple geometry objects into a MultiPoint, MultiLineString, or MultiPolygon object or combines multiple MultiPoint, MultiLineString, or MultiPolygon objects into a GeometryCollection object.
Examples
Example 1: Obtain a MultiPoint object.
SELECT ST_AsText(ST_Collect(ST_MakePoint(0,0),ST_MakePoint(0,1))) AS g;
The following result is returned:
+---------------------------+ | g | +---------------------------+ | MULTIPOINT ((0 0), (0 1)) | +---------------------------+
Example 2: Obtain a GeometryCollection object.
SELECT ST_AsText(ST_Collect(ST_MakePoint(0,0),ST_GeomFromText('LINESTRING(0 2,0 3)'))) AS g;
The following result is returned:
+--------------------------------+ | g | +--------------------------------+ | GEOMETRYCOLLECTION (POINT (0 | | 0), LINESTRING (0 2, 0 3)) | +--------------------------------+
ST_Difference
The ST_Difference function returns a geometry object representing the part that the first geometry object that you specify does not intersect with the second geometry object that you specify.
Syntax
ST_Difference(geometry geomA, geometry geomB)
Parameters
Parameter | Description |
Parameter | Description |
geomA | The first geometry object that you want to specify. |
geomB | The second geometry object that you want to specify. |
You can specify a geometry object of the following types: Point, LineString, Polygon, MultiPoint, MultiLineString, MultiPolygon, and GeometryCollection.
If the first geometry object that you specify is empty or is fully contained by the second geometry object that you specify, an empty object is returned.
If you specify a polygon that has intersecting edges, this function may do not return a result.
The order of the input geometry objects affects the output geometry object.
Examples
SELECT ST_AsText(ST_Difference(ST_GeomFromText('LINESTRING(50 100, 50 200)'),ST_GeomFromText('LINESTRING(50 50, 50 150)'))) AS diff;
The following result is returned:
+-----------------------------+
| diff |
+-----------------------------+
| LINESTRING (50 150, 50 200) |
+-----------------------------+
ST_Intersection
The ST_Intersection function returns a geometry object representing the part that the first geometry object that you specify intersects with the second geometry object that you specify.
Syntax
ST_Intersection(geometry geomA, geometry geomB)
Parameters
Parameter | Description |
Parameter | Description |
geomA | The first geometry object that you want to specify. |
geomB | The second geometry object that you want to specify. |
You can specify a geometry object of the following types: Point, LineString, Polygon, MultiPoint, MultiLineString, MultiPolygon, and GeometryCollection.
If one of the specified geometry objects is empty, an empty object is returned.
If you specify a polygon that has intersecting edges, this function may do not return a result.
Examples
SELECT ST_AsText(ST_Intersection(ST_GeomFromText('POINT(0 0)'), ST_GeomFromText('LINESTRING ( 0 0, 0 2 )'))) AS inter;
The following result is returned:
+-------------+
| inter |
+-------------+
| POINT (0 0) |
+-------------+
ST_MinimumBoundingCircle
The ST_MinimumBoundingCircle function returns the smallest circle that can fully contain the specified geometry object.
Syntax
ST_MinimumBoundingCircle(geometry g)
Parameters
Parameter | Description |
Parameter | Description |
g | The geometry object that is fully contained by the returned cycle. |
The ST_MinimumBoundingCircle
function is commonly used for MultiPoint, MultiLineString, MultiPolygon, or GeometryCollection objects. This function is not an aggregate function. However, you can use this function together with the ST_Collect
function to obtain the smallest bounding circle of a set of geometry objects. Example: ST_MinimumBoundingCircle(ST_Collect(geometry geomA, geometry geomB, geometry geomC, ...))
.
Examples
SELECT ST_AsText(ST_MinimumBoundingCircle(ST_GeomFromText('POLYGON((0 0,1 0,1 1,0 1,0 0))'))) AS g;
The following result is returned:
+--------------------------------+
| g |
+--------------------------------+
| POLYGON ((1.2071067811865475 |
| 0.5, 1.1935199226610738 |
| 0.3620503103585285, |
| 1.1532814824381883 |
| 0.2294019499269015, |
| 1.0879378012096794 |
| 0.1071525208064489, 1 |
| 0, 0.8928474791935512 |
| -0.0879378012096794, |
| 0.7705980500730986 |
| -0.1532814824381883, |
| 0.6379496896414716 |
| -0.1935199226610738, |
| 0.5 -0.2071067811865476, |
| 0.3620503103585285 |
| -0.1935199226610738, |
| 0.2294019499269015 |
| -0.1532814824381883, |
| 0.107152520806449 |
| -0.0879378012096794, |
| 0 -0.0000000000000001, |
| -0.0879378012096794 |
| 0.1071525208064489, |
| -0.1532814824381883 |
| 0.2294019499269014, |
| -0.1935199226610738 |
| 0.3620503103585282, |
| -0.2071067811865476 |
| 0.4999999999999999, |
| -0.1935199226610738 |
| 0.6379496896414716, |
| -0.1532814824381884 |
| 0.7705980500730985, |
| -0.0879378012096795 |
| 0.892847479193551, |
| -0.0000000000000001 |
| 1, 0.1071525208064489 |
| 1.0879378012096794, |
| 0.2294019499269011 |
| 1.1532814824381883, |
| 0.3620503103585282 |
| 1.1935199226610735, |
| 0.4999999999999999 |
| 1.2071067811865475, |
| 0.6379496896414716 |
| 1.1935199226610738, |
| 0.7705980500730987 |
| 1.1532814824381883, |
| 0.8928474791935508 |
| 1.0879378012096796, |
| 0.9999999999999999 |
| 1, 1.0879378012096794 |
| 0.892847479193551, |
| 1.1532814824381883 |
| 0.7705980500730989, |
| 1.1935199226610735 |
| 0.6379496896414718, |
| 1.2071067811865475 0.5)) |
+--------------------------------+
ST_MinimumBoundingRadius
The ST_MinimumBoundingRadius function returns the radius of the smallest circle that can fully contain the specified geometry object.
Syntax
ST_MinimumBoundingRadius(geometry g)
Parameters
Parameter | Description |
Parameter | Description |
g | The geometry object that is fully contained by the returned cycle. |
By default, the returned results support DOUBLE values with up to 16 decimal places.
You can use this function together with the
ST_Collect
function to obtain the radius of the smallest bounding circle of a set of geometry objects.
Examples
SELECT ST_MinimumBoundingRadius(ST_GeomFromText('POLYGON((0 1,-1 0,0 -1,1 0,0 1))')) AS radius;
The following result is returned:
+----------+
| radius |
+----------+
| 1.0 |
+----------+
ST_MakeValid
The ST_MakeValid function converts an invalid geometry object to a valid geometry object.
Syntax
geometry ST_MakeValid(geometry g)
Parameters
Parameter | Description |
Parameter | Description |
g | The geometry object that you want to specify. |
You can call the
ST_MakeValid
function to convert a self-intersecting Polygon object into a MultiPolygon object that consists of multiple Polygon objects.For a MultiPolygon objects that consists of multiple overlapping Polygon objects, you can call the
ST_MakeValid
function to calculate the union of the overlapping Polygon objects and merge the objects into one Polygon object.
Examples
Example 1: Convert a self-intersecting Polygon object into a MultiPolygon object that consists of multiple Polygon objects without self-intersection.
SELECT ST_AsText(geom) as original, ST_AsText(ST_MakeValid(geom)) AS validated FROM mapdata WHERE id=10000001;
The following result is returned:
+--------------------------------+--------------------------------+ | original | validated | +--------------------------------+--------------------------------+ | POLYGON ((0 0, 10 10, 0 10, 10 | MULTIPOLYGON (((0 0, 5 5, 10 | | 0, 0 0)) | 0, 0 0)), ((5 5, 0 10, 10 10, | | | 5 5))) | +--------------------------------+--------------------------------+
Example 2: Convert a MultiPolygon objects that consists of multiple overlapping Polygon objects into a Polygon object that is the union of the overlapping Polygon objects.
SELECT ST_AsText(geom) as original, ST_AsText(ST_MakeValid(geom)) AS validated FROM mapdata WHERE adcode=10000002;
The following result is returned:
+--------------------------------+--------------------------------+ | original | validated | +--------------------------------+--------------------------------+ | MULTIPOLYGON (((0 0, 5 6, 10 | POLYGON ((4.166666666666667 | | 0, 0 0)), ((5 4, 0 10, 10 10, | 5, 0 10, 10 10, | | 5 4))) | 5.833333333333333 5, 10 0, 0 | | | 0, 4.166666666666667 5)) | +--------------------------------+--------------------------------+
ST_Union
The ST_Union function returns a geometry object representing the union of multiple specified geometry objects.
Syntax
ST_Union(geometry g1, geometry g2, ..., geometry gn)
This function can return a single geometry object, a MultiPoint, MultiLineString, or MultiPolygon object, or a GeometryCollection object.
Parameters
Parameter | Description |
Parameter | Description |
g1 | The first geometry object that you want to specify. |
g2 | The second geometry object that you want to specify. |
gn | The Nth geometry object that you want to specify. |
Geometry objects supported by this function include point, LineString, and polygon objects.
The type of geometry objects returned by this function varies based on the input parameters that you specify:
If you specify two geometry objects, this function returns a single geometry object, a MultiPoint, MultiLineString, or MultiPolygon object, or a GeometryCollection object. If one of the two geometry objects that you specify is NULL, this function returns NULL. If one of the two geometry objects that you specify is an empty object, this function returns the other geometry object.
If you specify three or more geometry objects, this function returns a single geometry object or a MultiPoint, MultiLineString, or MultiPolygon object.
The
ST_Collect
function andST_Union
can be interchanged in usage. In general, the computing performance ofST_Collect
is superior toST_Union
. This is becauseST_Collect
does not split the boundary of the specified geometry objects or check whether the specified MultiPolygon objects overlap.If you specify a polygon that has intersecting edges, this function may do not return a result.
Examples
SELECT ST_AsText(ST_Union(ST_MakePoint(1.0, 1.0), ST_MakePoint(2.0, 2.0))) AS u;
The following result is returned:
+---------------------------+
| u |
+---------------------------+
| MULTIPOINT ((1 1), (2 2)) |
+---------------------------+