1 DATABASE WHY? - ELOGeo

obtainablerabbiΔιαχείριση Δεδομένων

31 Ιαν 2013 (πριν από 4 χρόνια και 5 μήνες)

169 εμφανίσεις

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
WEB MAP APPLICATION
FRONT-END
http://www.landscapebritain.org.uk/
GEOMORPHOLOGY
THEMATIC (e.g. the BRITICE project )
SPATIALLY RESTRICTED (e.g. GeoEast)
BROAD-BRUSH(e.g. National Character Areas)
SOURCES
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
PhD dissertations
books
government reports
DATABASE
1 DATABASE
DESIGN
IMPLEMENTATION
WHICH DATABASE?
SCHEMA
1 DATABASE
- 9 level1 terms
- 164 level2 terms
- 10 level2 terms have level3 terms
1 DATABASE
1 DATABASE
- 83 level3 terms
SQLServer
1 DATABASE
SPATIAL DATABASES
Oracle Spatial
PostGreSQL/PostGIS
SQLLite
IBM DB2
MySQL
Interoperability
MySQL Vs. PostgreSQL/PostGIS
Spatial functions
General functions
Usability
Upload spatial data to database tables
Visualize and edit spatial data stored in database tables
store, manage and analyse spatial data
full-text search indexes
transactions and foreign keys
stored procedures in different languages
statistical analyses
performance of spatial operations
Administration
Popularity
1 DATABASE
 
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)
useful for mapping applications
 
KML, SVG, GML, GEOJSON, GeoHash
SPATIAL FUNCTIONS
Number of spatial functions
Limited. Before MySQL 5.0.16, these features are available for
MyISAM tables only
more than 350
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,
difference, intersection, union)
 
X
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
geometries( scale shift, rotate)
 
X
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
activated by cascading updates and deletes even when caused by a
SQL statement.
can execute any user-defined function from any of its
procedural languages
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.
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.
A single storage engine
Asynchronous API for use by client applications
 
X
 
 
 
Popularity
X
 
Easy to administer
X
 
SPATIAL TABLES
Location
stores the bounding boxes
Place
UK gazetteer 1:50,000 about 260,000 point
set "GEOM"=
ST_Transform
(ST_GeometryFromText('SRID=4277;POINT('|| "LONG" ||' '|| "LAT" || ')'),
4326
);
Proj4 library : OSGB36 to WGS84
Features
Natural reserves, National Trust properties, Sites
of special scientific interest ……
1 DATABASE
WHY?
To ease the data
management
Security
*Administrator
manage the database structure
*Data manager
Add/update/delete data using
the front-end
*User
Query the data using the web map
application
2. Front-End
REFERENCES LIST
INSERT/UPDATE
REFERENCES
INSERT/UPDATE
ABSTRACTS
INSERT/UPDATE
LEVELS
BOUNDING BOXES
IMAGES
{
"type": "FeatureCollection",
"features": [
<!--(section name=sec loop=$rs)-->
{ "type": "Feature", "properties":
{"id":<!--(
$rs[sec].id|json_encode
)-->
<!--(foreach from=$rs[sec] key=prop item=val)-->
<!--(if $prop != 'geom' && $prop != 'id')-->
,<!--(
$prop|json_encode
)-->:<!--($val|json_encode)-->
<!--(/if)-->
<!--(/foreach)-->
},
"geometry": <!--(
$rs[sec].geom
)-->
}
<!--(if not $smarty.section.sec.last)-->,<!--(/if)-->
<!--(/section)-->
]
}
{

"type": "FeatureCollection",
"features": [
{ "type": "Feature",
"properties": {"
id
":"285178" ,"
NAME
":"The Combe" },

"
geometry
": {
"type":"Point",
"coordinates":[-2.381338277553394,51.210511371454508]}
}
]
}
TEMPLATE
GeoJson
if ($n>0){
for ($i = 1; $i < $n; $i++) {
$others .=" OR \"NAME\"='".$places[$i]."'";

}
}
$sql = "select \"
PLACE_ID\" as id
, \"
NAME
\",
ST_AsGeoJSON(\"GEOM\") as geom
from \"public\".\"PLACE\"
where \"NAME\"='".$first."'".$others;
$rsdata = $this->db->Execute($sql)->GetRows();
$rs = array();
$this->assign('rs', $rsdata);
$this->
display($data_template);
3. Web Mapping Application
3. Web Mapping Application
QUERY
FUTURE DEVELOPMENT
Mobile application
3D Visualization
http://www.openwebglobe.org
http://www.sencha.com/products/touch/
The Physical Landscape of Britain and Northern
Ireland: Technical Development
http://www.landscapebritain.org.uk/