· The business table is maintained by some person or group outside of the GIS department (we will call this the BUSINESS department).
· The spatial data (geometry) is maintained by the GIS department.
· The GIS users will benefit from at least viewing and searching on the business data.
· The BUSINESS department will benefit by at least viewing and searching on the spatial data.
· Some (or all) business data can be maintained by the GIS users.
· Some (or all) business data can be maintained by the BUSINESS department.
· Do not duplicate the business data into the GIS spatial data.
1. Creation of the spatial geometry table, including spatial indexes and metadata entries.
2. Loading of existing spatial data into the geometry table.
3. Creation of the view, as a join between the business data and the spatial data, including the creation of the metadata entries.
4. Creation of the triggers to maintain the data between the view and the actual tables.
· The feature table must have a primary key constraint consisting of a single numeric or string/character column to represent the feature ID. This primary key is required to enable versioning using the standard versioning system of the Oracle Workspace Manager.
· The table must have a geometric (SDO_GEOMETRY) column specifying the feature geometry, and this geometry column must be registered in the Oracle Spatial metadata table (ALL_SDO_GEOM_METADATA or the related USER_SDO_GEOM_METADATA view for the user).
· The table fields must be of a common type, not a user defined type.
· Geometry must be of similar types, meaning all geometries must be of point, line, or polygon type, not a mixture of these.
· Since there is a rotation value for both text and cells, for their graphical display, there can also be a optional rotation field defined. This can be placed in the spatial table or the business table, but I would suspect that this will be placed in the spatial table, just to keep the spatial data together. For the purpose of this exercise we will call this field ‘ROTATION’, it will be numeric field.
· Since there is also a X and Y scale factor for point features (text and cell), we will also add these fields to a point feature spatial table. We will call these X_SCALE and Y_SCALE and make them numeric also.
1. Create the spatial table. During table creation, make sure you have a column for maintaining a primary key on the new spatial table and another column to maintain a foreign key to the primary feature table.
2. Make sure you have the SDO.GEOMETRY column defined.
3. If you want to maintain the annotation rotation and size, then make sure you also have a rotation, x_scale and y_scale column.
4. Create a spatial index on the table.
5. Create a reference to this spatial table in the user_sdo_geom_metadata table.
6. Create the primary key constraint.
7. Create a sequence generator for the table if you want to post new instances back to the spatial database.
8. Create the foreign key constraint referencing the primary spatial table.
 Actually you could have done this differently, via SQL, but this is one of several options.
 If you do not want to delete the business data, but instead delete the spatial data, then you would remove the deletion from the point_feat_prop table from this trigger. This way the GIS department would not be able to delete the records from the business table, when deleting spatial data.