5 Replies Latest reply: Feb 25, 2013 2:43 PM by rpitts RSS

    Lines intersecting that has a point in a polygon

    CrackerJack
      Hi Spatial Community,

      I would like to get your expertise in trying to see how can we get this done via Oracle Spatial.

      The requirement is to write a file

      - passed intersection of line that have a point within a polygon
      - failed intersections( doe snot have any line intersecting) that have a point within a polygon

      Any clues is much appreciated.

      Edited by: CrackerJack on Feb 25, 2013 12:46 PM
        • 2. Re: Lines intersecting that has a point in a polygon
          CrackerJack
          help with the process on how we can utilise these spatial operators?
          • 3. Re: Lines intersecting that has a point in a polygon
            _jum
            In the documentation, there is a complete example with a table cola_markets.
            Please check the example and come back with a precise question, where you need help/support.
            • 4. Re: Lines intersecting that has a point in a polygon
              Ivan Bush
              Jeyanthy,

              It is not obvious to me exactly what your query is trying to do. Could you please explain in a bit more detail and then I am sure somebody will be able to provide you with a sample solution.

              Regards.

              Ivan
              • 5. Re: Lines intersecting that has a point in a polygon
                rpitts
                Comments before have been pretty much on the money perhaps I might just add

                Using the cola_markets example from the manual...

                -- Return the topological intersection of two geometries.
                SELECT SDO_GEOM.SDO_INTERSECTION(c_a.shape, c_c.shape, 0.005)
                FROM cola_markets c_a, cola_markets c_c
                WHERE c_a.name = 'cola_a' AND c_c.name = 'cola_c';

                SDO_GEOM.SDO_INTERSECTION(C_A.SHAPE,C_C.SHAPE,0.005)(SDO_GTYPE, SDO_SRID, SDO_PO
                --------------------------------------------------------------------------------
                SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
                AY(4, 5, 3, 3, 5, 3, 5, 5, 4, 5))

                So that finds the ones that DO interact

                To do the DISJOINT: The boundaries and interiors do not intersect.

                Use SDO_ANYINTERACT as someone else also suggested

                The expression SDO_ANYINTERACT(geometry1,geometry2) = 'TRUE' evaluates to TRUE for object pairs that have the ANYINTERACT topological relationship, and FALSE otherwise.

                The False version which your will need

                SQL> SELECT c.mkt_id, c.name
                FROM cola_markets c
                WHERE SDO_ANYINTERACT(c.shape,
                SDO_GEOMETRY(2003, NULL, NULL,
                SDO_ELEM_INFO_ARRAY(1,1003,3),
                SDO_ORDINATE_ARRAY(4,6, 8,8))
                ) != 'TRUE';
                2 3 4 5 6 7
                MKT_ID NAME
                ---------- --------------------------------
                3 cola_c
                5 cola_d5

                And for completeness the reverse

                SQL> SELECT c.mkt_id, c.name
                FROM cola_markets c
                WHERE SDO_ANYINTERACT(c.shape,
                SDO_GEOMETRY(2003, NULL, NULL,
                SDO_ELEM_INFO_ARRAY(1,1003,3),
                SDO_ORDINATE_ARRAY(4,6, 8,8))
                ) = 'TRUE';
                2 3 4 5 6 7
                MKT_ID NAME
                ---------- --------------------------------
                1 cola_a
                2 cola_b
                4 cola_d

                Cheers
                Rich