=====Intégration des thèmes de l'IGN à notre base de données=====
===== octobre 2010 =====
==== Calcul de la taille sur disque des tables du schéma ign_bd_topo (5 départements)====
<box round 60%>pg_total_relation_size(regclass) renvoit l'espace disque total utilisé par la table de cet OID ou nom, incluant les index et les données de la partie TOAST</box>

voici les statistiques pour notre serveur (RHEL 4, diques en ext4) : 

<code sql>
SELECT f_table_name, pg_total_relation_size('ign_bd_topo.'||f_table_name), pg_size_pretty(pg_total_relation_size('ign_bd_topo.'||f_table_name))
  FROM geometry_columns
  WHERE f_table_schema='ign_bd_topo'
</code>

Au total, l'ensemble des tables prend une place d'environ 2.9 Giga, le mnt n'y est pas encore intégré (http://trac.osgeo.org/postgis/wiki/WKTRaster|piste]])
^nom de la table^[[http://docs.postgresqlfr.org/8.4/functions-admin.html#functions-admin-dbsize|pg_total_relation_size()]]^[[http://docs.postgresqlfr.org/8.4/functions-admin.html#functions-admin-dbsize|pg_size_pretty(pg_total_relation_size())]]^
^aire_triage|172032|168 kB|
^bati_indifferencie|265838592|254 MB|
^bati_industriel|15687680|15 MB|
^bati_remarquable|2703360|2640 kB|
^canalisation_eau|155648|152 kB|
^chef_lieu|385024|376 kB|
^chemin|185024512|176 MB|
^cimetiere|794624|776 kB|
^conduite|90112|88 kB|
^construction_lineaire|19144704|18 MB|
^construction_ponctuelle|1294336|1264 kB|
^construction_surfacique|466944|456 kB|
^emprise|417792|408 kB|
^gare|90112|88 kB|
^hydronyme|475136|464 kB|
^lieu_dit|34013184|32 MB|
^lieu_dit_habite|4743168|4632 kB|
^lieu_dit_non_habite|4349952|4248 kB|
^ligne_electrique|434176|424 kB|
^ligne_orographique|31105024|30 MB|
^oronyme|2719744|2656 kB|
^pai_administratif_militaire|548864|536 kB|
^pai_culture_loisirs|385024|376 kB|
^pai_espace_naturel|4685824|4576 kB|
^pai_gestion_eaux|311296|304 kB|
^pai_hydrographie|475136|464 kB|
^pai_industriel_commercial|958464|936 kB|
^pai_orographie|2719744|2656 kB|
^pai_religieux| 507904|496 kB|
^pai_sante| 122880|120 kB|
^pai_science_enseignement|393216|384 kB|
^pai_sport|90112|88 kB|
^pai_transport|638976|624 kB|
^pai_zone_habitation|5169152|5048 kB|
^piste_aerodrome|172032|168 kB|
^point_eau|1253376|1224 kB|
^poste_transformation|114688|112 kB|
^pylone|2088960|2040 kB|
^reservoir|1941504|1896 kB|
^reservoir_eau|1662976|1624 kB|
^route|509304832|486 MB|
^route_nommee|130162688|124 MB|
^route_primaire|12091392|12 MB|
^route_secondaire|270352384|258 MB|
^surface_activite|2908160|2840 kB|
^surface_eau|17031168|16 MB|
^surface_route|483328|472 kB|
^terrain_sport|1474560|1440 kB|
^toponyme_communication|237568|232 kB|
^toponyme_divers|622592|608 kB|
^toponyme_ferre|81920|80 kB|
^transport_cable|106496|104 kB|
^troncon_cours_eau|104742912|100 MB|
^troncon_laisse|892928|872 kB|
^troncon_voie_ferree|2121728|2072 kB|
^zone_vegetation|1361412096|1298 MB|
^**total**|**3008372736**|**2869 MB**|

===== juillet 2010 =====
==== Création de la table des lieu-dits pour l'interface de saisie ====
Les toponymes sont stockés dans 4 tables de la [[integration_donnees:externes:ign#integration_de_la_bdtopo|BD TOPO]] :
  * lieu_dit_habite
  * lieu_dit_non_habite
  * oronyme
  * hydronyme

De manière à pouvoir proposer efficacement une aide à la saisie d'un lieu-dit selon l'endroit ou la donnée a été localisée sur la carte, nous allons rassembler ces informations dans une seule et même table nommée lieu_dit, dans le schéma public de notre base de données :
<file sql lieu_dit.sql>
CREATE TABLE lieu_dit
(
  id character varying(24) PRIMARY KEY,
  origin_nom character varying(13),
  nom character varying(70),
  importance character varying(2),
  nature character varying(24),
  code_insee character varying(5), 
  /* Ce dernier champ n'existe pas initialement dans les tables "nymes" de la BD TOPO 
  Nous le rajoutons pour permettre des jointures avec la table commune et accélérer 
  le temps de récupération des lieu-dits proches d'un objet créé sur la carte */
  gid serial UNIQUE -- utile pour QGIS
)
WITH (
  OIDS=FALSE
);

/* création de la colonne géométrique de type point en lambert 93 */

SELECT AddGeometryColumn('lieu_dit', 'geometrie', 310024140, 'POINT', 2 );

/* remplissage de la table */

INSERT INTO lieu_dit(id, origin_nom, nom, importance, nature, geometrie)
SELECT id, origin_nom, nom, importance, nature, geometrie FROM ign_bd_topo.lieu_dit_habite;
INSERT INTO lieu_dit(id, origin_nom, nom, importance, nature, geometrie)
SELECT id, origin_nom, nom, importance, nature, geometrie FROM ign_bd_topo.lieu_dit_non_habite;
/*INSERT INTO lieu_dit(id, origin_nom, nom, importance, nature, geometrie)
SELECT id, origin_nom, nom, importance, nature, geometrie FROM ign_bd_topo.hydronyme;
INSERT INTO lieu_dit(id, origin_nom, nom, importance, nature, geometrie)
SELECT id, origin_nom, nom, importance, nature, geometrie FROM ign_bd_topo.oronyme;*/

/* indexation des géométries */

CREATE INDEX lieu_dit_geometrie_gist
  ON lieu_dit
  USING gist
  (geometrie);

/* mise à jour du code insee */

UPDATE lieu_dit
SET code_insee=ign_bd_topo.commune.code_insee
FROM ign_bd_topo.commune
WHERE st_intersects(ign_bd_topo.commune.geometrie, lieu_dit.geometrie);

/* indexation du code_insee des lieu-dits */
  
CREATE INDEX ind_lieu_dit_code_insee
  ON lieu_dit
  USING btree
  (code_insee);
</file>

==== Comment lister les lieux-dits situés dans un rayon de 2 km de l'objet créé ====
<code sql>
SELECT ign_bd_topo.commune.nom||' | '||lieu_dit.nom||' - '||round(st_distance(centroid(st_transform(saisie_faune.geometrie,310024140)), lieu_dit.geometrie))||' m.', round(st_distance(centroid(st_transform(saisie_faune.geometrie,310024140)), lieu_dit.geometrie)), ign_bd_topo.commune.nom
FROM (select * from saisie_faune where gid=4) as saisie_faune JOIN ign_bd_topo.commune ON st_intersects(ign_bd_topo.commune.geometrie, centroid(st_transform(saisie_faune.geometrie,310024140)))
JOIN lieu_dit USING (code_insee)
WHERE st_intersects(st_buffer(st_transform(saisie_faune.geometrie,310024140),2000), lieu_dit.geometrie)
ORDER BY round(st_distance(st_transform(saisie_faune.geometrie,310024140), lieu_dit.geometrie)) ASC, ign_bd_topo.commune.nom
</code>

<code sql>
SELECT ign_bd_topo.commune.nom||' | '||lieu_dit.nom||' - '||round(st_distance(centroid(st_transform(geomfromtext(),310024140)), lieu_dit.geometrie))||' m.', round(st_distance(centroid(st_transform(geomfromtext(),310024140)), lieu_dit.geometrie)), ign_bd_topo.commune.nom
FROM ign_bd_topo.commune JOIN lieu_dit USING (code_insee) 
WHERE st_intersects(ign_bd_topo.commune.geometrie, centroid(st_transform(geomfromtext(),310024140)))
AND st_intersects(st_buffer(st_transform(geomfromtext(),310024140),2000), lieu_dit.geometrie)
ORDER BY round(st_distance(st_transform(geomfromtext(),310024140), lieu_dit.geometrie)) ASC, ign_bd_topo.commune.nom
</code>
===== février 2010 =====
==== integration de la BD PARCELLAIRE dans le schema ign_bd_parcellaire ====
  * format : shp
  * projection : lambert 93

===== janvier 2010 =====
==== Intégration de la BDTOPO ====
=== Transformation des MIF en shp ===
//La BD topo a été initialement commandée en MIF/MID...//
<code bash>
#!/bin/bash
set -x
i=1 # on initialise le compteur
option=''
for rep in `ls -d */`; do
	for mif in `find $rep -iname "*.MIF"`; do
                #echo $i : $PWD : $rep : $(echo $(basename $mif) | cut -d"." -f1) | tr '[A-Z]' '[a-z]'
                echo ogr2ogr $option -a_srs \"+init=IGNF:LAMB93\" -f \"ESRI Shapefile\" SHP/$(echo $(basename $mif) | cut -d"." -f1 | tr '[A-Z]' '[a-z]').shp ./$mif
                #echo ogr2ogr $option -f \"PostgreSQL\" PG:\"host=localhost user=dba dbname=ign password=dbroot\" $PWD/$rep$mif -nln bdtopo.$(echo $mif | cut -d"." -f1) | tr '[A-Z]' '[a-z]' 
		eval ogr2ogr $option -a_srs \"+init=IGNF:LAMB93\" -f \"ESRI Shapefile\" SHP/$(echo $(basename $mif) | cut -d"." -f1 | tr '[A-Z]' '[a-z]').shp ./$mif
	done
	let $[ i+=1 ] # incremente i de 1 a chaque boucle
	option='-update -append' # après la première série de fichier, il faudra ajouter les nouvelles données aux fichiers shp déja existants.
done
</code>
=== Intégration des shp à la base de données===
<code bash>
#!/bin/bash
for shp in `ls -R *.shp`; do
	eval shp2pgsql -d -W "latin1" -s 310024140 -g geometrie -I -S $shp ign_bd_topo.$(echo $(basename $shp) | cut -d"." -f1 | tr '[A-Z]' '[a-z]')>../SQL/$(echo $(basename $shp) | cut -d"." -f1 | tr '[A-Z]' '[a-z]').sql
	eval psql -f ../SQL/$(echo $(basename $shp) | cut -d"." -f1 | tr '[A-Z]' '[a-z]').sql -h localhost -U dba sicen 2>>../erreurs.log
done</code>




===== février 2009 =====

Transformer les shapes en sql :
**""for /R %F IN (*.shp) DO shp2pgsql -s 27572 -g geometrie -I "%F" "%~nF">"ign_bdcarto_%~nF.sql"""**

Chargement dans la base de données :
**""for /R %F IN (*.sql) DO psql -f "%F" -h addresse_ip_hote -U utilisateur base_de_donnée""**

- **Données de la BD Parcellaire (Département du Gard)**

=====octobre 2008=====
==== intégration des différentes couches de la BD CARTHAGE ====

La version mise à disposition sur le site du sandre introduit des accents dans le nommage des colonnes.

Après téléchargement des couches (ici au format mif/mid, si shp alors passer à l'étape suivante), les transformer en shp à l'aide de la console (windows):
<code>for /R %F IN (*.MIF) DO ogr2ogr -f "ESRI ShapeFile" -s_srs "EPSG:2154" -t_srs "EPSG:27582" "%~nF.shp" %F</code>

Transformer ces shapes en sql :
<code>for /R %F IN (*.shp) DO shp2pgsql -s 27582 -S -g geometrie -I  "%F"  "ign_bdc_%~nF">"prefixe_%~nF_suffixe.sql"</code>

Les charger dans la base de données :
<code>for /R %F IN (*.sql) DO psql -f "%F" -h addresse_ip_hote -U utilisateur base_de_donnée</code>