This package enables you to use Oracle Locator and the Spatial Option within Application Express. In an Application Express context, the logon user of the database session is typically APEX_PUBLIC_USER or ANONYMOUS. Spatial developers can not directly use DML on USER_SDO_GEOM_METADATA within such a session, for example, in SQL Commands within SQL Workshop. The Spatial view's trigger performs DML as the logon user, but it has to run as the application owner or workspace user. With the APEX_SPATIAL API, developers can use the procedures and functions below to insert, update and delete rows of USER_SDO_GEOM_METADATA as the current Application Express user. The package also provides a few utilities that simplify the use of Spatial in Application Express.
The data types used by this package are described in this section.
subtype t_srid is number;
c_no_reference_system constant t_srid := null;
c_wgs_84 constant t_srid := 4326; -- World Geodetic System, EPSG:4326
This procedure modifies a spatial metadata record.
APEX_SPATIAL.CHANGE_GEOM_METADATA (
p_table_name IN VARCHAR2,
p_column_name IN VARCHAR2,
p_new_table_name IN VARCHAR2 DEFAULT NULL,
p_new_column_name IN VARCHAR2 DEFAULT NULL,
p_diminfo IN mdsys.sdo_dim_array,
p_srid IN t_srid );
Table 23-1 CHANGE_GEOM_METADATA Parameters
| Parameter | Description |
|---|---|
|
|
Name of the feature table. |
|
|
Name of the column of type |
|
|
New name of a feature table (or null, to keep the current value). |
|
|
New name of the column of type |
|
|
|
|
|
|
The code below modifies the dimensions of column CITIES.SHAPE.
begin
for l_meta in ( select *
from user_sdo_geom_metadata
where table_name = 'CITIES'
and column_name = 'SHAPE' )
loop
apex_spatial.change_geom_metadata (
p_table_name => l_meta.table_name,
p_column_name => l_meta.column_name,
p_diminfo => SDO_DIM_ARRAY (
SDO_DIM_ELEMENT('X', -180, 180, 0.1),
SDO_DIM_ELEMENT('Y', -90, 90, 0.1) ),
p_srid => l_meta.srid );
end loop;
end;
This function creates a polygon that approximates a circle at (p_lon, p_lat) with radius of p_radius. See mdsys.sdo_util.circle_polygon for details.
APEX_SPATIAL.CIRCLE_POLYGON (
p_lon IN NUMBER,
p_lat IN NUMBER,
p_radius IN NUMBER,
p_arc_tolerance IN NUMBER DEFAULT 20,
p_srid IN t_srid DEFAULT c_wgs_84 )
RETURN mdsys.sdo_geometry;
Table 23-2 CIRCLE_POLYGON Parameters
| Parameter | Description |
|---|---|
|
p_lon |
Longitude position of the lower left point. |
|
p_lat |
Latitude position of the lower left point. |
|
|
Radius of the circle in meters. |
|
|
Arc tolerance (default 20). |
|
p_srid |
Reference system (default c_wgs_84). |
Table 23-3 CIRCLE_POLYGON Function Returns
| Return | Description |
|---|---|
|
|
The geometry for the polygon that approximates the circle. |
This example is a query that returns a polygon that approximates a circle at (0, 0) with radius 1.
select apex_spatial.circle_polygon(0, 0, 1) from dual
This procedure deletes a spatial metadata record.
APEX_SPATIAL.DELETE_GEOM_METADATA (
p_table_name IN VARCHAR2,
p_column_name IN VARCHAR2,
p_drop_index IN BOOLEAN DEFAULT FALSE );
Table 23-4 DELETE_GEOM_METADATA Parameters
| Parameter | Description |
|---|---|
|
|
Name of the feature table. |
|
|
Name of the column of type |
|
|
If TRUE (default is FALSE), drop the spatial index on the column. |
This example deletes metadata on column CITIES.SHAPE and drops the spatial index on this column.
begin
apex_spatial.delete_geom_metadata (
p_table_name => 'CITIES',
p_column_name => 'SHAPE',
p_drop_index => true );
end;
This procedure inserts a spatial metadata record and optionally creates a spatial index.
APEX_SPATIAL.INSERT_GEOM_METADATA (
p_table_name IN VARCHAR2,
p_column_name IN VARCHAR2,
p_diminfo in mdsys.sdo_dim_array,
p_srid in t_srid,
p_create_index_name IN VARCHAR2 DEFAULT NULL );
Table 23-5 INSERT_GEOM_METADATA_LONLAT Parameters
| Parameter | Description |
|---|---|
|
|
The name of the feature table. |
|
|
The name of the column of type |
|
|
The |
|
p_srid |
The SRID value for the coordinate system for all geometries in the column. |
|
|
If not null, a spatial index on the column is created with this name. Only simple column names are supported, function based indexes or indexes on object attributes cause an error. For more complex requirements, leave this parameter null (the default) and manually create the index. |
This example creates table CITIES, spatial metadata and an index on column CITIES.SHAPE.
create table cities (
city_id number primary key,
city_name varchar2(30),
shape mdsys.sdo_geometry )
/
begin
apex_spatial.insert_geom_metadata (
p_table_name => 'CITIES',
p_column_name => 'SHAPE',
p_diminfo => SDO_DIM_ARRAY (
SDO_DIM_ELEMENT('X', -180, 180, 1),
SDO_DIM_ELEMENT('Y', -90, 90, 1) ),
p_srid => apex_spatial.c_wgs_84 );
end;
/
create index cities_idx_shape on cities(shape) indextype is mdsys.spatial_index
/
This procedure inserts a spatial metadata record that is suitable for longitude/latitude and optionally creates a spatial index.
APEX_SPATIAL.INSERT_GEOM_METADATA_LONLAT (
p_table_name IN VARCHAR2,
p_column_name IN VARCHAR2,
p_tolerance IN NUMBER DEFAULT 1,
p_create_index_name IN VARCHAR2 DEFAULT NULL );
Table 23-6 INSERT_GEOM_METADATA_LONLAT Parameters
| Parameter | Description |
|---|---|
|
|
Name of the feature table. |
|
|
Name of the column of type |
|
|
Tolerance value in each dimension, in meters (default 1). |
|
|
if not null, a spatial index on the column is created with this name. Only simple column names are supported, function based indexes or indexes on object attributes cause an error. For more complex requirements, leave this parameter null (the default) and manually create the index. |
The code below creates table CITIES and spatial metadata for the column CITIES.SHAPE. By passing CITIES_IDX_SHAPE to p_create_index_name, the API call automatically creates an index on the spatial column.
create table cities (
city_id number primary key,
city_name varchar2(30),
shape mdsys.sdo_geometry )
/
begin
apex_spatial.insert_geom_metadata_lonlat (
p_table_name => 'CITIES',
p_column_name => 'SHAPE',
p_create_index_name => 'CITIES_IDX_SHAPE' );
end;
/
This function creates a point at (p_lon, p_lat).
APEX_SPATIAL.POINT (
p_lon IN NUMBER,
p_lat IN NUMBER,
p_srid IN t_srid DEFAULT c_wgs_84 )
RETURN mdsys.sdo_geometry;
| Parameter | Description |
|---|---|
|
|
Longitude position. |
|
|
Latitude position. |
|
|
Reference system (default c_wgs_84). |
This example is a query that returns a point at (10, 50).
select apex_spatial.point(10, 50) from dual;
This example is equivalent to:
select mdsys.sdo_geometry(2001, 4326, sdo_point_type(10, 50, null), null, null) from dual;
This function creates a rectangle from point at (p_lon1, p_lat1) to (p_lon2, p_lat2).
APEX_SPATIAL.RECTANGLE (
p_lon1 IN NUMBER,
p_lat1 IN NUMBER,
p_lon2 IN NUMBER,
p_lat2 IN NUMBER,
p_srid IN t_srid DEFAULT c_wgs_84 )
RETURN mdsys.sdo_geometry;
Table 23-9 RECTANGLE Parameters
| Parameter | Description |
|---|---|
|
|
Longitude position of the lower left point. |
|
|
Latitude position of the lower left point. |
|
|
Longitude position of the upper right point. |
|
|
Latitude position of the upper right point. |
|
|
Reference system (default c_wgs_84). |
Table 23-10 RECTANGLE Function Returns
| Return | Description |
|---|---|
|
|
The geometry for the rectangle (p_lon1, p_lon2, p_lon2, p_lat2). |
This example is a query that returns a rectangle from (10, 50) to (11, 51).
select apex_spatial.rectangle(10, 50, 11, 51) from dual
This example is equivalent to:
select mdsys.sdo_geometry(
2003, 4326, null,
sdo_elem_info_array(1, 1003, 1),
sdo_ordinate_array(10, 50, 11, 50, 11, 51, 10, 51, 10, 50))
from dual;