Configure Spatial Data

This page guides you in the setup and configuration of spatial data for the Oracle Analytics Server dashboards in Oracle Utilities Analytics Warehouse (OUAW).

Note: The mapping between the spatial columns and the dimensional columns (such as City and State) from the Address dimension is defined in the spatial metadata catalog, which is available as a part of the Web Catalog component in OUAW. Through the spatial data and the map metadata, users can view the star schema data in the map reports that are delivered out-of-box.

On this page:

• Configure Spatial Data
• • Procure Spatial Data
  • Install Spatial Data
  • Import Spatial Data into Spatial Data Tables
  • • Import Theme-Related Spatial Data into Oracle Utilities Analytics Warehouse
    • • State Spatial Data Import into Oracle Utilities Analytics
      • City Spatial Data Import into Oracle Utilities Analytics Warehouse
      • County Spatial Data Import into Oracle Utilities Analytics Warehouse
      • Postal Code Spatial Data Import into Oracle Utilities Analytics Warehouse
    • Improve Performance by Prefetching the Map Tiles
  • Load Oracle Utilities Specific Themes
  • Additional Setup for Oracle Utilities Outage Analytics Warehouse

Procure Spatial Data

Oracle Utilities Analytics Warehouse does not include any spatial data. The spatial data can be loaded from the various third party vendors, some of which can be found in Oracle Spatial Partners Data.

Note: Some of these vendors might require you to purchase a separate license for the spatial data. Contact the vendor directly to do this. You can then download a spatial data dump from the spatial data vendor.

Back to Top

Install Spatial Data

Spatial data acquired from a third party vendor can have various formats. Commonly, vendors provide shape files (.shp) or a database dump for download. The exact spatial data model varies from vendor to vendor, since there is not a standardized format for it. Hence, the documentation that comes along with the spatial data is of utmost importance, as it is necessary to identify what type of spatial data is available and where to locate it.

First, you must import the data in the shape file into the DWADM schema.

Notes:

• A database dump can be imported by the standard oracle impdp command.
• A shape file can also contain the spatial data tables, along with the data that can be imported by Oracle MapBuilder.

To import the data into the DWADM schema:

1. Unzip the .zip file (typically, the shape file is zipped for data compression) to a temp directory.
   Example: /u01/Spatial/
2. Download the standalone MapBuilder application from this site:
   https://www.oracle.com/middleware/technologies/fusion-middleware-mapviewer-downloads.html
   
3. Start the MapBuilder.

   Note: Refer to the MapViewer documentation for detailed instructions on installing and running MapBuilder from this site: http://www.oracle.com/technetwork/middleware/mapviewer/documentation/index.html

4. Create a connection to the DWADM schema in the Oracle Utilities Analytics Data Warehouse database by navigating to File and then to New Connection.
5. Navigate to Tools and then to Import Shapefile. Click onNext.
6. Click on the shapefile.
7. Browse to the C:\Spatial directory, select the .shp file, and click on Open.
8. Click onNext.
9. Ensure the Create Predefined Theme box is unchecked, and click on Next.
10. Review the summary information and click on Finish. A sample screenshot is shown below.

    

Back to Top

Import Spatial Data into Spatial Data Tables

Once the spatial data tables from a third party vendor have been imported into the DWADM schema, the next step is to import the spatial data needed by Oracle Utilities Analytics Warehouse (OUAW) into certain specific tables. This step to import a certain subset of the data into a standardized set of OUAW tables is for decoupling. This decoupling helps the Oracle Analytics Server map reports and other Oracle Analytics Server map configuration to remain oblivious to the variations in the spatial data schema coming from various vendors. This way you do not need to handle the spatial metadata catalog or to modify any of the map reports being delivered out-of-the-box.

Oracle Utilities Analytics Warehouse needs the administrative boundary type of spatial data at four levels: State, City, County and Postal Code. All map reports in OUAW are using one of these levels. Since the spatial data model varies from vendor to vendor, further details provided in this section are based on a sample spatial data from a specific vendor. The following sections describe how to import the data based on a sample spatial data dump provided by HERE Technologies (formerly NAVTEQ).This sample data set contains administrative boundary data for the OHIO city in the US. The sample data set from the Oracle partner can be downloaded from Oracle Spatial Partners Data.

