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 22.214.171.124.0 - 64bit Production
SQL> DESCRIBE PARCELS;
Name Null? Type
------------------------------------ -------- ------------------
XFM_ID NOT NULL VARCHAR2(36)
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
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_ELEMENT('X', 2197290.78, 2401264.08, 0.00000005),
MDSYS.SDO_DIM_ELEMENT('Y', 703310.077, 911592.401, 0.00000005)
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: