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

adjacent polygons

don123 Newbie
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))


thanks in advance..
  • 1. Re: adjacent polygons
    John O'Toole Journeyer
    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
    _jum Journeyer
    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
    Stefan Jager Journeyer
    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
    _jum Journeyer
    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'
      START WITH id=4000;
    
    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
    don123 Newbie
    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
    eometry, 0.5)='TOUCH' start with p.eid=6164;



    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
    _jum Journeyer
    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
    don123 Newbie
    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
    _jum Journeyer
    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'
      START WITH id=4000;
    To the second question, yes of course you can use SUBSTR on ID or on PATH.
  • 9. Re: adjacent polygons
    don123 Newbie
    Currently Being Moderated
    Many Thanks

    regards
  • 10. Re: adjacent polygons
    _jum Journeyer
    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
  • Helpful Answers - 5 points