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, 2014 at 21:04
  • @Vince that's what I was figuring. Just had to check my logic wasn't flawed.
    – Erik L
    Apr 10, 2014 at 3:02

3 Answers 3

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

11
  • That is brilliant, I am working right now to see if it works.
    – Erik L
    Apr 10, 2014 at 17:26
  • I don't think you actually NEED the function, but is does clean things up quite a bit.
    – travis
    Apr 12, 2014 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, 2014 at 13:01
  • 1
    For NULL values, the function needs to handle that. I have updated my reply to include that. May 7, 2014 at 14:32
  • 1
    Yes if you are using versions from 12.2 up, you can use a _v2 index type. The internal structures are identical, but the v2 index has more flexibility when dealing with partitioned tables. The original index is restricted to range partitioning. The V2 allows all partitioning methods. The v2 index also has the ability to use a simple btree structure for 2D points instead of a traditional rtree. May 14, 2022 at 21:41
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. WHERE...IS NOT NULL

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_AGG

will not have the chance to use the index, but

SELECT ROOT FROM TST_AGG WHERE ROOT IS NOT NULL

should be able to.


Esri Article: How To: Create Oracle Spatial function-based indexing

-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, 2020 at 4:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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