This section contains instructions on how to use the scripts in various databases (PostgreSQL/PostGIS, Oracle and SQL Server) and an overview of the key principles on which the scripts are based.
The SQL scripts are available in the SQL directory of the GitHub repository.
Introduction to the SQL scripts
Before using the SQL scripts, you need to determine how your OS MasterMap Topography Layer loading software handles the descriptiveGroup and descriptiveTerm attributes, because these attributes often contain multiple values. Some loaders use a simple VARCHAR(254) data type and load in the data as a comma separated list of values, while others load in the data as an array. For example, this is what a descriptiveTerm with multiple values, looks like when loading OS MasterMap Topography Layer data using:
This is an important distinction as it determines which SQL queries you can use to find the different descriptiveGroup and descriptiveTerm values. If you are unsure which method your loader uses, look at the data in your database or at the CREATE TABLE SQL statement that was used. Either of these will show you the data type of each attribute field. For example, in PostgreSQL/PostGIS using OGR2OGR, if the descriptive term field was created as descriptiveterm character varying[], it is an ARRAY. When you know this, you can choose the correct SQL query script to use.
Before running the SQL scripts, please check your schema and table names as these may be different from those used in our SQL queries. You can use find and replace in a text editor to tweak the SQL queries to match your database configuration.
PostgreSQL/PostGIS
This section is only relevant if you are using GML data. The data in the GeoPackage and Vector tiles (MBTiles) formats of the product already include a style_code column and other
As mentioned in the previous section, the structure of your database and data depends on how you load your data. You need to use the SQL query that correctly matches your data structure. Different operators are available to find the attribute values, depending on whether your data is loaded as a string or an array.
For example, in PostGIS:
String: You can use the ~operator to find a value within the string of values, for example:
WHEN descriptivegroup ~ ‘Building’ AND descriptiveterm IS NULL THEN ‘Building Fill’
Array: You can use the @> (contains) operator to find a value within the array of values, for example:
WHEN descriptivegroup @> ‘{Building}’ AND descriptiveterm IS NULL THEN ‘Building Fill’
Both types of SQL query create a new table using a CASE statement. This is more efficient than using an UPDATE SET statement, because the PostgreSQL MVCC model creates a new row for each row affected by the UPDATE. This means that the original rows then need to be deleted. Previous tests that compared the methods showed that the CASE method took seven hours for a GB OS MasterMap Topography Layer set, whereas the UPDATE method took several days.
Because the queries essentially create duplicate tables, you need to ensure that you have enough server hard drive space before running them.
The example below uses pgAdmin III.
To run either SQL query:
Open pgAdmin.
Click (Execute SQL Query) in the toolbar.
Copy the SQL code from the correct PostGIS SQL file and paste into the PostGIS SQL Query window.
See scripts above for guidance on how to choose the correct script.
Click (Execute Query) in the toolbar to run the script.
At Ordnance Survey we run all the SQL scripts (that is, for all feature types) simultaneously. Before attempting this, please check that your PostgreSQL configuration is set to handle this approach because it may consume a lot of server resources.
Post processing may take several hours, depending on the amount of OS MasterMap Topography Layer data you have loaded and your configuration settings.
After the SQL query has run, you will have both your original database table (for example “osmm. topo.topographicarea”) and a new table that includes the additional styling attributes (for example, “osmm_topo.topographicarea_styled”). You can manage these tables in one of two ways:
DROP/DELETE the original table and rename the new styled table the name of the original table. The advantage of this is that existing connections to that database table will continue to work.
DROP/DELETE the original table and leave the new styled table as it is. The disadvantage of this is that an existing database connection looking for your original table will fail; you will need to connect to the new styled database table.
Oracle
The SQL scripts for Oracle work with descriptiveGroup and descriptiveTerm attributes that are strings and use the INSTR function to find attribute values within the string. We chose to use INSTR rather than LIKE because in our tests INSTR SQL queries ran faster. The Oracle scripts UPDATE and SET the new style_description and style_code attributes and you need to add extra columns to your database tables for these attributes before running the SQL scripts.
The example below adds the style_description attribute to the “topographicarea” table. We use SQL Developer to run the SQL script.
To run the Oracle SQL queries:
Open SQL Developer and navigate to the database in which your OS MasterMap Topography Layer is stored.
Add the extra attribute column/s:
Our example adds a “style_description” column to the “sw_topographicarea” table.
In the Connections panel select the table to which you want to add a new attribute column/s.
In the Worksheet area, enter an SQL query to add the a new attribute column.
ALTER TABLE sw_topographicarea ADD (style_description varchar(250), style_code number);
Substitute the table name as necessary.
Click (Run) to run the query.
If the SQL runs successfully, the message “table <table name> altered” for example “table SW_TOPOGRAPHICAREA altered” will display in the Script Output area (below the Worksheet area).
Click in the Connections panel to refresh the list. This ensures that the new “style_description/style_code” column displays in the list.
In the Connections panel, select the table (for example, “sw_topographicarea”) once again. A new tab showing the revised data structure of the table (including the new column) will open.
Repeat this procedure (steps a. to d. above) for the other OS MasterMap Topography Layer database tables.
CartographicText requires additional attributes to be added before running the SQL below:
Run the styling SQL scripts using the same procedure as above:
Select the appropriate table in the Connections panel (for example, “sw_topographicarea”).
Copy and paste the content of the matching SQL script (for example, “topographicarea_update_string.sql”) into the Worksheet area.
Click (Run) to run the query.
Repeat this procedure (steps a. to c. above) for the other OS MasterMap Topography Layer database tables.
You can now go ahead and use the new stylesheets with the data.
SQL Server
The SQL scripts for SQL Server work with descriptiveGroup and descriptiveTerm attributes that are strings and use the CHARINDEX function to find attribute values within the string. The SQL Server scripts UPDATE and SET the new style_description and style_code attributes. Before running the SQL scripts, you need to add extra columns for these attributes to your database tables.
To run the SQL Server scripts:
Open SQL Server Management Studio and connect to your OS MasterMap Topography Layer database.
Add new attribute column/s:
Open the Query Editor and run the following SQL query.
ALTER TABLE sw_topographicarea ADD (style_description varchar(250), style_code number);
Our example query adds “style_description” and “style_code” columns to a database table called “sw_topographicarea”. Substitute the table name as necessary.
Repeat this procedure for the other OS MasterMap Topography Layer database tables.
CartographicText requires additional attributes to be added before running the SQL:
Next, run each of styling SQL scripts using the same procedure as above.
You can now go ahead and use the new stylesheets with the data.
Key SQL query principles
The SQL scripts were written to create the new style attributes as efficiently as possible. The SQL queries are based on the key principles detailed below:
Rule order: The rule order is based on the descending total count of a discrete feature.
This means that as each feature is assessed, fewer rules need to be parsed to get to a matching rule for the feature. The only exception is in the Topographic Area SQL queries where the rules for built environment features are blocked together above the rules for natural environment features.
Minimal attributes: The rules try to examine as few attributes as possible. Consider, the following example rules:
WHEN descriptivegroup ~ ‘Building’ THEN ‘Building Fill’
WHEN descriptivegroup ~ ‘Building’ AND descriptiveterm = ‘Archway’ THEN ‘Archway Fill’
In this example, the second rule would never to be used, because every feature with “Building” in the descriptiveGroup would match the first rule and therefore be styled as “Building Fill”. To overcome this, we need to amend the first rule to take the descriptiveTerm into consideration.
WHEN descriptivegroup ~ ‘Building’ AND descriptiveterm IS NULL THEN ‘Building Fill’
WHEN descriptivegroup ~ ‘Building’ AND descriptiveterm = ‘Archway’ THEN ‘Archway Fill’
This change allows Archway features to pass the first rule and get styled by the second. However, this means that the first rule needs to examine both the descriptiveGroup and descriptiveTerm attributes, making it fractionally slower than the original rule. We have tried to keep the number of attributes that need to be checked as low as possible.
Minimal rules: The greater the number of rules, the greater the time to process all the features. To minimize the number of rules, we used different database operators to make finding certain attribute values easier. Consider the following example descriptiveTerms:
Nonconiferous Trees (Scattered), Rough Grassland Scrub,Nonconiferous Trees Nonconiferous Trees,Rough Grassland,Scrub
If we would like all of these to be styled as “Nonconiferous Tree Fill”, one solution is to write three rules:
WHEN descriptiveterm = ‘Nonconiferous Trees (Scattered),Rough Grassland’ THEN ‘Nonconiferous Tree Fill’
WHEN descriptiveterm = ‘Scrub,Nonconiferous Trees’ THEN ‘Nonconiferous Tree Fill’
WHEN descriptiveterm = ‘Nonconiferous Trees,Rough Grassland,Scrub’ THEN ‘Nonconiferous Tree Fill’
A better alternative is to use one rule that looks for “Nonconiferous Tree” or “Nonconiferous Tree (Scattered)” at any position of the descriptiveTerm.
WHEN descriptiveterm ~ ‘Nonconiferous Trees’ OR descriptiveterm ~ ‘Nonconiferous Trees (Scattered)’ THEN ‘Nonconiferous Tree Fill’
If all of these features have a style_description of “Nonconiferous Tree Fill”, it does not matter at which position the “Nonconiferous Tree” is as it will always be styled as “Nonconiferous Tree Fill”.
.svg?alt=media&token=1e8c5cb8-8313-4dee-9a83-897615d7ab1f)
.svg?alt=media&token=52cad126-f5f3-479b-a4fe-3db53f640273)
