- Creating Oracle Spatial Views

The following may be helpful to create an Oracle view that can be registered and queried as a spatial feature.

The PARCELS has a number of columns:

Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production
SQL> DESCRIBE PARCELS;

Name                                 Null?   Type

------------------------------------ -------- ------------------

OWNER                                       VARCHAR2(50)
ADDRESS_1                                   VARCHAR2(50)
CITY_STATE_ZIP                              VARCHAR2(30)
ADDRESS_2                                   VARCHAR2(50)
LAND_TYPE                                   VARCHAR2(24)
TOWNSHIP                                    VARCHAR2(24)
FIRE_DISTRICT                               VARCHAR2(24)
ZONING                                      VARCHAR2(8)
TAX_DISTRICT                                VARCHAR2(8)
OGC_GEOMETRY                                MDSYS.SDO_GEOMETRY
XFM_ID                          NOT NULL    VARCHAR2(36)
ACRES                                       NUMBER(8,2)
ASSESSMENT_YEAR                             NUMBER(8)
DEEDBOOK                                    NUMBER(8)
LAND_VALUE                                  NUMBER(8)
MARKET_VALUE                                NUMBER(8)
REMODELED_YEAR                              NUMBER(8)
ROOMS                                       NUMBER(8)
SALE_PRICE                                  NUMBER(8)
TAXABLE_VALUE                               NUMBER(8)

These columns are also visible in OpenCities Map using the Data Browser or Analyze Feature:

This complete set of property data may be needed for managing the parcel data in OpenCities Map by GIS editors. However, for map reviewers, it may be necessary to provide a limited set of property data. To to that, you can set up an Oracle view.

In the following example, a view called PARCEL_MAIL_VIEW is created with the following columns from the PARCELS table: OWNER, ADDRESS_1, CITY_STATE_ZIP, XFM_ID, and OGC_GEOMETRY

CREATE VIEW PARCEL_MAIL_VIEW
AS SELECT OWNER, ADDRESS_1, CITY_STATE_ZIP, XFM_ID, OGC_GEOMETRY
FROM PARCELS;

OpenCities Map requires a primary key be defined on the spatial table. However you can’t have a primary key on a view. To get around this limitation, a primary key is created and then disabled.

ALTER VIEW PARCEL_MAIL_VIEW ADD PRIMARY KEY(XFM_ID) DISABLE;

As with spatial tables, the geometry metadata needs to be added:

INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)
 VALUES ('PARCEL_MAIL_VIEW', 'OGC_GEOMETRY',
 MDSYS.SDO_DIM_ARRAY
(
MDSYS.SDO_DIM_ELEMENT('X', 2197290.78, 2401264.08, 0.00000005),
MDSYS.SDO_DIM_ELEMENT('Y', 703310.077, 911592.401, 0.00000005)
),
NULL);

Reviewing the contents of the view in Oracle SQL Developer shows that it only contains those columns as specified in the CREATE VIEW statement.

When registering the Oracle data source in Bentley Geospatial Administrator, the view will appear in the Register Features page of the wizard.

Once registered, you can proceed to insert the feature placement metadata, define symbology and perform any other administrator tasks required. Once saved and exported, the same columns are available for review using the Data Browser:

Using Oracle views is a great way to show limited property data in OpenCities Map. In this example, you could query the data and easily save the mailing address into a CSV file to use with a Microsoft Word mail merge.

Lastly, you can also consider creating Oracle user roles to provide further protection to the data. For example, a GIS_EDITOR role for the users who require full access to the spatial tables and data, and a GIS_VIEWER role for those users who only need to view the data.

A PDF of this article is available for download:

PDF