Current Oracle Spatial Blog Articles
I had someone contact me the other day and ask:
“I find your articles very helpful and interesting. I was wondering if you can guide me to a link or something that may contain how to figure out surrounding blocks to a given block.”
I am assuming that the person wants to know how to find all polygons that adjoin an adjacent polygon. The following tutorial describes how I would go about this in Oracle Spatial.
First let’s select all parcels that share any sort of boundary with a particular parcel. That is, those that share at a common corner (only) or a common line.
Now, what if we wanted to select those that shared only a common point or just a common line?
From what I can see of the documentation (“Section 1.8 Spatial Relationships and Filtering” – please let me know if I am wrong) Oracle has implemented Egenhofer’s Nine-Intersection Model (9DIM matrix) for topological relationships. That model does not distinguish between the different forms of relationships like TOUCH (MEET). The Egenhofer 9-intersection model (9DIM matrix) has been extended by Clementini et al (1993) in “A small set of formal topological relationships suitable for end-user interactions” by taking into account the dimensionality of the resultant relationships/intersections. Clementini’s additions would allow me to know when TOUCH between two geometries was 0-D (point) or 1-D (line) or a mix.
SDO_TOUCH covers both point and line sharing but can’t be used to discriminate between the two. I is no other Oracle spatial operator that can help us. I also cannot (yet) think of a combination, executed using a set operator like MINUS that can be used. I validated this thinking via the following SQL.
You will notice from the results that everything that surrounds is reported as touches. Not that useful.
We are left, therefore, with having to do a little more heavy-lifting in SQL. We know that the point or line that results from a TOUCH relationship can be discovered via use of an intersection operator like Oracle’s sdo_geom.sdo_intersection.
So, here is one approach coded in SQL:
Now, sdo_geom.sdo_intersection is a Spatial feature that must be paid for. Is there a method that will work in Locator?
A Locator based method is encapsulated in the following SQL snippet. Note that the approach here is to join the parcel returned by the search to the search parcel via their vertices! The distinct is put in because the first/last vertex of a polygon is the same and so could skew the results.
Thus, those that return a vertexCount of 1 must join only at the corners, whereas those with a higher count must share a common boundary line. So, to find these parcels the complete SQL is:
Which is the same result as the on that used sdo_intersection above.
I hope this is useful to someone out there!