This guide will walk you through how to setup the Astun Loader to translate Ordnance Survey GML data products.

👍
Prerequisites

Python 2.7

GDAL

LXML==2.3

PostGIS

Decent text editor

Basic understanding of using command prompt

1. The Basics

In order to use the Astun Loader we need to have the following software installed:

Python 27.
GDAL
LXML==2.3
PostgreSQL/PostGIS (if you want to load the data into PostGIS)

Once the software is installed you then need to modify the configuration files to specify the following:

Which OS product the Loader is translating
Where are the source data files
An output directory
A temp directory
Database credentials - so the loader can connect to the database and load the data

You then run the Loader from the command prompt.

2. Installing the software

2.1 Install Python

Download Python 2.7 from here
Install using the default settings
Once installed you will need to add to your PATH environment variable.

;C:\Python27;C:\Python27\Scripts

❗️
BEWARE
Do not delete the contents of the PATH variable, you must append to the end

Test this by opening a command prompt window and type the following

python --version

You should see the installed Python version.

Python 2.7.8

If this fails then you have not added the Python directories to your PATH environment variable properly.

2.2 Install GDAL

GDAL is what actually does the translating from GML to different geospatial formats.

There are several ways to install GDAL, the following is my method which is tried and tested.

Download GDAL Complete from here.

There are several tables on this page. It is the top table you need to use and I would recommend the stable versions and also the 32bit version or unless you are going to use GDAL for large raster file transformations.

Click the Downloads link for the MSVC200x (Win32) -stable.
On the next page download the file named "gdal-111-1600-core.msi" (the numbering maybe slightly different depending on when this page is accessed)
Once the msi is downloaded install the program using the "Complete" installation setting.
Once installed you need to add the path to GDAL to the PATH environment variable (like we did above for Python), so append

;C:\Program Files (x86)\GDAL

onto the end of the PATH variable.

This can be tested by opening a command prompt window and running the following:

ogr2ogr --fomrats

This should show a long list of spatial vector formats GDAL supports.

2.3 How to install LXML

LXML is used by the Loader to parse the GML data.

