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 ( https //en wikipedia org/wiki/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) argument data type description geo point , linestring , or polygon a geospatial object to be grouped into a cluster based on the dbscan algorithm eps double a numeric argument that controls the separation between objects before they are considered a new cluster the eps argument is in meters min neighbors bigint the minimum number of objects within the appropriate distance of each other that need to exist before it is considered a cluster 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) argument data type description geo1 point , linestring , or polygon a geospatial object to be compared to geo2 the point values in geo1 are evaluated to determine if they are contained within geo2 geo2 point , linestring , or polygon a geospatial object to be compared to geo1 the point values in geo2 are evaluated to determine if they are contained within geo1 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) argument data type description geo1 point , linestring , or polygon a geospatial object to be compared to geo2 the point values in geo1 are evaluated to determine if they are contained within geo2 geo2 point , linestring , or polygon a geospatial object to be compared to geo1 the point values in geo2 are evaluated to determine if they are contained within geo1 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) argument data type description geo1 point , linestring , or polygon a geospatial object to be compared to geo2 the point values in geo2 are evaluated to determine if they are contained within geo2 geo2 point , linestring , or polygon a geospatial object to be compared to geo1 the point values in geo2 are evaluated to determine if they are contained within geo1 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) argument data type description geo1 point , linestring , or polygon a geospatial object to be compared to geo2 the point values in geo1 are evaluated to determine if they are contained within geo2 geo2 point , linestring , or polygon a geospatial object to be compared to geo1 the point values in geo1 are evaluated to determine if they are contained within geo2 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) argument data type description geo1 point , linestring , or polygon a geospatial object to be compared to geo2 to evaluate whether they cross geo2 point , linestring , or polygon a geospatial object to be compared to geo1 to evaluate whether they cross 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 /#stintersects function syntax st disjoint(geo1, geo2) argument data type description geo1 point , linestring , or polygon a geospatial object to be compared to geo2 to evaluate whether they intersect geo2 point , linestring , or polygon a geospatial object to be compared to geo1 to evaluate whether they intersect 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) argument data type description geo1 point , linestring , or polygon a geospatial object to be compared to geo2 to evaluate whether they are within the specified distance distance meters geo2 point , linestring , or polygon a geospatial object to be compared to geo1 to evaluate whether they are within the specified distance distance meters distance meters double a numeric value that represents the number of meters to evaluate the distance between geo1 and geo2 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) argument data type description geo1 point , linestring , or polygon a geospatial object to be compared to geo2 to evaluate whether they are equal geo2 point , linestring , or polygon a geospatial object to be compared to geo1 to evaluate whether they are equal 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 /#stdisjoint function syntax st intersects(geo1, geo2) argument data type description geo1 point , linestring , or polygon a geospatial object to be compared to geo2 to evaluate whether they intersect geo2 point , linestring , or polygon a geospatial object to be compared to geo1 to evaluate whether they intersect 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 /#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) argument data type description line point , linestring , or polygon if you specify a linestring object, the function evaluates it to determine whether its starting and ending points are equal if you specify a polygon or point value, the function returns true if the specified geography is not empty 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) argument data type description poly polygon a polygon object to evaluate to determine whether its point values align in a clockwise direction 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) argument data type description poly polygon a polygon object to evaluate to determine whether its point values align in a counter clockwise direction 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 /#stisclosed and /#stissimple if you specify a value other than linestring , the function throws an error syntax st isring(geo) argument data type description line linestring a linestring object to evaluate to determine whether its starting and ending points are equal and whether it has no intersections 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) argument data type description geo point , linestring , or polygon a geospatial object to evaluate to determine if any points intersect 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 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 https //www ogc org/standard/sfa/ 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) argument data type description geo point , linestring , or polygon a geospatial object to evaluate to determine if it is a well formed geography 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) argument data type description geo1 point , linestring , or polygon a geospatial object for evaluation if it is shaped similarly and intersects geo2 geo2 point , linestring , or polygon a geospatial object for evaluation if it is shaped similarly and intersects geo1 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) argument data type description geo point , linestring , or polygon a geospatial object for evaluation if it is contained within a circle of the specified dimensions center x double the x coordinate of the center of the circle to evaluate center y double the y coordinate of the center of the circle to evaluate circle radius double the radius in meters of the circle to evaluate example select st pointinsidecircle( st point(1, 3), 1, 2, 3000000); output true st relate returns the https //postgis net/workshops/postgis intro/de9im html 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]) parameter data type description geo1 point , linestring , or polygon a geospatial object to relate with geo2 geo2 point , linestring , or polygon a geospatial object to relate with geo1 intersectionpattern string optional intersection pattern to query the nature of the intersection of geo1 and geo2 against must contain exactly 9 characters, corresponding to the component (interior, boundary, or exterior) intersections considered by de 9im supported characters are t — components intersect f — components do not intersect 0 — c omponent intersection is zero dimensional 1 — c omponent intersection is 1 dimensional 2 — c omponent intersection is 2 dimensional — specify n o restriction on the component intersection 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) argument data type description geo1 point , linestring , or polygon a geospatial object for evaluation if it touches geo2 geo2 point , linestring , or polygon a geospatial object for evaluation if it touches geo1 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 /#stcontains related links docid\ ogtviwl gtbgv0chhrh 3 docid 7gkks8lo euyq6 kadujb docid\ aawcrtqvk1nkc0hcgthci