As mentioned in my last post on Geospatial Analytics (http://justanalytics.com/spatial), the shapefile (.shp) is a standard file, which contains the geospatial information that we need to plot our information on a base map like Google Maps. This blog will cover the usage of Oracle Map Builder, especially in the process of importing the shapefiles.

Map Builder is an Oracle tool which is used in the management of map/spatial metadata. Map Builder helps in importing shapefiles, creating themes, styles, spatial tables, indexes, etc. The tool is available for download in the Oracle website (http://www.oracle.com/technetwork/middleware/mapviewer/downloads/index.html) and does not require any installation.

The first step in setting up Geospatial Analytics in OBIEE is importing the shapefile. Depending on how many levels of geographic information your data has (or the number of levels you want to configure), as many number of shapefiles are needed. For example, if the geographic data/hierarchy in your application data is as Country--> Region-->State-->City, then you will need 4 shapefiles for each of the 4 levels. Let's start with setting up Map Builder first.

1. First, configure the connectivity to the database where you want to store the spatial information. This database will ultimately hold all the spatial data in the form of spatial tables and the associated objects.

In Map Builder, go to File --> New Connection. This will bring up the Database connection parameters window, where you need to give the usual database information.

Once this has been done, load this connection (Click on Files ---> Load/Add/Remove Connection or Ctrl+L and click on Load)

2. Next, assuming you already have the required shapefiles, go to the Import Shapefile wizard (Tools --> Import Shapefile). This brings up the 'welcome' window. Upon clicking 'Next', we come to the main window in the wizard.

3. Here, the following information need to be given

• ‘Shapefile’ - Click on the ‘Shapefile’ button to browse through your local or network folder to the location of the shapefile (.shp file)
• Geometry table – The name of the spatial table to be created. The import process creates a spatial table and the spatial indexes and imports information in the shapefile into this spatial table.
• Geometry column – The name of the column in the table that will hold the actual geometric spatial data. The datatype of the column would be SDO_GEOMETRY (this is a specific datatype for use in spatial purposes)

Make sure you also have the .dbf file and the .shx file in the same folder, as Map Builder reads these file during the import process.

Multiple shapefiles could also be imported at once by going for the Multiple Files/Directories option.

Once you have given all the above information, click ‘Next’

4. In the next window, specify the SRID of the geodetic information stored in the .shp. (More information about SRID, including finding one from a .prj file, have been given in my previous blog). This step also takes care of creating the spatial indexes for the spatial table to be created.

5. In the next step, we specify the name of the theme (known as layer in Oracle BI). The style of the theme depends on what sort of SHP is being imported. In our case, we are importing shapefile which contains spatial information about Districts in Sri Lanka. In that case, the theme we style that would be C.WOM_SEE_THROUGH. Why specifically that? Because, the end goal is to overlay this Districts information on top of a base Google Map. If it weren’t see through, we wouldn’t be able to see the base map information along with the District information (sort of like placing a see through tracing paper over a picture).

6. Click Finish on the summary page, a window flashes with the progress, and the import process has completed. As soon as the process is completed, you will be presented with the details of the theme which has been created, including table name, theme details, etc.

7. The next step is to create a layer key. Essentially, this means we identify a column from the spatial table that should finally relate to a presentation column in Oracle BI.

8. Once this is done, save the theme (File --> Save). This completes the shapefile import process.

You may now go to the database and query the spatial table we created above. If you deep-dive into the ‘Geometry’ column, we would see that the information stored is in the form of an array. This is because it stores the latitudes and longitudes (X and Y coordinates) of all the points that make up the shape of the geography that we were importing.

I have seen that Toad gives a better representation of the way data is stored in the SDO_GEOMETRY columns, but then, that's just my opinion.

Stay tuned for my next posting, where I would be discussing about Oracle Map Viewer (which is the mapping engine that Oracle BI would leverage on for rendering map views) and how Oracle BI is finally plugged in to the equation.

Related Blogs

Geospatial Analytics in OBIEE Basics
Role of Mapviewer