Database-driven mapfile

Avez-vous déjà essayé de construire une mapfile qui serait dynamiquement contrôlé par la base de données? À l'occasion, cette astuce peut aider à produire des services Web cartographiques basés sur des données dynamiques d'un système d'envergure. Voici une description de table provenant de Postgesql: Table "public.om_users" Column      | Type                   | Modifiers ------------+------------------------+------------------------------------- om_id       | integer                | not null longitude   | numeric(12,9)          | latitude    | numeric(12,9)          | color       | character varying(11)  | default '255 0 0'::character varying label       | character varying(100) | the_geom    | geometry               | feature     | character varying(32)  | default 'circle'::character varying size        | integer                | default 5 outline     | character varying(11)  | default '0 0 0'::character varying Indexes: "om_users_pkey" PRIMARY KEY, btree (om_id) Check constraints: "enforce_dims_the_geom" CHECK (st_ndims(the_geom) = 2) "enforce_geotype_the_geom" CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL) "enforce_srid_the_geom" CHECK (st_srid(the_geom) = 4326) On place simplement les champs de données au bon endroit dans le mapfile. Pour chacun des enregistrements, mapserver prendra les données de la table pour cartographier l'entité. LAYER   NAME "UsersLayers"   INCLUDE "ec/ec_connec_db_pg.map"   DATA "the_geom from om_users using srid=4326"   TYPE POINT   METADATA     "wms_name" "UsersLayer"     "wms_title" "UsersLayer"     "wms_server_version" "1.1.1"     "wms_srs" "EPSG:4326 EPSG:900913"     "gml_featureid" "oid"     "gml_include_items" "all"   END   PROJECTION     "init=epsg:4326"   END   CLASSITEM "om_id"   CLASS   NAME "Status"   STYLE     SYMBOL [feature]       COLOR [color]       OUTLINECOLOR [outline]       SIZE [size]     END   END END

Charger un CSV avec psql dans Postgresql

Pour convertir un fichier CSV avec des coordonnées x,y en champ de type géométrie dans PostGIS, on peux utiliser la commande COPY en ligne de commande psql! 1) Copier le fichier CSV dans un répertoire visible du serveur Posrgresql. Dans le cas de Linux utiliser sudo et changer les droits du fichier CSV: sudo cp fichier_csv dans_repertoire_postgresql sudo chown postgres:postgres fichier_csv 2)Créer la table dans Postgres create table ma_table ( code character(10) NOT NULL, poste character(100) NOT NULL, adresse character(100) NOT NULL, municipalite character(100) NOT NULL, cp character(7) NOT NULL, _lat float, _long float, CONSTRAINT ma_table_pk PRIMARY KEY (code) ); 3)Charger les données du fichier CSV: set client_encoding='LATIN1'; COPY ma_table FROM '/srv/postgresql/scripts/ma_table.csv' delimiter ';'; 4)Ajouter la colonne geométrique select AddGeometryColumn ('public','ma_table','the_geom',4326,'POINT',2); 5)faire le update à partir des coordonnées x,y UPDATE ma_table set the_geom = ST_GeomFromText('POINT('||_long||' '|| _lat||')',4326); 6) Enjoy!

Rownum dans Postgresql

Dans Oracle ceux qui ont l'habitude d'utiliser l'option rownum pour limiter le nombre de résultat d'une requête SQL pourront retrouver le même effet avec l'option limit dans Postgresql select no_appart from immeubles_p where rownum select no_appart from immeubles_p limit 10; no_appart ----------- 25 703 543 204 14 234 890 27 184 765 (10 rows) ------------------------------------------------------------

Un autre roadmap de création d’une BD spatiale sous Postgresql/PostGIS

Je reprends dans ce billet, la procédure complète pour monter rapidement une BD spatiale sous Postgresql/PostGIS. On utilisera souvent cette base de donnée simplement pour avoir un contenant de nos données géométriques.  Plusieurs utiliseront la chemin le plus court pour se connecter au serveur avec l’usager/mot de passe par défaut soit: postgres/postgres.  Il faut par contre idéalement, créer un autre utilisateur pour accéder au données SANS oublier de changer le mot de passe par défaut du serveur.

 

1) Installation du serveur Postgresql/PostGIS ET de l’utilitaire pgadmin pour un client ubuntu sudo apt-get update sudo apt-get install postgresql postgresql-client postgresql-contrib postgresql-8.4-postgis sudo apt-get install pgadmin3 sudo su postgres psql alter user postgres with password 'pw_defaut_a_changer_de_grace';

2) Un fois l’extension PostGIS intallée dans Postgresql, il ne reste qu’a trouver les deux fichiers SQL permettant d’accéder au fonctions spatiales et de la cherger avec psql.

Note: On va créer la BD template avec un encodage UTF-8.  Dépendant des versions à venir, le répertoire et même le nom des fichiers SQL peut varier... sudo su postgres createdb -E utf8 -O postgres postgis createlang plpgsql postgis psql -d postgis -f /usr/share/postgresql/8.4/contrib/postgis-1.5/postgis.sql psql -d postgis -f /usr/share/postgresql/8.4/contrib/postgis-1.5/spatial_ref_sys.sql 3) Vérifier l’installation Postgresql / PostGIS est bien correcte. On doit avoir la table geometry_table et spatial_ref_sys sudo su postgres psql postgis d Liste des relations Schéma |        Nom        | Type  | Propriétaire --------+-------------------+-------+-------------- public | geography_columns | vue   | postgres public | geometry_columns  | table | postgres public | spatial_ref_sys   | table | postgres 4) Par la suite on peux créer des BD (une bd par projet par exemple) avec le template postgis et un user sudo su postgres createdb -E utf8 -W -O postgres -T postgis osm psql -d osm CREATE USER osmusr WITH PASSWORD 'osm'; GRANT ALL PRIVILEGES ON DATABASE osm to osmusr; GRANT ALL ON TABLE geometry_columns TO osmusr; GRANT ALL ON TABLE spatial_ref_sys TO osmusr; alter user postgres with password 'petitpou'; q exit (sortir du super user postgres) psql -d osm -U osmusr -W