We’re using Oracle Spatial on the application I’m working on and while most of the time any spatial queries we make are done from Java code we wanted to be able to run them directly from SQL as well to verify the code was working correctly.
We normally end up forgetting how to construct a query so I thought I’d document it.
Assuming we have a table table_with_shape which has a column shape which is a polygon, if we want to check whether a lat/long value interacts with that shape we can do that with the following query:
SELECT * FROM table_with_shape tws WHERE SDO_ANYINTERACT(tws.shape, SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(<LongValueHere>, <LatValueHere>, NULL), NULL, NULL) ) = 'TRUE'
The first parameter to SDO_GEOMETRY defines the type of geometry which in this case is a point.
The second parameter is the coordinate system which is 8307 since we’re using the ‘WGS 84 longitude/latitude’ system.
The third parameter is our point and the rest of the parameters aren’t interesting here so we pass null for them.