Oracle Spatial: Querying by a point/latitude/longitude
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.
About the author
I'm currently working on short form content at ClickHouse. I publish short 5 minute videos showing how to solve data problems on YouTube @LearnDataWithMark. I previously worked on graph analytics at Neo4j, where I also co-authored the O'Reilly Graph Algorithms Book with Amy Hodler.