However, the steps can be used with a little variation to set up the spatial data for OUAW for any city from any country. The extent of the changes need to be done based on the vendor used to purchase the spatial data. Read through the documentation of the spatial data model provided by the vendor to understand the type of data provided and and where to locate it. 

The following table provides a simple mapping based on the HERE maps sample data.

Note: Apart from these four levels, there are also some standard themes and styles that need to be imported.

Next on this page, you will find the SQL statements to create and populate the various spatial data tables for OUAW. The prerequisites are:

• The procurement of spatial data from a third party vendor.
• The import of the spatial data dump into the DWADM schema.

  Note: It is assumed that the eLocation website is being used as the base map in the Oracle Analytics Server mapviewer setup, for which the Spatial Reference System Identifier (SRID) is 54004. The SRID is a unique value used to identify the coordinate system used in a Geographic Information System (GIS) application. If some other base map is used, then the transform parameter in the insert statements should specify the SRID of the base map being used.

Back to Top

Import Theme-Related Spatial Data into Oracle Utilities Analytics Warehouse

The sdo_ tables are standard tables expected to be present in the spatial data from any vendor.

INSERT INTO user_sdo_maps SELECT * FROM sdo_maps;
INSERT INTO user_sdo_themes SELECT * FROM sdo_themes;
INSERT INTO user_sdo_styles SELECT * FROM sdo_styles;
INSERT INTO user_sdo_cached_maps SELECT * FROM sdo_cached_maps; 

Back to Top

State Spatial Data Import into Oracle Utilities Analytics

In OUAW, the table Q1_STATES54004 is used to store the state-related administrative boundary. Create the table based on the DDL below and insert the values from one of the spatial data tables obtained from the vendor.

CREATE TABLE Q1_STATES54004 ( feature_id NUMBER, feature_name VARCHAR2(255), area_id NUMBER, name_langcode VARCHAR2(35), feature_type VARCHAR2(30), country_code_3 VARCHAR2(5), geometry SDO_GEOMETRY, carto_id NUMBER(10,0) );

INSERT INTO Q1_STATES54004_SDXSELECT NULL, name, NULL, lang_code, feature_type,ISO_COUNTRY_CODE, sdo_cs.transform(geometry, 3857), carto_idFROM WOM_AREA WHERE feature_type = 909996 AND iso_country_code ='ISO'; 

Note: Customize the table name WOM_AREA, the feature type value and the ISO country code in accordance with the procured spatial data model. The following insert statement defines the Q1_ table as a spatial table and to update the spatial metadata view to reflect the dimensional information for the areas. The parameters for the insert statement are: table name, geometry column name, X and Y boundaries (latitude and longitude) based on the data, and tolerance value.

