Working with 3rd Party Shapefiles

The past few posts may have seemed rather fundamental, however if these basic tasks are not done it is impossible to be successful in deploying maps within OBIEE.

When I've done OBIEE and Spatial Analytic presentations at conferences I am often asked where can one get more maps.  I covered online sources for map shapefiles in this previous post.

With MapBuilder you now have 2 options for using map shapefiles.  The original and my preferred use is to import shapefiles into the Oracle database as a table with geometry data and build necessary themes from that base table.  In version 11 of MapBuilder many enhancements were add and most notably with regard to ShapeFiles is Custom Geometry Themes.  I will leave you to the documentation to explore this option or wait until I write up a blog post on it.


Importing Shapefiles

The shapefile used in the following example was downloaded from U.S. Census Bureau.
 
http://www.census.gov/geo/maps-data/data/tiger.html
 
As indicated in the shapefile post the Cartographic Boundary Shapefiles will be sufficient.
 

 
The next option is the map scale and for OBIEE maps.  The 20m = 1:20,000,000 resolution level will be fine. 
 

 
Download and save to a location on your computer and extract the .zip file.

 
You can look at the metadata that is included with the map by opening the .dbf file with Excel.
 

 
Note that this data file contains administrative boundary information for the 50 states and the District of Columbia and Puerto Rico.
 
Looking though the data we need to find column(s) that contain unique data.  This is important when linking to OBIEE tabular data. 
 
The key columns in this data set that we can work with are:

• STATEFP - State FIPS
• GEOID
• STUPS (abbreviation)
• NAME

As a note when working with US County data the State and County FIPS or a composite County - State Abbreviation will be needed since a County Name is not unique.
 
Now that the shapefile has been extracted it needs to be uploaded into the mvdata schema on the Oracle Database.
 
Launch MapBuilder and load the connection to the mvdata schema.

From top menu select Tools > Import Shapefile
 

Step 1 of the Import Shapefile is the Data Selection dialog.

Keep the option set to Single FIle and Click the Shapefile button

An open file dialog window will appear. Navigate to your saved and extracted Shapefiles and Select the .shp file

The Geometry table name is auto populated based on file name it can be changed, just be aware of database naming requirements.  Click Next

Set the SRID.  This is set from the .prj file within the Shapefile collection.  Keep the checkbox for Create/Update spatial index checked.  Click Next

A theme can be automatically created upon import.  For the sake of this exercise it will be skipped and saved for later example.  Click Next

Review the activities to be executed.  Click Finish

An import status window will display

Once the import is complete click the Refresh button to reload MapBuilder

In Lower left corner click the Show Data toggle and Expand the Spatial Tables and Geometry Tables.

Find the imported table.  Right click and select Preview

On the right side of the toolbar there is a green arrow to start the query to preview the map

The icons in upper left of toolbar allow for zoom and pan. 

Zoom in on the Map to get a better view.

The Identify tool in the Preview control allows you to see the attribute data that accompanies the map.

Now that there is a spatial data loaded to our MVDATA schema we are ready to create a Map Theme that we can use as a Map Layer in OBIEE, but that is the topic for another post.