9 Replies Latest reply: Jul 3, 2012 11:39 AM by abacy RSS

    VALIDATE GEOMETRY

    abacy
      Hello,
      I have a table that contains boundary of governorates,
      I am using VALIDATE_GEOMETRY_WITH_CONTEXT to check that each geometry is in valid format or not,
      i need to check each geometry if there is any intersect, overlap, CoveredBy,Touch...... with its neighbor geometry
      when i am trying using function from the SDO_GEOM package such as WITHIN_DISTANCE to get neighbor geometry and SDO_INTERSECTION , SDO_RELATE It takes long time

      Is there any function gets what i need and takes less time ?

      should i use topology ?


      Thanks in advance,
      Abacy
        • 1. Re: VALIDATE GEOMETRY
          Ying Hu-Oracle
          Hi Abacy,

          If you can show us your queries, we will have a better idea.
          That said, you may try operator: sdo_within_distance. For example,
          in http://docs.oracle.com/cd/E11882_01/appdev.112/e11830/sdo_complex_queries.htm#CIHIAIDD

          SELECT /*+ ORDERED */ c.city
          FROM geod_interstates i, geod_cities c
          WHERE i.highway = 'I170'
          AND sdo_within_distance (
          c.location, i.geom,
          'distance=15 unit=mile') = 'TRUE';

          sdo_within_distance uses a spatial index, so that it is faster than function SDO_GEOM.WITHIN_DISTANCE.

          Furthermore, if possible, use 11.2.0.3. If not, get patch 12400749 on 11.2.0.2.

          Thanks,
          Ying
          • 2. Re: VALIDATE GEOMETRY
            abacy
            Hi Ying,
            i already tried it SDO_WITHIN_DISTANCE
            this is the query
            SELECT a.objectid , b.objectid
            FROM TEMP_2 a , TEMP_2 b
            WHERE SDO_WITHIN_DISTANCE(a.geom, b.geom, 'DISTANCE=10 UNIT=METER ')='TRUE'
            and b.objectid < a.objectid

            temp_2 table has 28 rows
            i 'm using sdo_within_distance between all geometries without repeating,
            it takes 19.21 mins and result 58 rows




            Thanks for reply :),

            Abacy
            • 3. Re: VALIDATE GEOMETRY
              Ying Hu-Oracle
              Hi Abacy,

              Please try the following queries with some hints

              SELECT /*+ LEADING(b a) USE_NL_WITH_INDEX(a your_spatial_index) */ a.objectid , b.objectid
              FROM TEMP_2 a , TEMP_2 b
              WHERE SDO_WITHIN_DISTANCE(a.geom, b.geom, 'DISTANCE=10 UNIT=METER ')='TRUE'
              and b.objectid < a.objectid

              SELECT /*+ LEADING(b a) USE_NL_WITH_INDEX(a your_spatial_index) */ a.objectid , b.objectid
              FROM TEMP_2 a , TEMP_2 b
              WHERE SDO_ANYINTERACT(a.geom, b.geom)='TRUE'
              and b.objectid < a.objectid

              And what DB version are you using?

              Thanks,
              Ying
              • 4. Re: VALIDATE GEOMETRY
                abacy
                Oracle Database 11g Enterprise 11.2.0.1.0-64 bit

                i'm trying the queries now
                the first query takes 17 mins till now and not finished yet
                and the second one takes 8 mins and not finished yet
                • 5. Re: VALIDATE GEOMETRY
                  abacy
                  first query takes 18 mins
                  and the second takes 11 mins



                  Thanks,
                  Abacy
                  • 6. Re: VALIDATE GEOMETRY
                    Ying Hu-Oracle
                    OK, your geometries could be very complex, assuming the spatial-index-based
                    query plan is already chosen. Anyway, please upgrade it to 11.2.0.3 because
                    sdo_within_distance and sdo_anyinteract is significantly improved for complex
                    geometries. If 11.2.0.3 is not available, try to get patch 12400749 or
                    patch 14168342 on 11.2.0.2. Sorry, no patch on 11.2.0.1 for now.
                    • 7. Re: VALIDATE GEOMETRY
                      abacy
                      Hi Ying,
                      do you know link to download 11.2.0.2 and the patches for windows server 2008 64 or 32 bit?
                      i found only 11.2.0.1 @ Oracle site
                      http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html
                      • 8. Re: VALIDATE GEOMETRY
                        Ying Hu-Oracle
                        It says "11/10/11: Patch Set 11.2.0.3 for Linux, Solaris, Windows, AIX and HP-UX Itanium is now available on support.oracle.com."
                        So this is only what I know. Maybe contact oracle support?
                        • 9. Re: VALIDATE GEOMETRY
                          abacy
                          I don't have support Identifier.

                          Thanks for reply Ying,
                          Abacy.