As with GDAL there are several ways to install, for people on windows the simplest method is to install the correct installer from this [page] (https://pypi.python.org/pypi/lxml/2.3). Otherwise the other option is to install via PIP. PIP which is a tool for installing and managing Python packages. So essentially we use Python + PIP to install LXML.

2.3.1 Install PIP

To install PIP we need to download and save the get-pip.py script from here
Open a command prompt window in the folder where you saved the script to.

HINT: In a windows folder hold SHIFT + right hand mosue click somewhere in the folder and on context menu should be "Open Command Window Here"

To install PIP run the following command:

python get-pip.py

This will install PIP.

Once PIP is installed you can now install LXML

2.3.2 Install LXML

Now that PIP is installed we can use it to install the version of LXML we require

In the same command prompt window as above (or a new one if you closed it) run the following command:

pip install lxml==2.3

To check LXML has installed successfully you can check what Python modules are installed by running the following commands in a command prompt window:

python
help('modules')

After a few moments you should see a long list of Python modules and you should see LXML.

2.4 Get Loader

Finally we need to download Loader from github.

Download the zip file from here and unzip in a useful location

3. Setting up the database schema and tables

Because of the loading method we will use later we need to create the schema and database tables before loading the data.

For example these are the SQL statements for creating the schema and database tables for OSMM.

DROP SCHEMA IF EXISTS osmm CASCADE;
COMMIT;
CREATE SCHEMA osmm;
COMMIT;


-- Drops any existing OSMM related tables and creates fresh tables ready to receive data

DROP TABLE IF EXISTS "osmm"."boundaryline" CASCADE;
DELETE FROM geometry_columns WHERE f_table_name = 'boundaryline' AND f_table_schema = 'osmm';

CREATE TABLE "osmm"."boundaryline" ( OGC_FID SERIAL, CONSTRAINT "boundaryline_pk" PRIMARY KEY (OGC_FID) );
SELECT AddGeometryColumn('osmm','boundaryline','wkb_geometry',27700,'GEOMETRY',2);
ALTER TABLE "osmm"."boundaryline" ADD COLUMN "fid" varchar;
ALTER TABLE "osmm"."boundaryline" ADD COLUMN "featurecode" INTEGER;
ALTER TABLE "osmm"."boundaryline" ADD COLUMN "version" INTEGER;
ALTER TABLE "osmm"."boundaryline" ADD COLUMN "versiondate" varchar;
ALTER TABLE "osmm"."boundaryline" ADD COLUMN "theme" varchar[];
ALTER TABLE "osmm"."boundaryline" ADD COLUMN "accuracyofposition" varchar;
ALTER TABLE "osmm"."boundaryline" ADD COLUMN "changedate" varchar[];
ALTER TABLE "osmm"."boundaryline" ADD COLUMN "reasonforchange" varchar[];
ALTER TABLE "osmm"."boundaryline" ADD COLUMN "descriptivegroup" varchar[];
ALTER TABLE "osmm"."boundaryline" ADD COLUMN "descriptiveterm" varchar[];
ALTER TABLE "osmm"."boundaryline" ADD COLUMN "make" varchar;
ALTER TABLE "osmm"."boundaryline" ADD COLUMN "physicallevel" INTEGER;
ALTER TABLE "osmm"."boundaryline" ADD COLUMN "physicalpresence" varchar;
ALTER TABLE "osmm"."boundaryline" ADD COLUMN "filename" varchar;
ALTER TABLE "osmm"."boundaryline" ADD COLUMN "themes" varchar;
ALTER TABLE "osmm"."boundaryline" ADD COLUMN "descriptivegroups" varchar;
ALTER TABLE "osmm"."boundaryline" ADD COLUMN "descriptiveterms" varchar;

DROP TABLE IF EXISTS "osmm"."cartographicsymbol" CASCADE;
DELETE FROM geometry_columns WHERE f_table_name = 'cartographicsymbol' AND f_table_schema = 'osmm';

CREATE TABLE "osmm"."cartographicsymbol" ( OGC_FID SERIAL, CONSTRAINT "cartographicsymbol_pk" PRIMARY KEY (OGC_FID) );
SELECT AddGeometryColumn('osmm','cartographicsymbol','wkb_geometry',27700,'POINT',2);
ALTER TABLE "osmm"."cartographicsymbol" ADD COLUMN "fid" varchar;
ALTER TABLE "osmm"."cartographicsymbol" ADD COLUMN "featurecode" INTEGER;
ALTER TABLE "osmm"."cartographicsymbol" ADD COLUMN "version" INTEGER;
ALTER TABLE "osmm"."cartographicsymbol" ADD COLUMN "versiondate" varchar;
ALTER TABLE "osmm"."cartographicsymbol" ADD COLUMN "theme" varchar[];
ALTER TABLE "osmm"."cartographicsymbol" ADD COLUMN "changedate" varchar[];
ALTER TABLE "osmm"."cartographicsymbol" ADD COLUMN "reasonforchange" varchar[];
ALTER TABLE "osmm"."cartographicsymbol" ADD COLUMN "descriptivegroup" varchar[];
ALTER TABLE "osmm"."cartographicsymbol" ADD COLUMN "descriptiveterm" varchar[];
ALTER TABLE "osmm"."cartographicsymbol" ADD COLUMN "orientation" INTEGER;
ALTER TABLE "osmm"."cartographicsymbol" ADD COLUMN "physicallevel" INTEGER;
ALTER TABLE "osmm"."cartographicsymbol" ADD COLUMN "physicalpresence" varchar;
ALTER TABLE "osmm"."cartographicsymbol" ADD COLUMN "referencetofeature" VARCHAR;
ALTER TABLE "osmm"."cartographicsymbol" ADD COLUMN "filename" varchar;
ALTER TABLE "osmm"."cartographicsymbol" ADD COLUMN "themes" varchar;
ALTER TABLE "osmm"."cartographicsymbol" ADD COLUMN "descriptivegroups" varchar;
ALTER TABLE "osmm"."cartographicsymbol" ADD COLUMN "descriptiveterms" varchar;
ALTER TABLE "osmm"."cartographicsymbol" ADD COLUMN "orientdeg" varchar;

DROP TABLE IF EXISTS "osmm"."cartographictext" CASCADE;
DELETE FROM geometry_columns WHERE f_table_name = 'cartographictext' AND f_table_schema = 'osmm';

CREATE TABLE "osmm"."cartographictext" ( OGC_FID SERIAL, CONSTRAINT "cartographictext_pk" PRIMARY KEY (OGC_FID) );
SELECT AddGeometryColumn('osmm','cartographictext','wkb_geometry',27700,'POINT',2);
ALTER TABLE "osmm"."cartographictext" ADD COLUMN "fid" varchar;
ALTER TABLE "osmm"."cartographictext" ADD COLUMN "featurecode" INTEGER;
ALTER TABLE "osmm"."cartographictext" ADD COLUMN "version" INTEGER;
ALTER TABLE "osmm"."cartographictext" ADD COLUMN "versiondate" varchar;
ALTER TABLE "osmm"."cartographictext" ADD COLUMN "theme" varchar[];
ALTER TABLE "osmm"."cartographictext" ADD COLUMN "changedate" varchar[];
ALTER TABLE "osmm"."cartographictext" ADD COLUMN "reasonforchange" varchar[];
ALTER TABLE "osmm"."cartographictext" ADD COLUMN "descriptivegroup" varchar[];
ALTER TABLE "osmm"."cartographictext" ADD COLUMN "descriptiveterm" varchar[];
ALTER TABLE "osmm"."cartographictext" ADD COLUMN "make" varchar;
ALTER TABLE "osmm"."cartographictext" ADD COLUMN "physicallevel" INTEGER;
ALTER TABLE "osmm"."cartographictext" ADD COLUMN "physicalpresence" varchar;
ALTER TABLE "osmm"."cartographictext" ADD COLUMN "anchorposition" INTEGER;
ALTER TABLE "osmm"."cartographictext" ADD COLUMN "font" INTEGER;
ALTER TABLE "osmm"."cartographictext" ADD COLUMN "height" FLOAT8;
ALTER TABLE "osmm"."cartographictext" ADD COLUMN "orientation" INTEGER;
ALTER TABLE "osmm"."cartographictext" ADD COLUMN "textstring" varchar;
ALTER TABLE "osmm"."cartographictext" ADD COLUMN "filename" varchar;
ALTER TABLE "osmm"."cartographictext" ADD COLUMN "themes" varchar;
ALTER TABLE "osmm"."cartographictext" ADD COLUMN "descriptivegroups" varchar;
ALTER TABLE "osmm"."cartographictext" ADD COLUMN "descriptiveterms" varchar;
ALTER TABLE "osmm"."cartographictext" ADD COLUMN "orientdeg" varchar;


DROP TABLE IF EXISTS "osmm"."topographicarea" CASCADE;
DELETE FROM geometry_columns WHERE f_table_name = 'topographicarea' AND f_table_schema = 'osmm';

CREATE TABLE "osmm"."topographicarea" ( OGC_FID SERIAL, CONSTRAINT "topographicarea_pk" PRIMARY KEY (OGC_FID) );
SELECT AddGeometryColumn('osmm','topographicarea','wkb_geometry',27700,'POLYGON',2);
ALTER TABLE "osmm"."topographicarea" ADD COLUMN "fid" varchar;
ALTER TABLE "osmm"."topographicarea" ADD COLUMN "featurecode" INTEGER;
ALTER TABLE "osmm"."topographicarea" ADD COLUMN "version" INTEGER;
ALTER TABLE "osmm"."topographicarea" ADD COLUMN "versiondate" varchar;
ALTER TABLE "osmm"."topographicarea" ADD COLUMN "theme" varchar[];
ALTER TABLE "osmm"."topographicarea" ADD COLUMN "calculatedareavalue" FLOAT8;
ALTER TABLE "osmm"."topographicarea" ADD COLUMN "changedate" varchar[];
ALTER TABLE "osmm"."topographicarea" ADD COLUMN "reasonforchange" varchar[];
ALTER TABLE "osmm"."topographicarea" ADD COLUMN "descriptivegroup" varchar[];
ALTER TABLE "osmm"."topographicarea" ADD COLUMN "descriptiveterm" varchar[];
ALTER TABLE "osmm"."topographicarea" ADD COLUMN "make" varchar;
ALTER TABLE "osmm"."topographicarea" ADD COLUMN "physicallevel" INTEGER;
ALTER TABLE "osmm"."topographicarea" ADD COLUMN "physicalpresence" varchar;
ALTER TABLE "osmm"."topographicarea" ADD COLUMN "filename" varchar;
ALTER TABLE "osmm"."topographicarea" ADD COLUMN "themes" varchar;
ALTER TABLE "osmm"."topographicarea" ADD COLUMN "descriptivegroups" varchar;
ALTER TABLE "osmm"."topographicarea" ADD COLUMN "descriptiveterms" varchar;

DROP TABLE IF EXISTS "osmm"."topographicline" CASCADE;
DELETE FROM geometry_columns WHERE f_table_name = 'topographicline' AND f_table_schema = 'osmm';

CREATE TABLE "osmm"."topographicline" ( OGC_FID SERIAL, CONSTRAINT "topographicline_pk" PRIMARY KEY (OGC_FID) );
SELECT AddGeometryColumn('osmm','topographicline','wkb_geometry',27700,'MULTILINESTRING',2);
ALTER TABLE "osmm"."topographicline" ADD COLUMN "fid" varchar;
ALTER TABLE "osmm"."topographicline" ADD COLUMN "featurecode" INTEGER;
ALTER TABLE "osmm"."topographicline" ADD COLUMN "version" INTEGER;
ALTER TABLE "osmm"."topographicline" ADD COLUMN "versiondate" varchar;
ALTER TABLE "osmm"."topographicline" ADD COLUMN "theme" varchar[];
ALTER TABLE "osmm"."topographicline" ADD COLUMN "accuracyofposition" varchar;
ALTER TABLE "osmm"."topographicline" ADD COLUMN "changedate" varchar[];
ALTER TABLE "osmm"."topographicline" ADD COLUMN "reasonforchange" varchar[];
ALTER TABLE "osmm"."topographicline" ADD COLUMN "descriptivegroup" varchar[];
ALTER TABLE "osmm"."topographicline" ADD COLUMN "descriptiveterm" varchar[];
ALTER TABLE "osmm"."topographicline" ADD COLUMN "nonboundingline" varchar;
ALTER TABLE "osmm"."topographicline" ADD COLUMN "heightabovedatum" FLOAT8;
ALTER TABLE "osmm"."topographicline" ADD COLUMN "accuracyofheightabovedatum" varchar;
ALTER TABLE "osmm"."topographicline" ADD COLUMN "heightabovegroundlevel" FLOAT8;
ALTER TABLE "osmm"."topographicline" ADD COLUMN "accuracyofheightabovegroundlevel" varchar;
ALTER TABLE "osmm"."topographicline" ADD COLUMN "make" varchar;
ALTER TABLE "osmm"."topographicline" ADD COLUMN "physicallevel" INTEGER;
ALTER TABLE "osmm"."topographicline" ADD COLUMN "physicalpresence" varchar;
ALTER TABLE "osmm"."topographicline" ADD COLUMN "filename" varchar;
ALTER TABLE "osmm"."topographicline" ADD COLUMN "themes" varchar;
ALTER TABLE "osmm"."topographicline" ADD COLUMN "descriptivegroups" varchar;
ALTER TABLE "osmm"."topographicline" ADD COLUMN "descriptiveterms" varchar;


DROP TABLE IF EXISTS "osmm"."topographicpoint" CASCADE;
DELETE FROM geometry_columns WHERE f_table_name = 'topographicpoint' AND f_table_schema = 'osmm';

CREATE TABLE "osmm"."topographicpoint" ( OGC_FID SERIAL, CONSTRAINT "topographicpoint_pk" PRIMARY KEY (OGC_FID) );
SELECT AddGeometryColumn('osmm','topographicpoint','wkb_geometry',27700,'POINT',2);
ALTER TABLE "osmm"."topographicpoint" ADD COLUMN "fid" varchar;
ALTER TABLE "osmm"."topographicpoint" ADD COLUMN "featurecode" INTEGER;
ALTER TABLE "osmm"."topographicpoint" ADD COLUMN "version" INTEGER;
ALTER TABLE "osmm"."topographicpoint" ADD COLUMN "versiondate" varchar;
ALTER TABLE "osmm"."topographicpoint" ADD COLUMN "theme" varchar[];
ALTER TABLE "osmm"."topographicpoint" ADD COLUMN "accuracyofposition" varchar;
ALTER TABLE "osmm"."topographicpoint" ADD COLUMN "changedate" varchar[];
ALTER TABLE "osmm"."topographicpoint" ADD COLUMN "reasonforchange" varchar[];
ALTER TABLE "osmm"."topographicpoint" ADD COLUMN "descriptivegroup" varchar[];
ALTER TABLE "osmm"."topographicpoint" ADD COLUMN "descriptiveterm" varchar[];
ALTER TABLE "osmm"."topographicpoint" ADD COLUMN "heightabovedatum" FLOAT8;
ALTER TABLE "osmm"."topographicpoint" ADD COLUMN "accuracyofheightabovedatum" varchar;
ALTER TABLE "osmm"."topographicpoint" ADD COLUMN "make" varchar;
ALTER TABLE "osmm"."topographicpoint" ADD COLUMN "physicallevel" INTEGER;
ALTER TABLE "osmm"."topographicpoint" ADD COLUMN "physicalpresence" varchar;
ALTER TABLE "osmm"."topographicpoint" ADD COLUMN "referencetofeature" VARCHAR;
ALTER TABLE "osmm"."topographicpoint" ADD COLUMN "filename" varchar;
ALTER TABLE "osmm"."topographicpoint" ADD COLUMN "themes" varchar;
ALTER TABLE "osmm"."topographicpoint" ADD COLUMN "descriptivegroups" varchar;
ALTER TABLE "osmm"."topographicpoint" ADD COLUMN "descriptiveterms" varchar;

4. Altering the config files

The next step to getting the Astun Loader setup is to tweak the configuration files so the Loader does what you want.

Astun technology have provided a very useful wiki showing examples of the various configuration options and this can be found here.

My example here will show you how to configure Loader for OS MasterMap Topography Layer

If you have downloaded the Astun Loader zip file from GitHub, unzip the files and put them in a useful location, e.g C:\Loader

The Loader is made up of several python scripts and a configuration script, and it is this configuration script that needs tweaking for your database, your source files and the product.

At the time of writing there are two ways to load data using Loader into PostGIS:

Traditional Insert - inserts one row of data for each transaction
Dump method - loads lots of rows of data in one transaction

The original loader.config file uses the INSERT method and I would recommend against this method not matter how small a number of data files you are loading.

Instead we want to use the loader.config file found in

extras\ordnancesurvey\osmm\topo\pgdump

I like to rename this file to loader_dump.config so that I know this is the different version and I copy it to the python folder back at the root of the zip file.

Then open this config file in your favourite text editor and make the following changes:

src_dir=/path/to/source/data/folder         eg C:\OSData\Topo\Files
out_dir=/path/to/output/data/folder         eg C:\OSData\Topo\Output
tmp_dir=/path/to/temp/folder                eg C:\OSData\Topo\Temp

You may need to create the extra Output and Temp folders.

You also need to alter the following line to make sure it is going to read the correct product, notice how it says *prep_osmm_topo", see the Loader Wiki for other options.

prep_cmd=python prepgml4ogr.py $file_path prep_osgml.prep_osmm_topo

Then change the database details to match yours in this line:

post_cmd=psql -U postgres -d postgis -f $output_dir/$base_file_name.sql

Once all the above modifications have been made Loader can be run.

You can now start loading the data by opening a command prompt in the python directory and type the following:

python loader.py loader_dump.config

The command prompt should tell you its translating and loading the data.

5. Post Processing the Database

5.1 Creating Spatial indexes

Once the data has finished loading you will need to create attribute and spatial indexes:

-- Creates a spatial index for each OSMM table
CREATE INDEX "boundaryline_geom_idx" ON "osmm"."boundaryline" USING GIST ("wkb_geometry");
CREATE INDEX "cartographicsymbol_geom_idx" ON "osmm"."cartographicsymbol" USING GIST ("wkb_geometry");
CREATE INDEX "cartographictext_geom_idx" ON "osmm"."cartographictext" USING GIST ("wkb_geometry");
CREATE INDEX "topographicarea_geom_idx" ON "osmm"."topographicarea" USING GIST ("wkb_geometry");
CREATE INDEX "topographicline_geom_idx" ON "osmm"."topographicline" USING GIST ("wkb_geometry");
CREATE INDEX "topographicpoint_geom_idx" ON "osmm"."topographicpoint" USING GIST ("wkb_geometry");

5.2 Creating os_cat styling column

If you want to use the Ordnance Survey SLD stylesheets then you will need to post process the data using the following SQL code, if you do not then you can ignore the rest of this section.

5.2.1 OS_Cat for BoundaryLine table

ALTER TABLE osmm.boundaryline ADD COLUMN os_cat VARCHAR;
		UPDATE osmm.boundaryline
			SET os_cat = (CASE 
				WHEN (descriptivegroup ='{"Political Or Administrative"}' and descriptiveterm ='{County}' and physicalpresence ='Boundary') then 'boundaryCounty'
				WHEN (descriptivegroup ='{"Political Or Administrative"}' and descriptiveterm ='{District}' and physicalpresence ='Boundary') then 'boundaryDistrict'
				WHEN (descriptivegroup ='{"Political Or Administrative"}' and descriptiveterm ='{Electoral}' and physicalpresence ='Boundary') then 'boundaryElectoral'
				WHEN (descriptivegroup ='{"Political Or Administrative"}' and descriptiveterm ='{Parish}' and physicalpresence ='Boundary') then 'boundaryParish'
				WHEN (descriptivegroup ='{"Political Or Administrative"}' and descriptiveterm ='{Parliamentary}' and physicalpresence ='Boundary') then 'boundaryParliamentary'
				ELSE 'boundaryUnknown'			  
			  END)
		WHERE os_cat is null;

5.2.2 OS_Cat for CartographicSymbol table

ALTER TABLE osmm.cartographicsymbol ADD COLUMN os_cat VARCHAR;
		UPDATE osmm.cartographicsymbol
		SET os_cat = (CASE 
				WHEN (descriptiveterm ='{"Bench Mark"}') then 'symbolBenchMark'
				WHEN (descriptiveterm ='{Culvert}') then 'symbolCulvert'
				WHEN (descriptiveterm ='{"Direction Of Flow"}') then 'symbolDirectionOfFlow'
				WHEN (descriptiveterm ='{"Boundary Half Mereing"}') then 'symbolBoundaryHalfMereing'
				WHEN (descriptiveterm ='{Switch}') then 'symbolRailwaySwitch'
				WHEN (descriptiveterm ='{"Road Related Flow"}') then 'symbolRoadRelatedFlow'	
				ELSE 'symbolUnknown'			  
					  END)
		WHERE os_cat is null;

5.2.3 OS_Cat for CartographicText table

ALTER TABLE osmm.cartographictext ADD COLUMN os_cat VARCHAR;
		UPDATE osmm.cartographictext
		SET os_cat = (CASE 
				WHEN (descriptivegroup ='{"Buildings Or Structure"}') then 'textBuilding'
				WHEN (descriptivegroup ='{"Built Environment"}' and descriptiveterm ='{Compound}') then 'textBuiltEnvironment'
				WHEN (descriptivegroup ='{"General Feature"}' and make ='{Manmade}') then 'textGeneralFeatureManmade'
				WHEN (descriptivegroup ='{"General Feature"}') then 'textGeneralFeature'
				WHEN (descriptivegroup ='{"General Surface"}' and make = 'Manmade') then 'textGeneralSurfaceManmade'
				WHEN (descriptivegroup ='{"Historic Interest"}') then 'textHistoricInterest'
				WHEN (descriptivegroup ='{"Inland Water"}') then 'textInlandWater'
				WHEN (descriptivegroup ='{Landform}' and make = 'Manmade') then 'textLandformManmade'
				WHEN (descriptivegroup ='{Landform}' and make = 'Natural') then 'textLandformNatural'
				WHEN (descriptivegroup ='{"Political Or Administrative"}') then 'textPoliticalAdministrative'
				WHEN (descriptivegroup ='{Rail}') then 'textRail'
				WHEN (descriptivegroup ='{"Road Or Track"}') then 'textRoadOrTrack'
				WHEN (descriptivegroup ='{Roadside}') then 'textRoadside'
				WHEN (descriptivegroup ='{Structure}') then 'textStructure'
				WHEN (descriptivegroup ='{"Terrain And Height"}') then 'textTerrainAndHeight'
				WHEN (descriptivegroup ='{"Tidal Water"}' and descriptiveterm ='{Foreshore}' and make = 'Natural') then 'textForeshoreNatural'
				WHEN (descriptivegroup ='{"Tidal Water"}' and make ='Natural') then 'textTidalWater'
				WHEN (descriptivegroup ='{Unclassified}') then 'textUnclassified'		
				ELSE 'textUnknown'			  
					  END)
		WHERE os_cat is null;

5.2.4 Add geo_x and geo_y to CartographicText table

ALTER TABLE osmm.cartographictext ADD COLUMN "geo_x" FLOAT8;
COMMIT;
ALTER TABLE osmm.cartographictext ADD COLUMN "geo_y" FLOAT8;
COMMIT;


update osmm.cartographictext set geo_x = 0, geo_y = 0 where anchorposition = 0;
COMMIT;
update osmm.cartographictext set geo_x = 0, geo_y = 0.5 where anchorposition = 1;
COMMIT;
update osmm.cartographictext set geo_x = 0, geo_y = 1 where anchorposition = 2;
COMMIT;
update osmm.cartographictext set geo_x = 0.5, geo_y = 0 where anchorposition = 3;
COMMIT;
update osmm.cartographictext set geo_x = 0.5, geo_y = 0.5 where anchorposition = 4;
COMMIT;
update osmm.cartographictext set geo_x = 0.5, geo_y = 1 where anchorposition = 5;
COMMIT;
update osmm.cartographictext set geo_x = 1, geo_y = 0 where anchorposition = 6;
COMMIT;
update osmm.cartographictext set geo_x = 1, geo_y = 0.5 where anchorposition = 7;
COMMIT;
update osmm.cartographictext set geo_x = 1, geo_y = 1 where anchorposition = 8;
COMMIT;

5.2.5 OS_Cat for TopographicArea table

ALTER TABLE osmm.topographicarea ADD COLUMN os_cat VARCHAR;
		UPDATE osmm.topographicarea
		SET os_cat = (CASE 
				WHEN (descriptivegroup ='{"General Surface","Inland Water"}') then 'fillInlandWater'
				WHEN (descriptivegroup ='{"General Surface","Tidal Water"}') then 'fillTidalWater'
				WHEN (descriptivegroup ='{"General Surface","Historic Interest"}') then 'fillHeritage'
				WHEN (descriptivegroup ='{"General Surface","Path"}') then 'fillPath'
				WHEN (descriptivegroup ='{"General Surface","Road Or Track"}') then 'fillRoadOrTrack'
				WHEN (descriptivegroup ='{"General Surface","Rail"}' and make = 'Manmade') then 'fillRail'
				WHEN (descriptivegroup ='{"General Surface","Rail"}' and make = 'Natural') then 'fillNaturalSurface'
				WHEN (descriptivegroup ='{"General Surface"}' and make = 'Natural') then 'fillNaturalSurface'
				WHEN (descriptivegroup ='{"General Surface"}' and make = 'Manmade') then 'fillMadeSurface'
				WHEN (descriptivegroup ='{"General Surface"}' and descriptiveterm = '{"Multi Surface"}') then 'fillMultipleSurface'
				WHEN (descriptivegroup ='{"General Surface"}' and make = 'Unknown') then 'fillUnknownSurface'
				WHEN (descriptivegroup ='{Roadside}' and make = 'Natural') then 'fillRoadsideNatural'
				WHEN (descriptivegroup ='{Roadside}' and make = 'Manmade') then 'fillRoadsideManmade'
				WHEN (descriptivegroup ='{Roadside}' and make = 'Unknown') then 'fillRoadsideUnknown'
				
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Boulders (Scattered)", Heath, "Rock (Scattered)", "Rough Grassland", Scrub}') then 'fillHeathAndRockScatteredAndRoughGrassland'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{Boulders, "Nonconiferous Trees (Scattered)", "Rock (Scattered)", "Rough Grassland"}') then 'fillBouldersAndNonconiferousTreesScatteredAndRoughGrassland'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Boulders (Scattered)", "Rock (Scattered)", "Rough Grassland", Scrub}') then 'fillRockScatteredAndRoughGrasslandAndScrub'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Coniferous Trees (Scattered)", "Nonconiferous Trees (Scattered)", "Rough Grassland", Scrub}') then 'fillNonconiferousTreesScatteredAndConiferousTreesScatteredAndScrub'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{Heath, "Nonconiferous Trees (Scattered)", "Rock (Scattered)", "Rough Grassland"}') then 'fillHeathAndNonconiferousTreesScatteredAndRoughGrassland'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Boulders (Scattered)", "Nonconiferous Trees (Scattered)", "Rough Grassland", Scrub}') then 'fillNonconiferousTreesScatteredAndScrubAndRoughGrassland'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Boulders (Scattered)", "Nonconiferous Trees (Scattered)", Rock, "Rough Grassland"}') then 'fillNonconiferousTreesScatteredAndRockAndRoughGrassland'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Boulders (Scattered)", Heath, "Rock (Scattered)", "Rough Grassland"}') then 'fillHeathAndRockScatteredAndRoughGrassland'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Nonconiferous Trees (Scattered)", "Rock (Scattered)", "Rough Grassland", Scrub}') then 'fillNonconiferousTreesScatteredAndScrubAndRoughGrassland'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{Boulders, Heath, "Rock (Scattered)", "Rough Grassland"}') then 'fillHeathAndRockScatteredAndRoughGrassland'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{Boulders, "Nonconiferous Trees (Scattered)", "Rough Grassland", Scrub}') then 'fillBouldersAndRoughGrasslandAndScrub'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{Heath, "Nonconiferous Trees (Scattered)", "Rough Grassland", Scrub}') then 'fillHeathAndNonconiferousTreesScatteredAndScrub'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{Heath, "Rock (Scattered)", "Rough Grassland", Scrub}') then 'fillHeathAndScrubAndRoughGrassland'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Nonconiferous Trees", "Rock (Scattered)", "Rough Grassland", Scrub}') then 'fillNonconiferousTreesAndScrubAndRoughGrassland'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Marsh Reeds Or Saltmarsh", "Rock (Scattered)", "Rough Grassland", Scrub}') then 'fillMarshAndRoughGrasslandAndScrub'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Boulders (Scattered)", Heath, "Rough Grassland", Scrub}') then 'fillHeathAndScrubAndRoughGrassland'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Boulders (Scattered)", Heath, Rock, "Rough Grassland"}') then 'fillHeathAndRockAndRoughGrassland'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{Boulders, Rock, "Rough Grassland", Scrub}') then 'fillRoughGrasslandAndBouldersAndRock'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Coniferous Trees", "Nonconiferous Trees", "Rough Grassland", Scrub}') then 'fillNonconiferousTreesAndConiferousTreesAndScrub'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{Heath, "Marsh Reeds Or Saltmarsh", "Rough Grassland", Scrub}') then 'fillHeathAndScrubAndRoughGrassland'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{Heath, "Nonconiferous Trees", "Rough Grassland", Scrub}') then 'fillHeathAndNonconiferousTreesAndScrub'
				
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Boulders (Scattered)", "Confierous Trees", "Rock (Scattered)"}') then 'fillBouldersScatteredAndConiferousTreesAndRockScattered'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Boulders (Scattered)", "Confierous Trees (Scattered)", "Rough Grassland"}') then 'fillBouldersScatteredAndConiferousTreesScatteredAndRoughGrassland'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Boulders (Scattered)", Heath, "Nonconfierous Trees (Scattered)"}') then 'fillBouldersScatteredAndHeathAndNonconiferousTreesScattered'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Boulders (Scattered)", Heath, "Rock (Scattered)"}') then 'fillBouldersScatteredAndHeathAndRockScattered'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Boulders (Scattered)", "Nonconfierous Trees", "Rock (Scattered)"}') then 'fillBouldersScatteredAndNonconiferousTreesAndRockScattered'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Boulders (Scattered)", "Nonconfierous Trees (Scattered)", Scrub}') then 'fillBouldersScatteredAndNonconiferousTreesScatteredAndScrub'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Confierous Trees (Scattered)", "Nonconfierous Trees (Scattered)", "Rough Grassland"}') then 'fillConiferousTreesScatteredAndNonconiferousTreesScatteredAndRoughGrassland'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Confierous Trees (Scattered)", "Nonconfierous Trees (Scattered)", Scrub}') then 'fillConiferousTreesScatteredAndNonconiferousTreesScatteredAndScrub'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Confierous Trees (Scattered)", "Rock (Scattered)", "Rough Grassland"}') then 'fillConiferousTreesScatteredAndRockScatteredAndRoughGrassland'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Nonconfierous Trees (Scattered)", "Rock (Scattered)", "Rough Grassland"}') then 'fillNonconiferousTreesScatteredAndRockScatteredAndRoughGrassland'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Nonconfierous Trees (Scattered)", "Rock (Scattered)", Scrub}') then 'fillNonconiferousTreesScatteredAndRockScatteredAndScrub'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Rough Grassland (Scattered)", Rock, "Boulders (Scattered)"}') then 'fillRoughGrasslandScatteredAndRockAndBouldersScattered'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{Boulders, "Nonconfierous Trees (Scattered)", "Rough Grassland"}') then 'fillBouldersAndNonconiferousTreesScatteredAndRoughGrassland'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{Boulders, "Nonconfierous Trees (Scattered)", Scrub}') then 'fillBouldersAndNonconiferousTreesScatteredAndScrub'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{Boulders, Heath, "Rock (Scattered)"}') then 'fillBouldersAndHeathAndRockScattered'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Boulders (Scattered)", "Coniferous Trees", "Nonconiferous Trees"}') then 'fillBouldersScatteredAndConiferousTreesAndNonconiferousTrees'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Boulders (Scattered)", Heath, "Rough Grassland"}') then 'fillBouldersScatteredAndHeathAndRoughGrassland'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Boulders (Scattered)", Heath, Scrub}') then 'fillBouldersScatteredAndHeathAndScrub'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Boulders (Scattered)", "Marsh Reeds Or Saltmarsh", "Rough Grassland"}') then 'fillBouldersScatteredAndMarshAndRoughGrassland'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Boulders (Scattered)", "Nonconiferous Trees", "Rough Grassland"}') then 'fillBouldersScatteredAndNonconiferousTreesAndRoughGrassland'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Boulders (Scattered)", "Nonconiferous Trees", Scrub}') then 'fillBouldersScatteredAndNonconiferousTreesAndScrub'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Boulders (Scattered)", Rock, "Rough Grassland"}') then 'fillBouldersScatteredAndRockAndRoughGrassland'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Boulders (Scattered)", "Rough Grassland", Scrub}') then 'fillBouldersScatteredAndRoughGrasslandAndScrub'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Coniferous Trees", "Nonconiferous Trees", "Rock (Scattered)"}') then 'fillConiferousTreesAndNonconiferousTreesAndRockScattered'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Coniferous Trees", "Rock (Scattered)", "Rough Grassland"}') then 'fillConiferousTreesAndRockScatteredAndRoughGrassland'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Coniferous Trees (Scattered)", Heath, "Rough Grassland"}') then 'fillConiferousTreesScatteredAndHeathAndRoughGrassland'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Coniferous Trees (Scattered)", Heath, Scrub}') then 'fillConiferousTreesScatteredAndHeathAndScrub'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Coniferous Trees (Scattered)", "Nonconiferous Trees", Scrub}') then 'fillConiferousTreesScatteredAndNonconiferousTreesAndScrub'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Coniferous Trees (Scattered)", Rock, "Rough Grassland"}') then 'fillConiferousTreesScatteredAndRockAndRoughGrassland'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Coniferous Trees (Scattered)", "Rough Grassland", Scrub}') then 'fillConiferousTreesScatteredAndRoughGrasslandAndScrub'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{Heath, "Nonconiferous Trees (Scattered)", Scrub}') then 'fillHeathAndNonconiferousTreesScatteredAndScrub'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{Heath, "Rock (Scattered)", "Rough Grassland"}') then 'fillHeathAndRockScatteredAndRoughGrassland'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{Heath, "Rock (Scattered)", Scrub}') then 'fillHeathAndRockScatteredAndScrub'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Marsh Reeds Or Saltmarsh", "Rock (Scattered)", "Rough Grassland"}') then 'fillMarshAndRockScatteredAndRoughGrassland'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Nonconiferous Trees", "Rock (Scattered)", "Rough Grassland"}') then 'fillNonconiferousTreesAndRockScatteredAndRoughGrassland'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Nonconiferous Trees", "Rock (Scattered)", Scrub}') then 'fillNonconiferousTreesAndRockScatteredAndScrub'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Nonconiferous Trees (Scattered)", Rock, "Rough Grassland"}') then 'fillNonconiferousTreesScatteredAndRockAndRoughGrassland'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Nonconiferous Trees (Scattered)", Rock, Scrub}') then 'fillNonconiferousTreesScatteredAndRockAndScrub'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Nonconiferous Trees (Scattered)", Scrub, "Rough Grassland"}') then 'fillNonconiferousTreesScatteredAndScrubAndRoughGrassland'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Rock (Scattered)", "Rough Grassland", Scrub}') then 'fillRockScatteredAndRoughGrasslandAndScrub'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Rough Grassland", Boulders, "Rock (Scattered)"}') then 'fillRoughGrasslandAndBouldersAndRockScattered'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Rough Grassland", Scrub, "Rock (Scattered)"}') then 'fillRoughGrasslandAndScrubAndRockScattered'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Rough Grassland (Scattered)", "Nonconiferous Trees", Scrub}') then 'fillRoughGrasslandScatteredAndNonconiferousTreesAndScrub'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Rough Grassland (Scattered)", Rock, Boulders}') then 'fillRoughGrasslandScatteredAndRockAndBoulders'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Rough Grassland (Scattered)", Rock, Heath}') then 'fillRoughGrasslandScatteredAndRockAndHeath'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{Boulders, Heath, "Rough Grassland"}') then 'fillBouldersAndHeathAndRoughGrassland'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{Boulders, "Marsh Reeds Or Saltmarsh", "Rough Grassland"}') then 'fillBouldersAndMarshAndRoughGrassland'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{Boulders, "Nonconiferous Trees", "Rough Grassland"}') then 'fillBouldersAndNonconiferousTreesAndRoughGrassland'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{Boulders, "Nonconiferous Trees", Scrub}') then 'fillBouldersAndNonconiferousTreesAndScrub'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{Boulders, "Rough Grassland", Scrub}') then 'fillBouldersAndRoughGrasslandAndScrub'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Coniferous Trees", "Coppice Or Osiers", "Nonconiferous Trees"}') then 'fillConiferousTreesAndCoppiceOrOsiersAndNonconiferousTrees'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Coniferous Trees", "Coppice Or Osiers", Scrub}') then 'fillConiferousTreesAndCoppiceOrOsiersAndScrub'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Coniferous Trees", Heath, Rock}') then 'fillConiferousTreesAndHeathAndRock'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Coniferous Trees", Heath, "Rough Grassland"}') then 'fillConiferousTreesAndHeathAndRoughGrassland'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Coniferous Trees", Heath, Scrub}') then 'fillConiferousTreesAndHeathAndScrub'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Coniferous Trees", "Marsh Reeds Or Saltmarsh", "Nonconiferous Trees"}') then 'fillConiferousTreesAndMarshAndNonconiferousTrees'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Coniferous Trees", "Nonconiferous Trees", Rock}') then 'fillConiferousTreesAndNonconiferousTreesAndRock'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Coniferous Trees", Rock, "Rough Grassland"}') then 'fillConiferousTreesAndRockAndRoughGrassland'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Coniferous Trees", Scrub, "Rough Grassland"}') then 'fillConiferousTreesAndScrubAndRoughGrassland'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Coppice Or Osiers", "Nonconiferous Trees", "Rough Grassland"}') then 'fillCoppiceOrOsiersAndNonconiferousTreesAndRoughGrassland'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{Heath, "Nonconiferous Trees", "Rough Grassland"}') then 'fillHeathAndNonconiferousTreesAndRoughGrassland'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{Heath, "Nonconiferous Trees", Scrub}') then 'fillHeathAndNonconiferousTreesAndScrub'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{Heath, Rock, "Rough Grassland"}') then 'fillHeathAndRockAndRoughGrassland'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{Heath, Rock, Scrub}') then 'fillHeathAndRockAndScrub'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{Heath, Scrub, "Rough Grassland"}') then 'fillHeathAndScrubAndRoughGrassland'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Marsh Reeds Or Saltmarsh", "Nonconiferous Trees", "Rough Grassland"}') then 'fillMarshAndNonconiferousTreesAndRoughGrassland'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Marsh Reeds Or Saltmarsh", "Nonconiferous Trees", Scrub}') then 'fillMarshAndNonconiferousTreesAndScrub'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Marsh Reeds Or Saltmarsh", "Rough Grassland", Scrub}') then 'fillMarshAndRoughGrasslandAndScrub'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Nonconiferous Trees", Rock, "Rough Grassland"}') then 'fillNonconiferousTreesAndRockAndRoughGrassland'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Nonconiferous Trees", Rock, Scrub}') then 'fillNonconiferousTreesAndRockAndScrub'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{Rock, "Rough Grassland", Boulders}') then 'fillRockAndRoughGrasslandAndBoulders'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{Rock, "Rough Grassland", Scrub}') then 'fillRockAndRoughGrasslandAndScrub'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Rough Grassland", Heath, Marsh}') then 'fillRoughGrasslandAndHeathAndMarsh'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Rough Grassland", "Nonconiferous Trees", "Coniferous Trees"}') then 'fillRoughGrasslandAndNonconiferousTreesAndConiferousTrees'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Rough Grassland", "Nonconiferous Trees", Scrub}') then 'fillRoughGrasslandAndNonconiferousTreesAndScrub'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{Scrub, "Coniferous Trees", "Nonconiferous Trees"}') then 'fillScrubAndConiferousTreesAndNonconiferousTrees'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{Scrub, "Nonconiferous Trees", "Coppice Or Osiers"}') then 'fillScrubAndNonconiferousTreesAndCoppiceOrOsiers'
				
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Boulders (Scattered)", "Nonconiferous Trees (Scattered)"}') then 'fillBouldersScatteredAndNonconiferousTreesScattered'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Boulders (Scattered)", "Rock (Scattered)"}') then 'fillBouldersScatteredAndRockScattered'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Nonconiferous Trees (Scattered)", "Coniferous Trees (Scattered)"}') then 'fillNonconiferousTreesScatteredAndConiferousTreesScattered'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Nonconiferous Trees (Scattered)", "Rock (Scattered)"}') then 'fillNonconiferousTreesScatteredAndRockScattered'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{Boulders, "Nonconiferous Trees (Scattered)"}') then 'fillBouldersAndNonconiferousTreesScattered'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Boulders (Scattered)", "Coppice Or Osiers"}') then 'fillBouldersScatteredAndCoppiceOrOsiers'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Boulders (Scattered)", Heath}') then 'fillBouldersScatteredAndHeath'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Boulders (Scattered)", "Nonconiferous Trees"}') then 'fillBouldersScatteredAndNonconiferousTrees'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Boulders (Scattered)", Rock}') then 'fillBouldersScatteredAndRock'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Coniferous Trees", "Boulders (Scattered)"}') then 'fillConiferousTreesAndBouldersScattered'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Coniferous Trees", "Nonconiferous Trees (Scattered)"}') then 'fillConiferousTreesAndNonconiferousTreesScattered'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Coniferous Trees", "Rock (Scattered)"}') then 'fillConiferousTreesAndRockScattered'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Coniferous Trees (Scattered)", Rock}') then 'fillConiferousTreesScatteredAndRock'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Coniferous Trees (Scattered)", "Rough Grassland"}') then 'fillConiferousTreesScatteredAndRoughGrassland'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Coniferous Trees (Scattered)", Scrub}') then 'fillConiferousTreesScatteredAndScrub'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{Heath, "Boulders (Scattered)"}') then 'fillHeathAndBouldersScattered'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{Heath, "Rock (Scattered)"}') then 'fillHeathAndRockScattered'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{Heath, "Boulders (Scattered)"}') then 'fillHeathAndBouldersScattered'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{Heath, "Nonconiferous Trees (Scattered)"}') then 'fillHeathAndNonconiferousTreesScattered'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Marsh Reeds Or Saltmarsh", "Nonconiferous Trees (Scattered)"}') then 'fillMarshAndNonconiferousTreesScattered'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Marsh Reeds Or Saltmarsh", "Rock (Scattered)"}') then 'fillMarshAndRockScattered'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Nonconiferous Trees", "Rock (Scattered)"}') then 'fillNonconiferousTreesAndRockScattered'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Nonconiferous Trees (Scattered)", Rock}') then 'fillNonconiferousTreesScatteredAndRock'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Rough Grassland", "Boulders (Scattered)"}') then 'fillRoughGrasslandAndBouldersScattered'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Rough Grassland", "Nonconiferous Trees (Scattered)"}') then 'fillRoughGrasslandAndNonconiferousTreesScattered'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Rough Grassland", "Rock (Scattered)"}') then 'fillRoughGrasslandAndRockScattered'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{Scrub, "Boulders (Scattered)"}') then 'fillScrubAndBouldersScattered'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{Scrub, "Nonconiferous Trees (Scattered)"}') then 'fillScrubAndNonconiferousTreesScattered'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{Scrub, "Rock (Scattered)"}') then 'fillScrubAndRockScattered'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{Boulders, Heath}') then 'fillBouldersAndHeath'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{Boulders, "Nonconiferous Trees"}') then 'fillBouldersAndNonconiferousTrees'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{Boulders, Rock}') then 'fillBouldersAndRock'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Coniferous Trees", Boulders}') then 'fillConiferousTreesAndBoulders'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Coniferous Trees", Heath}') then 'fillConiferousTreesAndHeath'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Coniferous Trees", "Marsh Reeds Or Saltmarsh"}') then 'fillConiferousTreesAndMarsh'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Coniferous Trees", Rock}') then 'fillConiferousTreesAndRock'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Coniferous Trees", Scrub}') then 'fillConiferousTreesAndScrub'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Coppice Or Osiers", "Rough Grassland"}') then 'fillCoppiceOrOsiersAndRoughGrassland'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Coppice Or Osiers", Scrub}') then 'fillCoppiceOrOsiersAndScrub'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{Heath, "Marsh Reeds Or Saltmarsh"}') then 'fillHeathAndMarsh'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{Heath, "Nonconiferous Trees"}') then 'fillHeathAndNonconiferousTrees'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{Heath, Rock}') then 'fillHeathAndRock'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{Heath, Scrub}') then 'fillHeathAndMarsh'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Marsh Reeds Or Saltmarsh", "Nonconiferous Trees"}') then 'fillMarshAndNonconiferousTrees'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Nonconiferous Trees", "Coniferous Trees"}') then 'fillNonconiferousTreesAndConiferousTrees'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Nonconiferous Trees", "Coppice Or Osiers"}') then 'fillNonconiferousTreesAndCoppiceOrOsiers'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Nonconiferous Trees", Rock}') then 'fillNonconiferousTreesAndRock'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Nonconiferous Trees", "Rough Grassland"}') then 'fillNonconiferousTreesAndRoughGrassland'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Nonconiferous Trees", Scrub}') then 'fillNonconiferousTreesAndScrub'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Rough Grassland", Boulders}') then 'fillRoughGrasslandAndBoulders'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Rough Grassland", "Coniferous Trees"}') then 'fillRoughGrasslandAndConiferousTrees'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Rough Grassland", Heath}') then 'fillRoughGrasslandAndHeath'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Rough Grassland", "Marsh Reeds Or Saltmarsh"}') then 'fillRoughGrasslandAndMarsh'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Rough Grassland", "Nonconiferous Trees"}') then 'fillRoughGrasslandAndNonconiferousTrees'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Rough Grassland", Rock}') then 'fillRoughGrasslandAndRock'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Rough Grassland", Scree}') then 'fillRoughGrasslandAndScree'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Rough Grassland", Scrub}') then 'fillRoughGrasslandAndScrub'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{Scrub, Boulders}') then 'fillScrubAndBoulders'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{Scrub, "Marsh Reeds Or Saltmarsh"}') then 'fillScrubAndMarsh'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{Scrub, Rock}') then 'fillScrubAndRock'
				
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Boulders (Scattered)"}') then 'fillBouldersScattered'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Coniferous Trees (Scattered)"}') then 'fillConiferousTreesScattered'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Nonconiferous Trees (Scattered)"}') then 'fillNonconiferousTreesScattered'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Rock (Scattered)"}') then 'fillRockScattered'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{Boulders}') then 'fillBoulders'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Coniferous Trees"}') then 'fillConiferousTrees'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Coppice Or Osiers"}') then 'fillCoppiceOrOsiers'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{Heath}') then 'fillHeath'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Marsh Reed Or Saltmarsh"}') then 'fillMarsh'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Nonconiferous Trees"}') then 'fillNonconiferousTrees'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{Orchard}') then 'fillOrchard'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{Rock}') then 'fillRock'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{"Rough Grassland"}') then 'fillRoughGrassland'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{Scree}') then 'fillScree'
				WHEN (descriptivegroup ='{"Natural Environment"}' and descriptiveterm = '{Scrub}') then 'fillScrub'
				
				WHEN (descriptivegroup ='{Building}' and descriptiveterm = '{Archway}') then 'fillArchway'
				WHEN (descriptivegroup ='{Building}') then 'fillBuilding'
				WHEN (descriptivegroup ='{Glasshouse}' and make = 'Manmade') then 'fillGlasshouse'
				WHEN (descriptivegroup ='{"Historic Interest"}' and descriptiveterm = '{Slope}') then 'fillSlopeHistoric'
				WHEN (descriptivegroup ='{"Historic Interest"}' and descriptiveterm = '{Cliff}') then 'fillCliffHistoric'
				WHEN (descriptiveterm ='{Slope}' and make = 'Manmade') then 'fillSlope'
				WHEN (descriptiveterm ='{Cliff}' and make = 'Natural') then 'fillCliff'
				WHEN (descriptivegroup ='{"Inland Water"}') then 'fillInlandWater'
				WHEN (descriptivegroup ='{"Tidal Water"}' and descriptiveterm = '{Foreshore}') then 'fillForeshore'
				WHEN (descriptivegroup ='{"Tidal Water"}') then 'fillTidalWater'
				WHEN (descriptivegroup ='{Path, Rail, "Road Or Track"}' and make = 'Natural') then 'fillNaturalSurface'
				WHEN (descriptivegroup ='{Path, Rail, "Road Or Track"}' and make = 'Unknown') then 'fillUnknownSurface'
				WHEN (descriptivegroup ='{Path}') then 'fillPath'
				WHEN (descriptivegroup ='{Path, Rail, "Road Or Track"}' and descriptiveterm = '{"Traffic Calming"}') then 'fillTrafficCalming'
				WHEN (descriptivegroup ='{"Road Or Track"}') then 'fillRoadOrTrack'
				WHEN (descriptivegroup ='{Rail}') then 'fillRail'
				WHEN (descriptivegroup ='{Structure}' and descriptiveterm = '{"Upper Level Of Communication"}') then 'fillStructureULC'
				WHEN (descriptivegroup ='{Structure}' and descriptiveterm = '{"Overhead Construction"}') then 'fillStructureOverheadConstruction'
				WHEN (descriptivegroup ='{Structure}' and descriptiveterm = '{"Pylon"}') then 'fillStructurePylon'
				WHEN (descriptivegroup ='{Structure}') then 'fillStructure'
				WHEN (descriptivegroup ='{Unclassified}') then 'fillUnclassified'
				ELSE 'fillUnknown'			  
					  END)
		WHERE os_cat is null;

