This topic describes the processing functions that are supported by Lindorm Ganos.

ST_Buffer

You can call the ST_Buffer function to obtain 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

ParameterDescription
gThe source geometry object based on which the 2D distance of points is calculated.
radiusThe 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

You can call the ST_BufferSphere function to obtain 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

ParameterDescription
gThe source geometry object based on which the spherical distance of points is calculated.
radiusThe 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

You can call the ST_Collect function to combine 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

ParameterDescription
geomAThe first geometry object that you want to specify.
geomBThe second geometry object that you want to specify.
geomNThe 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 Ganos 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

You can call the ST_Difference function to obtain 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

ParameterDescription
geomAThe first geometry object that you want to specify.
geomBThe 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_MinimumBoundingCircle

You can call the ST_MinimumBoundingCircle function to obtain the smallest circle that can fully contain the specified geometry object.

Syntax

ST_MinimumBoundingCircle(geometry g);

Parameters

ParameterDescription
gThe 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

You can call the ST_MinimumBoundingRadius function to obtain the radius of the smallest circle that can fully contain the specified geometry object.

Syntax

ST_MinimumBoundingRadius(geometry g);

Parameters

ParameterDescription
gThe 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_Intersection

You can call the ST_Intersection function to obtain 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

ParameterDescription
geomAThe first geometry object that you want to specify.
geomBThe 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_Union

You can call the ST_Union function to obtain 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

ParameterDescription
g1The first geometry object that you want to specify.
g2The second geometry object that you want to specify.
gnThe 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)) |
+---------------------------+

ST_MakeValid

You can call the ST_MakeValid function to convert an invalid geometry object to a valid geometry object.

Syntax

geometry ST_MakeValid(geometry g);

Parameters

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