Changes between Version 66 and Version 67 of CookBook join ROE_CCM


Ignore:
Timestamp:
Apr 28, 2010 9:16:53 AM (15 years ago)
Author:
celine
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • CookBook join ROE_CCM

    v66 v67  
    44 
    55= Join ROE - CCM = 
    6 == Putting geobs into the CCM == 
     6== Putting geobs2010 into the CCM == 
    77Initially, the database was located in another table, below we just copy the table shema roe into the eda2.0 database 
    88 
    … …  
    1111REM pour Cédric 
    1212CD C:\Documents and Settings\cedric\Mes documents\Migrateur\eda\Barrages 
    13 C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump -U postgres -n geobs ROE> roe_schema.sql 
     13C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump -U postgres -n geobs2010 ROE> roe_schema.sql 
    1414C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -h localhost -p 5433 -U postgres -f roe_schema.sql 
    1515REM==================================== 
    … …  
    2424We will create a new column a new constraint and fill this column with reprojected data 
    2525{{{ 
    26 select AddGeometryColumn('geobs', 'obstacle_referentiel','ref_position_etrs89' , 3035,'POINT',2); 
     26select AddGeometryColumn('geobs2010', 'obstacle_referentiel','ref_position_etrs89' , 3035,'POINT',2); 
    2727-- this function also creates constraint « enforce_srid_ref_position_etrs89  
    28 select SRID (ref_position_locale) FROM geobs.obstacle_referentiel; -- all lines 27572 (Lambert II) 
    29 update geobs.obstacle_referentiel set ref_position_etrs89 =ST_Transform(ref_position_locale,3035); 
     28select SRID (ref_position_locale) FROM geobs2010.obstacle_referentiel; -- all lines 27572 (Lambert II) 
     29update geobs2010.obstacle_referentiel set ref_position_etrs89 =ST_Transform(ref_position_locale,3035); 
    3030}}} 
    3131 
    … …  
    9595 we have 43937 lines, after joining with France we should have less 
    9696{{{ 
    97 SELECT count(ref_id) from geobs.obstacle_referentiel -- 43937 
     97SELECT count(ref_id) from geobs2010.obstacle_referentiel -- 43937 
    9898}}} 
    9999{{{ 
    … …  
    104104The following does not work and it makes sense, we should only have one geom of france 
    105105{{{ 
    106 SELECT  count(ref_id) from geobs.obstacle_referentiel o 
     106SELECT  count(ref_id) from geobs2010.obstacle_referentiel o 
    107107 join france.departement f ON ST_DWithin(f.the_geom, o.ref_position_etrs89, 2000); --49468 
    108108}}} 
    … …  
    113113I first aggregate all departements and then join this glued layer with the dam layer, the distance is 2000 
    114114{{{ 
    115 SELECT  count(ref_id) from geobs.obstacle_referentiel o 
     115SELECT  count(ref_id) from geobs2010.obstacle_referentiel o 
    116116join (SELECT ST_Union(f.the_geom) as singlegeom 
    117117         FROM france.departement  As f) as sub 
    … …  
    130130-- Création de la table correspondance, je rajoute un the_geom a la requete pour pouvoir voir la table dans Qgis  
    131131{{{ 
    132 CREATE INDEX indexgeobs ON geobs.obstacle_referentiel  USING GIST (ref_position_etrs89 GIST_GEOMETRY_OPS); 
    133 DROP TABLE IF EXISTS geobs.correspondance; 
    134 CREATE TABLE geobs.correspondance as ( 
     132CREATE INDEX indexgeobs ON geobs2010.obstacle_referentiel  USING GIST (ref_position_etrs89 GIST_GEOMETRY_OPS); 
     133DROP TABLE IF EXISTS geobs2010.correspondance; 
     134CREATE TABLE geobs2010.correspondance as ( 
    135135        SELECT distinct on (ref_id) ref_id, gid, min(distance) as distance, ref_position_etrs89 as the_geom FROM ( 
    136136               SELECT ref_id, gid ,CAST(distance(r.the_geom, b.ref_position_etrs89) as  decimal(15,1)) as distance ,b.ref_position_etrs89 
    137                FROM geobs.obstacle_referentiel As b 
     137               FROM geobs2010.obstacle_referentiel As b 
    138138               INNER JOIN  ccm21.riversegments r ON ST_DWithin(r.the_geom, b.ref_position_etrs89,1000) 
    139139--             WHERE b.goodproj IS TRUE -- Attention c'est faux 
    … …  
    146146-- si la commande ci dessus ne marche pas ... 
    147147INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type") 
    148 SELECT '', 'geobs', 'correspondance', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom) 
    149 FROM geobs.correspondance LIMIT 1; 
     148SELECT '', 'geobs2010', 'correspondance', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom) 
     149FROM geobs2010.correspondance LIMIT 1; 
    150150 
    151151-- creation d'index, clé primaire, et constraintes qui vont bien 
    152 alter table geobs.correspondance add column id serial PRIMARY KEY; 
    153 alter table geobs.correspondance add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2); 
    154 alter table geobs.correspondance add  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL); 
    155 alter table geobs.correspondance add  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035); 
    156  
    157 CREATE INDEX indexgeobscorrespondance ON geobs.correspondance 
     152alter table geobs2010.correspondance add column id serial PRIMARY KEY; 
     153alter table geobs2010.correspondance add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2); 
     154alter table geobs2010.correspondance add  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL); 
     155alter table geobs2010.correspondance add  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035); 
     156 
     157CREATE INDEX indexgeobscorrespondance ON geobs2010.correspondance 
    158158  USING GIST ( the_geom GIST_GEOMETRY_OPS ); 
    159159}}} 
    160160The final script is below 
    161161{{{ 
    162 ALTER TABLE geobs.obstacle_referentiel ADD COLUMN goodproj boolean; 
    163 UPDATE geobs.obstacle_referentiel set goodproj=FALSE ; 
    164 UPDATE geobs.obstacle_referentiel set goodproj=TRUE where ref_id IN ( 
    165 SELECT  ref_id from geobs.correspondance); 
     162ALTER TABLE geobs2010.obstacle_referentiel ADD COLUMN goodproj boolean; 
     163UPDATE geobs2010.obstacle_referentiel set goodproj=FALSE ; 
     164UPDATE geobs2010.obstacle_referentiel set goodproj=TRUE where ref_id IN ( 
     165SELECT  ref_id from geobs2010.correspondance); 
    166166}}} 
    167167We now have to use, and can display the dams left out in qgis 
    168168{{{ 
    169 select * from geobs.obstacle_referentiel where goodproj=TRUE 
     169select * from geobs2010.obstacle_referentiel where goodproj=TRUE 
    170170}}} 
    171171== Some statistics about the dam projection == 
    … …  
    183183Calcul du pourcentage de barrages projetés par région. 
    184184{{{ 
    185 DROP TABLE if exists geobs.pourcentageprojette; 
    186 CREATE TABLE geobs.pourcentageprojette AS( 
     185DROP TABLE if exists geobs2010.pourcentageprojette; 
     186CREATE TABLE geobs2010.pourcentageprojette AS( 
    187187SELECT 100*JOINT/(JOINT +DISJOINT) as pourcentage, nom_region,the_geom FROM(  
    188188SELECT nom_region, sum(cast(cast(goodproj AS integer)as NUMERIC)) as joint, sum(cast(cast(NOT goodproj AS integer) AS NUMERIC)) as disjoint, the_geom 
    189 from  france.region f, geobs.obstacle_referentiel g 
     189from  france.region f, geobs2010.obstacle_referentiel g 
    190190WHERE ST_Intersects(f.the_geom,g.ref_position_etrs89) 
    191191group by nom_region, the_geom)as sub); 
    192 SELECT geometrytype(the_geom) FROM geobs.pourcentageprojette; 
    193 ALTER TABLE geobs.pourcentageprojette ADD column pourc_id serial PRIMARY KEY; 
    194 alter table geobs.pourcentageprojette  add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2); 
    195 alter table geobs.pourcentageprojette  add  CONSTRAINT enforce_geomtype_the_geom CHECK(geometrytype(the_geom) = 'MULTIPOLYGON'::text OR geometrytype(the_geom) = 'POLYGON'::text OR the_geom IS NULL);; 
    196 alter table geobs.pourcentageprojette  add  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035); 
     192SELECT geometrytype(the_geom) FROM geobs2010.pourcentageprojette; 
     193ALTER TABLE geobs2010.pourcentageprojette ADD column pourc_id serial PRIMARY KEY; 
     194alter table geobs2010.pourcentageprojette  add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2); 
     195alter table geobs2010.pourcentageprojette  add  CONSTRAINT enforce_geomtype_the_geom CHECK(geometrytype(the_geom) = 'MULTIPOLYGON'::text OR geometrytype(the_geom) = 'POLYGON'::text OR the_geom IS NULL);; 
     196alter table geobs2010.pourcentageprojette  add  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035); 
    197197}}} 
    198198Pourcentage de barrages projettés avec un buffer de 300 m 
    … …  
    203203 
    204204 
    205 When we compare the geobs.correspondance and the geobs.obstacle_referentiel :[[BR]] 
    206 [[Image(source:data/Docs/trac/ROE/Obstacle referentiel et correspondance.png,800px)]] 
    207 in red the dams in geobs.obstacle_referentiel which are not well projected 
     205When we compare the geobs2010.correspondance and the geobs2010.obstacle_referentiel :[[BR]] 
     206[[Image(source:data/Docs/trac/ROE/Obstacle referentiel et correspondance.png,800px)]][[BR]] 
     207in red the dams in geobs2010.obstacle_referentiel which are not well projected 
    208208in violet the dams which are "well" projected and projected in the CCM layer 
    209209There are two dams (in violet in the East) which are projected in the CCM layer but aren't in France