Performing a spatial relationship in Oracle SQL with only lat/long.

Question

I currently have a query that pulls a latitude field and a longitude field (separate fields). This is sufficient for one portion of customer product, but for a second table I need to add a second component that relates the coordinates to a polygon. Is there a query to perform a spatial query or join using SQL (Oracle) without having the vector component, but instead the lat/long values in separate fields?

Answer 1

What you need is to use a function-based spatial index, i.e. define a spatial index that constructs a geometry object dynamically. Here is an example on a table called US_CITIES_SX that looks like this:

Name         Null?    Type
------------ -------- -----------------
ID                    NUMBER
CITY                  VARCHAR2(42 CHAR)
STATE_ABRV            VARCHAR2(2 CHAR)
POP90                 NUMBER
RANK90                NUMBER
LONGITUDE             NUMBER
LATITUDE              NUMBER

1) Setup the spatial metadata (needed to build the spatial index)

insert into user_sdo_geom_metadata (table_name, column_name, diminfo, srid)
values (
  'US_CITIES_XY', 
  'MDSYS.SDO_GEOMETRY(2001,8307,SDO_POINT_TYPE(LONGITUDE,LATITUDE,NULL),NULL,NULL)',
  sdo_dim_array (
    sdo_dim_element('long', -180.0, 180.0, 0.5),
    sdo_dim_element('lat', -90.0, 90.0, 0.5)
  ),
  4326
);
commit;

2) create the function-based spatial index

create index us_cities_xy_sx
  on us_cities_xy (sdo_geometry(2001, 4326, sdo_point_type(longitude, latitude, null), null, null))
  indextype is mdsys.spatial_index;

3) Perform queries

select c.city, c.pop90
from   us_cities_xy c, us_states s
where  sdo_inside (
         sdo_geometry(2001,4326,sdo_point_type(c.longitude,c.latitude,null),null,null), 
         s.geom
       ) = 'TRUE'
and    s.state = 'Colorado';

4) Create a view to hide the function call

create or replace view us_cities_xy_v as
select id, city, state_abrv, pop90, rank90, sdo_geometry(2001,4326,sdo_point_type(longitude,latitude,null),null,null) as location
from us_cities_xy;

5) Perform queries on view

select c.city, c.pop90
from   us_cities_xy_v c, us_states s
where  sdo_inside (c.location, s.geom) = 'TRUE'
and    s.state = 'Colorado';

Answer 2

I have handled a similar issue in MySQL by creating a view that includes making a point geometry. It appears that in Oracle the function is SDO_POINT_TYPE( X, Y, SRID).

It is important that you use the correct SRID to allow querying against other geometries.