|
Surrounding Parcels
Friday October 17 2008 at 18:27
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;
| SEARCHGID |
SURROUNDINGGID |
| 2 |
1 |
| 2 |
3 |
| 2 |
4 |
| 2 |
5 |
| 2 |
6 |
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;
| gid1 |
gid2 |
result |
| 1 |
2 |
TOUCH |
| 1 |
3 |
TOUCH |
| 1 |
4 |
DISJOINT |
| 1 |
5 |
TOUCH |
| 1 |
6 |
DISJOINT |
| 2 |
3 |
TOUCH |
| 2 |
4 |
TOUCH |
| 2 |
5 |
TOUCH |
| 2 |
6 |
TOUCH |
| 3 |
4 |
DISJOINT |
| 3 |
5 |
TOUCH |
| 3 |
6 |
DISJOINT |
| 4 |
5 |
TOUCH |
| 4 |
6 |
TOUCH |
| 5 |
6 |
TOUCH |
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;
| SEARCHGID |
SURROUNDINGGID |
| 2 |
1 |
| 2 |
4 |
| 2 |
5 |
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
| searchGID |
surroundingGID |
vertexCount |
| 2 |
1 |
2 |
| 2 |
3 |
1 |
| 2 |
4 |
2 |
| 2 |
5 |
2 |
| 2 |
6 |
1 |
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;
| searchGID |
surroundingGID |
| 2 |
1 |
| 2 |
4 |
| 2 |
5 |
Which is the same result as the on that used sdo_intersection above.
I hope this is useful to someone out there!
        
|
SpatialDB Advisor
Home
Comment