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?
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).
To build on @Albert's excellent answer from 2014:
From Albert's answer:
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).
It might be possible to check for null coordinates with an out-of-box Oracle function (without spaces) instead of creating a custom function:
1A) The NVL2() function
nvl2(least(x,y),sdo_geometry(...),null)
Since anything + null = null, that means if either x or y are null, then the result will be null.
1B) The DECODE() function
decode(x,null,null,decode(y,null,null,sdo_geometry(...)))
Others functions?
There are probably other ways to do this with an out-of-box function too. Hopefully someone can think of something a bit cleaner than NVL2() or DECODE().
From Albert's answer:
Note that you must specify the name of the owner of the function (here SCOTT).
We should mention that the expression in ALL_SDO_GEOM_METADATA cannot contain spaces (I stumbled with this at first).
The following considerations apply to schema, table, and column names, and to any SDO_DIMNAME values, that are stored in any Oracle Spatial and Graph metadata views:
They must contain only letters, numbers, and underscores. For example, such a name cannot contain a space ( ), an apostrophe ('), a quotation mark ("), or a comma (,).
All letters in the names are converted to uppercase before the names are stored in geometry metadata views or before the tables are accessed. This conversion also applies to any schema name specified with the table name.
From Albert's answer:
Create the spatial index ... You can now perform spatial searches on that table.
It is, of course, always best to create a spatial index if we can.
But, @Albert has pointed out in a PDF that spatial indexes are now optional in Oracle 12.2: Oracle Spatial and Graph: What’s New?.
I thought that was an interesting development.
Spatial Indexes are now OPTIONAL
- In 12.1 and before a spatial index is required to do any spatial query:
- Since 12.2, spatial indexes are optional
- BUT: A spatial index is still required for moderate to large tables for achieving acceptable performance!
I'm not an expert, but it seems like there are cases where adding a IS NOT NULL where clause is necessary — in order for the index to be used:
Function Based Index not improving query performance
The optimizer can only use an index (in place of a table) if it knows that the index entries are one for one with the table. Because it does not know if that expression may return a NULL (which are NOT stored in the index) it cannot make that direct swap unless you inform the optimizer.
Hence
SELECT ROOT FROM TST_AGGwill not have the chance to use the index, but
SELECT ROOT FROM TST_AGG WHERE ROOT IS NOT NULLshould be able to.
Esri Article: How To: Create Oracle Spatial function-based indexing