5.2.6 OS_Cat for TopographicLine table

ALTER TABLE osmm.topographicline ADD COLUMN os_cat VARCHAR;
		UPDATE osmm.topographicline
		SET os_cat = (CASE 
				WHEN (descriptivegroup ='{Building}' and descriptiveterm = '{Outline}' and make = 'Manmade') then 'lineBuildingOverhead'
				WHEN (descriptivegroup ='{"General Feature"}' and descriptiveterm = '{"Overhead Construction"}') then 'lineStructureOverhead'
				WHEN (descriptiveterm = '{"Tunnel Edge"}') then 'lineDefaultUnderground'
				WHEN (descriptivegroup ='{Building}' and descriptiveterm = '{Outline}' and physicalpresence = 'Obstructing' and make = 'Manmade') then 'lineBuilding'
				WHEN (descriptivegroup ='{Building}' and descriptiveterm = '{Division}' and physicalpresence = 'Obstructing' and make = 'Manmade') then 'lineBuildingDivision'
				WHEN (descriptivegroup ='{Building, "Inland Water"}') and physicalpresence = 'Edge / Limit' then 'lineBuilding'
				WHEN (descriptiveterm = '{"Mean High Water (Springs)"}') then 'lineWaterMHW'
				WHEN (descriptiveterm = '{"Mean Low Water (Springs)"}') then 'lineWaterMLW'
				WHEN (descriptivegroup ='{"Inland Water"}') and descriptiveterm = '{Culvert}' then 'lineCulvert'
				WHEN (descriptivegroup ='{"Inland Water"}') then 'lineWater'
				WHEN (descriptivegroup ='{"Narrow Gauge"}') then 'lineRailwayNarrowGauge'
				WHEN (descriptivegroup ='{"Standard Gauge Track"}') then 'lineRailwayStandardGauge'
				WHEN (descriptivegroup ='{Rail}' and descriptiveterm = '{Buffer}') then 'lineRailwayBuffer'
				WHEN (descriptivegroup ='{Landform}' and descriptiveterm = '{"Top Of Slope"}') then 'lineLandformTopOfSlope'
				WHEN (descriptivegroup ='{Landform}' and descriptiveterm = '{"Top Of Cliff"}') then 'lineLandformTopOfCliff'
				WHEN (descriptivegroup ='{Landform}' and descriptiveterm = '{"Bottom Of Slope"}') then 'lineLandformBottomOfSlope'
				WHEN (descriptivegroup ='{Landform}' and descriptiveterm = '{"Bottom Of Cliff"}') then 'lineLandformBottomOfCliff'
				WHEN (descriptivegroup ='{Landform}' and descriptiveterm = '{"Ridge Or Rock Line"}') then 'lineLandformRidgeRock'
				WHEN (descriptivegroup ='{Landform}' and make = 'Manmade') then 'lineLandformManmade'
				WHEN (descriptivegroup ='{Landform}' and make = 'Natural') then 'lineLandformNatural'
				WHEN (physicalpresence = 'Minor Detail') then 'lineMinorDetail'
				WHEN (physicalpresence = 'Step') then 'lineStep'
				WHEN (descriptivegroup ='{"General Surface"}' and physicalpresence = 'Obstructing') then 'lineDefault'
				WHEN (descriptivegroup ='{"General Surface"}' and descriptiveterm = '{Step}' and make = 'Manmade') then 'lineDefault'
				WHEN (descriptivegroup ='{"General Surface"}' and physicalpresence = 'Edge / Limit') then 'lineDefaultDashed' 
				WHEN (descriptivegroup ='{"General Surface"}' and physicalpresence = 'Edge / Limit' and make = 'Natural') then 'lineGeneralSurfaceDashed'  
				WHEN (descriptiveterm = '{"Course Of Heritage"}') then 'lineHeritageOfCourse'
				WHEN (descriptiveterm = '{"Polygon Closing Link"}') then 'linePolygonClosingLink'
				WHEN (descriptiveterm = '{"Inferred Property Closing Link"}') then 'lineInferredPolygonClosingLink'
				WHEN (descriptiveterm = '{"Unmade Path Alignment"}') then 'linePathAlignment'
				WHEN (descriptivegroup ='{"Road Or Track"}' and descriptiveterm = '{Public}') then 'lineRoadPublic'
				WHEN (descriptivegroup ='{"Road Or Track"}' and descriptiveterm = '{"Traffic Calming"}') then 'lineTrafficCalming'
				WHEN (descriptivegroup ='{Structure}' and descriptiveterm = '{Pylon}') then 'linePylon'
				WHEN (descriptivegroup ='{Unclassified}') then 'lineUnclassified'
				ELSE 'lineUnknown'			  
					  END)
		WHERE os_cat is null;

