This discussion is archived
9 Replies Latest reply: Oct 4, 2012 9:04 PM by don123 RSS

select point geometry

don123 Newbie
Currently Being Moderated
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
    karel prech Newbie
    Currently Being Moderated
    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
    yhu Journeyer
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    Any chance of being shown the original geometries?
    regards
    Simon
  • 4. Re: select point geometry
    don123 Newbie
    Currently Being Moderated
    yhu, thanks, i am getting point geometry when i try as suggested by you
  • 5. Re: select point geometry
    don123 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    thanks

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points