SQLDeveloper and Map Data - part 2

In my last post SQLDeveloper and Map Data I showed how one can view Map Geometry Columns using SQL Developer. 

In the last part of the post I showed regions of the United States made up a number of states.

 

And the SQL statement identifying those regions made reference to a Region ID in  the TL_2014_US_STATE  geometry table.

Using this information and a Spatial function within the database wecan make a new geometry table with Regions allowing a user to drill through Maps as they would on a Dimension in a pivot.

Region
 - State
   - County

Creating new aggregated geometry from existing geometry table can be done in 5 simple steps:

1.Create New Table with Geometry Column and at least one key column

2.Update Oracle Spatial Metadata
3.Load data into new table using sdo_aggr_union Function
4.Create Spatial Index on new table
5.View new table / Custom Region

So let's get started.  We will connect to our MVDATA schema where we have our TL_2014_US_STATE table of US States with the Region Column.

First Create the New Table

create table us_custom_regions (
  region_id varchar2(2),
  region_name varchar2(15),
  region_name_caps varchar2(15),
  geometry mdsys.sdo_geometry);
commit;

Update the Oracle Spatial Metadata

This can be done via Right Mouse Click on the new table and bringing up the Spatial menu and selecting Update Spatial Metadata

Enter 8307 for the coordinate system identifier. Add two rows for the spatial dimensions and enter the values Longitude, -180, 180, 0.05 and Latitude, -90, 90, 0.05

This interface makes entries into the user_sdo_geom_metadata table

The SQL insert statement for the above action is:

Insert into user_sdo_geom_metadata values ('S_REGIONS', 'GEOMETRY',
mdsys.sdo_dim_array(mdsys.sdo_dim_element('Longitude', -180, 180, 0.05),
mdsys.sdo_dim_element('Latitude', -90, 90, 0.05)), 8307);

commit;

   

Load Data

 

We will source our TL_2014_US_STATE table of the US States and aggregate the geometry into one shape per region using the SDO_AGGR_UNION function within the database.

The SQL for selecting and loading the data is below

INSERT INTO US_REGIONS
select '1', 'East', 'EAST', sdo_aggr_union(sdoaggrtype(geometry, 0.05)) FROM US_STATE WHERE REGION = '1';

INSERT INTO US_REGIONS
select '2', 'Central', 'CENTRAL', sdo_aggr_union(sdoaggrtype(geometry, 0.05)) FROM US_STATE WHERE REGION = '2';

INSERT INTO US_REGIONS
select '3', 'South', 'SOUTH', sdo_aggr_union(sdoaggrtype(geometry, 0.05)) FROM US_STATE WHERE REGION = '3';

INSERT INTO US_REGIONS
select '4', 'West', 'WEST', sdo_aggr_union(sdoaggrtype(geometry, 0.05)) FROM US_STATE WHERE REGION = '4';

COMMIT;
 

Create Spatial Index

The Spatial index is created by invoking the Spatial Menu and selecting Create Spatial Index.

 

Provide a Name.

 

Again the actions done via the GUI can be done using SQL Statements.  The create spatial index SQL statement for the above screen is shown below
 CREATE INDEX MV_US_REGION_SPINDX ON "MVDATA"."US_REGIONS"("GEOMETRY") INDEXTYPE IS MDSYS.SPATIAL_INDEX

 

Viewing New Map Data

Bring up the Map Viewer within SQL Developer

  



 Create a SQL Statement

And view the results



 

 

 My next post will look at bringing this all together and incorporating in OBIEE.