5.2.7 OS_Cat for TopographicPoint table

ALTER TABLE osmm.topographicpoint ADD COLUMN os_cat VARCHAR;
    UPDATE osmm.topographicpoint
    SET os_cat = (CASE 
        WHEN (descriptiveterm ='{"Positioned Boulder"}') then 'pointBoulderPositioned'
        WHEN (descriptiveterm ='{"Positioned Coniferous Tree"}') then 'pointConiferousTreePositioned'
        WHEN (descriptiveterm ='{"Positioned Nonconiferous Tree"}') then 'pointNonconiferousTreePositioned'
        WHEN (descriptiveterm ='{"Bench Mark"}') then 'pointBenchMark'
        WHEN (descriptivegroup ='{"Historic Interest"}' and descriptiveterm ='{Structure}') then 'pointStructure'
        WHEN (descriptiveterm ='{Heritage}') then 'pointHistoric'
        WHEN (descriptiveterm ='{Culvert}') then 'pointCulvert'
        WHEN (descriptivegroup ='{"Inland Water"}' and make ='Manmade') then 'pointWaterStructure'
        WHEN (descriptivegroup ='{Landform}' and descriptiveterm ='{"Disused Feature"}') then 'pointLandformDisused'
        WHEN (descriptivegroup ='{Landform}') then 'pointLandform'
        WHEN (descriptiveterm ='{"Boundary Post Or Stone"}') then 'pointBoundaryPost'
        WHEN (descriptivegroup ='{Rail}' and descriptiveterm ='{Structure}') then 'pointRailwayStructure'
        WHEN (descriptivegroup ='{Roadside}' and descriptiveterm ='{Structure}') then 'pointRoadStructure'
        WHEN (descriptiveterm ='{"Triangulation Point Or Pillar"}') then 'pointTriangulationPillar'
        WHEN (descriptivegroup ='{Structure}' and descriptiveterm ='{"Overhead Construction"}') then 'pointStructure'
        WHEN (descriptivegroup ='{Structure}' and descriptiveterm ='{Structure}') then 'pointStructure'
        WHEN (descriptivegroup ='{Structure}') then 'pointStructure'
        WHEN (descriptiveterm ='{Spot Height}') then 'pointSpotHeight'
        WHEN (descriptivegroup ='{"Tidal Water"}') then 'pointTidalWater'
        
        ELSE 'pointStructure'       
            END)
    WHERE os_cat is null;

