Spatial Operators

{{ocientgeo}} spatial noperators perform geometry calculations on geospatial data to return a different type of geospatial data st boundingdiagonal returns the diagonal linestring from the minimum point to the maximum point of the bounding box that docid\ pifo3jqjej4vo8mp3zkyd returns syntax st boundingdiagonal(geo) 136,174,350 2 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type example select st boundingdiagonal( st polygon(st linestring('linestring(1 2, 1 3, 1 3, 1 2)')) ); output linestring(1 2,1 3) st buffer returns a geography that contains all points where the distance from the geography is less than or equal to the specified distance the function simplifies the geometry before buffering unless you specify the simplification tolerance argument with a zero or negative value the output of the function is an array of polygons for point and linestring data types, the returned array contains one polygon only syntax st buffer(geo, radius \[, num segments per quarter circle] \[, simplification tolerance] \[, maximum radius error] \[, join style] \[, miter limit] \[, endcap style] \[, side style]) 168,127,350 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type example select st buffer( st polygon('polygon((0 0, 1 0, 1 1, 0 0))'), 2000, 8, 500 0, 10 0, 'miter', 2 0); output \[polygon((2 202700241764562e 18 0 017986411844962975, 1 0179863625702694 0 0179863616840571, 1 0179892379654352 1 031486286416526, 1 009550207729886 1 0349816974070565, 0 03498171178141835 0 009548649645081482, 0 03148633724650228 0 01798632287046757, 2 202700241764562e 18 0 017986411844962975))] st closestpoint returns the two dimensional point of one specified geospatial object that is closest to a second specified geospatial object this function works with any geospatial type if either geospatial argument is empty, the function returns null syntax st closestpoint(geo1, geo2 \[, use spheroid] ) 136,175,350 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type example select st closestpoint( st polygon(st linestring('linestring(2 3, 2 4, 2 4, 2 3)')), st polygon(st linestring('linestring(1 2, 1 4, 1 5, 1 2)'))); output point(2,3) st convexhull returns a polygon representing the convex hull of the input geometry the convex hull is the smallest convex geometry that encloses the input geometry if the geo argument is empty, the function returns an empty polygon syntax st convexhull(geo) 136,175,350 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type example select st convexhull( st polygon('polygon((0 0,1 0,1 1,0 5 0 5,0 3 0 3,0 1,0 0))')); output polygon((0 0 0 0, 1 0 0 0, 1 0 1 0, 0 0 1 0, 0 0 0 0)) st differencearray returns an array containing any geospatial objects that are present in the first specified geospatial argument that are not found in the second geospatial argument syntax st differencearray(geo1, geo2) 100,199,350 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type example in this example, the function returns linestring(2 0 2 0, 3 0 3 0) because that portion of the first linestring argument is not present in the second argument select st differencearray( st linestring('linestring(1 1, 2 2, 3 3)'), st linestring('linestring(1 1, 2 2)')); output \[linestring(2 0 2 0, 3 0 3 0)] st envelope returns a polygon that represents the minimum bounding box for the specified geography if the specified argument is empty, the function returns an empty polygon a single point argument results in a single point syntax st envelope(geo) 103,198,350 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type example select st envelope( st polygon(st linestring('linestring(10 10, 0 0)'))); output polygon((0 0,10 0,10 10,0 10,0 0)) st expand returns the bounding box of a specified geospatial value, which is expanded by a specified length the unit of length is in meters syntax st expand(geo1, units to expand) 165,148,350 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type example st expand(st polygon('polygon((1 1, 2 1, 2 2, 1 1))'), 1 0); output polygon((0 99 0 99, 1 33 0 99, 1 66 0 99, 2 01 0 99, 2 00 2 00, 1 66 2 00, 1 33 2 00, 0 99 2 00, 0 99 0 99)) st exteriorring returns a linestring that represents the exterior ring of a provided polygon value if you specify a geography value that is not a polygon , the function returns null syntax st exteriorring(polygon) 136,160,350 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type example select st exteriorring(st polygon('polygon((1 1, 3 1, 2 2, 1 1))')); output linestring(1 0 1 0, 3 0 1 0, 2 0 2 0, 1 0 1 0) st flipcoordinates returns a new geographic object with the x and y coordinates switched using the specified argument the return type is the same as the specified type syntax st flipcoordinates(geo) 136,171,350 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type example st flipcoordinates(st point('point(1 3)')); output point(3 0 1 0) st force2d convert a geographic object into a two dimensional geography the output representations only have the x and y coordinates the returned type is the same as the specified type syntax st force2d(geo1) 136,178,350 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type example select st force2d(st point('point(1 1)')); output point(1 0 1 0) st interiorringn returns a linestring representing the interior ring of the specified polygon , which is specified by its index (1 indexed) returns null if the index is out of range, or if you specify a non polygon geography syntax st interiorringn(polygon, index) 110,191,350 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type example select st interiorringn( st polygon( st linestring(st point \[]\(st point(1, 2))), st linestring \[]\(st linestring(st point \[]\(st point(1, 2))))), 1\); output linestring(1 2) st intersectall returns the intersection of all geographies in the specified array all geographies in the array must be the same type the st intersectall function ignores null values in the array the output geography has the same type as the input geometries syntax st intersectall(array) 106,195,350 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type example select st intersectall( st point \[]\(st point(0, 0), st point(1, 1), st point(2, 2))); output point\[] st intersectionarray returns a geography that represents the point set intersection of two geographies if the two geographies are of different types, the function returns an array of the smallest geography for example, if geo1 is a point , and geo2 is a polygon , the function returns an array of point values syntax st intersectionarray(geo1, geo2) 110,202,350 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type example this example compares two polygons to find any intersections because both objects are polygon types, the result is also a polygon containing all intersection points select st intersectionarray( st polygon(st linestring(st point(1,2),st point(3,4))), st polygon(st linestring(st point(1,2),st point(4,5)))); output \[polygon((1, 2))] st longestline returns the longest linestring between two specified geospatial arguments syntax st longestline(geo1, geo2, use spheroid) 135,173,350 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type example select st longestline( st makeline('linestring(0 3, 1 3)'), st makeline('linestring(2 3, 0 3)')); output linestring(0 0 3 0, 2 0 3 0) to use st longestline as a spatiotemporal function to calculate the longest line between two points at a concurrent time, see the docid\ m7egwhp4eqnp5gr81gvwq function st makeenvelope alias for st envelope(st linestring(st point(xmin, ymin), st point(xmax, ymax))) returns a polygon with vertices representing the minimum bounding box for the specified coordinates syntax st makeenvelope(xmin, ymin, xmax, ymax) 136,167,350 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type example select st makeenvelope(0, 0, 10, 10); output polygon((0 0 0 0, 10 0 0 0, 10 0 10 0, 0 0 10 0, 0 0 0 0)) st minimumboundingcircle returns the smallest circle polygon that contains the specified geographic object the function requires an additional argument to define the number of segments for inclusion in each quarter of the returned circle syntax st minimumboundingcircle(geo, num segs per qt circ) 150,150,350 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type example select st minimumboundingcircle(st polygon('polygon((1 1, 2 1, 2 2, 1 1))'), 3); output polygon((1 49 2 91, 0 27 0 79, 2 72 0 79, 1 49 2 91)) st multidifferencearray returns an array of geographies representing the parts of the union of the geographies in the first array that do not intersect with the union of geographies in the second array this operation is not symmetric, meaning st multidifferencearray(array1, array2) does not produce the same results as st multidifferencearray(array2, array1) unless both arrays are identical the function ignores null types for types besides null, all geographies in both arrays must be of the same type the output geographies are the same type as the input geographies syntax st multidifferencearray(array1, array2) 136,172,350 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type example st multidifferencearray( point\[]\(st point(0, 0), st point(1, 1)), point\[]\(st point(1, 1), st point(2, 2))); output \['point(0 0 0 0)'] st multiintersectionarray when you specify two arrays of geospatial objects, this function returns an array of any intersections the function treats each specified array as a union of all its geospatial values all geographies in both arrays must be of the same type, not counting null values, which the function ignores the output geographies are the same type as the input geographies syntax st multiintersectionarray(array1, array2) 108,198,350 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type example select st multiintersectionarray( point\[]\(st point(0, 0), st point(1, 1)), point\[]\(st point(1, 1), st point(2, 2))); output \['point(1 0 1 0)'] st multisymdifferencearray alias for st multiunionarray(array1, array2) st multiintersectionarray(array1, array2) when you specify two arrays of geospatial objects, this function returns an array of any geospatial values that do not intersect the function treats each specified array as a union of all its geospatial values all geographies in both arrays must be of the same type, not counting null values, which the function ignores the output geographies are the same type as the input geographies syntax st multisymdifferencearray(array1, array2) 110,197,350 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type example select st multisymdifferencearray( point\[]\(st point(0, 0), st point(1, 1)), point\[]\(st point(1, 1), st point(2, 2))); output \['point(0 0 0 0)','point(2 0 2 0)'] st multiunionarray when you specify two arrays of geospatial objects, this function returns one array that represents a union of all geographies in both arrays all geographies in both arrays must be the same type the function ignores null values in the arrays the output geographies are the same type as the input geographies syntax st multiunionarray(array1, array2) 106,201,350 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type example select st multiunionarray( point\[]\(st point(0, 0), st point(1, 1)), point\[]\(st point(1, 1), st point(2, 2))); output \['point(1 0 1 0)','point(2 0 2 0)','point(0 0 0 0)'] st nrings returns the number of rings of the specified polygon , including both interior and exterior rings if you specify a geography object that is not a polygon , this function returns 0 syntax st nrings(geo) 106,189,350 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type example select st nrings( st polygon('polygon((1 0 2 0, 1 0 4 0, 1 0 5 0, 1 0 2 0))')); output 1 st numinteriorrings or st numinteriorring returns the number of interior rings of the specified polygon if you specify a geography object that is not a polygon , this function returns 0 syntax st numinteriorrings(polygon) st numinteriorring(polygon) 106,200,350 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type example select st numinteriorrings( st polygon( st linestring(st point \[]\(st point(1, 2))), st linestring \[]\(st linestring(st point \[]\(st point(1, 2)))))); output 1 st pointonsurface returns a point guaranteed to intersect the specified geospatial object syntax st pointonsurface(geo1) 101,205,350 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type example select st pointonsurface(st polygon('polygon((1 1, 2 1, 2 2, 1 1))')); output point(1 0 1 0) st project returns a point by projecting a distance and an azimuth value from the specified starting point value the distance must be in meters, and azimuth must be in radians syntax st project(point, distance, azimuth) 111,195,350 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type example select st project(st point(1, 3), 100, 100); output point(0 99 3 00) st reduceprecision returns a new geospatial object with all point values rounded to the specified decimal precision syntax st reduceprecision(geo, precision) 111,195,350 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type example select st reduceprecision(st point('point(1 21 1 34)'), 0 1); output point(1 2 1 3) st removerepeatedpoints returns a new geospatial object with no repeated point values the returned type is the same as the specified type syntax st removerepeatedpoints(geo) 102,198,350 2 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type example select st removerepeatedpoints(st linestring('linestring(1 2, 1 2, 2 1, 1 0)')); output linestring(1 0 2 0, 2 0 1 0, 1 0 0 0) st reverse returns a new geospatial object with the vertexes reversed the returned type is the same as the specified type syntax st reverse(geo) 101,206,350 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type example select st reverse(st linestring('linestring(1 2,1 10)')); output linestring(1 0 10 0, 1 0 2 0) st segmentize returns a geospatial object that the function modifies to have no segment longer than the specified max segment length in meters syntax st segmentize(geo, max segment length) 148,155,350 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type example select st segmentize( st polygon('polygon((1 1, 2 1, 2 2, 1 1))'), 150000 0); output polygon((1 0 1 0, 2 0 1 0, 2 0 2 0, 1 49 1 50, 1 0 1 0)) st shortestline returns the shortest linestring between two specified geospatial arguments syntax st shortestline(geo1, geo2, use spheroid) 136,172,350 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type example select st shortestline( st makeline('linestring(4 3, 10 3)'), st makeline('linestring(0 1, 0 3)')); output linestring(0 0 3 0, 4 0 3 0) to use st shortestline as a spatiotemporal operator to calculate the shortest line between two points at a concurrent time, see the docid\ m7egwhp4eqnp5gr81gvwq function in the spatiotemporal section st simplify returns a simplified version of the specified geography, which is either a point or linestring the function simplifies the input geography by replacing nearly straight edges that contain multiple points with a single straight edge the input geography does not change by more than the specified tolerance, which you specify as a double in meters syntax st simplify(geo, tolerance) 112,198,350 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type example select st simplify( st linestring('linestring(0 0, 0 05 0, 0 1 0, 0 15 0, 2 0)'), 1); output linestring(0 0, 2 0) st simplifyarray returns a polygon array that represents a simplified version of the specified geography, which is either a point , linestring , or polygon the function simplifies the input geography by replacing nearly straight edges that contain multiple points with a single straight edge the input geography does not change by more than the specified tolerance, which you specify as a double in meters syntax st simplifyarray(geo, tolerance) true 213,213,214 unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type example in this example, st simplifyarray evaluates a polygon with an outer ring of 13 points and an inner ring with four points the example sets a tolerance argument of 10,000 meters, meaning that is the maximum distance that the function simplifies select st simplifyarray( st polygon( 'polygon((0 0, 1 0, 1 0 49, 1 01 0 49, 1 01 0 51, 1 0 51, 1 1, 0 1, 0 0 51, 0 01 0 51, 0 01 0 49, 0 0 49, 0 0), ( 0 001 0 5, 1 001 0 5, 0 5 0 4, 0 001 0 5))' ), 10000\); output \[polygon((0 0 0 5000000762285838, 1 0 0 500000076228584, 1 0 1 0, 0 0 1 0, 0 0 0 5000000762285838)), polygon((1 0 0 4998004375649452, 0 5 0 4, 0 0 0 4998004375649452, 0 0 0 0, 1 0 0 0, 1 0 0 4998004375649452))] as shown in the output, the st simplifyarray function has reduced the size of the outer polygon from 13 points to 5 note that the function actually increases the number of points in the inner ring, going from four to six the increase is due to how close the inner ring is to the edges of the outer ring taken altogether, the function still reduced the total number of points in the polygon st snaptogrid returns a new geography value with all point values rounded to the specified precision this function is similar to docid\ pifo3jqjej4vo8mp3zkyd but it allows you to specify the precision for the x and y coordinates separately syntax st snaptogrid(geo1, precision x, precision y) 135,174,351 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type example select st snaptogrid( st makepoint(1 1, 3 11), 1, 0 1); output point(1 0 3 1) st symdifferencearray returns a geographic array that contains the parts that are not common between two geographic objects, geo1 and geo2 syntax st symdifferencearray(geo1, geo2) 105,207,350 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type example select st symdifferencearray( st linestring('linestring(1 1, 2 2)'), st linestring('linestring(1 1, 2 2, 3 3)')); output \[linestring(2 0 2 0, 3 0 3 0)] st unionarray performs a union of the input geography values to produce a geographic array syntax st unionarray(geo1, geo2) 108,200,350 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type example select st unionarray( st linestring('linestring(0 0, 1 1, 2 2)'), st linestring('linestring(1 1, 2 2, 3 3)')); output \['linestring(0 0 0 0, 1 0 1 0, 2 0 2 0, 3 0 3 0)'] related links docid\ czxgepf3prq9ufwhwbiuy docid\ vgbw5fb dtz1vzsb6lria docid\ j48pdvqo xrpcwp eecyi