SQL Server STRelate equivalent of Oracles SDO_RELATE(...mask=anyinteract)

Question

In Oracle Spatial, we are running checks on a graph. The Check should provide Nodes, which are marked as endnodes (Attribute "EDGEDEGREE" equals 1) but are in fact located somewhere along an edge. We are using Oracles Relate function to do this with the spatial relation pattern "mask=anyinteract"

In Oracle the Query looks like this:

SELECT 'Node' AS OBJECTTYPE,n.objectid AS OBJECTID,...
  FROM NODE n, EDGE e
  WHERE n.edgedegree=1 
  AND n.neighbornode_objectid IS NULL
  AND SDO_RELATE(n.SHAPE,e.SHAPE,'mask=anyinteract')='TRUE'
  AND e.nodefrom_objectid<>n.objectid 
  AND e.nodeto_objectid<>n.objectid;

Now we want to do the same on SQL Server (2012 has advanced spatial relation functions now). STRelate uses DE-9IM Sequences to do this. mask=anyinteract corresponds to "non-disjoint" or simply "intersect", but there are 4 different types of intersect. Which DE-9IM Sequence corresponds best to mask=anyintersect for the following T-SQL Query?

SELECT 'Node' AS OBJECTTYPE,n.objectid AS OBJECTID,...
  FROM NODE n, EDGE e
  WHERE n.edgedegree=1 
  AND n.neighbornode_objectid IS NULL
  AND n.SHAPE.STRelate(e.SHAPE.MakeValid(),'<?DE-9IM Sequence?>')='TRUE' 
  AND e.nodefrom_objectid<>n.objectid 
  AND e.nodeto_objectid<>n.objectid;

Could you please explain me these four intersection possibilities, when relating a point on a line?

travis · Accepted Answer · 2013-08-29T20:45:49.057

The four intersection possibilities are:

If the interiors intersect (T * * * * * * * *)
If A interior intersects the B boundary (* T * * * * * * *)
If A boundary intersects the B interior (* * * T * * * * *)
If the boundaries intersect (* * * * T * * * *)

For a point, there is no boundary and the interior is defined as the point. For a line, the boundary is usually the start and end point, with the interior being the rest of the geometry. Unless the line is closed, then the boundary is empty.

So for a point on line situation:

Will be true if the point is part way through the line.
- point (1 1), linestring (0 0, 2 2)
Will be true if the point is at one of the end points, and the point is specified first.
- point (2 2), linestring (0 0, 2 2)
Will be true if the point is at one of the end points, and the point is specified second.
- linestring (0 0, 2 2), point(2 2)
Will never be true because points do not have boundaries.

For more information, you can check out the feature specification for the well known geometry types. I find the Esri stuff good, although hard to find. For the feature specification you can read Constructor functions for ST_Geometry and for the relationship definitions you can read Relational functions for ST_Geometry.

I found the SQL Server doc:

Boundary, Interior, and Exterior

Extended Static Geometry Methods, then follow the link for OGC Specifications, Simple Feature Access Part 2 – SQL Options. You can download the 'OpenGIS Implementation Specification for Geographic information - Simple feature access - Part 2: SQL option' pdf from there.

Thanks for that explanation, I thought it would be just the other way round: I thought interior of point and line isn't defined, so the only possibility that can get true is 4. Looks like I have to read some specifications. Thank you very much for the links. — Jürgen Zornig, Aug 30 '13 at 07:24
NP, if you have multipart linestrings the interior/boundary boundary gets really interesting. You should definitely read through the spec for that. — travis, Aug 30 '13 at 18:23

score 0 · Answer 2 · edited Apr 13 '17 at 12:33

As the text of your wiki/DE-9IM link say, the strings of the DE-9IM "offers a full descriptive assertion about the two input geometries", so, queries with strings are "less general/more descritive" expressions, and queries with spatial predicates (TOUCH, EQUAL, INSIDE, CONTAINS, etc.) are "more general/less descriptive" expressions. So the two kinds of expressions have its best-niche applications.

There are a lack of DE-9IM-strings in Oracle, and a lack of predicate-mask in SQL-SERVER (and PostGIS also!)... But the strings are more general, and can be interpreted as an "ckecklist of predicates" as showed by this answer.

A "more generic and defenitive" solution is to implement the SDO_RELATE() function in your SQL-SERVER database.

You can adapt my "pure SQL" function ST_relate_summary() to SQL-SERVER, and, then, adapt to the Oracle's jargon, adding OVERLAPBDYDISJOINT, OVERLAPBDYINTERSECT, and ANYINTERACT; I can help you in this task if you want.

SQL Server STRelate equivalent of Oracles SDO_RELATE(...mask=anyinteract)

2 Answers2