Having finished running all the post processing tasks the data can now be used with the various default Desktop GIS applications and Geographic Servers that exist in the market place.

Using Loading to create FCODE column during Loading

Turns out that you can create the styling column during the loading process, it just required a few changes to the prep_os.gml file.

So we need a new create table sql

-- Drops any existing OSMM related tables and creates fresh tables ready to receive data

DROP TABLE IF EXISTS "osmm"."boundaryline" CASCADE;
DELETE FROM geometry_columns WHERE f_table_name = 'boundaryline' AND f_table_schema = 'osmm';

CREATE TABLE "osmm"."boundaryline" ( OGC_FID SERIAL, CONSTRAINT "boundaryline_pk" PRIMARY KEY (OGC_FID) );
SELECT AddGeometryColumn('osmm','boundaryline','wkb_geometry',27700,'GEOMETRY',2);
ALTER TABLE "osmm"."boundaryline" ADD COLUMN "fid" varchar;
ALTER TABLE "osmm"."boundaryline" ADD COLUMN "featurecode" INTEGER;
ALTER TABLE "osmm"."boundaryline" ADD COLUMN "version" INTEGER;
ALTER TABLE "osmm"."boundaryline" ADD COLUMN "versiondate" varchar;
ALTER TABLE "osmm"."boundaryline" ADD COLUMN "theme" varchar[];
ALTER TABLE "osmm"."boundaryline" ADD COLUMN "accuracyofposition" varchar;
ALTER TABLE "osmm"."boundaryline" ADD COLUMN "changedate" varchar[];
ALTER TABLE "osmm"."boundaryline" ADD COLUMN "reasonforchange" varchar[];
ALTER TABLE "osmm"."boundaryline" ADD COLUMN "descriptivegroup" varchar[];
ALTER TABLE "osmm"."boundaryline" ADD COLUMN "descriptiveterm" varchar[];
ALTER TABLE "osmm"."boundaryline" ADD COLUMN "make" varchar;
ALTER TABLE "osmm"."boundaryline" ADD COLUMN "physicallevel" INTEGER;
ALTER TABLE "osmm"."boundaryline" ADD COLUMN "physicalpresence" varchar;
ALTER TABLE "osmm"."boundaryline" ADD COLUMN "filename" varchar;
ALTER TABLE "osmm"."boundaryline" ADD COLUMN "themes" varchar;
ALTER TABLE "osmm"."boundaryline" ADD COLUMN "descriptivegroups" varchar;
ALTER TABLE "osmm"."boundaryline" ADD COLUMN "descriptiveterms" varchar;

DROP TABLE IF EXISTS "osmm"."cartographicsymbol" CASCADE;
DELETE FROM geometry_columns WHERE f_table_name = 'cartographicsymbol' AND f_table_schema = 'osmm';

