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

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?

Replay

The four intersection possibilities are:

  1. If the interiors intersect (T * * * * * * * *)
  2. If A interior intersects the B boundary (* T * * * * * * *)
  3. If A boundary intersects the B interior (* * * T * * * * *)
  4. 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:

  1. Will be true if the point is part way through the line.
    • point (1 1), linestring (0 0, 2 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)
  3. 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)
  4. 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.

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.

Category: sql server Time: 2013-08-29 Views: 9

Related post

iOS development

Android development

Python development

JAVA development

Development language

PHP development

Ruby development

search

Front-end development

Database

development tools

Open Platform

Javascript development

.NET development

cloud computing

server

Copyright (C) avrocks.com, All Rights Reserved.

processed in 0.236 (s). 12 q(s)