Changes between Version 29 and Version 30 of CookBook join RHT-BDMAP

Timestamp:

Oct 13, 2011 4:13:02 PM (14 years ago)

Author:

cedric

Comment:

--

CookBook join RHT-BDMAP

@@ -106 +106 @@
 select count(*) from bdmap2009.station_bdmapv2_final stat inner join bd_carthage2011.troncon_hydrographique as bdc on bdc.code_hydro=stat.eh_codegen; --58875 ca ne marche pas du tout
 }}}
-
-
-reprojection de bdmap
-{{{
+travail de projection des données
+{{{
+#!sql
+/* 
+Travail brouillon intégration bd_map
+cedric Briand 13-octobre 2011
+*/
+
+/* 
+questions
+Laurent
+
+Est que les points ont été reprojettés. Est ce que comme lannee dernière le fichier correspond à la reprojection de 2008 seulement,
+si non avez vous des shapes plus récents : => NON
+Est ce que les données correspondent à station st_abcisse_l93 st_ordonnee_l93 ? => NON CES COLONNES SONT VIDES
+Si c'est le cas à quoi sert le fichier bdmap2009.station_bdmapv2_final ? => OK VOUS N'AVEZ PAS RETRAVAILLE DESSUS MAIS IL FAUT RECUPERER LES FICHIERS DU RCS POUR AVOIR CERTAINES COORDONNEES MANQUANTES ... AU FINAL IL EN MANQUE PAS BEAUCOUP
+Lors de cette reprojection avez vous une table avec les identifiants id_bdcarthage du cours d'eau.... c'est sur cette hypothèse que nous travaillions...
+=> CELINE NE PEUT PAS AVANCER RAPIDEMENT AVEC LES DONNEES FOURNIES  !
+
+Est ce que station_bdmapv2_final correspond à stationsp2 ? => NON IL FAUT LA REFAIRE
+Pourquoi est ce que dans station_bdmapv2_final ? -- 8759 alors que céline select count(*) from bd_map.stationsp2  --10193 lignes
+=> PARCE QUE STATIONSP2 EST UNE JOINTURE RIGHT JOIN ENTRE DEUX TABLES
+*/
+
+-- quelques requêtes pour voir les tables
+/*
+select * from bdmap2009.station_bdmapv2_final limit 10;
+select * from bdmap2009.station limit 100;
+select count(*) from bdmap2009.station_bdmapv2_final -- 8759
+select count(*) from bdmap2009.station --11379
+select * from bd_carthage2011.troncon_hydrographique limit 10;
+select distinct on (code_hydro) count(*) from bd_carthage2011.troncon_hydrographique group by code_hydro
+-- pas de jointure avec la méthode proposée par Céline
+select count(*) from bdmap2009.station_bdmapv2_final stat 
+inner join bd_carthage2011.troncon_hydrographique as bdc on bdc.code_hydro=stat.th_code --12325
+*/
+
+-- reprojection de bdmap2009.station_bdmapv2_final
+
 alter table bdmap2009.station_bdmapv2_final drop constraint enforce_srid_the_geom;
 update bdmap2009.station_bdmapv2_final set the_geom=st_transform(the_geom,3035);
 alter table bdmap2009.station_bdmapv2_final add constraint enforce_srid_the_geom CHECK (st_srid(the_geom) = 3035);
-}}}
+
+--select st_transform(PointFromText('POINT(' || st_abcisse || ' ' || st_ordonnee || ')',27572),3035) from bdmap2009.station;
+--select * from bdmap2009.station where st_abcisse_l93>0 -- 0 lignes on en peut pas utiliser
+
+SELECT AddGeometryColumn('bdmap2009', 'station','the_geom', 3035,'POINT',2); 
+UPDATE bdmap2009.station SET the_geom=st_transform(PointFromText('POINT(' || st_abcisse || ' ' || st_ordonnee || ')',27572),3035);
+ALTER TABLE bdmap2009.station SET WITH OIDS;
+CREATE INDEX indexStations ON bdmap2009.station
+  USING GIST ( the_geom GIST_GEOMETRY_OPS ); 
+
+-- il faut aller chercher les stations qui manquent dans bdmap2009.station_bdmapv2_final
+
+DROP TABLE IF EXISTS bdmap2009.stationsp2;
+CREATE TABLE bdmap2009.stationsp2 WITH OIDS AS(
+  select 
+  bdmap2009.station.st_altitude,
+  bdmap2009.station.st_abcisse,
+  bdmap2009.station.st_codecsp,
+  bdmap2009.station.st_datearret,
+  bdmap2009.station.st_datecreation,
+  bdmap2009.station.st_distancesource,
+  bdmap2009.station.st_distancemer,
+  bdmap2009.station.st_finalite,
+  bdmap2009.station.st_imageign,
+  bdmap2009.station.st_imagedept,
+  bdmap2009.station.st_lieudit,
+  bdmap2009.station.st_limites,
+  bdmap2009.station.st_localisation,
+  bdmap2009.station.st_longueur,
+  bdmap2009.station.st_moduleia,
+  bdmap2009.station.st_cd_naturecourseau,
+  bdmap2009.station.st_ordonnee,
+  bdmap2009.station.st_penteign,
+  bdmap2009.station.st_pkaval,
+  bdmap2009.station.st_raisremp,
+  bdmap2009.station.st_sbv,
+  bdmap2009.station.st_t_janvier,
+  bdmap2009.station.st_t_juillet,
+  bdmap2009.station.st_cd_typecourseau,
+  bdmap2009.station.st_cd_tet,
+  bdmap2009.station.st_st_id,
+  bdmap2009.station.st_cm_id,
+  bdmap2009.station.st_cx_id,
+  bdmap2009.station.st_th_id,
+  bdmap2009.station.st_eh_id,
+  bdmap2009.station.st_uh_id,
+  bdmap2009.station.st_dt_cre,
+  bdmap2009.station.st_dt_maj,
+  bdmap2009.station.st_qi_maj,
+  bdmap2009.station.st_masseeau,
+  CASE WHEN bdmap2009.station_bdmapv2_final.the_geom IS NULL THEN station.the_geom
+       ELSE bdmap2009.station_bdmapv2_final.the_geom
+       END AS the_geom
+  FROM bdmap2009.station_bdmapv2_final right join bdmap2009.station on station_bdmapv2_final.st_codecsp=bdmap2009.station.st_codecsp);--11379
+COMMENT ON TABLE bdmap2009.stationsp2 is 'Données BDMAP avec l année 2009 transmises par Laurent Beaulaton bdmap20110803.backup et mise à jour avec les coordonnées reprojetées par Hélène'
+
+-- je vire les stations hors de France, par rapport à la dernière fois deux sont rentrées en France avec des coordonnées correctes.
+delete from bdmap2009.stationsp2 where st_codecsp IN ('03890162','03270062','03270061','03270063','03270064','06260126','06420055','06070241')
+
+
+
+
+CREATE INDEX indexStationsp2 ON bdmap2009.stationsp2
+  USING GIST ( the_geom GIST_GEOMETRY_OPS );
+
+
+-- creation de la table bdmap2009.bdmap_rht, je rajoute un the_geom a la requete pour pouvoir voir la table dans Qgis  
+DROP TABLE IF EXISTS bdmap2009.bdmap_rht;
+CREATE TABLE bdmap2009.bdmap_rht as (
+        SELECT distinct on (st_codecsp) st_codecsp, id_drain, min(distance) as distance, the_geom FROM (
+               SELECT st_codecsp, id_drain ,CAST(distance(r.the_geom, s.the_geom) as  decimal(15,1)) as distance,s.the_geom 
+               FROM bdmap2009.stationsp2 As s
+               INNER JOIN  rht.rht r ON ST_DWithin(r.the_geom, s.the_geom,300)
+               WHERE s.the_geom IS NOT NULL
+               ORDER BY st_codecsp) AS sub 
+        GROUP BY st_codecsp, distance,id_drain, the_geom
+);
+alter table bdmap2009.bdmap_rht add column id serial;
+-- mise à jour de la table geometry_columns
+INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
+SELECT '', 'bdmap2009', 'bdmap_rht', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom)
+FROM bdmap2009.bdmap_rht LIMIT 1;
+
+-- creation d'index, clé primaire, et constraintes qui vont bien
+alter table bdmap2009.bdmap_rht add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2);
+alter table bdmap2009.bdmap_rht add  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL);
+alter table bdmap2009.bdmap_rht add  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035);
+alter table bdmap2009.bdmap_rht ADD CONSTRAINT pk_id PRIMARY KEY(id);
+CREATE INDEX indexbdmap_rht ON bdmap2009.bdmap_rht
+  USING GIST ( the_geom GIST_GEOMETRY_OPS );
+
+-- je vire les deux anciennes tables de Céline qui ne servent plus puisque je viens de refaire le travail de projection
+
+DROP TABLE IF EXISTS rht.bdmap_rht_id;
+DROP TABLE IF EXISTS rht.bdmap_rht;
+
+-- creation de la table bdmap_bd_carthage, je rajoute un the_geom a la requete pour pouvoir voir la table dans Qgis  
+-- normalement on projette sur la bd_carthage et on utilise la jointure Rht bd_carthage pour récupérer les tronçons.
+
+
+DROP table if exists bdmap2009.bdmap_bd_carthage;
+CREATE TABLE bdmap2009.bdmap_bd_carthage as (
+        SELECT distinct on (st_codecsp) st_codecsp,  id_bdcarth, min(distance) as distance, the_geom FROM (
+               SELECT st_codecsp, id_bdcarth ,CAST(distance(r.the_geom, s.the_geom) as  decimal(15,1)) as distance,s.the_geom 
+               FROM bdmap2009.stationsp2 As s
+               INNER JOIN  bd_carthage2011.troncon_hydrographique r ON ST_DWithin(r.the_geom, s.the_geom,300)
+               WHERE s.the_geom IS NOT NULL
+               AND nature !='Aqueduc, conduite forcée' and num_superp !=1 -- je vire les tronçons superposés.
+               ORDER BY st_codecsp) AS sub 
+        GROUP BY st_codecsp, distance, id_bdcarth, the_geom
+);--11181
+alter table bdmap2009.bdmap_bd_carthage add column id serial;
+-- mise à jour de la table geometry_columns
+INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
+SELECT '', 'bdmap2009', 'bdmap_bd_carthage', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom)
+FROM bdmap2009.bdmap_bd_carthage LIMIT 1;
+
+-- creation d'index, clé primaire, et constraintes qui vont bien
+alter table bdmap2009.bdmap_bd_carthage add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2);
+alter table bdmap2009.bdmap_bd_carthage add  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL);
+alter table bdmap2009.bdmap_bd_carthage add  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035);
+CREATE INDEX indexbdmap_ccm ON bdmap2009.bdmap_bd_carthage
+  USING GIST ( the_geom GIST_GEOMETRY_OPS );
+
+
+
+  -- vérifications il manque peu de stations
+select count(*) from bdmap2009.station --11379
+select count(*) from bdmap2009.stationsp2 --11371 0.07 %
+select * from bdmap2009.stationsp2 where the_geom is null; -- 110 stations ou il manque la geométrie
+select count(*) from bdmap2009.bdmap_bd_carthage; --11181 sur les tronçons hydro 1.74 %
+select count(*) from bdmap2009.bdmap_bd_carthage t join  rht.rht_bdcarthage r on r.id_bdcarth=t.id_bdcarth; -- 10998 sur les tronçons du RHT 3.34 %
+
+}}}