CREATE TABLE "osmm"."cartographicsymbol" ( OGC_FID SERIAL, CONSTRAINT "cartographicsymbol_pk" PRIMARY KEY (OGC_FID) );
SELECT AddGeometryColumn('osmm','cartographicsymbol','wkb_geometry',27700,'POINT',2);
ALTER TABLE "osmm"."cartographicsymbol" ADD COLUMN "fid" varchar;
ALTER TABLE "osmm"."cartographicsymbol" ADD COLUMN "featurecode" INTEGER;
ALTER TABLE "osmm"."cartographicsymbol" ADD COLUMN "version" INTEGER;
ALTER TABLE "osmm"."cartographicsymbol" ADD COLUMN "versiondate" varchar;
ALTER TABLE "osmm"."cartographicsymbol" ADD COLUMN "theme" varchar[];
ALTER TABLE "osmm"."cartographicsymbol" ADD COLUMN "changedate" varchar[];
ALTER TABLE "osmm"."cartographicsymbol" ADD COLUMN "reasonforchange" varchar[];
ALTER TABLE "osmm"."cartographicsymbol" ADD COLUMN "descriptivegroup" varchar[];
ALTER TABLE "osmm"."cartographicsymbol" ADD COLUMN "descriptiveterm" varchar[];
ALTER TABLE "osmm"."cartographicsymbol" ADD COLUMN "orientation" INTEGER;
ALTER TABLE "osmm"."cartographicsymbol" ADD COLUMN "physicallevel" INTEGER;
ALTER TABLE "osmm"."cartographicsymbol" ADD COLUMN "physicalpresence" varchar;
ALTER TABLE "osmm"."cartographicsymbol" ADD COLUMN "referencetofeature" VARCHAR;
ALTER TABLE "osmm"."cartographicsymbol" ADD COLUMN "filename" varchar;
ALTER TABLE "osmm"."cartographicsymbol" ADD COLUMN "themes" varchar;
ALTER TABLE "osmm"."cartographicsymbol" ADD COLUMN "descriptivegroups" varchar;
ALTER TABLE "osmm"."cartographicsymbol" ADD COLUMN "descriptiveterms" varchar;
ALTER TABLE "osmm"."cartographicsymbol" ADD COLUMN "orientdeg" varchar;

DROP TABLE IF EXISTS "osmm"."cartographictext" CASCADE;
DELETE FROM geometry_columns WHERE f_table_name = 'cartographictext' AND f_table_schema = 'osmm';

CREATE TABLE "osmm"."cartographictext" ( OGC_FID SERIAL, CONSTRAINT "cartographictext_pk" PRIMARY KEY (OGC_FID) );
SELECT AddGeometryColumn('osmm','cartographictext','wkb_geometry',27700,'POINT',2);
ALTER TABLE "osmm"."cartographictext" ADD COLUMN "fid" varchar;
ALTER TABLE "osmm"."cartographictext" ADD COLUMN "featurecode" INTEGER;
ALTER TABLE "osmm"."cartographictext" ADD COLUMN "version" INTEGER;
ALTER TABLE "osmm"."cartographictext" ADD COLUMN "versiondate" varchar;
ALTER TABLE "osmm"."cartographictext" ADD COLUMN "theme" varchar[];
ALTER TABLE "osmm"."cartographictext" ADD COLUMN "changedate" varchar[];
ALTER TABLE "osmm"."cartographictext" ADD COLUMN "reasonforchange" varchar[];
ALTER TABLE "osmm"."cartographictext" ADD COLUMN "descriptivegroup" varchar[];
ALTER TABLE "osmm"."cartographictext" ADD COLUMN "descriptiveterm" varchar[];
ALTER TABLE "osmm"."cartographictext" ADD COLUMN "make" varchar;
ALTER TABLE "osmm"."cartographictext" ADD COLUMN "physicallevel" INTEGER;
ALTER TABLE "osmm"."cartographictext" ADD COLUMN "physicalpresence" varchar;
ALTER TABLE "osmm"."cartographictext" ADD COLUMN "anchorposition" INTEGER;
ALTER TABLE "osmm"."cartographictext" ADD COLUMN "font" INTEGER;
ALTER TABLE "osmm"."cartographictext" ADD COLUMN "height" FLOAT8;
ALTER TABLE "osmm"."cartographictext" ADD COLUMN "orientation" INTEGER;
ALTER TABLE "osmm"."cartographictext" ADD COLUMN "textstring" varchar;
ALTER TABLE "osmm"."cartographictext" ADD COLUMN "filename" varchar;
ALTER TABLE "osmm"."cartographictext" ADD COLUMN "themes" varchar;
ALTER TABLE "osmm"."cartographictext" ADD COLUMN "descriptivegroups" varchar;
ALTER TABLE "osmm"."cartographictext" ADD COLUMN "descriptiveterms" varchar;
ALTER TABLE "osmm"."cartographictext" ADD COLUMN "orientdeg" varchar;
ALTER TABLE "osmm"."cartographictext" ADD COLUMN "fcode" smallint;
ALTER TABLE "osmm"."cartographictext" ADD COLUMN "anchor" varchar;

DROP TABLE IF EXISTS "osmm"."topographicarea" CASCADE;
DELETE FROM geometry_columns WHERE f_table_name = 'topographicarea' AND f_table_schema = 'osmm';

CREATE TABLE "osmm"."topographicarea" ( OGC_FID SERIAL, CONSTRAINT "topographicarea_pk" PRIMARY KEY (OGC_FID) );
SELECT AddGeometryColumn('osmm','topographicarea','wkb_geometry',27700,'POLYGON',2);
ALTER TABLE "osmm"."topographicarea" ADD COLUMN "fid" varchar;
ALTER TABLE "osmm"."topographicarea" ADD COLUMN "featurecode" INTEGER;
ALTER TABLE "osmm"."topographicarea" ADD COLUMN "version" INTEGER;
ALTER TABLE "osmm"."topographicarea" ADD COLUMN "versiondate" varchar;
ALTER TABLE "osmm"."topographicarea" ADD COLUMN "theme" varchar[];
ALTER TABLE "osmm"."topographicarea" ADD COLUMN "calculatedareavalue" FLOAT8;
ALTER TABLE "osmm"."topographicarea" ADD COLUMN "changedate" varchar[];
ALTER TABLE "osmm"."topographicarea" ADD COLUMN "reasonforchange" varchar[];
ALTER TABLE "osmm"."topographicarea" ADD COLUMN "descriptivegroup" varchar[];
ALTER TABLE "osmm"."topographicarea" ADD COLUMN "descriptiveterm" varchar[];
ALTER TABLE "osmm"."topographicarea" ADD COLUMN "make" varchar;
ALTER TABLE "osmm"."topographicarea" ADD COLUMN "physicallevel" INTEGER;
ALTER TABLE "osmm"."topographicarea" ADD COLUMN "physicalpresence" varchar;
ALTER TABLE "osmm"."topographicarea" ADD COLUMN "filename" varchar;
ALTER TABLE "osmm"."topographicarea" ADD COLUMN "themes" varchar;
ALTER TABLE "osmm"."topographicarea" ADD COLUMN "descriptivegroups" varchar;
ALTER TABLE "osmm"."topographicarea" ADD COLUMN "descriptiveterms" varchar;
ALTER TABLE "osmm"."topographicarea" ADD COLUMN "fcode" smallint;

DROP TABLE IF EXISTS "osmm"."topographicline" CASCADE;
DELETE FROM geometry_columns WHERE f_table_name = 'topographicline' AND f_table_schema = 'osmm';

CREATE TABLE "osmm"."topographicline" ( OGC_FID SERIAL, CONSTRAINT "topographicline_pk" PRIMARY KEY (OGC_FID) );
SELECT AddGeometryColumn('osmm','topographicline','wkb_geometry',27700,'LINESTRING',2);
ALTER TABLE "osmm"."topographicline" ADD COLUMN "fid" varchar;
ALTER TABLE "osmm"."topographicline" ADD COLUMN "featurecode" INTEGER;
ALTER TABLE "osmm"."topographicline" ADD COLUMN "version" INTEGER;
ALTER TABLE "osmm"."topographicline" ADD COLUMN "versiondate" varchar;
ALTER TABLE "osmm"."topographicline" ADD COLUMN "theme" varchar[];
ALTER TABLE "osmm"."topographicline" ADD COLUMN "accuracyofposition" varchar;
ALTER TABLE "osmm"."topographicline" ADD COLUMN "changedate" varchar[];
ALTER TABLE "osmm"."topographicline" ADD COLUMN "reasonforchange" varchar[];
ALTER TABLE "osmm"."topographicline" ADD COLUMN "descriptivegroup" varchar[];
ALTER TABLE "osmm"."topographicline" ADD COLUMN "descriptiveterm" varchar[];
ALTER TABLE "osmm"."topographicline" ADD COLUMN "nonboundingline" varchar;
ALTER TABLE "osmm"."topographicline" ADD COLUMN "heightabovedatum" FLOAT8;
ALTER TABLE "osmm"."topographicline" ADD COLUMN "accuracyofheightabovedatum" varchar;
ALTER TABLE "osmm"."topographicline" ADD COLUMN "heightabovegroundlevel" FLOAT8;
ALTER TABLE "osmm"."topographicline" ADD COLUMN "accuracyofheightabovegroundlevel" varchar;
ALTER TABLE "osmm"."topographicline" ADD COLUMN "make" varchar;
ALTER TABLE "osmm"."topographicline" ADD COLUMN "physicallevel" INTEGER;
ALTER TABLE "osmm"."topographicline" ADD COLUMN "physicalpresence" varchar;
ALTER TABLE "osmm"."topographicline" ADD COLUMN "filename" varchar;
ALTER TABLE "osmm"."topographicline" ADD COLUMN "themes" varchar;
ALTER TABLE "osmm"."topographicline" ADD COLUMN "descriptivegroups" varchar;
ALTER TABLE "osmm"."topographicline" ADD COLUMN "descriptiveterms" varchar;
ALTER TABLE "osmm"."topographicline" ADD COLUMN "fcode" smallint;


DROP TABLE IF EXISTS "osmm"."topographicpoint" CASCADE;
DELETE FROM geometry_columns WHERE f_table_name = 'topographicpoint' AND f_table_schema = 'osmm';

CREATE TABLE "osmm"."topographicpoint" ( OGC_FID SERIAL, CONSTRAINT "topographicpoint_pk" PRIMARY KEY (OGC_FID) );
SELECT AddGeometryColumn('osmm','topographicpoint','wkb_geometry',27700,'POINT',2);
ALTER TABLE "osmm"."topographicpoint" ADD COLUMN "fid" varchar;
ALTER TABLE "osmm"."topographicpoint" ADD COLUMN "featurecode" INTEGER;
ALTER TABLE "osmm"."topographicpoint" ADD COLUMN "version" INTEGER;
ALTER TABLE "osmm"."topographicpoint" ADD COLUMN "versiondate" varchar;
ALTER TABLE "osmm"."topographicpoint" ADD COLUMN "theme" varchar[];
ALTER TABLE "osmm"."topographicpoint" ADD COLUMN "accuracyofposition" varchar;
ALTER TABLE "osmm"."topographicpoint" ADD COLUMN "changedate" varchar[];
ALTER TABLE "osmm"."topographicpoint" ADD COLUMN "reasonforchange" varchar[];
ALTER TABLE "osmm"."topographicpoint" ADD COLUMN "descriptivegroup" varchar[];
ALTER TABLE "osmm"."topographicpoint" ADD COLUMN "descriptiveterm" varchar[];
ALTER TABLE "osmm"."topographicpoint" ADD COLUMN "heightabovedatum" FLOAT8;
ALTER TABLE "osmm"."topographicpoint" ADD COLUMN "accuracyofheightabovedatum" varchar;
ALTER TABLE "osmm"."topographicpoint" ADD COLUMN "make" varchar;
ALTER TABLE "osmm"."topographicpoint" ADD COLUMN "physicallevel" INTEGER;
ALTER TABLE "osmm"."topographicpoint" ADD COLUMN "physicalpresence" varchar;
ALTER TABLE "osmm"."topographicpoint" ADD COLUMN "referencetofeature" VARCHAR;
ALTER TABLE "osmm"."topographicpoint" ADD COLUMN "filename" varchar;
ALTER TABLE "osmm"."topographicpoint" ADD COLUMN "themes" varchar;
ALTER TABLE "osmm"."topographicpoint" ADD COLUMN "descriptivegroups" varchar;
ALTER TABLE "osmm"."topographicpoint" ADD COLUMN "descriptiveterms" varchar;

We then also need a new GFS file that will read the new fcode field we are adding

