GeoTools
  1. GeoTools
  2. GEOT-1940

Oracle datastore is not able to properly return data when the bbox filter exceeds the world bounds

    Details

    • Type: Bug Bug
    • Status: Closed Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.5-M2
    • Fix Version/s: None
    • Component/s: jdbc
    • Labels:
      None

      Description

      Unlike other spatial databases Oracle knows about lat/lon periodicity and normalizes the coordinates provided in the query, meaning a bbox exceeding the world bounds end ups being smaller than the actual world bounds and return less records.
      For example in a sample dataset covering the world bounds with 19 polygons here are some results:

      SELECT count(*)
      FROM "FAO_MAJOR" 
      WHERE SDO_RELATE("GEOMETRY",MDSYS.SDO_GEOMETRY(2003,4326,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(-179.99999,-89.99999,179.99999,89.99999)),'mask=anyinteract querytype=WINDOW') = 'TRUE';
      --> 19
      
      SELECT count(*)
      FROM "FAO_MAJOR" 
      WHERE SDO_RELATE("GEOMETRY",MDSYS.SDO_GEOMETRY(2003,4326,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(-180,-90,180,90)),'mask=anyinteract querytype=WINDOW') = 'TRUE';
      --> 17 (not sure why)
      
      SELECT count(*)
      FROM "FAO_MAJOR" 
      WHERE SDO_RELATE("GEOMETRY",MDSYS.SDO_GEOMETRY(2003,4326,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(-310.1484375,-143.2460937499999,310.1484375,145.2460937500001)),'mask=anyinteract querytype=WINDOW') = 'TRUE';
      --> 12!
      

        Issue Links

          Activity

          Hide
          Andrea Aime added a comment -
          This issue will be fixed against the jdbc-ng version of the Oracle datastore
          Show
          Andrea Aime added a comment - This issue will be fixed against the jdbc-ng version of the Oracle datastore
          Hide
          Andrea Aime added a comment -
          Hopefully fixed. Thanks to Alessio Fabiani and Ivano Picco for providing a patch and keeping up with my requests of improvement.
          Show
          Andrea Aime added a comment - Hopefully fixed. Thanks to Alessio Fabiani and Ivano Picco for providing a patch and keeping up with my requests of improvement.

            People

            • Assignee:
              Andrea Aime
              Reporter:
              Andrea Aime
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: