Ireland: Technical Development Claudio Piccinini and Mike Smith, - PowerPoint PPT Presentation

The Physical Landscape of Britain and Northern Ireland: Technical Development Claudio Piccinini and Mike Smith, School of Geography, Geology and the Environment, Kingston University Janet Hooke and Katherine Hesketh, Department of Geography, University of Liverpool http://www.landscapebritain.org.uk/ WEB MAP APPLICATION FRONT-END DATABASE

GEOMORPHOLOGY THEMATIC (e.g. the BRITICE project ) BROAD-BRUSH(e.g. National Character Areas) SOURCES SPATIALLY RESTRICTED (e.g. GeoEast) THIS PROJECT provide the interested professional, researcher and the general public with access to information, data and knowledge on the geomorphology of the British landscape journal articles government reports books PhD dissertations

1 DATABASE DATABASE DESIGN IMPLEMENTATION SCHEMA WHICH DATABASE?

1 DATABASE

1 DATABASE Environment/ Timescale or LEVEL1 Landform Processes Impact Material period Attribute within system Hazards Technique Management Aeolian Accretion agricultural impact Alluvium Annual antecedent Breach archaeology biodiversity Bog Advance climate change Bedrock Century baseline Debris flow DTM/ DEM buffer Coastal Denudation dam impact Boulders Decadal Change Drought Environmenal Magnetism buffer zone catchment Downland Deposition desertification Clay Event chaos Erosion geochemistry management environmental Escarpment Erosion impacts Colluvium Historical complex Flood geochronology/dating channelization Geographic Information climate change Estuarine Fire eutrophication Consolidated Holocene coupling Landslide system (GIS) adaptation Global Positioning System climate change Fells Glaciation human impacts Gravel Longer dynamic Mudflow (GPS) mitigation Groundwater Fluvial land - use impact Minerogenic Millenia equilibrium Pollution mapping conservation Hydraulics Glacial mining impact Organic Pleistocene Erodibility Rockfall modelling Conservation status Hydrology Hills Sea Level Rise Peat Quaternary feedback Sedimentation monitoring desalinization Karst Isostasy storm surges Sand Seasonal frequency Storm palynology Dredging Lacustrine Mass movement tourism impact Silt Tertiary Grain size Subsidence remote sensing Flood defence LEVEL2 Lowland Migration urbanisation Soil inheritance Surge stratigraphy hard engineering Unconsolidat marine Nutrient flux ed Instability Tsunami tracer preservation Moorland Pollution magnitude Protection Mountain Retreat Rate Quarrying Runoff Periglacial regime Reclamation Plateau Sea-level resilience restoration slope Sediment transport Resistance soft engineering return period/ recurrence Water resources Structural Sedimentation interval Upland Solutes store Urban Storage sustainability Wetland Storms thresholds Tides Water quality Uplift Water flow Waves Weathering - 9 level1 terms - 164 level2 terms - 10 level2 terms have level3 terms

1 DATABASE LEVEL2 Fluvial Coastal Mass movement slopes Aeolian Glacial Periglacial Structural Karst Lake Alluvial fan Bar Debris flow Cliff (slopes) Dune cirque/ corrie circles Fault cave Delta (lake) patterned Bank Beach Landslide debris cones crag and tail ground Fold dry valley bathymetry Bar berm (coastal) Mudflow Debris flow Drift polygons gorge(karst) lake solifluction Braiding breach Rockfall gullies drumlin lobes inselberg mere Buried channel Cliff(coastal) Pediment erratics stone pavement karren Catchment currents Pipes esker stripes limestone pavement Channel cusp (coastal) rills hummocks terrace pothole Cut-off Delta (coastal) scree kame sinkhole LEVEL3 Floodplain Dune kame terrace tor Gorge (fluvial) Estuary moraine roche Gully headland mountonée Levee Lagoon Meander Nearshore Palaeochann el notch River Offshore River terraces platform Valley Saltmarsh Waterfall Spit tombolo - 83 level3 terms

