6

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?

2
  • 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
11
+50

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

9
  • 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. 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
1

To build on @Albert's excellent answer from 2014:


1. Checking for null coordinates

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


2. No spaces in ALL_SDO_GEOM_METADATA

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

2.8 Geometry Metadata Views

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.


3. Spatial indexes are now optional

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!

4. Metadata not necessary for ArcGIS

It turns out that it's not necessary to add a record to ALL_SDO_GEOM_METADATA when using the function-based spatial index in ArcGIS Desktop 10.7.1.

-3

Please follow this web link: Using Locator: Scenario and Examples.

1
  • A good answer should be complete and self-contained, using links as reference rather than as the answer. Please edit your answer to include the details on what to do and why/how it will answer the question. Link-only answers are likely to be deleted.
    – Midavalo
    Dec 20 '20 at 4:58

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.