By default, the registering features from of an Oracle or MSSQL spatial table will define properties as Text Properties. To enhance this, the OpenCities Map provides the ability to identify lookup tables in the database and use them as XFM domain lists. This way, properties can be defined as pick lists.
In the following example, the Building feature has two domain lists to control the values for the Heating System and the Usage. The Heating System consists of a single column of values while the Usage contains two.
Configuring database tables for use as domain list is a two-step process.
The following script can be used to create the simple table for Building features in SQL Server:
/*Create Building table*/
CREATE TABLE Buildings(
ID int IDENTITY(1,1) NOT NULL,
BuildingID int NULL,
YearBuilt int NULL,
Usage varchar(24) NULL,
Heating_System varchar(24) NULL,
GEOM geometry NULL,
CONSTRAINT PK_Buildings PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];
NOTE: At the time of this writing, OpenCities Map supports up to 24 columns in a lookup table.
The following creates a new table called Bldg_Usage with two columns: Usage and Code. Note that the Usage datatype varchar(24) must match the same column in parent table.
/****** Create Building Usage Lookup Table for domain list ******/
CREATE TABLE Bldg_Usage(
Usage varchar(24) NOT NULL,
Code varchar(24) NULL,
CONSTRAINT PK_Bldg_Usage PRIMARY KEY CLUSTERED
[Usage] ASC
) ON [PRIMARY];
The following creates a new table called Bldg_Heating_System with one column to specify the Type. Note that the datatype varchar(24) match the Heating_System column in parent table.
/****** Create Building Heating System Lookup Table for domain list ******/
CREATE TABLE Bldg_Heating_System(
Type varchar(24) NOT NULL,
CONSTRAINT PK_Bldg_Heating_System PRIMARY KEY CLUSTERED
[Type] ASC
Values are inserted into the newly create lookup table for Bldg_Usage.
/** Insert values into Usage lookup table.
INSERT Bldg_Usage (Usage, Code) VALUES ('Residential', 'R-303')
INSERT Bldg_Usage (Usage, Code) VALUES ('Business', 'B-304')
INSERT Bldg_Usage (Usage, Code) VALUES ('Educational', 'E-305')
INSERT Bldg_Usage (Usage, Code) VALUES ('Detention', 'D-306')
INSERT Bldg_Usage (Usage, Code) VALUES ('Care', 'H-307')
INSERT Bldg_Usage (Usage, Code) VALUES ('Industrial', 'I1-308')
INSERT Bldg_Usage (Usage, Code) VALUES ('Factory', 'I2-308')
INSERT Bldg_Usage (Usage, Code) VALUES ('Miscellaneous', 'M-309');
Values are inserted into the newly create lookup table for Bldg_Heating System.
/** Insert values into Heating System lookup table*/
INSERT Bldg_Heating_System (Type) VALUES ('Natural Gas')
INSERT Bldg_Heating_System (Type) VALUES ('Oil')
INSERT Bldg_Heating_System (Type) VALUES ('Electric')
INSERT Bldg_Heating_System (Type) VALUES ('Wood')
INSERT Bldg_Heating_System (Type) VALUES ('Geothermal')
INSERT Bldg_Heating_System (Type) VALUES ('Solar')
INSERT Bldg_Heating_System (Type) VALUES ('Unknown');
A foreign key is added to formalize the relationship between the lookup table Bldg_Usage and the parent table Buildings. Note that the column Usage exists in both tables and is the same datatype.
This ensures referential integrity of the database.
/** Add foreign key for Building Usage lookup table */
ALTER TABLE Buildings WITH CHECK
ADD CONSTRAINT FK_Building_Usage
FOREIGN KEY(Usage)
REFERENCES Bldg_Usage (Usage);
ALTER TABLE Buildings CHECK CONSTRAINT FK_Building_Usage;
A foreign key can also be added to formalize the relationship between the lookup table Bldg_Heating_System and the parent table Buildings. In this case, the column Type in Bldg_Heating_System is the same datatype as the Heating_System column in Buildings table.
/** Add foreign key for Heating System lookup table */
ADD CONSTRAINT FK_Building_Heating_System
FOREIGN KEY (Heating_System)
REFERENCES Bldg_Heating_System (Type);
ALTER TABLE Buildings CHECK CONSTRAINT FK_Building_Heating_System;
With the tables created, and the foreign keys defined, the features can be registered in the Geospatial Administrator.
On the first page, select both Simple Geometry Features and Nonspatial Features. On the Features to Register page, only select the feature with geometry.
The relationships defined in the database are converted into list domains and are noted in the Domains Registration page
When the registration is complete, the feature properties with domain lists are correctly generated as combo box properties.
The contents of the database lookup tables are added to the Domains node.
NOTE: The contents of domain lists are generated from the lookup table in the database. The columns are displayed in alphabetical order which may not correspond to the table layout.
NOTE: The columns are named Value1, Value2, Value3, … etc, however the feature definition refers to them as col1, col2, col3,… etc.
The following content deals specifically with the Bldg_Usage domain list.
The appearance and behavior of domain lists can be further defined in the ComboBox Property definition. For example, the first column in the Bldg_Usage can be named Code, and the second column can be named Usage.
These are entered in the preferred display order in the List Columns section of the ComboBox Property definition page.
NOTE: col1 and col2 are case sensitive.
During feature placement, the Usage (col2) column should be shown in the picklist, so it is set as Edit Col Name. The Edit Col Name is used as the display value for PBA, Labeling, XFM Place and Edit Dialog, Analyze, and Element Info.
The Value Col Name is the stored value which will display in the Search dialog, Data Browser, Thematic, Export to file, and Post to database.
In this example, col2 is used for both the Value Col Name and Edit Col Name in the ComboBox Property page:
NOTE: To learn more about setting the Value and Edit Col Names, refer to:
https://communities.bentley.com/products/geospatial/desktop/w/geospatial_desktop__wiki/21741/--value-col-name-vs-edit-col-name
Lastly, the feature metadata is inserted, and the project is exported. When done correctly, the lookup tables will display as a picklists when placing or editing the Buildings feature.
NOTE: The contents of the list is generated alphabetically.
A zip file containing the project schema, SQL Script, and PDF of this article commands are provided for your learning.
The SQL Scripts were run and tested against Microsoft SQL Server 2014 only.
It is understood that the script is provided AS IS and should only be run in a scratch or test database.
Bentley Systems, Incorporated assumes no liability for damages direct, indirect, or consequential, which may result from the use of this script. Use at your own risk.
Buildings and two domain lists.zip