This discussion is archived
14 Replies Latest reply: Oct 18, 2012 8:36 AM by _jum RSS

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

627886 Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    karel prech Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    That was it.Thanks.
  • 14. Re: sdo_relate--inside vs contains--result problems with both
    _jum Journeyer
    Currently Being Moderated
    So please mark the thread as answered an earn some points.

Legend

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