9 Replies Latest reply: Oct 4, 2012 11:04 PM by don123 RSS

    select point geometry

    don123
      hi,

      I want to select point geometry in following cases.

      (1) line geometry crosses polygon geometry at a point.
      (2) line geometry crosses line geometry at a point.

      I have used following sql, but it is returning line geometry, can you please suggest...??

      select sdo_geom.sdo_intersection(a.geometry, b.geometry,0.5) geom from line a, poly b where sdo_relate(a.geometry, b.geometry, 'mask=ANYINTERACT')='TRUE';

      The sample output is given below.


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

      SDO_GEOMETRY(2002, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
      -118.41258, 33.936309, -118.41256, 33.936344))

      SDO_GEOMETRY(2002, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
      -118.41253, 33.936728, -118.41248, 33.936702, -118.41241, 33.93667, -118.41233,
      33.936646, -118.41226, 33.936625))

      SDO_GEOMETRY(2002, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
      -118.41291, 33.936552, -118.41285, 33.9366, -118.41281, 33.936649, -118.41278, 3
      3.936698, -118.41278, 33.936703))

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

      SDO_GEOMETRY(2002, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
      -118.41907, 33.935643, -118.41907, 33.935653))


      52 rows selected.
        • 1. Re: select point geometry
          prechk
          Hello,

          you can't use mask "ANYINTERACT", if you want only specific relation between geometries. Look in the Oracle Spatial documentation :).

          Regards,
          Karel
          • 2. Re: select point geometry
            Ying Hu-Oracle
            Maybe try the following query with SDO_UTIL.POLYGONTOLINE:
            select sdo_geom.sdo_intersection(a.geometry, SDO_UTIL.POLYGONTOLINE(b.geometry),0.5) geom from line a, poly b where sdo_relate(a.geometry, b.geometry, 'mask=ANYINTERACT')='TRUE';
            • 3. Re: select point geometry
              Simon Greener
              Any chance of being shown the original geometries?
              regards
              Simon
              • 4. Re: select point geometry
                don123
                yhu, thanks, i am getting point geometry when i try as suggested by you
                • 5. Re: select point geometry
                  don123
                  Simon, please see original geometry..

                  SQL> select geometry from line;

                  SDO_GEOMETRY(2002, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
                  -118.39524, 33.938303, -118.39554, 33.938315, -118.39567, 33.938326, -118.39582,
                  33.938343, -118.39593, 33.938361, -118.39606, 33.938382, -118.39616, 33.938404,
                  -118.39621, 33.938416, -118.39634, 33.938451, -118.39651, 33.938503, -118.39677
                  , 33.938597, -118.39688, 33.938648, -118.39702, 33.938718, -118.39719, 33.938814
                  ))

                  SDO_GEOMETRY(2002, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(

                  GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
                  --------------------------------------------------------------------------------
                  -118.43425, 33.946834, -118.43437, 33.94678, -118.43442, 33.946742, -118.43448,
                  33.946688, -118.43452, 33.94662, -118.43453, 33.94661, -118.43455, 33.946566, -1
                  18.43458, 33.946486, -118.43459, 33.94644, -118.4346, 33.946382, -118.43458, 33.
                  946309, -118.43456, 33.946241, -118.43453, 33.946182, -118.43448, 33.946114, -11
                  8.43445, 33.946086, -118.43442, 33.946064))

                  SDO_GEOMETRY(2002, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
                  -118.40857, 33.94947, -118.40906, 33.949414, -118.40912, 33.949405, -118.40918,
                  33.949392, -118.40923, 33.94937, -118.40927, 33.949344, -118.4093, 33.949314, -1
                  18.40932, 33.949294, -118.40934, 33.949265, -118.40935, 33.949244, -118.40936, 3
                  3.949209, -118.40937, 33.949186, -118.40937, 33.949159))

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

                  SDO_GEOMETRY(2002, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
                  -118.40975, 33.949349, -118.4097, 33.949347, -118.40965, 33.949345, -118.4096, 3
                  3.949341, -118.40956, 33.949327, -118.40951, 33.949308, -118.40947, 33.949284, -
                  118.40944, 33.949256, -118.40941, 33.949225, -118.40939, 33.949194, -118.40939,
                  33.949184, -118.40937, 33.949159))

                  SDO_GEOMETRY(2002, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
                  -118.40007, 33.937813, -118.40023, 33.937795, -118.40044, 33.937776, -118.40058,
                  33.937769, -118.40075, 33.937772, -118.40091, 33.937782, -118.40105, 33.937793,
                  -118.40115, 33.937807, -118.40131, 33.937835, -118.40146, 33.937862, -118.40146

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


                  SQL> select geometry from poly;


                  SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
                  AY(-118.43427, 33.947056, -118.4342, 33.946644, -118.43417, 33.946648, -118.4309
                  7, 33.946976, -118.43019, 33.947056, -118.42829, 33.94725, -118.42601, 33.947484
                  , -118.42267, 33.947825, -118.42076, 33.948021, -118.41886, 33.948216, -118.4179
                  3, 33.948311, -118.41658, 33.948448, -118.41498, 33.948612, -118.41111, 33.94900
                  9, -118.4104, 33.949081, -118.40967, 33.949155, -118.40905, 33.949219, -118.4045
                  2, 33.949682, -118.4035, 33.949786, -118.40338, 33.949798, -118.40267, 33.94987,
                  -118.40163, 33.949977, -118.40163, 33.949992, -118.40169, 33.950379, -118.40169
                  , 33.950393, -118.40175, 33.950386, -118.40222, 33.950339, -118.40225, 33.950335

                  GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
                  --------------------------------------------------------------------------------
                  , -118.41048, 33.949496, -118.41119, 33.949422, -118.41559, 33.948972, -118.4166
                  , 33.948869, -118.42018, 33.948501, -118.42103, 33.948414, -118.42663, 33.94784,
                  -118.42665, 33.947838, -118.42786, 33.947714, -118.43036, 33.947457, -118.43129
                  , 33.947361, -118.43423, 33.947059, -118.43427, 33.947056))

                  SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
                  AY(-118.41937, 33.936027, -118.41931, 33.935619, -118.41883, 33.935668, -118.418
                  8, 33.935672, -118.41861, 33.935691, -118.41317, 33.936248, -118.41292, 33.93627
                  4, -118.4125, 33.936317, -118.41205, 33.936363, -118.40929, 33.936645, -118.4082
                  9, 33.936747, -118.40691, 33.936889, -118.40507, 33.937077, -118.40453, 33.93713
                  3, -118.40437, 33.937145, -118.40358, 33.937229, -118.40341, 33.937247, -118.401

                  GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
                  --------------------------------------------------------------------------------
                  57, 33.937435, -118.4009, 33.937504, -118.39893, 33.937705, -118.39817, 33.93778
                  3, -118.39697, 33.937906, -118.39691, 33.937912, -118.39643, 33.937961, -118.395
                  96, 33.938009, -118.39323, 33.938289, -118.39035, 33.938583, -118.38972, 33.9386
                  48, -118.38884, 33.938737, -118.38768, 33.938856, -118.38419, 33.939212, -118.38
                  381, 33.939251, -118.38299, 33.939335, -118.3829, 33.939344, -118.38287, 33.9393
                  47, -118.38293, 33.939756, -118.38296, 33.939753, -118.38305, 33.939744, -118.38
                  369, 33.939678, -118.38757, 33.939282, -118.38863, 33.939173, -118.38951, 33.939
                  084, -118.39085, 33.938946, -118.39311, 33.938715, -118.39414, 33.93861, -118.39
                  548, 33.938473, -118.39749, 33.938267, -118.39794, 33.938222, -118.39942, 33.938
                  07, -118.40076, 33.937933, -118.40219, 33.937787, -118.40294, 33.93771, -118.403
                  77, 33.937625, -118.40415, 33.937586, -118.40565, 33.937433, -118.40714, 33.9372

                  GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
                  --------------------------------------------------------------------------------
                  8, -118.40802, 33.93719, -118.40861, 33.93713, -118.40943, 33.937046, -118.4117,
                  33.936814, -118.41313, 33.936666, -118.41773, 33.936196, -118.41816, 33.936152,
                  -118.41884, 33.936082, -118.41937, 33.936027))
                  • 6. Re: select point geometry
                    Simon Greener
                    The suggestion made by Yhu is correct.

                    Here is a test case that shows the correct point intersection results.
                    with lines as (
                    select SDO_GEOMETRY(2002, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY( -118.39524, 33.938303, -118.39554, 33.938315, -118.39567, 33.938326, -118.39582, 33.938343, -118.39593, 33.938361, -118.39606, 33.938382, -118.39616, 33.938404, -118.39621, 33.938416, -118.39634, 33.938451, -118.39651, 33.938503, -118.39677, 33.938597, -118.39688, 33.938648, -118.39702, 33.938718, -118.39719, 33.938814))
                    as geom from dual union all select SDO_GEOMETRY(2002, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY( -118.43425, 33.946834, -118.43437, 33.94678, -118.43442, 33.946742, -118.43448, 33.946688, -118.43452, 33.94662, -118.43453, 33.94661, -118.43455, 33.946566, -118.43458, 33.946486, -118.43459, 33.94644, -118.4346, 33.946382, -118.43458, 33.946309, -118.43456, 33.946241, -118.43453, 33.946182, -118.43448, 33.946114, -118.43445, 33.946086, -118.43442, 33.946064)) 
                    as geom from dual union all select  SDO_GEOMETRY(2002, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY( -118.40857, 33.94947, -118.40906, 33.949414, -118.40912, 33.949405, -118.40918, 33.949392, -118.40923, 33.94937, -118.40927, 33.949344, -118.4093, 33.949314, -118.40932, 33.949294, -118.40934, 33.949265, -118.40935, 33.949244, -118.40936, 33.949209, -118.40937, 33.949186, -118.40937, 33.949159))
                    as geom from dual union all select  SDO_GEOMETRY(2002, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY( -118.40975, 33.949349, -118.4097, 33.949347, -118.40965, 33.949345, -118.4096, 33.949341, -118.40956, 33.949327, -118.40951, 33.949308, -118.40947, 33.949284, -118.40944, 33.949256, -118.40941, 33.949225, -118.40939, 33.949194, -118.40939, 33.949184, -118.40937, 33.949159)) 
                    as geom from dual union all select  SDO_GEOMETRY(2002, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY( -118.40007, 33.937813, -118.40023, 33.937795, -118.40044, 33.937776, -118.40058, 33.937769, -118.40075, 33.937772, -118.40091, 33.937782, -118.40105, 33.937793, -118.40115, 33.937807, -118.40131, 33.937835, -118.40146, 33.937862, -118.40146, 33.937862))
                    as geom from dual 
                    ),
                    polys as (
                    select SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARRAY(-118.43427, 33.947056, -118.4342, 33.946644, -118.43417, 33.946648, -118.43097, 33.946976, -118.43019, 33.947056, -118.42829, 33.94725, -118.42601, 33.947484, -118.42267, 33.947825, -118.42076, 33.948021, -118.41886, 33.948216, -118.41793, 33.948311, -118.41658, 33.948448, -118.41498, 33.948612, -118.41111, 33.949009, -118.4104, 33.949081, -118.40967, 33.949155, -118.40905, 33.949219, -118.40452, 33.949682, -118.4035, 33.949786, -118.40338, 33.949798, -118.40267, 33.94987,-118.40163, 33.949977, -118.40163, 33.949992, -118.40169, 33.950379, -118.40169, 33.950393, -118.40175, 33.950386, -118.40222, 33.950339, -118.40225, 33.950335, -118.41048, 33.949496, -118.41119, 33.949422, -118.41559, 33.948972, -118.4166, 33.948869, -118.42018, 33.948501, -118.42103, 33.948414, -118.42663, 33.94784,-118.42665, 33.947838, -118.42786, 33.947714, -118.43036, 33.947457, -118.43129, 33.947361, -118.43423, 33.947059, -118.43427, 33.947056))
                    as geom from dual union all select SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARRAY(-118.41937, 33.936027, -118.41931, 33.935619, -118.41883, 33.935668, -118.4188, 33.935672, -118.41861, 33.935691, -118.41317, 33.936248, -118.41292, 33.936274, -118.4125, 33.936317, -118.41205, 33.936363, -118.40929, 33.936645, -118.40829, 33.936747, -118.40691, 33.936889, -118.40507, 33.937077, -118.40453, 33.937133, -118.40437, 33.937145, -118.40358, 33.937229, -118.40341, 33.937247, -118.40157, 33.937435, -118.4009, 33.937504, -118.39893, 33.937705, -118.39817, 33.937783, -118.39697, 33.937906, -118.39691, 33.937912, -118.39643, 33.937961, -118.39596, 33.938009, -118.39323, 33.938289, -118.39035, 33.938583, -118.38972, 33.938648, -118.38884, 33.938737, -118.38768, 33.938856, -118.38419, 33.939212, -118.38381, 33.939251, -118.38299, 33.939335, -118.3829, 33.939344, -118.38287, 33.939347, -118.38293, 33.939756, -118.38296, 33.939753, -118.38305, 33.939744, -118.38369, 33.939678, -118.38757, 33.939282, -118.38863, 33.939173, -118.38951, 33.939084, -118.39085, 33.938946, -118.39311, 33.938715, -118.39414, 33.93861, -118.39548, 33.938473, -118.39749, 33.938267, -118.39794, 33.938222, -118.39942, 33.93807, -118.40076, 33.937933, -118.40219, 33.937787, -118.40294, 33.93771, -118.40377, 33.937625, -118.40415, 33.937586, -118.40565, 33.937433, -118.40714, 33.93728, -118.40802, 33.93719, -118.40861, 33.93713, -118.40943, 33.937046, -118.4117, 33.936814, -118.41313, 33.936666, -118.41773, 33.936196, -118.41816, 33.936152, -118.41884, 33.936082, -118.41937, 33.936027))
                    as geom from dual 
                    )
                    select sdo_geom.sdo_intersection(l.geom,sdo_util.polygontoline(a.geom),0.005) as iGeom
                      from lines l,
                           polys a
                    where sdo_geom.relate(l.geom,'DETERMINE',a.geom,0.005) <> 'DISJOINT';
                    -- Results
                    --
                    IGEOM
                    -----
                    SDO_GEOMETRY(2001,8307,NULL,SDO_ELEM_INFO_ARRAY(1,1,1),SDO_ORDINATE_ARRAY(-118.40937,33.949186))
                    SDO_GEOMETRY(2001,8307,NULL,SDO_ELEM_INFO_ARRAY(1,1,1),SDO_ORDINATE_ARRAY(-118.409389929954,33.9491839108393))
                    SDO_GEOMETRY(2001,8307,NULL,SDO_ELEM_INFO_ARRAY(1,1,1),SDO_ORDINATE_ARRAY(-118.39615786651,33.9384035308923))
                    SDO_GEOMETRY(2001,8307,NULL,SDO_ELEM_INFO_ARRAY(1,1,1),SDO_ORDINATE_ARRAY(-118.401458302543,33.9378617069454))
                    Visually: http://www.spatialdbadvisor.com/images/210.png

                    The reason for this is as follows:

                    1. The intersection between a line and a polygon can only be a single point when the end (boundary) of the line falls exactly on the boundary (line) of the polygon.
                    2. Otherwise, if the line cross the boundary and is both in and out of the polygon then the intersection is, correctly, that part of the line that intersects the interior of the polygon (interior of line intersects interior of polygon)

                    So, to get an intersection point you are really asking for the intersection of the boundary LINE and the search LINE. This could return many things but in all your cases this returns a point - the point where the line crosses the boundary.

                    Thus, to do this you have to convert the polygon boundary into a linestring and this is what Yhu suggested when he recommended sdo_util.polygontoline.

                    I needed to see the problem, Yhu didn't (well done!).

                    I think points to Yhu and myself...

                    Oh, if you use SQL Plus to print out geometries again, you can avoid the header being printed in the middle of a geometry by setting the PAGESIZE to something large and the LINESIZE likewise.

                    regards
                    Simon
                    • 7. Re: select point geometry
                      don123
                      Simon, thanks for detailed explanation.

                      how to provide points to both you and Yhu ?? Am i allowed to mark both answers are correct ??

                      thanks
                      • 8. Re: select point geometry
                        Simon Greener
                        I can't answer that.
                        Check the functionality of the forum.
                        But, if you are left to assign the points to one of us, assign them to Yhu.
                        S
                        • 9. Re: select point geometry
                          don123
                          thanks