<GMLFeatureClassList>
  <GMLFeatureClass>
    <Name>TopographicArea</Name>
    <ElementPath>TopographicArea</ElementPath>
    <GeometryType>3</GeometryType>
    <SRSName>EPSG:27700</SRSName>
    <PropertyDefn>
      <Name>fid</Name>
      <ElementPath>fid</ElementPath>
      <Type>String</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>featureCode</Name>
      <ElementPath>featureCode</ElementPath>
      <Type>Integer</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>version</Name>
      <ElementPath>version</ElementPath>
      <Type>Integer</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>versionDate</Name>
      <ElementPath>versionDate</ElementPath>
      <Type>String</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>theme</Name>
      <ElementPath>theme</ElementPath>
      <Type>StringList</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>calculatedAreaValue</Name>
      <ElementPath>calculatedAreaValue</ElementPath>
      <Type>Real</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>changeDate</Name>
      <ElementPath>changeHistory|changeDate</ElementPath>
      <Type>StringList</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>reasonForChange</Name>
      <ElementPath>changeHistory|reasonForChange</ElementPath>
      <Type>StringList</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>descriptiveGroup</Name>
      <ElementPath>descriptiveGroup</ElementPath>
      <Type>StringList</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>descriptiveTerm</Name>
      <ElementPath>descriptiveTerm</ElementPath>
      <Type>StringList</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>make</Name>
      <ElementPath>make</ElementPath>
      <Type>String</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>physicalLevel</Name>
      <ElementPath>physicalLevel</ElementPath>
      <Type>Integer</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>physicalPresence</Name>
      <ElementPath>physicalPresence</ElementPath>
      <Type>String</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>themes</Name>
      <ElementPath>themes</ElementPath>
      <Type>String</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>descriptiveGroups</Name>
      <ElementPath>descriptiveGroups</ElementPath>
      <Type>String</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>descriptiveTerms</Name>
      <ElementPath>descriptiveTerms</ElementPath>
      <Type>String</Type>
    </PropertyDefn>
	<PropertyDefn>
      <Name>fcode</Name>
      <ElementPath>fcode</ElementPath>
      <Type>Integer</Type>
    </PropertyDefn> 
  </GMLFeatureClass>
  <GMLFeatureClass>
    <Name>CartographicText</Name>
    <ElementPath>CartographicText</ElementPath>
    <GeometryType>1</GeometryType>
    <SRSName>EPSG:27700</SRSName>
    <PropertyDefn>
      <Name>fid</Name>
      <ElementPath>fid</ElementPath>
      <Type>String</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>featureCode</Name>
      <ElementPath>featureCode</ElementPath>
      <Type>Integer</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>version</Name>
      <ElementPath>version</ElementPath>
      <Type>Integer</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>versionDate</Name>
      <ElementPath>versionDate</ElementPath>
      <Type>String</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>theme</Name>
      <ElementPath>theme</ElementPath>
      <Type>StringList</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>changeDate</Name>
      <ElementPath>changeHistory|changeDate</ElementPath>
      <Type>StringList</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>reasonForChange</Name>
      <ElementPath>changeHistory|reasonForChange</ElementPath>
      <Type>StringList</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>descriptiveGroup</Name>
      <ElementPath>descriptiveGroup</ElementPath>
      <Type>StringList</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>descriptiveTerm</Name>
      <ElementPath>descriptiveTerm</ElementPath>
      <Type>StringList</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>make</Name>
      <ElementPath>make</ElementPath>
      <Type>String</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>physicalLevel</Name>
      <ElementPath>physicalLevel</ElementPath>
      <Type>Integer</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>physicalPresence</Name>
      <ElementPath>physicalPresence</ElementPath>
      <Type>String</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>anchorPosition</Name>
      <ElementPath>textRendering|anchorPosition</ElementPath>
      <Type>Integer</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>font</Name>
      <ElementPath>textRendering|font</ElementPath>
      <Type>Integer</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>height</Name>
      <ElementPath>textRendering|height</ElementPath>
      <Type>Real</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>orientation</Name>
      <ElementPath>textRendering|orientation</ElementPath>
      <Type>Integer</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>orientDeg</Name>
      <ElementPath>textRendering|orientDeg</ElementPath>
      <Type>Real</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>textString</Name>
      <ElementPath>textString</ElementPath>
      <Type>String</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>themes</Name>
      <ElementPath>themes</ElementPath>
      <Type>String</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>descriptiveGroups</Name>
      <ElementPath>descriptiveGroups</ElementPath>
      <Type>String</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>descriptiveTerms</Name>
      <ElementPath>descriptiveTerms</ElementPath>
      <Type>String</Type>
    </PropertyDefn>
	<PropertyDefn>
      <Name>fcode</Name>
      <ElementPath>fcode</ElementPath>
      <Type>Integer</Type>
    </PropertyDefn>
	<PropertyDefn>
      <Name>anchor</Name>
      <ElementPath>anchor</ElementPath>
      <Type>String</Type>
    </PropertyDefn>
  </GMLFeatureClass>
  <GMLFeatureClass>
    <Name>CartographicSymbol</Name>
    <ElementPath>CartographicSymbol</ElementPath>
    <GeometryType>1</GeometryType>
    <SRSName>EPSG:27700</SRSName>
    <PropertyDefn>
      <Name>fid</Name>
      <ElementPath>fid</ElementPath>
      <Type>String</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>featureCode</Name>
      <ElementPath>featureCode</ElementPath>
      <Type>Integer</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>version</Name>
      <ElementPath>version</ElementPath>
      <Type>Integer</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>versionDate</Name>
      <ElementPath>versionDate</ElementPath>
      <Type>String</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>theme</Name>
      <ElementPath>theme</ElementPath>
      <Type>StringList</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>changeDate</Name>
      <ElementPath>changeHistory|changeDate</ElementPath>
      <Type>StringList</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>reasonForChange</Name>
      <ElementPath>changeHistory|reasonForChange</ElementPath>
      <Type>StringList</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>descriptiveGroup</Name>
      <ElementPath>descriptiveGroup</ElementPath>
      <Type>StringList</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>descriptiveTerm</Name>
      <ElementPath>descriptiveTerm</ElementPath>
      <Type>StringList</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>orientation</Name>
      <ElementPath>orientation</ElementPath>
      <Type>Integer</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>orientDeg</Name>
      <ElementPath>textRendering|orientDeg</ElementPath>
      <Type>Real</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>physicalLevel</Name>
      <ElementPath>physicalLevel</ElementPath>
      <Type>Integer</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>physicalPresence</Name>
      <ElementPath>physicalPresence</ElementPath>
      <Type>String</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>referenceToFeature</Name>
      <ElementPath>referenceToFeature</ElementPath>
      <Type>Untyped</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>themes</Name>
      <ElementPath>themes</ElementPath>
      <Type>String</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>descriptiveGroups</Name>
      <ElementPath>descriptiveGroups</ElementPath>
      <Type>String</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>descriptiveTerms</Name>
      <ElementPath>descriptiveTerms</ElementPath>
      <Type>String</Type>
    </PropertyDefn>
  </GMLFeatureClass>
  <GMLFeatureClass>
    <Name>BoundaryLine</Name>
    <ElementPath>BoundaryLine</ElementPath>
    <SRSName>EPSG:27700</SRSName>
    <PropertyDefn>
      <Name>fid</Name>
      <ElementPath>fid</ElementPath>
      <Type>String</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>featureCode</Name>
      <ElementPath>featureCode</ElementPath>
      <Type>Integer</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>version</Name>
      <ElementPath>version</ElementPath>
      <Type>Integer</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>versionDate</Name>
      <ElementPath>versionDate</ElementPath>
      <Type>String</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>theme</Name>
      <ElementPath>theme</ElementPath>
      <Type>StringList</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>accuracyOfPosition</Name>
      <ElementPath>accuracyOfPosition</ElementPath>
      <Type>String</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>changeDate</Name>
      <ElementPath>changeHistory|changeDate</ElementPath>
      <Type>StringList</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>reasonForChange</Name>
      <ElementPath>changeHistory|reasonForChange</ElementPath>
      <Type>StringList</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>descriptiveGroup</Name>
      <ElementPath>descriptiveGroup</ElementPath>
      <Type>StringList</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>descriptiveTerm</Name>
      <ElementPath>descriptiveTerm</ElementPath>
      <Type>StringList</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>make</Name>
      <ElementPath>make</ElementPath>
      <Type>String</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>physicalLevel</Name>
      <ElementPath>physicalLevel</ElementPath>
      <Type>Integer</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>physicalPresence</Name>
      <ElementPath>physicalPresence</ElementPath>
      <Type>String</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>themes</Name>
      <ElementPath>themes</ElementPath>
      <Type>String</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>descriptiveGroups</Name>
      <ElementPath>descriptiveGroups</ElementPath>
      <Type>String</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>descriptiveTerms</Name>
      <ElementPath>descriptiveTerms</ElementPath>
      <Type>String</Type>
    </PropertyDefn>
  </GMLFeatureClass>
  <GMLFeatureClass>
    <Name>TopographicPoint</Name>
    <ElementPath>TopographicPoint</ElementPath>
    <GeometryType>1</GeometryType>
    <SRSName>EPSG:27700</SRSName>
    <PropertyDefn>
      <Name>fid</Name>
      <ElementPath>fid</ElementPath>
      <Type>String</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>featureCode</Name>
      <ElementPath>featureCode</ElementPath>
      <Type>Integer</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>version</Name>
      <ElementPath>version</ElementPath>
      <Type>Integer</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>versionDate</Name>
      <ElementPath>versionDate</ElementPath>
      <Type>String</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>theme</Name>
      <ElementPath>theme</ElementPath>
      <Type>StringList</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>accuracyOfPosition</Name>
      <ElementPath>accuracyOfPosition</ElementPath>
      <Type>String</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>changeDate</Name>
      <ElementPath>changeHistory|changeDate</ElementPath>
      <Type>StringList</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>reasonForChange</Name>
      <ElementPath>changeHistory|reasonForChange</ElementPath>
      <Type>StringList</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>descriptiveGroup</Name>
      <ElementPath>descriptiveGroup</ElementPath>
      <Type>StringList</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>descriptiveTerm</Name>
      <ElementPath>descriptiveTerm</ElementPath>
      <Type>StringList</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>heightAboveDatum</Name>
      <ElementPath>heightAboveDatum|heightAboveDatum</ElementPath>
      <Type>Real</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>accuracyOfHeightAboveDatum</Name>
      <ElementPath>heightAboveDatum|accuracyOfHeightAboveDatum</ElementPath>
      <Type>String</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>make</Name>
      <ElementPath>make</ElementPath>
      <Type>String</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>physicalLevel</Name>
      <ElementPath>physicalLevel</ElementPath>
      <Type>Integer</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>physicalPresence</Name>
      <ElementPath>physicalPresence</ElementPath>
      <Type>String</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>referenceToFeature</Name>
      <ElementPath>referenceToFeature</ElementPath>
      <Type>Untyped</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>themes</Name>
      <ElementPath>themes</ElementPath>
      <Type>String</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>descriptiveGroups</Name>
      <ElementPath>descriptiveGroups</ElementPath>
      <Type>String</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>descriptiveTerms</Name>
      <ElementPath>descriptiveTerms</ElementPath>
      <Type>String</Type>
    </PropertyDefn>
  </GMLFeatureClass>
  <GMLFeatureClass>
    <Name>TopographicLine</Name>
    <ElementPath>TopographicLine</ElementPath>
    <GeometryType>2</GeometryType>
    <SRSName>EPSG:27700</SRSName>
    <PropertyDefn>
      <Name>fid</Name>
      <ElementPath>fid</ElementPath>
      <Type>String</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>featureCode</Name>
      <ElementPath>featureCode</ElementPath>
      <Type>Integer</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>version</Name>
      <ElementPath>version</ElementPath>
      <Type>Integer</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>versionDate</Name>
      <ElementPath>versionDate</ElementPath>
      <Type>String</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>theme</Name>
      <ElementPath>theme</ElementPath>
      <Type>StringList</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>accuracyOfPosition</Name>
      <ElementPath>accuracyOfPosition</ElementPath>
      <Type>String</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>changeDate</Name>
      <ElementPath>changeHistory|changeDate</ElementPath>
      <Type>StringList</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>reasonForChange</Name>
      <ElementPath>changeHistory|reasonForChange</ElementPath>
      <Type>StringList</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>descriptiveGroup</Name>
      <ElementPath>descriptiveGroup</ElementPath>
      <Type>StringList</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>descriptiveTerm</Name>
      <ElementPath>descriptiveTerm</ElementPath>
      <Type>StringList</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>nonBoundingLine</Name>
      <ElementPath>nonBoundingLine</ElementPath>
      <Type>String</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>heightAboveDatum</Name>
      <ElementPath>heightAboveDatum|heightAboveDatum</ElementPath>
      <Type>Real</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>accuracyOfHeightAboveDatum</Name>
      <ElementPath>heightAboveDatum|accuracyOfHeightAboveDatum</ElementPath>
      <Type>String</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>heightAboveGroundLevel</Name>
      <ElementPath>heightAboveGroundLevel|heightAboveGroundLevel</ElementPath>
      <Type>Real</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>accuracyOfHeightAboveGroundLevel</Name>
      <ElementPath>heightAboveGroundLevel|accuracyOfHeightAboveGroundLevel</ElementPath>
      <Type>String</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>make</Name>
      <ElementPath>make</ElementPath>
      <Type>String</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>physicalLevel</Name>
      <ElementPath>physicalLevel</ElementPath>
      <Type>Integer</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>physicalPresence</Name>
      <ElementPath>physicalPresence</ElementPath>
      <Type>String</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>themes</Name>
      <ElementPath>themes</ElementPath>
      <Type>String</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>descriptiveGroups</Name>
      <ElementPath>descriptiveGroups</ElementPath>
      <Type>String</Type>
    </PropertyDefn>
    <PropertyDefn>
      <Name>descriptiveTerms</Name>
      <ElementPath>descriptiveTerms</ElementPath>
      <Type>String</Type>
    </PropertyDefn>
	<PropertyDefn>
      <Name>fcode</Name>
      <ElementPath>fcode</ElementPath>
      <Type>Integer</Type>
    </PropertyDefn>
  </GMLFeatureClass>
