How To: Use St_relate to Determine if Two End Points of Two Lines Intersect

Summary

SQL can be used to detect if there is a spatial relationship between the end points of two lines by using the st_relate operator.

Discovering and validating which line's end point is coincident to an existing line's end point (to verify connectivity between features) can be accomplished by using the st_relate operator.

Procedure

The following example demonstrates how to detect lines whose end points intersect a specific sewer line end point. Such an operation may need to be performed for validating end points for two lines intersecting at the same x, y location.

The example is selecting the ObjectID from the sewer table where the sewer line ObjectID attribute equals 1 and where any other sewer line's end points intersect the selected sewer's end points. By using the st_relate operator and specifiying 0 (which represents a point dimension) for the mask value when comparing boundary/boundary conditions, only sewer lines at their boundaries with a dimension of 0 (which is a point) are returned in the queries result set.

For example, if another sewer line is partially co-linear (over lapping) to the selected sewer, it would not be returned because its dimension would be 1 (because part of the sewer line's interior intersects the selected sewer's boundary) and the statement is only testing boundary/boundary conditions.

Code:
SQL> SELECT b.objectid 
  2  FROM sewers a, sewers b 
  3  WHERE a.objectid = 1 AND b.objectid <> 1 
  4  AND sde.st_relate(a.shape,b.shape,'****0****') = 1 
  5  AND sde.st_envintersects(b.shape,a.shape) = 1;

  OBJECTID
----------
         5

Notice, in the above statement, the additional filter st_envintersects. Without the st_envintersects filter, the query must compare every sewer line with the one sewer where ObjectID equals 1, checking if the end points are intersecting. By applying the spatial filter with the st_envintersects operator, the input, a.sewers shape, is applied as a filter against the b.sewers shape to only compare those sewer lines that intersect a.sewers shape's envelope.

Article ID: 000010442

Software:

Legacy Products

Discover more on this topic

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options

Use st_relate to determine if two end points of two lines intersect

Summary

Procedure

Receive notifications and find solutions for new or common issues

Discover more on this topic