This discussion is archived
10 Replies Latest reply: Oct 22, 2012 7:57 AM by _jum

Currently Being Moderated
Hi,

I have four polygons with following id and geometry. They are connected with one another. How to find the order of their geometrical connection with other polygon from left-to-right OR right-to-left.

For example, visually i can see that these four polygons are connected in the order: From left-to-right, first ID is 5884, second ID is 5886, thrid ID is 5844 and fourth ID is 4000.

How to do this with SQL/PLSQL.

ID, GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
5886

SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
AY(-87.896678, 41.972409, -87.896728, 41.972413, -87.896762, 41.972422, -87.8968
, 41.972434, -87.897299, 41.972246, -87.897306, 41.972204, -87.897319, 41.972174
, -87.897338, 41.972152, -87.89736, 41.972139, -87.89738, 41.972131, -87.897244,
41.971755, -87.89705, 41.97179, -87.896881, 41.97181, -87.896657, 41.971833, -8
7.896529, 41.971838, -87.896328, 41.971838, -87.896456, 41.972429, -87.896499, 4
1.972421, -87.896553, 41.972413, -87.896615, 41.972407, -87.896678, 41.972409))

ID, GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
4000

SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
AY(-87.895632, 41.972382, -87.895605, 41.972412, -87.89557, 41.972441, -87.89554
4, 41.972459, -87.895494, 41.972481, -87.89543, 41.972495, -87.895436, 41.97286,
-87.895516, 41.97286, -87.895591, 41.972865, -87.895664, 41.972873, -87.895734,
41.972891, -87.895781, 41.972911, -87.895825, 41.972935, -87.896136, 41.972772,
-87.896132, 41.97274, -87.896135, 41.972691, -87.896143, 41.972652, -87.896163,
41.972613, -87.896185, 41.972581, -87.896225, 41.97254, -87.89566, 41.972321, -
87.895632, 41.972382))

ID, GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
-----------------------------------------------------------------------------------
5884

SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
AY(-87.897662, 41.971652, -87.897535, 41.97169, -87.897354, 41.971732, -87.89724
4, 41.971755, -87.89738, 41.972131, -87.897475, 41.972132, -87.897547, 41.972138
, -87.897632, 41.972153, -87.897914, 41.971833, -87.89792, 41.971818, -87.897931
, 41.971805, -87.897774, 41.971616, -87.897662, 41.971652))

ID, GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------

5844

SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
AY(-87.895431, 41.972072, -87.895504, 41.972103, -87.895543, 41.972124, -87.8955
84, 41.972151, -87.895628, 41.972194, -87.895654, 41.972245, -87.895663, 41.9722
92, -87.89566, 41.972321, -87.896225, 41.97254, -87.896256, 41.972514, -87.89629
, 41.97249, -87.896343, 41.972465, -87.896391, 41.972447, -87.896456, 41.972429,
-87.896328, 41.971838, -87.89544, 41.971836, -87.895431, 41.972072))

• ###### 1. Re: adjacent polygons
Currently Being Moderated
Hi 908275,

Purely out of curiosity, could you explain what you need this for?
This is the type of thing you'd need to experiment with to find the most suitable approach for the type of data you are likely to be running it on. My first approach would probably be to generate the centroid of each polygon, then to try to order based on that. If you expecting your polygons to always be horizontally adjacent to each other, then you'd get the order by just looking at the X coordinate of the centroid. Alternatively you could try to string the centroid together into the shortest valid line possible and then you'll know which polygon corresponds to which vertex on the line and hence get some sense of order.

John
• ###### 2. Re: adjacent polygons
Currently Being Moderated
John O'Toole gave the "exact" way to solve the problem.
You could use the SDO_GEOM.RELATE function to get a first clue.
``````DROP TABLE sdo_test;

CREATE TABLE sdo_test (
id      NUMBER,
GEOM    MDSYS.SDO_GEOMETRY);

INSERT INTO sdo_test VALUES (5886, SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1),
SDO_ORDINATE_ARRAY(-87.896678, 41.972409, -87.896728, 41.972413, -87.896762, 41.972422, -87.8968, 41.972434, -87.897299, 41.972246, -87.897306, 41.972204, -87.897319, 41.972174,
-87.897338, 41.972152, -87.89736, 41.972139, -87.89738, 41.972131, -87.897244, 41.971755, -87.89705, 41.97179, -87.896881, 41.97181, -87.896657, 41.971833,
-87.896529, 41.971838, -87.896328, 41.971838, -87.896456, 41.972429, -87.896499, 41.972421, -87.896553, 41.972413, -87.896615, 41.972407, -87.896678, 41.972409)));

INSERT INTO sdo_test VALUES (4000, SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1),
SDO_ORDINATE_ARRAY(-87.895632, 41.972382, -87.895605, 41.972412, -87.89557, 41.972441, -87.895544, 41.972459, -87.895494, 41.972481, -87.89543, 41.972495, -87.895436, 41.97286,
-87.895516, 41.97286, -87.895591, 41.972865, -87.895664, 41.972873, -87.895734, 41.972891, -87.895781, 41.972911, -87.895825, 41.972935, -87.896136, 41.972772,
-87.896132, 41.97274, -87.896135, 41.972691, -87.896143, 41.972652, -87.896163, 41.972613, -87.896185, 41.972581, -87.896225, 41.97254, -87.89566, 41.972321, -87.895632, 41.972382)));

