Geo Fence: how to find if a point or figure is inside a polygon using oracle

Question

How to find if a point or polygon is inside another polygon using oracle spatial SQL query

Here is the script;

I have a table (STATE_TABLE) that contains a spatial type (sdo_geometry) that is a polygon (like state), I have another table (UNIVERSITY_TABLE) that contains spatial data (sdo_geometry) (point / polygon) that contains universities ;

Now, how to find out if the selected university is selected in this state using the SQL select statement.

First of all, I want to find the existence of the given object (s) in the geofence.

Thanks.

Answer 1

You will need to use SDO_CONTAINS or SDO_RELATE with the mask 'CONTAINS'

See the documentation located in

SDO_CONTAINS http://download.oracle.com/docs/cd/E11882_01/appdev.112/e11830/sdo_operat.htm#sthref1064 for

or

operator SDO_RELATE http://download.oracle.com/docs/cd/E11882_01/appdev.112/e11830/sdo_operat.htm#i78531

Or one of them you would do something like the following (assuming that your spatial information is contained in the "GEOM" column, which is spatially indexed):

select ST.NAME, UT.UNIVERSITY_NAME from STATE_TABLE ST INNER JOIN UNIVERSITY_TABLE UT ON SDO_CONTAINS(ST.GEOM, UT.GEOM) = 'TRUE' 

You will have to forgive me, because I specifically don’t remember the correct syntax for this, and I don’t know if the above connection will work properly. That should be enough to point you in the right direction.