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.