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

Question

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

Here is the scenario;

I have a table (STATE_TABLE) which contains a spatial type (sdo_geometry) which is a polygon (of say a State), I have another table (UNIVERSITY_TABLE) which contains spatial data (sdo_geometry) (point/polygon) which contains Universities;

Now how do i find if a selected University('s) are in a given State using SQL select statement.

Primarily i want to locate existence of given object(s) in a geofence.

Thanks.

Answer 1

You're going to need to use either SDO_CONTAINS or SDO_RELATE with a mask of 'CONTAINS'

Please see documentation located at

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

or

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

Either one of these you'd do something like the following (assuming your spatial information is contained in a column called 'GEOM' that 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'll have to forgive me as I don't specifically remember the correct syntax for this and I don't know if the above join will work properly at all. This should be enough to point you in the right direction though.