"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
This is an old revision of the document!
Voyons comment utiliser l'extension PostGIS de Postgresql pour stocker et gérer de l'information géographique dans un SGBD.
CREATE EXTENSION postgis;
Pour la suite, ne pas oublier l'indispensable documentation avec son chapitre PostGIS Reference
CREATE TABLE my4capitals (id serial PRIMARY KEY, name text); SELECT AddGeometryColumn( 'my4capitals', 'the_geom', 4326, 'POINT', 2 );
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));
SELECT ST_AsEWKT(the_geom) FROM my4capitals; SELECT ST_AsGeoJSON(the_geom) FROM my4capitals;
SELECT ST_AsEWKT(ST_Transform(the_geom, 21781)) FROM my4capitals;
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.
<?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); ?>
On a besoin d'un GeoManager (GeoManager.php) qui sait communiquer avec la geodatabase.
<?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); } } ?>
TODO
INSERT INTO my4capitals (cartodb_id, name, the_geom) VALUES ( 0, 'Paris', ST_GeometryFromText( 'POINT(2.333 48.867)', 4326)); INSERT INTO my4capitals (cartodb_id, name, the_geom) VALUES ( 1, 'Bern', ST_GeometryFromText( 'POINT(7.433 46.95)', 4326)); INSERT INTO my4capitals (cartodb_id, name, the_geom) VALUES ( 2, 'Rome', ST_GeometryFromText( 'POINT(12.5 41.883)', 4326)); INSERT INTO my4capitals (cartodb_id, name, the_geom) VALUES ( 3, 'Madrid', ST_GeometryFromText( 'POINT(-3.71 40.41)', 4326));
SELECT * from my4capitals