Spatial Data Example for Apex

Staring with version 4.2.5, APEX provides the APEX_SPATIAL package that allows you to manage spatial metadata and indexes. Below is a quick example showing basic usage:

[sourcecode language=”sql”]
— Create table with a latitude and longitude column, as well as a sdo_geometry column
— which allows you to perform spatial queries against the data.
create table geo_testing (id number
,lat varchar2(30)
,lng varchar2(30)
,geom sdo_geometry);

— Insert sample data.
insert into geo_testing (id,lat,lng)
values (1,’32.894146491759976′,’-97.10038661956787′);

insert into geo_testing (id,lat,lng)
values (2,’32.91158569379285′,’-97.09038734436035′);

— Convert latitude and longitude to sdo_geometry.
update geo_testing set geom =
MDSYS.SDO_GEOMETRY(2001
,4326
,MDSYS.SDO_POINT_TYPE (lng, lat,NULL)
,NULL
,NULL);

— Create spatial metadata and index.
— This is neccessary for performing spatial operations on the data.
— The APEX_SPATIAL package is needed for this because the table USER_SDO_GEOM_METADATA
— has a trigger that fires as schema user. Since in the APEX context the user is
— APEX_PUBLIC_USER (or anonymous), that trigger would fail. The INSERT_GEOM_METADATA
— procedure works around this issue.
begin
apex_spatial.insert_geom_metadata (
p_table_name => ‘GEO_TESTING’
,p_column_name => ‘GEOM’
,p_diminfo => SDO_DIM_ARRAY (
SDO_DIM_ELEMENT(‘X’, -180, 180, 1),
SDO_DIM_ELEMENT(‘Y’, -90, 90, 1)
)
,p_srid => 4326
,p_create_index_name => ‘GEO_TESTING_GEOM_IDX’
);
end;

— Sample query to calculate the distance in miles between the two points.
select round(sdo_geom.sdo_distance(a.geom
,b.geom
,1
,’unit=mile’
)
,2) as miles
from geo_testing a
, geo_testing b
where a.id = 1
and b.id = 2;

[/sourcecode]

To clean things up use:

[sourcecode language=”sql”]– Cleanup —
— Remove spatial metadata and index.
begin
apex_spatial.delete_geom_metadata (
p_table_name => ‘GEO_TESTING’,
p_column_name => ‘GEOM’,
p_drop_index => true );
end;

— Drop table.
drop table geo_testing;

[/sourcecode]

The various distance units available can be queried from MDSYS.SDO_DIST_UNITS:

select * from mdsys.sdo_dist_units
where sdo_unit is not null;