3 Replies Latest reply: Jan 8, 2014 6:16 AM by 1ad352ba-085e-4d0d-9e31-981990cdb378 RSS

    SDO_INSIDE Points in Polygon and Rectangle

    1ad352ba-085e-4d0d-9e31-981990cdb378

      Hi,

      I've created some point geometries in a database column and am trying to find out which ones lay inside a rectangle as a test, I anticipate this moving on to irregular shaped polygons. When I used SDO_INSIDE against a polygon I get points returned outside the polygon but when I use SDO_INSIDE against a rectangle all the points returned lay within the polygon.

       

      I've also tried SDO_RELATE  'mask=inside' and get the same results

       

      I'd be grateful of any suggestions with as to what I am missing.

       

      Thanks,

      Matt

       

      > SELECT *

      FROM v$version

      BANNER                                                                       

      --------------------------------------------------------------------------------

      Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production            

      PL/SQL Release 11.2.0.2.0 - Production                                         

      CORE    11.2.0.2.0    Production                                                       

      TNS for 32-bit Windows: Version 11.2.0.2.0 - Production                        

      NLSRTL Version 11.2.0.2.0 - Production   

                                          

       

      > ALTER TABLE sites

      add (site_geo_location SDO_GEOMETRY)

       

      table SITES altered.

       

      > UPDATE sites

      set site_geo_location=

         SDO_GEOMETRY(2001 , 8307 ,

           SDO_POINT_TYPE (longitude,latitude,NULL ), NULL, NULL )

           where latitude is not null

       

      3,818 rows updated.

       

      > INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)

         VALUES ('SITES','SITE_GEO_LOCATION',

         SDO_DIM_ARRAY

           (SDO_DIM_ELEMENT('LONGITUDE', -180.0, 180.0, 0.0000005),

           SDO_DIM_ELEMENT('LATITUDE', -90.0, 90.0, 0.0000005)),

         8307)

       

      1 rows inserted.

       

      > commit

       

      committed.

       

      > create INDEX sites_s_idx ON sites(site_geo_location)

        INDEXTYPE IS mdsys.spatial_index PARAMETERS ('layer_gtype=POINT' )

       

      index SITES_S_IDX created.

       

      > SELECT

      MIN(longitude),MAX(longitude),MIN(Latitude),MAX(Latitude)

      FROM sites s

        WHERE SDO_INSIDE(s.site_geo_location, mdsys.sdo_geometry(2003,8307 , NULL, mdsys.sdo_elem_info_array(1,1003,1),

      mdsys.sdo_ordinate_array(0,-20, 50,-20, 50,50, 0,50, 0,-20))) = 'TRUE'

       

      MIN(LONGITUDE) MAX(LONGITUDE) MIN(LATITUDE) MAX(LATITUDE)

      -------------- -------------- ------------- -------------

                .111          49.95           -20     51.92021 <===========Why is this included as 50 < 51.92021?????

       

      > SELECT

      MIN(longitude),MAX(longitude),MIN(Latitude),MAX(Latitude)

      FROM sites s

        WHERE SDO_INSIDE(s.site_geo_location, mdsys.sdo_geometry(2003,8307 , NULL, mdsys.sdo_elem_info_array(1,1003,3),

      mdsys.sdo_ordinate_array(0,-20, 50,50))) = 'TRUE'

      MIN(LONGITUDE) MAX(LONGITUDE) MIN(LATITUDE) MAX(LATITUDE)

       

      -------------- -------------- ------------- -------------

                .111          49.95           -20       49.9898 <=========== Max latitude <50 - as expected.