All Products
Search

This Product

    Lindorm:Processing functions

    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)) |
+---------------------------+