Defining Annotations
In addition to extract or read/write support on single table with spatial tables, Bentley Map supports the concept of storing annotations in SQL Server. This requires a separate spatial table that is related to the table with the parent feature in it. Adding an annotation table is a three step process:
• First, a spatial table is created;
• Second, a composite primary key is added to the table;
• Finally, a foreign key is added to ‘formalize’ the relationship between the annotation table and the parent table.
The following spatial table is used as the parent table for annotation:
CREATE TABLE PARCELS (Owner VARCHAR(20), Market_Value VARCHAR(10), Parcel_ID INT PRIMARY KEY, Geometry GEOMETRY)
The following the CREATE TABLE statement is used for a table that stores annotations for the parcels table:
CREATE TABLE OWNER_TEXT (Owner_Text_ID INT CONSTRAINT Owner_Text_ID PRIMARY KEY, Parcel_ID_REF INT NOT NULL, MS_Angle FLOAT, MS_X_Scale FLOAT, MS_Y_Scale FLOAT, Geometry GEOMETRY)
A foreign key constraint needs to be created to express the relationship between the data in the parent table and the table used to store annotations.
Adding a foreign key to link the Owner_Text table to the customers table requires the following ALTER TABLE statement:
ALTER TABLE OWNER_TEXT WITH NOCHECK ADD CONSTRAINT OWNER_TEXT_FK FOREIGN KEY (Parcel_ID_REF) REFERENCES PARCELS (Parcel_ID) ON DELETE CASCADE
This will ensure that the two tables are tied together and that a delete of a row in the root table will trigger a delete of all the rows that in the annotation table that have a reference to the deleted root table row.
The combination of the root and annotation tables, connected through the foreign key should be sufficient for Bentley Map to extract/modify/post features with annotations.