I am new to Oracle Spatial, I am trying to implement a service that will take coordinates from a map, and search our database to find any geometries that intersect with that map view. I works fine for the most part, but when I have the map zoomed out all the way where we can see the entire world, Oracle search returns no records at all.
Can someone see why the following SQL would return 0 records when I have over 20 records in the geometry table?
select g.id, g.name, SDO_UTIL.to_kmlgeometry(g.geom) as kml from geography g where SDO_RELATE(g.geom, SDO_GEOMETRY(2003,8307,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1), SDO_ORDINATE_ARRAY( -180,-90, 180,-90, 180,90, -180,90 )), 'mask=ANYINTERACT') = 'TRUE';
Several sample records inserted into the database:
INSERT INTO geography VALUES( GEOGRAPHY_ID_SEQ.nextVal, 'd2', SDO_GEOMETRY( 2003, -- 2d poly 8307, NULL, SDO_ELEM_INFO_ARRAY(1,1003,1), -- polygon SDO_ORDINATE_ARRAY(-77.01204711074818,38.88772105792418, -77.01200419540395,38.89196328335449, -77.00831347579947,38.89249647861828, -77.00827056045522,38.88768765356904, -77.01204711074818,38.88772105792418) ) ); INSERT INTO geography VALUES( GEOGRAPHY_ID_SEQ.nextVal, 'd3', SDO_GEOMETRY( 2003, -- 2d poly 8307, NULL, SDO_ELEM_INFO_ARRAY(1,1003,1), -- polygon SDO_ORDINATE_ARRAY(-77.05552889816111,38.86892085934577,-77.05329022237119,38.87065233921285,-77.05467677541158,38.87288138804671,-77.05786820241124,38.87248696633578,-77.05840989692072,38.87006762135151,-77.05552889816111,38.86892085934577) ) );