All Products
Search
Document Center

Lindorm:Processing functions

Last Updated:Oct 17, 2024

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

ST_Buffer

Returns a buffer geometry object consisting of all points whose 2D distance to a specified source geometry object is equal to the specified radius.

ST_BufferSphere

Returns a buffer geometry object consisting of all points whose spherical distance to a specified source geometry object is equal to the specified radius.

ST_Collect

Combines two or more geometry objects into a MultiPoint, MultiLineString, MultiPolygon, or GeometryCollection object.

ST_Difference

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.

ST_Intersection

Returns a geometry object representing the part that the first geometry object that you specify intersects with the second geometry object that you specify.

ST_MinimumBoundingCircle

Returns the smallest circle that can fully contain the specified geometry object.

ST_MinimumBoundingRadius

Returns the radius of the smallest circle that can fully contain the specified geometry object.

ST_MakeValid

Converts an invalid geometry object to a valid geometry object.

ST_Union

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

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.

Note
  • 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

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.

Note
  • 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

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.

Note
  • 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 and ST_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. The ST_Union may return a single geometry object.

    • The ST_Union function splits a LineString object at the intersection of nodes while the ST_Collect function returns a MultiLineString object.

    • In general, the computing performance of ST_Collect is superior to ST_Union. This is because ST_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

geomA

The first geometry object that you want to specify.

geomB

The second geometry object that you want to specify.

Note
  • 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

geomA

The first geometry object that you want to specify.

geomB

The second geometry object that you want to specify.

Note
  • 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

g

The geometry object that is fully contained by the returned cycle.

Note

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

g

The geometry object that is fully contained by the returned cycle.

Note
  • 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

g

The geometry object that you want to specify.

Note
  • 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)
Note

This function can return a single geometry object, a MultiPoint, MultiLineString, or MultiPolygon object, or a GeometryCollection object.

Parameters

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.

Note
  • 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 and ST_Union can be interchanged in usage. In general, the computing performance of ST_Collect is superior to ST_Union. This is because ST_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)) |
+---------------------------+