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 /#stenvelope returns syntax st boundingdiagonal(geo) argument data type description geo point , linestring , or polygon a geospatial object to be measured for the diagonal length of its bounding box 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]) argument data type description geo point , linestring , or polygon a geospatial object, specified as a point, linestring, or polygon, which the function buffers radius double radius, specified as a double, that indicates the distance to buffer the geospatial object in meters negative values cause polygons to shrink also, negative values cause the result to be empty for points and linestrings num segments per quarter circle bigint optional the number of segments to use per quarter circle when parts of the buffer are round, specified as a bigint only use if the join style or endcap style value is set to round defaults to 8 simplification tolerance double optional value that the function passes to the st simplifyarray function prior to buffering if you specify a zero or negative value, the function skips simplification of the geometry maximum radius error double optional maximum radius error, specified as a double the returned buffer might be slightly larger than the radius value by this amount this value is required because arcs of great circles cannot be parallel use this argument for linestrings and polygons only defaults to 10 0 join style string optional the type of join to use at the corners where segments of the geometry meet, specified as a string supported styles are round , bevel and miter use this argument for linestrings and polygons only defaults to round miter limit double optional miter limit, specified as a double use this argument only if the join style value is set to miter a mitered corner is cut off at a distance of miter ratio radius from the corner point of the geometry use this argument for linestrings and polygons only defaults to 1 0 endcap style string optional the type of end cap for use at the ends of linestrings, specified as a string supported styles are round , square , and flat use this argument for linestrings only defaults to round side style string optional the side, specified as a string, that denotes the side for buffering the geometry supported styles are both , left , and right use this argument for linestrings only defaults to both 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] ) argument data type description geo1 point , linestring , or polygon a geospatial object to be computed to determine its closest point value relative to geo2 geo2 point , linestring , or polygon a second geospatial object to be compared to geo1 to find the closest point value of geo1 use spheroid boolean optional if you set this value to true , this function uses a spheroid model instead of a spherical model defaults to false 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) argument data type description geo point , linestring , or polygon a geospatial object to be computed for a polygon of its convex hull if geo is a point or a single point linestring or polygon , the function returns a polygon containing only that point value if geo is null, the function returns null 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) argument data type description geo1 point , linestring , or polygon a geospatial object to be compared to geo2 to find any unique geospatial values geo2 point , linestring , or polygon a second geospatial object used to compare with geo1 to find unique values the function captures only objects that are unique to geo1 , and not those unique to geo2 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) argument data type description geo point , linestring , or polygon a geospatial object to be computed for the vertices of its bounding box 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) argument data type description geo point , linestring , or polygon a geospatial object to be computed for the vertices of its bounding box with the added units to expand units to expand double an additional distance to expand the bounding box of geo specify this argument only in meters 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) argument data type description polygon polygon a polygon object to be computed for a linestring of its perimeter 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) argument data type description geo point , linestring , or polygon a geographic object for switching its x and y coordinates 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) argument data type description geo point , linestring , or polygon a geographic object to be converted to a two dimensional object 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 specified a non polygon geography syntax st interiorringn(polygon, index) argument data type description polygon polygon a polygon object to be computed for its interior ring index integer an index that represents the specific interior ring of the polygon value to be captured interior ring indexes are 1 based, meaning the first object has an index of 1 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) argument data type description array array of point , linestring , or polygon an array of geographic objects, which all must be the same 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 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) argument data type description geo1 point , linestring , or polygon a geographic object to be compared to geo2 to find a set of intersections geo2 point , linestring , or polygon a geographic object to be compared to geo1 to find a set of intersections 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) argument data type description geo1 point , linestring , or polygon a geospatial object for the determination of the longest linestring relative to geo2 geo2 point , linestring , or polygon a geospatial object for the determination of the longest linestring relative to geo1 use spheroid boolean optional if you set this value to true , this function uses a spheroid model instead of a spherical model defaults to false 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\ wxa0sylklnrbwwv tlrxy 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) argument data type description xmin double the minimum x value for the bounding box ymin double the minimum y value for the bounding box xmax double the maximum x value for the bounding box ymax double the maximum y value for the bounding box 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 segment for inclusion in each quarter of the returned circle syntax st minimumboundingcircle(geo, num segs per qt circ) argument data type description geo point , linestring , or polygon a geospatial object for the determination of the smallest possible circle polygon to encompass it num segs per qt circ integer the number of segments to be included in each quarter of the returned circle polygon 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) argument data type description array1 array of point , linestring , or polygon values an array of geographic objects to be compared to the geographic objects in array2 the function returns any values in this array that do not intersect with array2 array2 array of point , linestring , or polygon values an array of geographic objects to be compared to the geographic objects in array1 the function returns only values that do not intersect in array1 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) argument data type description array1 array of point , linestring , or polygon values an array of geographic objects to be compared to the geographic objects in array2 for any intersections the data types in this array must match the data types in array2 unless they are null array2 array of point , linestring , or polygon values an array of geographic objects to be compared to the geographic objects in array1 for any intersections the data types in this array must match the data types in array1 unless they are null 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) argument data type description array1 array of point , linestring , or polygon values an array of geographic objects to be compared to the geographic objects in array2 for any values that do not intersect the data types in this array must match the data types in array2 unless they are null array2 array of point , linestring , or polygon values an array of geographic objects to be compared to the geographic objects in array1 for any values that do not intersect the data types in this array must match the data types in array1 unless they are null 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) argument data type description array1 array of point , linestring , or polygon values an array of geographic objects to be combined with array2 the data types in this array must match the data types in array2 unless they are null array2 array of point , linestring , or polygon values an array of geographic objects to be combined with array1 the data types in this array must match the data types in array1 unless they are null 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) argument data type description geo polygon an object to be computed to return its total number of rings 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) argument data type description polygon polygon an object to be computed to return its total number of interior rings 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) argument data type description geo point , linestring , or polygon an object to be computed for an intersection point value 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) argument data type description point point a starting point for locating a returned destination by using the distance and azimuth values distance double a distance value in meters azimuth double an azimuth value in radians 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) argument data type description geo point , linestring , or polygon a geospatial object to be rounded to the specified precision value precision double a decimal value that defines the level of precision for returned values for example, a precision of 0 01 returns values rounded to the hundredth decimal do not use digits other than 0 and 1 to represent the precision 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) argument data type description geo point , linestring , or polygon a geospatial object for removal of any duplicate point values 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) argument data type description geo point , linestring , or polygon a geospatial object for vertex reversal 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) argument data type description geo point , linestring , or polygon a geospatial object for segment modification max segment length double the maximum length of a segment to modify the specified geo object this value must be in meters 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) argument data type description geo1 point , linestring , or polygon a geospatial object for the determination of the shortest linestring relative to geo2 geo2 point , linestring , or polygon a geospatial object for the determination of the shortest linestring relative to geo1 use spheroid boolean optional if you set this argument to true , this function uses a spheroid model instead of a spherical model defaults to false 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\ wxa0sylklnrbwwv tlrxy 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) argument data type description geo point or linestring a geospatial object to be simplified tolerance double a value that represents the maximum number of meters to simplify in the specified geography 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) argument data type description geo point , linestring , or polygon a geospatial object to be simplified tolerance double a value that represents the maximum number of meters to simplify in the specified geography 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 precisions this function is similar to /#streduceprecision but it allows you to specify the precision for the x and y coordinates separately syntax st snaptogrid(geo1, precision x, precision y) argument data type description geo point , linestring , or polygon a geospatial object to be rounded to the specified precision values precision x double a decimal value representing the level of precision for the x value for example, a precision of 0 01 returns values rounded to the hundredth decimal do not use digits other than 0 and 1 to represent the precision precision y double a decimal value representing the level of precision for the y value for example, a precision of 0 1 returns values rounded to the tenth decimal do not use digits other than 0 and 1 to represent the precision 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) argument data type description geo1 point , linestring , or polygon a geographic object to be compared to the geographic objects in geo2 geo2 point , linestring , or polygon a geographic object to be compared to the geographic objects in geo1 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) argument data type description geo1 point , linestring , or polygon a geographic object to be unified with the geographic values in geo2 geo2 point , linestring , or polygon a geographic object to be unified with the geographic values in geo1 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\ ogtviwl gtbgv0chhrh 3 docid 7gkks8lo euyq6 kadujb docid\ yna2fagkyiddm kxv4zgw