0

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?

2

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';
  • This is really helpful. Our cloud-based db host company places limits on us building indices, but I think I can work around that within SQL developer. – Ian Horn Sep 26 '16 at 16:41
1

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.

  • Thanks for that. Do you have a snippet of syntax of creating sdo_point_type? – Ian Horn Sep 19 '16 at 19:04
  • Sorry I have experience with Postgres, MySQL and SQL Server but not Oracle. The cheetsheet at mapoholic.wordpress.com/2010/10/18/… gives some examples. If you construct the geometry from Well Known Text concatinate the text part with your lat and long values. – Jon Lynch Sep 19 '16 at 20:25
  • Thanks, as a temporary solution I exported and did a spatial join in ArcMap. I think creating the view is the correct way so I'll keep trying that. – Ian Horn Sep 19 '16 at 20:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.