INSERT INTO user_sdo_geom_metadata VALUES('Q1_STATES54004', 'GEOMETRY',MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',-20500000,20500000,0.0005),MDSYS.SDO_DIM_ELEMENT('X',-50000000,19000000,0.0005)), 3857 );

CREATE INDEX Q1_STATES54004_SDX ON Q1_STATES54004 (geometry)indextype IS MDSYS.SPATIAL_INDEX;

Back to Top

City Spatial Data Import into Oracle Utilities Analytics Warehouse

In OUAW, the table Q1_CITY54004 is used to store the city-related administrative boundary. Create the table based on the DDL below and insert the values from one of the spatial data tables obtained from the vendor.

CREATE TABLE Q1_CITY54004 ASSELECT upper(name) FEATURE_NAME, sdo_cs.transform(geometry, 3857)geometry, 'OHIO' state FROM pl39_d00 WHERE lsad_trans IN ('city','village');

Note: Customize the table name pl39_d00 and additional filters in accordance with the procured spatial data model. Map the state name dynamically for the current city record in the spatial data. The following insert statement defines the Q1_ table as a spatial table and to update the spatial metadata view to reflect the dimensional information for the areas. The parameters for the insert statement are: table name, geometry column name, X and Y boundaries (latitude and longitude) based on the data, and tolerance value.

INSERT INTO user_sdo_geom_metadata VALUES('Q1_CITY54004', 'GEOMETRY',MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',-20500000,20500000,0.0005),MDSYS.SDO_DIM_ELEMENT('X',-50000000,19000000,0.0005)), 3857 );

CREATE INDEX Q1_CITY54004_sdx ON Q1_CITY54004 (geometry) indextypeIS MDSYS.SPATIAL_INDEX;

UPDATE Q1_CITY54004 SET geometry =SDO_UTIL.RECTIFY_GEOMETRY(geometry, .05);

Back to Top

County Spatial Data Import into Oracle Utilities Analytics Warehouse

In OUAW, the table Q1_COUNTY54004 is used to store the county-related administrative boundary. Create the table based on the DDL below and insert the values from one of the spatial data tables obtained from the vendor.

CREATE TABLE Q1_COUNTY54004 AS
SELECT name FEATURE_NAME, sdo_cs.transform(geometry, 3857)geometry, 'OHIO' state FROM CO39_D00;

Note: Customize the table name CO39_D00 in accordance with the available spatial data model. The following insert statement defines the Q1_ table as a spatial table and to update the spatial metadata view to reflect the dimensional information for the areas. The parameters for the insert statement are: table name, geometry column name, X and Y boundaries (latitude and longitude) based on the data, and tolerance value.

INSERT INTO user_sdo_geom_metadata VALUES('Q1_COUNTY54004', 'GEOMETRY',MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',-20500000,20500000,0.0005),MDSYS.SDO_DIM_ELEMENT('X',-50000000,19000000,0.0005)), 3857 );

CREATE INDEX Q1_COUNTY54004_sdx ON q1_county54004 (geometry)indextype IS MDSYS.SPATIAL_INDEX;

UPDATE Q1_COUNTY54004 SET geometry =SDO_UTIL.RECTIFY_GEOMETRY(geometry, .05);

Back to Top

Postal Code Spatial Data Import into Oracle Utilities Analytics Warehouse

In OUAW, the table Q1_USZIP54004 is used to store the county-related administrative boundary. Create the table based on the DDL below and insert the values from one of the spatial data tables obtained from the vendor.

CREATE TABLE Q1_USZIP54004 ASSELECT zcta ZCTA5CE, sdo_cs.transform(geometry, 3857) geom. FROMZT39_D00;

Note: Customize the table name ZT39_D00 in accordance with the available spatial data model. The following insert statement defines the Q1_ table as a spatial table and update the spatial metadata view to reflect the dimensional information for the areas. The parameters for the insert statement are: table name, geometry column name, X and Y boundaries (latitude and longitude) based on the data, and tolerance value.

INSERT INTO user_sdo_geom_metadata VALUES('Q1_USZIP54004', 'GEOM',MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',-20500000,20500000,0.0005),MDSYS.SDO_DIM_ELEMENT('X',-50000000,19000000,0.0005)), 3857 );

CREATE INDEX Q1_USZIP54004_SDX ON Q1_USZIP54004 (geom) indextypeIS MDSYS.SPATIAL_INDEX;UPDATE Q1_USZIP54004 SET geom = SDO_UTIL.RECTIFY_GEOMETRY(geom,.05);

Back to Top

Improve Performance by Prefetching the Map Tiles

Rendering map tiles dynamically can affect system performance. To avoid this, you may consider prefetching tiles by issuing an admin request to MapViewer on a non-production server or when the server is not under load. When MapViewer receives a prefetching request, it issues many concurrent map tile requests to itself, which are basically XML map requests, and stores them in the cache. This improves the system's performance as the images are readily available. Follow the following procedure to prefetch map tiles. These instructions are for MapViewer
11.1.1.9.1 version. If you are using a different version, refer to the MapViewer documentation for instructions. 

1. In the MapViewer console, click on Admin in the top-right corner of the page and log in to the Admin Console page.
2. In the Admin Console page, click on Management and then on Manage Map Tile Layers.
3. Select the required online map tile and click on View Map/Manage Tiles. The Prefetching wizard opens.

   

4. Enter the X and Y coordinates of the center of the map, the SRID, and the zoom level. Click on Show Map.
   To prefetch map images, enter the latitude and longitude values. You need to know these values of the center of the location in interest. The SRID is a unique
   value used to identify the coordinate system used in a GIS application.
   Example: The following are the X and Y axis for Ohio city.

   

5. When you see the map, turn on Area Selection and select the area on the map for which you want to prefetch the tiles.
6. Select one or more zoom-levels for which you want to prefetch the tiles.

Back to Top

Load Oracle Utilities Specific Themes

This section describes how to load spatial metadata in the USER_SDO* tables for OUAW.

Use the following procedure to load spatial metadata in the target database:

1. Create the dump_dir directory in the database and copy the user_sdo.dmp file from ../ BI280/Spatial-Metadata folder to that location.
2. Import the released spatial tables to the target database using the following command:
   impdp directory=dump_dir dumpfile=user_sdo.dmp logfile= impdp_user_sdo.log schemas=DWADM remap_tablespace=cists_01:

   Note: Run this command from database server.
   Make sure to change the tablespace_name to a tablespace name on which DWADM has the quota. If DWADM user have quota on cists_01 tablespace, then exclude whole remap_tablespace clause from command.

3. Review the impdp_user_sdo.log file to ensure the tables are imported successfully.
4. After importing the tables, run the following SQL scripts from the ../BI280/SpatialMetadata folder:
   sqlplus dwadm/@database-name @copy_spatial_metadata.sql
   sqlplus dwadm/@database-name @clean_sdo_release_tbls.sql
5. Review the log files.

Back to Top

Additional Setup for Oracle Utilities Outage Analytics Warehouse

The Outage Analytics product within Oracle Utilities Analytics Warehouse (OUAW) requires some additional spatial data setup, as Outage Analytics alone uses point information from spatial data for plotting outage event-related information on maps. This section should be skipped if NMS is not one of the source systems for OUAW.

To begin with this setup, it is necessary to replicate the DIAGRAM_OBJECTS table into the Oracle Utilities Analytics Warehouse database. However, a suitable mechanism needs to be built to refresh this table with the latest data from Oracle Utilities Network Management System whenever there are changes in the source system.

After replicating the DIAGRAM_OBJECTS table, the following steps set the Oracle Utilities Analytics Warehouse dashboards up to use the data in this replicated table.

1. Create the required spatial metadata:
   INSERT INTO user_sdo_geom_metadata VALUES ('DIAGRAM_OBJECTS','LL_GEOMETRY', MDSYS.SDO_DIM_ARRAY (SDO_DIM_ELEMENT ('X', -20037508, 20037508, .05),
   SDO_DIM_ELEMENT ('Y', -45884542, 45884542, .05) ), 3857);
2. Ensure that the spatial index is created on the LL_GEOMETRY column. The LL_GEOMETRY column by default stores spatial data using SRID 54004, so this works without changes with the eLocation base map:
   create index Q1_DIAGRAM_OBJECTS_SDX on diagram_objects (ll_geometry) indextype is mdsys.spatial_index;

Once DIAGRAM_OBJECTS table is replicated in the Oracle Utilites Analytics Warehouse database, follow the next steps to create the Q1_DIAGRAM_OBJECTS_54004 table:

1. Connect to the DWADM schema of Oracle Utilities Analytics Warehouse database and execute the following:
   CREATE TABLE Q1_DIAGRAM_OBJECTS_54004 as select * from DIAGRAM_OBJECTS;
2. INSERT INTO user_sdo_geom_metadata: VALUES ('Q1_DIAGRAM_OBJECTS_54004','LL_GEOMETRY', MDSYS.SDO_DIM_ARRAY ( SDO_DIM_ELEMENT ('X', -20037508, 20037508, .05),SDO_DIM_ELEMENT ('Y', -45884542, 45884542, .05)), 3857);
3. CREATE INDEX Q1_DIAGRAM_OBJECTS_SDX_54004 on Q1_DIAGRAM_OBJECTS_54004 (ll_geometry) indextype is mdsys.spatial_index;.
4. CREATE INDEX Q1_DIAGRAM_OBJECTS_IDX_54004 ON Q1_DIAGRAM_OBJECTS_54004(H_CLS,H_IDX);
5. Under Tile Operations, click on Prefetch Tiles. Here is a snapshot of the area selection and the tile operation.

   

This process takes some time, depending upon the hardware configuration of the system and the amount of data to be prefetched. To check the current status of the operation, click on Refresh under Operation Status. The status will change to Finished when the operation is done.

Back to Top