Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: December 08, 2023 - 8:43 pm UTC

Last updated: December 08, 2023 - 8:43 pm UTC

Version:

Viewed 1000+ times

You Asked

Greetings,
I have created a map in Oracle APEX where the user can click and get the lat and long and drop a pin there. Now, I want to find which congressional district the pin was dropped in. I have a table with all congressional districts, but that is geojson data that gives polygons for the congressional districts. Is there a way for me to search a congressional district using lat and long?
Your help will be greatly appreciated
TIA
Vatsa

and we said...

Yes, you can query for containment using a spatial operator as described here: https://docs.oracle.com/en/database/oracle/oracle-database/19/spatl/spatial-operators-reference.html You can try it with this LiveLabs self-paced workshop: https://apexapps.oracle.com/pls/apex/r/dbpm/livelabs/view-workshop?wid=736 see Lab 4 Task 2 for containment queries.

Rating

  (4 ratings)

Comments

Thanks for your response.

Vatsa, December 11, 2023 - 3:07 pm UTC

I forgot to mention that the polygon data for the congressional districts in the table is ia CLOB and not SDO_GEOMETRY. Given a lat and long I want to find out which congressional district are those coordinates in.
Sorry for not being clear the first time.

Thanks
Vatsa

convert wkt to sdo_geometry

good luck, December 17, 2023 - 12:07 pm UTC

If you want to do spatial queries, you need to use sdo_geometry objects. You can convert the clob to a polygon dynamically in the query using the function sdo_util.from_wktgeometry

sdo_util.from_wktgeometry(your_clob_column)

Load geojson as SDO_GEOMETRY

Karin Patenge, January 08, 2024 - 8:49 am UTC

Vatsa,
Besides the conversion functions provided by the SDO_UTIL package, you can also load GeoJSON data via Oracle Spatial Studio. It automatically creates SDO_GEOMETRY objects from your GeoJSON.
Then, simply use SDO_RELATE with mask=INSIDE/ANYINTERACT or SDO_INSIDE/SDO_ANYINTERACT get your containment question answered.
Karin