Friday October 17 2008 at 07: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 NineIntersection Model (9DIM matrix) for topological relationships. That model does not distinguish between the different forms of relationships like TOUCH (MEET). The Egenhofer 9intersection model (9DIM matrix) has been extended by Clementini et al (1993) in “A small set of formal topological relationships suitable for enduser 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 0D (point) or 1D (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 heavylifting 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!

Comment [6]
This helps alot!!!!!..thank you very much
— Nolan Porther · 9 April 2012, 19:14 · #
Simon,
I have extracted vertices of surrounding polygons by using above code, but there are some duplicates.
How to extract the unique points (vertices) of surrounding polygons along with polygon id and vertex id so that linestrings can be formed
thanks
masood
— masood · 27 June 2012, 12:04 · #
Mansood,
Don’t forget that the first and last vertex of a polygon’s ring in an sdo_geometry object is duplicated. So if the polygon starts at a shared corner with another polygon then you will get duplicates.
But I don’t understand your usecase. This one is about finding the surrounding parcels where they touch at a point.
I can show you how to get the shared lines without needing to process the points.
Is that what you want?
If you send me test data I will construct and article on this for you.
regards
Simon
— Simon Greener · 27 June 2012, 12:19 · #
Hi Simon, I want to ask about a problem I have, I have a table in oracle with a rural parcels and I have been determined for each of the parcels, if within 1000 meters there is another rural parcels. You could give me one example.?
THANKS
— Javier · 19 December 2012, 19:28 · #
One again.. you’ve solved my problem !!
Thanks a million for all of your posts !!!
Cheers,
jb
— JeanBernard Gariépy · 16 January 2013, 15:16 · #
thanks about code of second figuer
but i want acode that know the direction of three shapes (1 , 4 , 5) for shape 2
that indicate which of them in north , south , east and west
thanks
wael
— wael · 26 February 2013, 10:52 · #