Created
August 8, 2013 13:00
-
-
Save tjmgis/6184358 to your computer and use it in GitHub Desktop.
PostGIS SQL command for creating the OS_Cat column for styling OS MaterMap layers to use with the released SLDs by Ordnance Survey
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
@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
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.