14 Replies Latest reply: Oct 18, 2012 10:36 AM by _jum RSS

    sdo_relate--inside vs contains--result problems with both

    627886
      I am doing a Point in Polygon analysis with Block centroids, ~8M points, and ZIP codes, ~30K polygons.

      With sdo_relate/inside I get 293 blocks getting assiged to two zip codes each--these problem cases seem to occur when two or more zip codes are enclosed by another zip code (but not in every case that this happens). For the problem cases the blocks are assigned to the zip code that it is inside and the zip that encloses that zip. I checked the ring orientation with SDO_UTIL.GETVERTICES and they are correct. There is no overlap in the zip code boundaries.

      With sdo_relate/contains I get unique block to zip assigments but two block centroids are dropped from the results. These same blocks were correctly assigned with the sdo_relate/inside method.

      So sdo_relate/inside gives me duplicates and sdo_relate/contains drops records. Anyone know what might be happening?

      I validated the layers with context and no issues were reported. I am using 10.2.0.4.0 and I know that several patches have been applied. Is there a query to find out the patch level?
        • 1. Re: sdo_relate--inside vs contains--result problems with both
          John O'Toole
          Difficult to know what's up with your queries without having sample data. For point in polygon queries I always use SDO_ANYINTERACT.
          Is there a query to find out the patch level?
          You can use opatch
          Example: opatch lsinventory -detail
          • 2. Re: sdo_relate--inside vs contains--result problems with both
            627886
            Thanks Reggie. I tried the "anyinteract" mask with sdo_relate and it also returned duplicates. I tried sdo_join with both "inside" and "contains" and it returned the same results, but both ways dropped two points that are clearly well inside the polygons.
            • 3. Re: sdo_relate--inside vs contains--result problems with both
              Luc Van Linden
              so you know the two block points that are missing from the result. I assume you also know then to which polygon they relate?



              In that case, you might try the function sdo_geom.relate to see what explicit relationship is being evaluated for those, using the 'determine' keyword:



              http://download.oracle.com/docs/html/B14255_01/sdo_objgeom.htm#sthref1561



              If I understand correctly, the zip code polygon that surrounds the other zip code, has a hole (inner ring) equal to the surrounded zip code? Is that correct? If feasible can you post your query as also the specific polygons and points?



              Luc

              Edited by: lucvanlinden on Dec 11, 2008 9:20 AM
              • 4. Re: sdo_relate--inside vs contains--result problems with both
                627886
                Thanks Luc--I am still getting inconsistent results that are very perplexing. Even among the various spatial operators the results are mixed. As you can see below some operators return correct results and other do not--all are using the same spatial tables.

                --find out what oracle thinks the relationship is between '530330081002030' and '98104'
                SELECT a.blc_gcode,
                SDO_GEOM.RELATE(a.geom, 'determine', b.geom, 0.05) relationship
                FROM block_centroid_points a, zip_code_polygons b WHERE a.blc_gcode ='530330081002030' and b.zip = '98104';
                --result = disjoint, this is not correct, block is inside zip
                -----------------------------------------------------
                --find out what oracle thinks the relationship is between '98104' and '530330081002030'
                SELECT a.blc_gcode,
                SDO_GEOM.RELATE(b.geom, 'determine', a.geom, 0.05) relationship
                FROM block_centroid_points a, zip_code_polygons b WHERE a.blc_gcode ='530330081002030' and b.zip = '98104';
                --result = disjoint, this is not correct, zip contains block
                -----------------------------------------------------
                --try sdo_relate/inside
                select
                a.blc_gcode block, b.zip
                from
                block_centroid_points a, zip_code_polygons b
                where
                b.zip = '98104' and sdo_relate(a.geom, b.geom, 'mask=inside') = 'TRUE'
                order by block;
                --this query returns 185 block centroids in zip 98104,
                --block centroid 530330081002030 is not included in the result???
                -----------------------------------------------------
                --try sdo_inside
                select
                a.blc_gcode block, b.zip
                from
                block_centroid_points a, zip_code_polygons b
                where
                b.zip = '98104' and sdo_inside(a.geom, b.geom) = 'TRUE'
                order by block;
                --this query returns 186 block centroids in zip 98104 and
                --block centroid 530330081002030 is correctly included in the result
                -----------------------------------------------------
                --try sdo_relate/contains
                select
                a.blc_gcode block, b.zip
                from
                block_centroid_points a, zip_code_polygons b
                where
                b.zip = '98104' and sdo_relate(b.geom, a.geom ,'mask=contains') = 'TRUE'
                order by block;
                --this query returns 186 block centroids in zip 98104 and
                --block centroid 530330081002030 is correctly included in the result
                -----------------------------------------------------
                --try sdo_contains
                select
                a.blc_gcode block, b.zip
                from
                block_centroid_points a, block_centroid_points b
                where
                b.zip = '98104' and sdo_contains(b.geom, a.geom) = 'TRUE'
                order by block;
                --this query returns 185 block centroids in zip 98104,
                --block centroid 530330081002030 is not included in the result???                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
                • 5. Re: sdo_relate--inside vs contains--result problems with both
                  Luc Van Linden
                  The difference in results for sdo_relate with mask_specified and its equivalent sdo_mask_specified for the same query is indeed strange and unexpected.

                  some questions:

                  In the query in the last example, table b was zip_code_polygons instead of block_centroid_points? I assume this is a typo.

                  The tolerance you used in the sdo_geom.relate function, 0.05, is this the same as the one in the user_sdo_geom_meta_data for both zip_code_polygons and block_centroid_points? Can you confirm this?

                  Run this query:

                  select t.sdo_dimname, t.sdo_tolerance from table(select diminfo
                  from user_sdo_geom_metadata
                  where table_name = 'zip_code_polygons') t

                  select t.sdo_dimname, t.sdo_tolerance from table(select diminfo
                  from user_sdo_geom_metadata
                  where table_name = 'block_centroid_points') t

                  Additionally, can you return the distance between that specific point and polygon using the same tolerance:

                  SELECT
                  SDO_GEOM.SDO_DISTANCE(a.geom, b.geom, 0.05) dist
                  FROM block_centroid_points a, zip_code_polygons b WHERE a.blc_gcode ='530330081002030' and b.zip = '98104';

                  Luc
                  • 6. Re: sdo_relate--inside vs contains--result problems with both
                    627886
                    yes, that was a typo in the last query, table b should have been "zip_code_polygons"

                    yes, the tolerance is the same as in the metadata table -- 0.05 (one small difference -- sdo_dimname was x,y vs X,Y in the two tables but I dont see how affect the queries)

                    the query:
                    SELECT
                    SDO_GEOM.SDO_DISTANCE(a.geom, b.geom, 0.05) dist
                    FROM block_centroid_points a, zip_code_polygons b WHERE a.blc_gcode ='530330081002030' and b.zip = '98104';

                    returns a distance of 0, which sounds right since the point is inside the polygon.
                    • 7. Re: sdo_relate--inside vs contains--result problems with both
                      Luc Van Linden
                      Assuming you already validated your data not only visually but also by using SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT or SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT, it seems to me you're getting very contradicting results by the system. You might run this through support and raise maybe a bug report against it.

                      Luc
                      • 8. Re: sdo_relate--inside vs contains--result problems with both
                        627886
                        Thanks for all your help Luc. I will persue an sr with oracle. I thought these issues were fixed with the 10.2.0.4 server patches. I'll post again when I get some news.

                        David
                        • 9. Re: sdo_relate--inside vs contains--result problems with both
                          690787
                          Hi all,
                          I have a similar problem. I want to see which polygon in a polygon layer contains a point.
                          I am trying this:

                          my point is defined as:
                          SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(15,25,NULL),NULL,NULL)

                          and to select the one polygon the points falls within:

                          select long_name from countries_polygon where sdo_relate(shape,SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(15,25,NULL),NULL,NULL)), 'mask=inside') = 'TRUE';

                          The error I am getting is:
                          ORA-00920: invalid relational operator
                          *00920. 00000 - "invalid relational operator"*
                          **Cause:*
                          **Action:*
                          Error at Line: 1 Column: 135

                          I dont think this has anything to do with projection/CS. The countries are in lat/long.
                          How can I select a polygon in which the point falls within?
                          Thanks
                          • 10. Re: sdo_relate--inside vs contains--result problems with both
                            prechk
                            Hi,

                            have your countries polygon set the SRID value? I think that than you have to set some (not null) SRID to your point too.

                            Karel
                            • 11. Re: sdo_relate--inside vs contains--result problems with both
                              690787
                              I dont think it is the SRID.
                              If I try to use a rectangle to search which countries are within it it works.
                              This
                              select long_name from esri_countries_polygon where sdo_relate(shape,SDO_GEOMETRY(2003, NULL,NULL, SDO_ELEM_INFO_ARRAY(1,1003,3), SDO_ORDINATE_ARRAY(-50,-50, 50,50)), 'mask=inside') = 'TRUE';

                              returns country names.

                              So how do I relate point to polygon?
                              • 12. Re: sdo_relate--inside vs contains--result problems with both
                                _jum
                                You have a typo in the brackets:
                                --typo
                                select long_name from countries_polygon where sdo_relate(shape,SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(15,25,NULL),NULL,NULL)), 'mask=inside') = 'TRUE';
                                
                                --ok
                                select long_name from countries_polygon where sdo_relate(shape,SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(15,25,NULL),NULL,NULL), 'mask=inside') = 'TRUE';
                                • 13. Re: sdo_relate--inside vs contains--result problems with both
                                  690787
                                  That was it.Thanks.
                                  • 14. Re: sdo_relate--inside vs contains--result problems with both
                                    _jum
                                    So please mark the thread as answered an earn some points.