Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
PostGIS SQL command for creating the OS_Cat column for styling OS MaterMap layers to use with the released SLDs by Ordnance Survey
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;
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;
Update the text position
ALTER TABLE osmm.cartographictext ADD COLUMN "geo_x" FLOAT8;
ALTER TABLE osmm.cartographictext ADD COLUMN "geo_y" FLOAT8;
They will need to have the two new geo_x geo_y columns for the following command to work
update osmm.cartographictext set geo_x = 0, geo_y = 0 where anchorposition = 0;
update osmm.cartographictext set geo_x = 0, geo_y = 0.5 where anchorposition = 1;
update osmm.cartographictext set geo_x = 0, geo_y = 1 where anchorposition = 2;
update osmm.cartographictext set geo_x = 0.5, geo_y = 0 where anchorposition = 3;
update osmm.cartographictext set geo_x = 0.5, geo_y = 0.5 where anchorposition = 4;
update osmm.cartographictext set geo_x = 0.5, geo_y = 1 where anchorposition = 5;
update osmm.cartographictext set geo_x = 1, geo_y = 0 where anchorposition = 6;
update osmm.cartographictext set geo_x = 1, geo_y = 0.5 where anchorposition = 7;
update osmm.cartographictext set geo_x = 1, geo_y = 1 where anchorposition = 8;
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;
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;
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;
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;
@David-A-James

This comment has been minimized.

Copy link

commented Aug 31, 2014

Thank you for this; it helped me a lot.

It looks as if the OS have changed some of their conventions ... I'm looking at the OS MasterMap sample dataset in GML format (dated 2014-03-14). After loading the GML file into PostGIS, the surrounding braces ({,}) and double quotation marks now only exist in the topographicarea table.

@walkermatt

This comment has been minimized.

Copy link

commented Apr 29, 2015

@tjmgis is the order of descriptivegroup values significant when calculating os_cat? Current script assigns fillRoadOrTrack when the descriptivegroup is {"General Surface","Road Or Track"} but fillUnknown when descriptivegroup is {"Road Or Track","General Surface"}. Would the preference be for both to be assigned fillRoadOrTrack?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.