Surrounding Parcels

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!