Top 5 Recent Articles
- Biography (1)
- Blog (41)
- Changes (1)
- Customers (1)
- Data Models (1)
- Education (2)
- General Software (21)
- Georaptor Blog (5)
- Image Catalog (2)
- Licensing (1)
- ManifoldGIS (3)
- MySQL Blog (4)
- MySQL Spatial (3)
- Networking and Routing (including Optimization) (3)
- Oracle Spatial (171)
- Philosophy (1)
- PostGIS (30)
- Press Releases (1)
- Source code (24)
- Space Curves (1)
- Spatial DB comparison (1)
- SQL (1)
- SQL Server Blog (58)
- SQL Server Spatial (General) (15)
- SQL Server Spatial (LRS) (37)
- Stored Procedure (2)
- Training (1)
- XML (5)
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.
select /*+ORDERED*/ p.gid as searchGID, p2.gid as surroundingGID from parcel p, parcel p2 where p.gid = 2 and SDO_TOUCH(p2.geometry,p.geometry) = 'TRUE' order by p2.gid;
!http://www.spatialdbadvisor.com/images/18.png (Parcels touching a parcel)!
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.
select /*+ORDERED*/ distinct case when p.gid < p2.gid then p.gid else p2.gid end as gid1, case when p.gid > p2.gid then p.gid else p2.gid end as gid2, sdo_geom.relate(p2.geometry,'DETERMINE',p.geometry,0.05) as result from parcel p, parcel p2 where p.gid <> p2.gid order by 1;
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:
select i.searchGID, i.surroundingGID from (select /*+ORDERED*/ p.gid as searchGID, p2.gid as surroundingGID, sdo_geom.sdo_intersection(p2.geometry,p.geometry,0.005) as geom from parcel p, parcel p2 where p.gid = 2 and SDO_TOUCH(p2.geometry,p.geometry) = 'TRUE' ) i where i.geom.sdo_gtype <> 2001 order by i.surroundingGID;
!http://www.spatialdbadvisor.com/images/19.png (Parcels sharing a boundary)!
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.
select i.searchGID, i.surroundingGID, count(*) as vertexCount from (select /*+ORDERED*/ distinct p.gid as searchGID, p2.gid as surroundingGID, v.x,v.y from parcel p, table(mdsys.sdo_util.GetVertices(p.geometry)) v, parcel p2, table(mdsys.sdo_util.GetVertices(p2.geometry)) v2 where p.gid = 2 and SDO_TOUCH(p2.geometry,p.geometry) = 'TRUE' and ( v2.x = v.x and v2.y = v.y ) order by 2 ) i group by i.searchGID, i.surroundingGID order by i.surroundingGID
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:
select i.searchGID, i.surroundingGID from (select /*+ORDERED*/ distinct p.gid as searchGID, p2.gid as surroundingGID, v.x,v.y from parcel p, table(mdsys.sdo_util.GetVertices(p.geometry)) v, parcel p2, table(mdsys.sdo_util.GetVertices(p2.geometry)) v2 where p.gid = 2 and SDO_TOUCH(p2.geometry,p.geometry) = 'TRUE' and ( v2.x = v.x and v2.y = v.y ) order by 2 ) i group by i.searchGID, i.surroundingGID having count(*) > 1 order by i.surroundingGID;
Which is the same result as the on that used sdo_intersection above.
I hope this is useful to someone out there!