INSERT INTO sdo_test VALUES (5884, SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1),
SDO_ORDINATE_ARRAY(-87.897662, 41.971652, -87.897535, 41.97169, -87.897354, 41.971732, -87.897244, 41.971755, -87.89738, 41.972131, -87.897475, 41.972132, -87.897547, 41.972138,
-87.897632, 41.972153, -87.897914, 41.971833, -87.89792, 41.971818, -87.897931, 41.971805, -87.897774, 41.971616, -87.897662, 41.971652)));

INSERT INTO sdo_test VALUES (5844, SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1),
SDO_ORDINATE_ARRAY(-87.895431, 41.972072, -87.895504, 41.972103, -87.895543, 41.972124, -87.895584, 41.972151, -87.895628, 41.972194, -87.895654, 41.972245, -87.895663, 41.972292, -87.89566, 41.972321, -87.896225, 41.97254, -87.896256, 41.972514,
-87.89629,  41.97249, -87.896343, 41.972465, -87.896391, 41.972447, -87.896456, 41.972429, -87.896328, 41.971838, -87.89544, 41.971836, -87.895431, 41.972072)));

COMMIT;
SELECT p1.id id1,p2.id id2, MDSYS.SDO_GEOM.relate(p1.geom,'DETERMINE',p2.geom,1) rela
FROM sdo_test p1, sdo_test p2
WHERE p1.id<p2.id
--AND MDSYS.SDO_GEOM.relate(p1.geom,'DETERMINE',p2.geom,1)='TOUCH';

ID1     ID2     RELA
-------------------------------
4000     5844     TOUCH
4000     5884     DISJOINT
4000     5886     DISJOINT
5844     5884     DISJOINT
5844     5886     TOUCH
5884     5886     TOUCH``````
Edited by: _jum on 10.10.2012 14:50 (missed a sign in geometry id=5844, that's why the geometry was not valid and the shapes didn't touch, corrected the code and the results)
• ###### 3. Re: adjacent polygons
Currently Being Moderated
Off the top of my head: I would create the centroids as John suggests, then take the lines between the centroids. An anyinteract of those lines with the polygons will give you the neighboring polygons, and using the azimuth of the lines will give you whether it's to the left or to the right. Assuming your polygons do not substantially overlap, of course, otherwise that will create other problems.

You could also create a Topology layer, and play around with that (not sure what kind of functions are in there, but maybe it has some useful stuff for your problem).

Are your polygons always east-west oriented? No chance of the odd one laying above or below the others?

HTH,
Stefan
• ###### 4. Re: adjacent polygons
Currently Being Moderated
In addition to my post, if the shapes touch each other you can use hierarchical query to find adjacent polygones (here already started with "first" shape):
``````SELECT p.id, level, SYS_CONNECT_BY_PATH(id,'->') path
FROM sdo_test p
CONNECT BY NOCYCLE
MDSYS.SDO_GEOM.relate(p.geom,'DETERMINE',PRIOR p.geom,0.000001)='TOUCH'

id      level    path
---------------------------------------------------------------
4000     1     ->4000
5844     2     ->4000->5844
5886     3     ->4000->5844->5886
5884     4     ->4000->5844->5886->5884``````
It would be possible to add other relations fe. 'OVERLAPBDYINTERSECT' too.
• ###### 5. Re: adjacent polygons
Currently Being Moderated
Hi, sorry for the delay, many thanks for reply

Is it possible to extract individual elements in the path and compare them if all the elements have same name. For example; the elements in the path 6164=5808=5811 all have name 'B'

SQL> select p.eid, level, name, sys_connect_by_path(eid,'=') path from newintpo
ly p connect by nocycle mdsys.sdo_geom.relate(p.geometry, 'determine', prior p.g

EID LEVEL NAME PATH
------- ----- --------------- ------------------------------
6164 1 B =6164
5808 2 B =6164=5808
5810 3 J =6164=5808=5810
5862 4 J =6164=5808=5810=5862
5811 4 B =6164=5808=5810=5811
5811 3 B =6164=5808=5811
5810 4 J =6164=5808=5811=5810
5862 5 J =6164=5808=5811=5810=5862

8 rows selected.

regards
• ###### 6. Re: adjacent polygons
Currently Being Moderated
Yes it possible to extract individual elements in the path and compare them if all the elements have same name ('B'), because you check if the geometries are TOUCHing. But but you'll get a result, that won't help much - like:
``->B->B->B->B``
So you need a column to identify the polygons.
• ###### 7. Re: adjacent polygons
Currently Being Moderated
i tried to "create table as select from" by using the above sql query to store in different table, but no success. i am thinking to extract the elements by using substr function on path, is it correct way??

any suitable approach to deal with the elements in path??
• ###### 8. Re: adjacent polygons
Currently Being Moderated
You can create a table from the result of the querie, but you have to give the column LEVEL an alias. If you try without, you should get
`` ORA-00998: must name this expression with a column alias:``
which is pretty self-explanatory. So try:
``````CREATE TABLE res_table AS
SELECT p.id, level lv, SYS_CONNECT_BY_PATH(id,'->') path
FROM sdo_test p
CONNECT BY NOCYCLE
MDSYS.SDO_GEOM.relate(p.geom,'DETERMINE',PRIOR p.geom,0.000001)='TOUCH'
To the second question, yes of course you can use SUBSTR on ID or on PATH.
• ###### 9. Re: adjacent polygons
Currently Being Moderated
Many Thanks

regards
• ###### 10. Re: adjacent polygons
Currently Being Moderated
Hope this helped.
If yes - you could earn me some points: 5 points for a "helpful" answer or 10 points for a "correct" one and also to mark the question answered.
This moment I posted the reply - you did - Thanks !

Edited by: _jum on 22.10.2012 16:56

#### Legend

• Correct Answers - 10 points