Mark Needham

Thoughts on Software Development

Oracle Spatial: Querying by a point/latitude/longitude

without comments

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.

Be Sociable, Share!

Written by Mark Needham

March 23rd, 2012 at 11:54 pm

Posted in Software Development

Tagged with ,