arcmap - Can I create an Oracle Spatial View from a non-spatial table?

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?

Answer

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).