1 DATABASE SQLServer Oracle Spatial SQLLite MySQL SPATIAL DATABASES PostGreSQL/PostGIS IBM DB2

1 DATABASE MySQL Vs. PostgreSQL/PostGIS Upload spatial data to database tables Interoperability Visualize and edit spatial data stored in database tables store, manage and analyse spatial data Spatial functions full-text search indexes transactions and foreign keys General functions stored procedures in different languages statistical analyses Usability performance of spatial operations Administration Popularity

Mysql PostgreSQL/PostGIS INTEROPERABILITY Free GIS Data and ETL Loaders OGR2OGR, shp2mysql.pl shp2pgsql, OGR2OGR, osm2pgsql, GeoKettle, Spatial Data Integrator Commercial GIS Data Loaders FME FME, Manifold, ArcGIS Server ArcSDE Free Desktop Viewers and Editors GvSIG OpenJump, QuantumGIS, GvSig, uDig Commercial Desktop Viewers and Editors FME FME, Manifold, free SpatialKit extension for ArcGIS 9.3-10.x, CadCorp, Autocad FDO, MapInfo 10+ Output other than text and binary formats (WKT, WKB) KML, SVG, GML, GEOJSON, GeoHash useful for mapping applications SPATIAL FUNCTIONS Number of spatial functions Limited. Before MySQL 5.0.16, these features are available for more than 350 MyISAM tables only Spatial index R-Tree (only for MyISAM tables) R-Tree index implemented on top of GiST idex Testing spatial relationships between geometries Before MySQL 5.6.1 tests use the feature bounding rectangle X Spatial operators that produce geometries (e.g. buffer, X difference, intersection, union) Additional Metadata Views geometry_column table, geography_column view Change reference system to another on the fly X Edit geometries (add, remove, move points); transform X geometries( scale shift, rotate) Linear Referencing functions X Spatial Aggregates X Geodetic support using the geography data type (only WGS 84 long-lat and output in meters) 3D Support PostGIS2.x Raster support PostGIS2.x Network routing using PgRouting

GENERAL FUNCTIONS Statistical analysis and graphs connect to R using the PL/R language Create stored procedures using different languages X Full-text-search indexes only for MyISAM tables X Foreign keys and transactions only InnoDB tables X Query optimization It considers only the query and how it could be optimized it considers also the database structure and uses a genetic algorithm to find the most effective way of executing a query Triggers are activated by SQL statements only. However they are not can execute any user-defined function from any of its activated by cascading updates and deletes even when caused by a procedural languages SQL statement. USABILITY Limits to insert big geometries By default the ‘' max_allowed_packet' parameter is set to 16M for the mysql client program and 1mb for the server. To insert bigger geometries you will need to increase the parameter value. On shared servers MySQL it may not be possible to increase the value Performance using spatial functionalities Some tests shows it can be slower than PostGIS Storage engines 9 different storage engines, the most popular InnoDB and MyISAM. A single storage engine The MyISAM engine is often the only database engine offered by webhosting providers. Both support geospatial types but only MyISAM supports geospatial indexing (see the following 2 tables) You are not restricted to using the same storage engine for an entire schema and can specify the storage engine for any table. Asynchronous API for use by client applications X Popularity X Easy to administer X

1 DATABASE SPATIAL TABLES Location stores the bounding boxes Place UK gazetteer 1:50,000 about 260,000 point Features Natural reserves, National Trust properties, Sites of special scientific interest …… Proj4 library : OSGB36 to WGS84 set "GEOM"=ST_Transform(ST_GeometryFromText('SRID=4277;POINT('|| "LONG" ||' '|| "LAT" || ')'),4326);

2. Front-End To ease the data WHY? management *Administrator manage the database structure *Data manager Security Add/update/delete data using the front-end *User Query the data using the web map application

REFERENCES LIST INSERT/UPDATE REFERENCES INSERT/UPDATE LEVELS INSERT/UPDATE BOUNDING BOXES ABSTRACTS IMAGES