Original Article Date: July 14, 2005
With special thanks to Krister Surell from Surell Consulting for this article. He writes....Ever want to display and query a spatial layer outside your MicroStation GeoGraphics project? Well, then you know that displaying elements using the Spatial Query tool works fine but that’s it. No review, no thematics, no annotation...no nothing. This is because MicroStation GeoGraphics requires additional columns, metadata, and feature data to fully recognize and use a spatial layer.
The solution described in this document is to create a database view and register that view in the oracle metadata table. The key is to create virtual columns for the columns required by GeoGraphics for displaying data properly. The required columns describes here are the minimum for GeoGraphics to be able to read and display the data properly. By modifying a SQL-script it is possible to quickly add a new Spatial layer for read-only use in GeoGraphics.This document describes the different parts of this script and which parts that needs to be modified for new layers.Initial setupThis example requires an existing GeoGraphics project in Oracle Spatial with default entries in MSCATALOG for the CATEGORY and FEATURE tables. Use the GeoGraphics project wizard to create an empty project if necessary. All other configuration is done by directly updating the database tables. This document assumes that the user is familiar with MicroStation GeoGraphics and its data model.Affected tablesBefore any data can be viewed a few of tables must be updated. It might seem like quite a lot work but once the SQL-script is created this could be repeated for multiple geospatial layers. In this example the Oracle table containing geospatial data is called GEOTABLE and the view matching this table is called MS_GEOTABLE. The entitynum will be 99, the feature mslink 999 and the layer_id 111. If your project already uses any of these ID’s, please change them before running the scripts.Green entries are important and require attention if you want to modify the setup. Red entries need to be changed before the scripts can be run. Hyperlinks show relations between different tables and columns.MSCATALOGA new entry has to be created for each geospatial view.insert into mscatalog (tablename, entitynum, sqlreview) values (‘ms_geotable', 99, null);
FEATUREAt least one new entry for each geospatial view is needed. Several features could be used but then the feature values has to be calculated in the view from some other column value.insert into feature (mslink,fcode,fname,category,tablename,ftype,flevel,fstyle,fweight,fcolor,digcmd,felement,fangle,fheight,fwidth,flinespace,linelength,ffont,fsymbol,fjustification,celllibrary,fcellname,cellscale,streamdelta,streamtolerance,streamunit,snaptype,snaptolerance,newdup) values (999, 'feature 999', 'External geometries',1,'ms_geotable',2,0,0,0,3, null,0,'0',0,0,0,0,0, null,'0',null,null,1,null,null,null,0,10,1);The most important values to remember are mslink (used in other places) and tablename. In this example the feature is added to category 1.
UGFEATUREEach entry in FEATURE must have an entry in UGFEATURE. insert into ugfeature (feature, descr, lsname, >layer_id) values (999, 'external feature', NULL, 111);The important point here is that feature matches mslink in the feature table and that layer_id matches the entry in uglayer (see below).
UGLAYERThis table links a spatial layer with a feature.insert into uglayer layer_id,layer_name,table_name,geom_col,dflag_col,udl_col,lock_col,gid_col,feat_id_col,txt_col,xml_col,create_col,revision_col,retire_col,style_col) values(111,'ms_geotable','ms_geotable','geometry','ms_dflag', null, 'ms_lock','mslink','ms_fid', null, null, null, null, null, 'ms_style');The layer_id must match layer_id in UGFEATURE, layer_name and table_name should match the view name and all other columns should match the corresponding columns in the view (see below).
The viewThe database view will impersonate a geospatial table that looks the way GeoGraphics wants it to. To do that we need an mslink column and a few virtual columns required by GeoGraphics Spatial reader.create or replace view ms_geotable(mslink, col1, col2, .., geometry, ms_fid, ms_style, ms_dflag, ms_lock) as selectg.indexcolumn, g.col1, g.col2, ..,g.geometry,fcode_list(fcode_item(2, 999, 1, 0),fcode_item(99, g.indexcolumn, 1, 0)),ug_style(999, 4, 0, 1, 1, null, 0, null, null),0,1 fromgeotable gwith read only;Please note that the view is read only. No data can be written back to the database since it is not possible to write to virtual columns. The number 2 in the first fcode_item refers to the FEATURE table in MSCATALOG. The number 4 in ug_style refers to line features. If there are other geometries in the layer this entry must be changed accordingly.
MslinkThe mslink column can be a unique index column in the source table or an generated value such as (col1*1000)+col2. If this approach is used the same value must be specified in the second fcode_item(see below). Do not use the Oracle keyword rownum to create a unique id. If rownum is used GeoGraphics will always fetch the whole layer since the Oracle operation mdsys.sdo_filter() used by GeoGraphics always returns all records. Also, review will fail since rownum does not uniquely identifies the original record.GeometryThe geometry column has to be included and should have the same name as specified in the table UGLAYER (see UGLAYER).Ms_fidThe ms_fid column specifies the database linkages of the resulting element. In GeoGraphics most elements has a feature linkage and in this case also a link to the external data. The view defines two linkages, the first is the feature linkage, and the second the external database linkage. Each fcode_item consist of fcode_item(entitynum,mslink,infotype,dastype). The important thing is to specify the correct entitynum and mslink. If a calculated value was used for mslink the same expression must be used for the second fcode_item.Ms_styleThis column defines different text attributes for the element. Ug_style is defined as ug_style(feature,etype,rotation,scale_x,scale_y,text_data,eclass,cell_lib,cell_name). The important fields here is feature which corresponds to mslink in the feature table. The rest of the values can be set like the example for line layers.Oracle meta dataThe last step is to add the view to the Oracle meta data table. This way GeoGraphics thinks this is a normal spatial layer. In order to do this you need to know the extent of the layer containing the geometry column. This layer should already be in the metadata so look at the extent and srid values for the existing table and use the same values for the view.insert into user_sdo_geom_metadata (table_name, column_name, srid, diminfo)values ('MS_GEOTABLE', a href="#geomcolumn">'GEOMETRY', 1000074, mdsys.sdo_dim_array(mdsys.sdo_dim_element('X', 64800.0, 128800.0, 0.001), mdsys.sdo_dim_element('Y', 60000.0, 100000.0, 0.001), mdsys.sdo_dim_element('Z', -1000, 1000, 0.001)));Actually all values except table_name can be copied from the record of the referring table.
Run setupIn order to automatically create a view of an existing spatial layer the file creSpatialView.sql should be edited to reflect actual values in the project. For a quick test simply update the table name, geometry column and specific column names in the view. Run the script from sqlplus or any other software capable of executing sql commands.View the dataWhen the script has been executed successfully it is time to open the project in GeoGraphics. When the project is opened click on the Spatial Query icon to open the Spatial Query dialog. Click on the Project Explorer icon and then drag the Spatial layer MS_GEOTABLE over to Spatial Layers in the Spatial Query dialog.
Click on Query to read all elements inside the current view (or fence if defined). The geometries are created with the specified feature and will also be linked to the original layer.Query the dataSince the elements now has database linkages all read-only database operations can be used. This includes review (both standard and vsql), thematic and annotation.ProblemsSome database columns/values cause problems in vsql review. Long numbers (number(12)) will result in illegal queries and no data will be displayed. If this is the case try to remove these fields from the view. This might also be true for other columns and/or values.
CommentsThis document is based on trial and error so there might be other ways or better ways to achieve similar results. Please mail your comments and ideas. If we are lucky it might result in better workflows and integration of the GeoGraphics/Oracle Spatial functionality in the future.
SQL-scriptModify all red (and perhaps green) entries and run this script to create a view that can be used to view and query and Oracle Spatial layer from MicroStation GeoGraphics.
-- CREATE ENTRY IN MSCATALOGDELETE FROM MSCATALOG WHERE ENTITYNUM = 99;INSERT INTO MSCATALOG (TABLENAME, ENTITYNUM, SQLREVIEW) VALUES ('MS_GEOTABLE', 99, NULL);-- CREATE ENTRY IN FEATUREDELETE FROM FEATURE WHERE MSLINK = 999;INSERT INTO FEATURE (MSLINK,FCODE,FNAME,CATEGORY,TABLENAME,FTYPE,FLEVEL,FSTYLE,FWEIGHT,FCOLOR, DIGCMD,FELEMENT,FANGLE,FHEIGHT,FWIDTH,FLINESPACE,LINELENGTH,FFONT,FSYMBOL, FJUSTIFICATION,CELLLIBRARY,FCELLNAME,CELLSCALE,STREAMDELTA,STREAMTOLERANCE,STREAMUNIT,SNAPTYPE,SNAPTOLERANCE,NEWDUP) VALUES (999, 'FEATURE 999', 'EXTERNAL GEOMETRIES',1,'MS_GEOTABLE',2,0,0,0,3,NULL,0,'0',0,0,0,0,0, NULL,'0',NULL, NULL,1,NULL,NULL,NULL,0,10,1);-- CREATE CORRESPONDING ENTRY IN UGFEATUREDELETE FROM UGFEATURE WHERE FEATURE = 999;INSERT INTO UGFEATURE (FEATURE, DESCR, LSNAME, LAYER_ID) VALUES (999, 'EXTERNAL FEATURE', NULL, 111);-- CREATE ENTRY IN UGLAYERDELETE FROM UGLAYER WHERE LAYER_ID = 111;INSERT INTO UGLAYER (LAYER_ID,LAYER_NAME,TABLE_NAME,GEOM_COL,DFLAG_COL,UDL_COL,LOCK_COL,GID_COL,FEAT_ID_COL,TXT_COL,XML_COL,CREATE_COL,REVISION_COL,RETIRE_COL,STYLE_COL)VALUES (111,'MS_GEOTABLE','MS_GEOTABLE','GEOMETRY','MS_DFLAG', NULL, 'MS_LOCK','MSLINK','MS_FID', NULL, NULL, NULL, NULL, NULL, 'MS_STYLE');-- CREATE VIEW FOR ORACLE SPATIAL LAYERCREATE OR REPLACE VIEW MS_GEOTABLE (MSLINK, COL1, COL2, GEOMETRY, MS_FID, MS_STYLE, MS_DFLAG, MS_LOCK) AS SELECTG.INDEXCOLUMN, G.COL1, G.COL2, G.GEOMETRY,FCODE_LIST(FCODE_ITEM(2, 999, 1, 0),FCODE_ITEM(99, G.INDEXCOLUMN, 1, 0)),UG_STYLE(999, 4, 0, 1, 1, NULL, 0, NULL, NULL),0,1 FROMGEOTABLE GWITH READ ONLY;-- CREATE ENTRY IN ORACLE META DATADELETE FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME = 'MS_GEOTABLE';INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, SRID, DIMINFO)VALUES ('MS_GEOTABLE', 'GEOMETRY', 1000074, MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X', 64800, 128800, 0.001), MDSYS.SDO_DIM_ELEMENT('Y', 60000, 100000, 0.001), MDSYS.SDO_DIM_ELEMENT('Z', -1000, 1000, 0.001)));
AskInga Article #253