Oracle search against intersect of a region that covers the whole world doesn't find any records

Question

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) ) );

Answer 1

If you are using a geodetic coordinate system (8307) a polygon cannot cover more than half the earth.

http://docs.oracle.com/cd/B13789_01/appdev.101/b10826/sdo_cs_concepts.htm#i891851

"No polygon element can have an area larger than one-half the surface of the Earth."

You could try making a multipolygon any time your shape crosses 0 latitude or longitude by splitting your polygon across these boundaries. You probably have no data large enough to need that treatment, just do it to the map extents polygon.

Answer 2

Russell is right, you can't have a polygon that is larger than half the world. But if you know that your query shape is a rectangle, there is an easier way. In that case, you can use an optimized rectangle, rather than try to split your query shape so that it is less than half the world. Additional, optimized rectangles will more closely follow the lines as latitude, and not the great circle like a normal polygon would.

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,3), 
        SDO_ORDINATE_ARRAY( -180,-90, 180,90)), 
    'mask=ANYINTERACT') = 'TRUE';

Oracle Doc