SQL Reference
Geospatial Functions
Spatial Relationships
{{ocientgeo}} spatial relationship functions use arguments to test for different types of spatial relationships st clusterdbscan returns the cluster number for each input geography based on a two dimensional implementation of the density based spatial clustering of applications with noise ( dbscan ) algorithm this function is a window function because the resulting value for an individual row depends on other rows and not just itself no frame specification is allowed this function supports sort orders, but it is unnecessary unless deterministic resolution of tie breaking in cluster determination is required this tie breaking happens only in situations where a point on the boundary of a cluster is equally valid for multiple clusters in these cases, the result depends on the sort order of the data for this reason, you might want to sort the data to force the deterministic behavior by using an order by in the over clause the function returns null for any row not included in a cluster syntax st clusterdbscan(geo, eps, min neighbors) 142,168,350 false 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 clusterdbscan(st point(1 5, 1 5), 1 0, 1) over(); output 1 st contains returns true if the first geographic argument, geo1 , contains the second geographic argument, geo2 geo1 contains geo2 only if all of these criteria are met n o point values of geo2 are located at the exterior of geo1 at least one point in the interior of geo2 lies in the interior of geo1 geographies do not contain their boundaries, therefore all point values in a linestring are interior, except for the endpoints, which are regarded as exterior boundaries if either geo1 or geo2 is null, then the function returns null syntax select st contains(geo1, geo2) 151,160,350 false 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 contains( st linefromtext('linestring(0 0, 1 1, 2 2)'), st point(1, 1)); output true st containsproperly this function is similar to st contains, but it has stricter criteria returns true if geo2 lies entirely in the interior of geo1 and does not intersect or touch the boundary or exterior points returns null if either geo1 or geo2 is null syntax st containsproperly(geo1, geo2) 151,156,350 false 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 examples in this example, the first geographic object is a polygon representing a simple square (0 0, 1 0, 1 1, 0 1) the second geographic object is a small linestring that spans from the point (0 25 0 25) to the point (0 75 0 75) because the line is located entirely within the polygon, never touching its boundaries, the function evaluates to true select st containsproperly( st makepolygon( st makeline( array \[st point(0, 0), st point(1, 0), st point(1, 1), st point(0, 1), st point(0, 0)])), st makeline(st point(0 25, 0 25), st point(0 75, 0 75))); output true the second geographic object is slightly altered in this example, where the line spans from (0 0) to (1 1) both of these endpoints touch the boundaries of the polygon, so the function now evaluates to false select st containsproperly( st makepolygon( st makeline( array \[st point(0, 0), st point(1, 0), st point(1, 1), st point(0, 1), st point(0, 0)])), st makeline(st point(0, 0), st point(1, 1))); output false st covers returns true if no point in geo2 is outside of geo1 returns null if either geo1 or geo2 is null syntax st covers(geo1, geo2) 151,155,350 false 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 covers( st makepolygon( st makeline( array \[st point(0, 0), st point(1, 0), st point(1, 1), st point(0, 1), st point(0, 0)])), st makeline(st point(0, 0), st point(1, 1))); output true st coveredby this function operates similarly to st covers, except the arguments are interchanged returns true if no point in geo1 is outside of geo2 returns null if either geo1 or geo2 is null syntax st coveredby(geo1, geo2) 150,159,350 false 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 coveredby( st point(1,1), st polygon('polygon((1 1, 2 1, 2 2, 1 1))')); output true st crosses returns true if two geospatial objects meet this criteria the intersection of the geospatial interiors is not empty the intersection is not equal to geo1 or geo2 neither geospatial object is a single point returns null if either argument is null syntax st crosses(geo1, geo2) 150,163,350 false 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 crosses( st linestring(st point(0,0), st point(2,2)), st linestring(st point(1,0), st point(1,5))); output true st disjoint returns true if the specified geographies have no intersection, including boundaries both geographic arguments can be different types this function is the opposite of the spatial relationships /#stintersects function syntax st disjoint(geo1, geo2) 150,156,350 false 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 disjoint( 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 true st dwithin returns true if the geographies are within a specified distance in meters syntax st dwithin(geo1, geo2, distance meters) 150,154,350 false 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 dwithin( st point('point(1 1)'), st point('point(0 0)'), 100\); output false st equals returns true if both geographies are spatially equal syntax st equals(geo1, geo2) 150,157,350 false 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 equals( st polygon(st linestring('linestring(10 10, 0 0, 0 0, 10 10)')), st polygon(st linestring('linestring(10 10, 0 0, 0 0, 10 10)'))); output true st intersects returns true if the specified geographies intersect, including boundaries this function is the opposite of the spatial relationships /#stdisjoint function syntax st intersects(geo1, geo2) 150,162,350 false 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 intersects( 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 false st isccw alias for spatial relationships /#stispolygonccw st isclosed returns true if an input linestring has starting and ending points that are equal if you specify a polygon or point value, the function returns true if the specified geography is not empty syntax st isclosed(line) 150,154,350 false 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 isclosed( st linestring('linestring(1 2, 1 3, 1 2)')); output true st ispolygoncw alias for boolnot(st isccw(poly)) returns true if an input polygon has an exterior that is clockwise if the polygon argument is empty, this function returns true if the argument is null, this function returns null syntax st ispolygoncw(poly) 150,161,350 false 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 ispolygoncw( st polygon('polygon((0 0,1 0,1 1,0 1,0 0))')); output false st ispolygonccw alias for st isccw returns true if an input polygon has an exterior that is counter clockwise if the polygon argument is empty, this function returns true if the argument is null, this function returns null syntax st ispolygonccw(poly) 150,153,350 false 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 ispolygonccw( st polygon('polygon((0 0,1 0,1 1,0 1,0 0))')); output true st isring returns true if the specified linestring is closed and does not intersect itself this function is equivalent to evaluating both spatial relationships /#stisclosed and spatial relationships /#stissimple if you specify a value other than linestring , the function throws an error syntax st isring(geo) 150,155,350 false 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 isring( st linestring('linestring(1 2, 2 4, 1 3, 1 2)')); output true st issimple for linestring values, this function returns false if any line segments intersect anywhere besides the endpoints for polygon values, the function returns false if either the exterior ring or any interior hole is not simple for point values, the function always returns true syntax st issimple(geo) 150,163,350 false 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 issimple( st polygon('polygon((1 2, 3 4, 3 5, 1 2))')); output true st isvalid returns true if the specified geospatial value is a well formed and valid geography according to the ogc standards linestring values are not valid if they are composed by more than one point with the same coordinates polygon values are valid if they are composed by one external ring (shell) and zero or more internal rings a ring is a linestring where only the first and last point intersect to each other all internal rings need to be contained inside the shell, and none of the rings should intersect with each other in more than one point syntax st isvalid(geo1) 132,171,350 false 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 isvalid( st linestring('linestring(1 2, 2 4)')); output true st overlaps returns true if both geographic arguments are of the same dimension and intersect, but neither contains the other returns null if either geo1 or geo2 is null syntax st overlaps(geo1, geo2) 112,198,350 false 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 overlaps( st linestring(st point(0,0), st point(2,2)), st linestring(st point(1,1), st point(3,3))); output true st pointinsidecircle returns true if the geographic object is inside a circle centered at the specified point coordinates and with the specified radius syntax st pointinsidecircle(geo, center x, center y, circle radius) 150,162,350 false 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 pointinsidecircle( st point(1, 3), 1, 2, 3000000); output true st relate returns the de 9im intersection string that represents the nature of the intersection with the specified geographies if you specify an intersection pattern as the third argument, this function returns true if the intersection of the geographies satisfies the pattern and false otherwise syntax st relate(geo1, geo2, \[, intersectionpattern]) true falsefalse unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type false unhandled content type examples determine the nature of the intersection between the point (0 0, 0 0) and the line from 1 0 to 1 0 this function returns the de 9im string 0fffff102 , which means that the boundaries do not intersect at all, but there is evidence of an intersection at the zero, one, and two dimensional spaces select st relate(st point(0 0, 0 0), st linestring('linestring( 1 0, 1 0)')); output 0fffff102 determine whether the intersection between the point (0 0, 0 0) and line from 1 0 to 1 0 follows the de 9im pattern string tf fff102 this function returns true , which means the nature of the intersection between these two geographies conforms to this pattern select st relate(st point(0 0, 0 0), st linestring('linestring( 1 0, 1 0)'), 'tf fff102'); output true st touches returns true if the only point values in common between the two geographic arguments lie in the union of their boundaries syntax st touches(geo1, geo2) 107,196,350 false 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 touches( st linestring('linestring(1 0 3 0, 0 0 3 0)'), st linestring('linestring(1 0 3 0, 2 0 3 0)')); output true st within alias for spatial relationships /#stcontains related links data types docid\ ogtviwl gtbgv0chhrh 3 spatial measurement docid 7gkks8lo euyq6 kadujb spatial operators docid\ aawcrtqvk1nkc0hcgthci