10 Replies Latest reply: Oct 22, 2012 9:57 AM by _jum RSS

    adjacent polygons

    don123
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          Many Thanks

                          regards
                          • 10. Re: adjacent polygons
                            _jum
                            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