In the first post of this series I set the scene for our spatial visualisation. I will now describe how to prepare spatial tables for usage in geo-spatial analysis in this post.If you recall, I mentioned that there is a hierarchy of regions that has to be implemented and visualised using maps. That hierarchy has Sales regions, Chiho regions and Prefectures (Chiho and Prefecture are administrative regions in Japan). The idea is to show a map on the sales region level and than drill down to Chiho and finally to Prefectures level.
I have also explained that the starting point for this is database table MAP_PREFECTURES which I found and downloaded from internet so the precision on the map might not be the best.
As you saw, table MAP_PREFECTURE has already an attribute called GEOMETRY, which is of SDO_GEOMETRY type. This means that spatial information is already present. So to be able to start using in my spatial analyses I just have to perform two tasks:
- register MAP_PREFECTURES table with USER_SDO_GEOM_METADATA view
- create a spatial index on GEOMETRY column
Register tables with spatial information with USER_SDO_GEOM_METADATA
USER_SDO_GEOM_METADATA contains metadata information for all spatial tables owned by the user (schema). This is the only view that can be updated, and it is the one in which spatial users must insert metadata related to spatial tables.
It is required to update the USER_SDO_GEOM_METADATA view before creating the spatial index.
To register MAP_PREFECTURE table with USER_SDO_GEOM_METADA view you have to issue the following command:
insert into USER_SDO_GEOM_METADATA
(TABLE_NAME, COLUMN_NAME,
DIMINFO, SRID)
VALUES
('MAP_PREFECTURES', 'GEOMETRY',
MDSYS.SDO_DIM_ARRAY(
MDSYS.SDO_DIM_ELEMENT('X', -180, 180, 0.05),
MDSYS.SDO_DIM_ELEMENT('Y',
-90, 90, 0.05)), 8307);
You can always check the content of USER_SDO_GEOM_METADATA with SELECT * FROM USER_SDO_GEOM_METADATA.
Create spatial index
Once data has been loaded into the spatial tables through either bulk or transactional loading, a spatial index must be created on the tables for efficient access to the data.
The introduction of spatial indexing capabilities into the Oracle database engine is a key feature of the Spatial option. A spatial index, like any other index, provides a mechanism to limit searches, but in this case based on spatial criteria such as intersection and containment. A spatial index is needed to find objects within an indexed data space that interact with a given point or area of interest (window query) and to find pairs of objects from within two indexed data spaces that interact spatially with each other (spatial join)
Creation of spatial index is very similar to creation of normal index:
create index MAP_PREFECTURES_SPI on MAP_PREFECTURES(GEOMETRY) indextype is mdsys.spatial_index;
In this moment, we could already have used this map in OBI, but before that let’s just create another two missing tables and than continue.
Aggregate spatial tables
Table MAP_PREFECTURES contains spatial data for each individual prefecture. Chihos in Japan consist of several prefectures. In order to achieve that in Oracle database spatial aggregation functions are used. SDO_AGGR_UNION is one of them.
SDO_AGGR_UNION returns a geometry object that is the topological union (OR operation) of the specified geometry objects. It has the following format:
SDO_AGGR_UNION(
AggregateGeometry SDOAGGRTYPE
) RETURN SDO_GEOMETRY;
AggregateGeometry is an object of type SDOAGGRTYPE that specifies the geometry column and dimensional array.
So let me apply this function to MAP_PREFECTURES table to create a new spatial table MAP_CHIHOS.
create table MAP_CHIHOS as (
select CHIHO, SALES_REGION,
SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(geometry, 0.05)) GEOMETRY
from MAP_PREFECTURES
group by CHIHO, SALES_REGION);
Create table above creates a new table with the following structure:
and its content:
From here on we already know that we need to add new recort into USER_SDO_GEOM_METADATA view and create new spatial index for new MAP_CHICHOS table.
insert into USER_SDO_GEOM_METADATA(
TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)
VALUES(
'MAP_CHIHOS', 'GEOMETRY',
MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X', -180, 180, 0.05),
MDSYS.SDO_DIM_ELEMENT('Y', -90, 90, 0.05)), 8307);
create index MAP_CHIHOS_SPI on MAP_CHIHOS(GEOMETRY) indextype is mdsys.spatial_index;
Now, I have to prepare one more table for SALES_REGION map layer:
- Apply spatial aggregation on MAP_CHIHO table to create new table MAP_SALES_REGIONS,
- Add new entry in USER_SDO_GEOM_METADATA and
- Create a new spatial index on MAP_SALES_REGIONS table.
create table MAP_SALES_REGIONS as (
select SALES_REGION,
SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(geometry, 0.05)) GEOMETRY
from MAP_CHIHOS
group by SALES_REGION);
insert into USER_SDO_GEOM_METADATA(
TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)
VALUES(
'MAP_SALES_REGIONS', 'GEOMETRY',
MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X', -180, 180, 0.05),
MDSYS.SDO_DIM_ELEMENT('Y', -90, 90, 0.05)), 8307);
create index MAP_SALES_REGIONS_SPI on MAP_SALES_REGIONS(GEOMETRY) indextype is mdsys.spatial_index;
Summary
My final result of this lesson is as follows:
- 3 tables MAP_PREFECTURES, MAP_CHIHOS, MAP_SALES_REGIONS
- 3 records in USER_SDO_GEOM_METADATA table, one for each table
- 3 spatial indexes, one on each table.