</GMLFeatureClassList>

Then need to add the following to the prep_os.gml file

def _add_fcode(self, feat_elm):

        if feat_elm.tag == 'TopographicArea':
            fcode_elm = etree.SubElement(feat_elm, "fcode")
            descgroup = feat_elm.xpath('//descriptiveGroup/text()')
            descterm = feat_elm.xpath('//descriptiveTerm/text()')
            make = feat_elm.xpath('//make/text()')

            if "Building" in descgroup and "Archway" in descterm:
                fcode_elm.text = '1'
            elif "Building" in descgroup:
                fcode_elm.text = '2'
            elif "Step" in descterm:
                fcode_elm.text = '3'
            elif "Glasshouse" in descgroup:
                fcode_elm.text = '4'
            elif "Historic Interest" in descgroup:
                fcode_elm.text = '5'
            elif "Inland Water" in descgroup:
                fcode_elm.text = '6'
            elif "Cliff" in descterm:
                fcode_elm.text = '7'
            elif "Slope" in descterm:
                fcode_elm.text = '8'
            elif "Landform" in descgroup and "Manmade" in descterm:
                fcode_elm.text = '9'
            elif "Landform" in descgroup and "Natural" in descterm:
                fcode_elm.text = '10'
            elif "Nonconiferous Trees" in descterm or "Nonconiferous Trees (Scattered)" in descterm:
                fcode_elm.text = '11'
            elif "Coniferous Trees" in descterm or "Coniferous Trees (Scattered)" in descterm:
                fcode_elm.text = '12'
            elif "Orchard" in descterm:
                fcode_elm.text = '13'
            elif "Coppice Or Osiers" in descterm:
                fcode_elm.text = '14'
            elif "Scrub" in descterm:
                fcode_elm.text = '15'
            elif "Boulders" in descterm or "Boulders (Scattered)" in descterm:
                fcode_elm.text = '16'
            elif "Rock" in descterm or "Rock (Scattered)" in descterm:
                fcode_elm.text = '17'
            elif "Scree" in descterm:
                fcode_elm.text = '18'
            elif "Rough Grassland" in descterm:
                fcode_elm.text = '19'
            elif "Heath" in descterm:
                fcode_elm.text = '20'
            elif "Marsh Reeds Or Saltmarsh" in descterm:
                fcode_elm.text = '21'
            elif "Path" in descgroup:
                fcode_elm.text = '22'
            elif "Rail" in descgroup and "Natural" in make:
                fcode_elm.text = '23'
            elif "Rail" in descgroup and "Unknown" in make:
                fcode_elm.text = '24'
            elif "Rail" in descgroup and "Manmade" in make:
                fcode_elm.text = '25'
            elif "Roadside" in descgroup and "Natural" in make:
                fcode_elm.text = '26'
            elif "Roadside" in descgroup:
                fcode_elm.text = '27'
            elif "Traffic Calming" in descterm:
                fcode_elm.text = '28'
            elif "Road Or Track" in descgroup:
                fcode_elm.text = '29'
            elif "Pylon" in descterm:
                fcode_elm.text = '30'
            elif "Overhead Construction" in descterm:
                fcode_elm.text = '31'
            elif "Structure" in descgroup:
                fcode_elm.text = '32'
            elif "Foreshore" in descterm:
                fcode_elm.text = '33'
            elif "Tidal Water" in descgroup:
                fcode_elm.text = '34'
            elif "Manmade" in make:
                fcode_elm.text = '35'
            elif "Natural" in make:
                fcode_elm.text = '36'
            elif "Multiple" in make:
                fcode_elm.text = '37'
            elif "Unknown" in descgroup:
                fcode_elm.text = '38'
            else:
                fcode_elm.text = '99'


        elif feat_elm.tag == 'TopographicLine':
            fcode_elm = etree.SubElement(feat_elm, "fcode")
            descgroup = feat_elm.xpath('//descriptiveGroup/text()')
            descterm = feat_elm.xpath('//descriptiveTerm/text()')
            make = feat_elm.xpath('//make/text()')
            physicalpresence = feat_elm.xpath('//physicalPresence/text()')

            if "Building" in descgroup and "Overhead" in physicalpresence:
                fcode_elm.text = '12'
            elif "Building" in descgroup and "Division" in physicalpresence:
                fcode_elm.text = '11'
            elif "Overhead Construction" in descterm:
                fcode_elm.text = '9'
            elif "Tunnel Edge" in descterm:
                fcode_elm.text = '7'
            elif "Building" in descgroup and "Obstructing" in physicalpresence:
                fcode_elm.text = '10'
            elif "Mean High Water (Springs)" in descterm:
                fcode_elm.text = '22'
            elif "Mean Low Water (Springs)" in descterm:
                fcode_elm.text = '23'
            elif "Culvert" in descterm:
                fcode_elm.text = '24'
            elif "Inland Water" in descgroup:
                fcode_elm.text = '21'
            elif "Narrow Gauge" in descterm:
                fcode_elm.text = '26'
            elif "Standard Gauge Track" in descterm:
                fcode_elm.text = '25'
            elif "Buffer" in descterm:
                fcode_elm.text = '27'
            elif "Top Of Slope" in descterm:
                fcode_elm.text = '18'
            elif "Top Of Cliff" in descterm:
                fcode_elm.text = '20'
            elif "Bottom Of Slope" in descterm:
                fcode_elm.text = '17'
            elif "Bottom Of Cliff" in descterm:
                fcode_elm.text = '19'
            elif "Pylon" in descterm:
                fcode_elm.text = '6'
            elif "Traffic Calming" in descterm:
                fcode_elm.text = '14'
            elif "Polygon Closing Link" in descterm:
                fcode_elm.text = '15'
            elif "Inferred Property Closing Link" in descterm:
                fcode_elm.text = '16'
            elif "Road Or Track" in descgroup:
                fcode_elm.text = '13'
            elif "Step" in descterm:
                fcode_elm.text = '4'
            elif "Path" in descgroup:
                fcode_elm.text = '5'
            elif "Landform" in descgroup and "Natural" in make:
                fcode_elm.text = '28'
            elif "Landform" in descgroup and "Manmade" in make:
                fcode_elm.text = '29'
            elif "Historic Interest" in descgroup:
                fcode_elm.text = '8'
            elif "General Feature" in descgroup and "Obstructing" in physicalpresence:
                fcode_elm.text = '1'
            elif "General Feature" in descgroup and "Edge / Limit" in physicalpresence:
                fcode_elm.text = '2'
            elif "General Surface" in descgroup and "Natural" in make:
                fcode_elm.text = '3'
            else:
                fcode_elm.text = '99'

        elif feat_elm.tag == 'CartographicText':
            fcode_elm = etree.SubElement(feat_elm, "fcode")
            anchor_elm = etree.SubElement(feat_elm, "anchor")
            
            featurecode = feat_elm.xpath('//featurecode/text()')
            make = feat_elm.xpath('//make/text()')
            anchorposition = feat_elm.xpath('//textRendering/anchorPosition/text()')

            if featurecode == 10026:
                fcode_elm.text = '1'
            elif featurecode == 10033:
                fcode_elm.text = '2'
            elif featurecode == 10043:
                fcode_elm.text = '3'
            elif featurecode == 10059 and "Natural" in make:
                fcode_elm.text = '4'
            elif featurecode == 10059 and "Manmade" in make:
                fcode_elm.text = '5'
            elif featurecode == 10074:
                fcode_elm.text = '6'
            elif featurecode == 10090:
                fcode_elm.text = '7'
            elif featurecode == 10102 and "Natural" in make:
                fcode_elm.text = '8'
            elif featurecode == 10102 and "Manmade" in make:
                fcode_elm.text = '9'
            elif featurecode == 10166:
                fcode_elm.text = '10'
            elif featurecode == 10169:
                fcode_elm.text = '11'
            elif featurecode == 10178:
                fcode_elm.text = '12'
            elif featurecode == 10184:
                fcode_elm.text = '13'
            elif featurecode == 10204:
                fcode_elm.text = '14'
            elif featurecode == 10205:
                fcode_elm.text = '15'
            elif featurecode == 10198:
                fcode_elm.text = '16'
            elif featurecode == 10133:
                fcode_elm.text = '17'
            else:
                fcode_elm.text = '99'


            if anchorposition == 0:
                anchor_elm.text = 'SW'
            elif anchorposition == 1:
                anchor_elm.text = 'W'
            elif anchorposition == 2:
                anchor_elm.text = 'NW'
            elif anchorposition == 3:
                anchor_elm.text = 'S'
            elif anchorposition == 5:
                anchor_elm.text = 'N'
            elif anchorposition == 6:
                anchor_elm.text = 'SE'
            elif anchorposition == 7:
                anchor_elm.text = 'E'
            elif anchorposition == 8:
                anchor_elm.text = 'NE'
            else:
                anchor_elm.text = ''

        return feat_elm

def _prepare_feat_elm(self, feat_elm):

        feat_elm = prep_osgml._prepare_feat_elm(self, feat_elm)
        feat_elm = self._add_lists_elms(feat_elm)
        feat_elm = self._add_fcode(feat_elm)

We can also use a bash script and use Parallel to automate the loading across multiple cores of the server.

So we need to install cygwin64 using the following tutorial

http://blogs.msdn.com/b/hpctrekker/archive/2013/03/30/preparing-and-uploading-datasets-for-hdinsight.aspx

During the install the packages we need are

make
ruby

One install use the above guide to configure and make parallel

Then using the cygwin64 terminal we can run the commands we need. Firstly cd into the folder where the code is.

time find c:/os/data/1000_load/ -type f -print0 | parallel -0 python loader.py osmmloader.config "src_dir={}"

Astun Loader

👍
Prerequisites

1. The Basics

2. Installing the software

2.1 Install Python

❗️
BEWARE

2.2 Install GDAL

2.3 How to install LXML

2.3.1 Install PIP

2.3.2 Install LXML

2.4 Get Loader

3. Setting up the database schema and tables

4. Altering the config files

5. Post Processing the Database

5.1 Creating Spatial indexes

5.2 Creating os_cat styling column

5.2.1 OS_Cat for BoundaryLine table

5.2.2 OS_Cat for CartographicSymbol table

5.2.3 OS_Cat for CartographicText table

5.2.4 Add geo_x and geo_y to CartographicText table

5.2.5 OS_Cat for TopographicArea table

5.2.6 OS_Cat for TopographicLine table

5.2.7 OS_Cat for TopographicPoint table

Using Loading to create FCODE column during Loading

👍Prerequisites

1. The Basics

2. Installing the software

2.1 Install Python

❗️BEWARE

2.2 Install GDAL

2.3 How to install LXML

2.3.1 Install PIP

2.3.2 Install LXML

2.4 Get Loader

3. Setting up the database schema and tables

4. Altering the config files

5. Post Processing the Database

5.1 Creating Spatial indexes

5.2 Creating os_cat styling column

5.2.1 OS_Cat for BoundaryLine table

5.2.2 OS_Cat for CartographicSymbol table

5.2.3 OS_Cat for CartographicText table

5.2.4 Add geo_x and geo_y to CartographicText table

5.2.5 OS_Cat for TopographicArea table

5.2.6 OS_Cat for TopographicLine table

5.2.7 OS_Cat for TopographicPoint table

Using Loading to create FCODE column during Loading

👍
Prerequisites

❗️
BEWARE