3

Let's say I have an Oracle database table with latitudes and longitudes. I don't have access to modify the schema of this table, or even create tables. However, I can create views and indexes on those views. I purpose the following problem:

Can I create a view that takes in two fields, and creates an SDO_GEOMETRY (point in this case) "on the fly", or does the field have to be based upon an already existing column?

  • 1
    You can't create an index on a view, only on a "materialized view" (which is really a table). How many rows are in the table? While creating a dynamic geometry is possible, the performance is not generally going to be adequate for common use. – Vince Apr 9 '14 at 21:04
  • @Vince that's what I was figuring. Just had to check my logic wasn't flawed. – Erik L Apr 10 '14 at 3:02
7

Yes, absolutely, you can do that. The principle is to define a function-based index. The steps are like this:

Assume I have a table like this:

create table customers (
  id number primary key,
  name varchar2(30),
  longitude number,
  latitude number
);

1) Define a function that transforms the long and lat columns into a geometry. Note that should any of the input values (longitude or latitude) be null, the function returns null (meaning the geometry will not be indexed and not searchable).

create or replace function make_point (
  longitude in number,
  latitude  in number)
return sdo_geometry deterministic is
begin
  if longitude is not null and latitude is not null then
    return
      sdo_geometry (
        2001, 4326,
        sdo_point_type (longitude, latitude, null),
        null, null
      );
  else
    return null;
  end if;
end;
/

Notice that the function must be defined as deterministic.

2) Setup the spatial metadata:

insert into user_sdo_geom_metadata (table_name, column_name, diminfo, srid)
values (
  'CUSTOMERS', 
  'SCOTT.MAKE_POINT(LONGITUDE,LATITUDE)',
  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;

Specify the expression that produced the geometry using the function you just defined. Note that you must specify the name of the owner of the function (here SCOTT)

3) Create the spatial index on the function:

create index customers_sx
  on customers (make_point(longitude, latitude))
  indextype is mdsys.spatial_index;

4) You can now perform spatial searches on that table. For example to find all customers within 10 km of one of our stores.

select c.id, c.name
from   customers c, stores s
where  sdo_within_distance (
         make_point(c.longitude, c.latitude),
         s.location,
         'distance=10 unit=km') = 'TRUE'
and    s.id = 'R456Bk';

5) You can also now define a view on that table, like this

create or replace view customers_v as
select id, name , make_point(longitude, latitude) location
from customers;

and use that view in your queries:

select c.id, c.name
from   customers_v c, stores s
where  sdo_within_distance (
         c.location,
         s.location,
         'distance=10 unit=km') = 'TRUE'
and    s.id = 'R456Bk';

If you want to also see the content of the view on a map (using some GIS tool), you will probably also need to define metadata for the view. This is NOT needed for spatial queries, but is a common requirement for GIS tools.

insert into user_sdo_geom_metadata (table_name, column_name, diminfo, srid)
values (
  'CUSTOMERS_V', 
  'LOCATION',
  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;

There will be a tiny performance penalty obviously, since the function will be called repeatedly, but the cost is negligible.

UPDATE:

As Travis mentions, you can actually do all the above without defining an explicit function: just use the default SDO_GEOMETRY constructor. Here are the steps:

1) Setup the spatial metadata. Notice that you need to explicity specify MDSYS as the owner of the function:

insert into user_sdo_geom_metadata (table_name, column_name, diminfo, srid)
values (
  'CUSTOMERS', 
  '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
);

2) create the function-based spatial index:

create index customers_sx
  on customers (sdo_geometry(2001,8307,sdo_point_type(longitude,latitude,null),null,null))
  indextype is mdsys.spatial_index;

3) Example of a spatial search:

select c.id, c.name
from   customers c, stores s
where  sdo_within_distance (
         sdo_geometry(2001,8307,sdo_point_type(c.longitude,c.latitude,null),null,null),
         s.location,
         'distance=10 unit=km') = 'TRUE'
and    s.id = 'R456Bk';

4) Again, use a view to hide the constructor call:

create or replace view customers_v as
select id, name, sdo_geometry(2001,8307,sdo_point_type(longitude,latitude,null),null,null) location
from customers;

and use it in the queries just like in the previous case:

select c.id, c.name
from   customers_v c, stores s
where  sdo_within_distance (
         c.location,
         s.location,
         'distance=10 unit=km') = 'TRUE'
and    s.id = 'R456Bk';

NOTE: This approach works only if all rows have their LONGITUDE and LATITUDE columns populated! If some are missing (=set to NULL), then you need an explicit function to handle those (and return a NULL geometry). Passing NULL values for X and Y to the SDO_GEOMETRY results in invalid geometries (and the index creation will fail).

  • That is brilliant, I am working right now to see if it works. – Erik L Apr 10 '14 at 17:26
  • I don't think you actually NEED the function, but is does clean things up quite a bit. – travis Apr 12 '14 at 0:59
  • Any ideas on if the lat/lon columns have null values? Should a view be created, or should the function handle that? – Erik L May 7 '14 at 13:01
  • 1
    For NULL values, the function needs to handle that. I have updated my reply to include that. – Albert Godfrind May 7 '14 at 14:32
  • Many thanks for this! For those that use oracle with geoserver, i confirm this approach works very well (tested with Geoserver 2.5.1). – eblondel Oct 5 '15 at 20:33

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.