GIS and Media fusion
"The explosive growth of the GeoWeb and geographic information has made GIS powerful media for the general public to communicate, but perhaps more importantly, GIS have also become media for constructive dialogs and interactions about social issues." - Sui & Goodchild
User Tools
geoinf14:postgis0
Differences
This shows you the differences between two versions of the page.
|
geoinf14:postgis0 [2014/12/01 14:03] oertz [Spatial database :: CartoDB] |
geoinf14:postgis0 [2018/05/16 10:05] |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | ====== Spatial database :: PostGIS ====== | ||
| - | Voyons comment utiliser l'extension PostGIS de Postgresql pour stocker et gérer de l'information géographique dans un SGBD. | ||
| - | * Installer PostGIS selon les instructions ici http://postgis.net/install. En résumé : | ||
| - | * pour Mac, préférer l'approche "tout-en-un" http://postgresapp.com | ||
| - | * pour Windows, installer d'abord Postgresql par [[http://www.enterprisedb.com/products-services-training/pgdownload|EnterpriseDB]], puis activer l'add-on PostGIS | ||
| - | * si aucune ne fonctionne, explorer les autres approches pour réaliser cette installation | ||
| - | * Compléter l'installation par la création d'une //geodatabase// : | ||
| - | * lancer le serveur Postgresql et s'y connecter avec un client type pgAdmin | ||
| - | * créer une database (ex. nommée //ogo//) | ||
| - | * dans le contexte de cette base, exécuter la commande SQL suivante | ||
| - | <code> | ||
| - | CREATE EXTENSION postgis; | ||
| - | </code> | ||
| - | |||
| - | Pour la suite, ne pas oublier l'indispensable [[http://postgis.refractions.net/documentation/manual-1.5/|documentation]] avec son chapitre [[http://postgis.refractions.net/documentation/manual-1.5/reference.html|PostGIS Reference]] | ||
| - | |||
| - | ===== Une première "geotable" ===== | ||
| - | * Créer une //géotable// | ||
| - | <code> | ||
| - | CREATE TABLE my4capitals (id serial PRIMARY KEY, name text); | ||
| - | SELECT AddGeometryColumn( 'my4capitals', 'the_geom', 4326, 'POINT', 2 ); | ||
| - | </code> | ||
| - | * Alimenter la table (cf. http://fr.wikipedia.org/wiki/Well-known_text) | ||
| - | <code> | ||
| - | INSERT INTO my4capitals VALUES ( nextval('my4capitals_id_seq'), 'Paris', ST_GeometryFromText( 'POINT(2.333 48.867)', 4326)); | ||
| - | INSERT INTO my4capitals VALUES ( nextval('my4capitals_id_seq'), 'Bern', ST_GeometryFromText( 'POINT(7.433 46.95)', 4326)); | ||
| - | INSERT INTO my4capitals VALUES ( nextval('my4capitals_id_seq'), 'Rome', ST_GeometryFromText( 'POINT(12.5 41.883)', 4326)); | ||
| - | INSERT INTO my4capitals VALUES ( nextval('my4capitals_id_seq'), 'Madrid', ST_GeometryFromText( 'POINT(-3.71 40.41)', 4326)); | ||
| - | </code> | ||
| - | * Lire la table et ses données géographiques | ||
| - | <code> | ||
| - | SELECT ST_AsEWKT(the_geom) FROM my4capitals; | ||
| - | SELECT ST_AsGeoJSON(the_geom) FROM my4capitals; | ||
| - | </code> | ||
| - | * Transformer des coordonnées d'une projection à une autre | ||
| - | <code> | ||
| - | SELECT ST_AsEWKT(ST_Transform(the_geom, 21781)) FROM my4capitals; | ||
| - | </code> | ||
| - | |||
| - | ===== Géoservice Get4Capitals.php ===== | ||
| - | Cet exemple montre comment créer un service sur mesure qui va //publier// l'information géographique de la géotable. Il est notamment faire usage du format GeoJSON pour transmettre ces données au client. OpenLayers saura par exemple l'exploiter facilement. | ||
| - | |||
| - | <code> | ||
| - | <?php | ||
| - | require_once 'GeoManager.php'; | ||
| - | $conn = new Connection("host=10.192.57.14 port=5432 dbname=ogo user=ogo password=1234"); | ||
| - | |||
| - | $query = "SELECT name, ST_AsGeoJSON(the_geom) from my4capitals"; | ||
| - | $result = $conn->selectQuery($query); | ||
| - | |||
| - | $i = 0; | ||
| - | $fc = new FeatureCollection(); | ||
| - | while ($row = pg_fetch_row($result)) { | ||
| - | $fc->addFeature(new Feature($i++, (json_decode($row[1])), array("name" => $row[0]))); | ||
| - | } | ||
| - | echo json_encode($fc); | ||
| - | ?> | ||
| - | </code> | ||
| - | |||
| - | On a besoin d'un GeoManager (GeoManager.php) qui sait communiquer avec la geodatabase. | ||
| - | <code> | ||
| - | <?php | ||
| - | class Connection { | ||
| - | |||
| - | private $CONFIG; | ||
| - | private $CONN; | ||
| - | |||
| - | function __construct($cfg) { | ||
| - | $this->CONFIG = $cfg; | ||
| - | return $this->CONN = pg_connect($this->CONFIG) or die('connection failed'); | ||
| - | } | ||
| - | |||
| - | function selectQuery($query) { | ||
| - | $result = pg_query($this->CONN, $query); | ||
| - | if (!$result) throw new ErrorException($query); | ||
| - | return $result; | ||
| - | } | ||
| - | |||
| - | function insertQuery($query) { | ||
| - | pg_query($this->CONN, "BEGIN WORK"); | ||
| - | $result = pg_query($this->CONN, $query); | ||
| - | if (!$result) { | ||
| - | pg_query($this->CONN, "ROLLBACK"); | ||
| - | } else { | ||
| - | pg_query($this->CONN, "COMMIT"); | ||
| - | } | ||
| - | } | ||
| - | } | ||
| - | |||
| - | class Feature | ||
| - | { | ||
| - | var $type; | ||
| - | var $geometry; | ||
| - | var $id; | ||
| - | var $properties; | ||
| - | |||
| - | function Feature($id,$geom,$properties) { | ||
| - | $this->type = "Feature"; | ||
| - | $this->geometry = $geom; | ||
| - | $this->id = $id; | ||
| - | $this->properties = $properties; | ||
| - | |||
| - | } | ||
| - | } | ||
| - | |||
| - | class FeatureCollection | ||
| - | { | ||
| - | var $type; | ||
| - | var $features; | ||
| - | |||
| - | function FeatureCollection() | ||
| - | { | ||
| - | $this->type = "FeatureCollection"; | ||
| - | $this->features = array(); | ||
| - | } | ||
| - | |||
| - | function addFeature($feature) { | ||
| - | array_push($this->features,$feature); | ||
| - | } | ||
| - | } | ||
| - | ?> | ||
| - | </code> | ||
| - | |||
| - | __TODO__ | ||
| - | - Tester ce géoservice et analyser les différentes étapes du processus | ||
| - | - Terminer l'exemple en exploitant le géoservice dans OpenLayers avec une OpenLayers.Layer.Vector | ||
| - | |||
| - | ====== Spatial database :: CartoDB ====== | ||
| - | * Créer un compte chez cartodb.com | ||
| - | * Créer la //geotable// | ||
geoinf14/postgis0.txt · Last modified: 2018/05/16 10:05 (external edit)
Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Noncommercial-Share Alike 4.0 International
