geolocationoracle-dbmssdo-geometry
I'm working on a project to link existing entities in an Oracle database with their geolocation, but the database is a tabular not spatial. Is there a compatible type that can store the geolocation or another way to serialize the geodata and store it in a column with a suitable datatype?
I did search for suitable datatypes but all say that its SDO_GEOMETRY which is not found in the tabular Oracle.
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).
Let's assume that your table is like this:
create table central_offices (
id number primary key,
lat varchar2(20),
lng varchar2(20)
);
insert into central_offices (id, lat, lng) values (1, '39.018483', '22.9983436');
commit;
Your strings are stored with a point as decimal separator. When you use this string in a statement that expects a number, Oracle will automatically do the proper type casting and conversion, i.e. parse the string into a number.
BUT: that parsing happens in the context of the locale you use in your session. Since 2100 is a Greek system, I assume you are in Greece, and so use a Greek locale where the decimal separator is a comma. If you try converting the above string to a number, you will get the following error:
SQL> select to_number('39.018483') from dual;
select to_number('39.018483') from dual
*
ERROR at line 1:
ORA-01722: μη αποδεκτός αριθμός
Replacing ',' with '.' on the fly is a valid solution. A simpler approach is just to override the locale for your session:
alter session set nls_numeric_characters = '.,';
select id,
sdo_cs.transform (
sdo_geometry (2001, 4326, sdo_point_type(lng, lat, null), null,null),
2100
).get_wkt()
from central_offices;
ID SDO_CS.TRANSFORM(SDO_GEOMETRY(2001,4326,SDO_POINT_TYPE(LNG,LAT,NULL),NULL,NULL)
---- -------------------------------------------------------------------------------
1 POINT (413136.397473566 4319017.56676377)
Best Answer
Well, you can still just store the location information (I am assuming you mean long/lat GPS coordinates) as two X and Y (or longitude and latitude) number columns. Or you could also use a text string encoded in GeoJSON. Or an XMLTYPE holding a GML encoding.
Those are all possible and legal, and fine if all you want is to store the coordinates, without doing any sort of processing in the database. If you want to perform any kind of spatial querying or processing (like: searching for entities that are within some distance of another, measure the distance between entities etc) then, as Vince indicated, you need to use some specialized data type. The simplest (and cheapest: it's free) is to use Oracle's native spatial type called SDO_GEOMETRY.
That type comes with Oracle Locator, the free part of Oracle Spatial. It is installed by default in all variants of the Oracle Database (including the free Express Edition). If it does not exist in your database, that means that the DBA (or whoever created it) did an explicit choice of NOT including it in the database. To add it, just ask him/her to use the DBCA (Database Configuration Assistant) to add it.