back to first page [..][[BR]]
back to ["CookBook Eda"][[BR]]
back to ["RNABE"][[BR]]


== In !ArcGis merge the different layers ==
To find : if it's possible to do it in Postgre (not in Qgis) ? [[BR]]

Add the differents layers on !ArcGis : A_RWBody, B1_RWBody, B2_RWBody, C_RWBody, D_RWBody, E_RWBody, F_RWBody, G_RWBody, H_RWBody.

In Data Management Tools > General > Merge
   - Input Datasets : A_RWBody, B1_RWBody, B2_RWBody, C_RWBody, D_RWBody, E_RWBody, F_RWBody, G_RWBody, H_RWBody.
   - Output Dataset : change the directory if it's necessary and change the name of the shape file : RWBody_Merge.shp

== Download the file RWBody_Merge.shp into Postgre ==
{{{
REM se placer dans le répertoire ou sont les données
D:
cd D:\CelineJouanin\RNABE\France_RNABE
}}}
{{{
C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 4258 -I Fr_RWBody_Merge.shp Fr_RWBody > Fr_RWBody.sql
C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 4258 -I Fr_CWBody_Merge.shp Fr_CWBody > Fr_CWBody.sql
C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 4258 -I Fr_GWBody_Merge.shp Fr_GWBody > Fr_GWBody.sql
C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 4258 -I Fr_LWBody_Merge.shp Fr_LWBody > Fr_LWBody.sql
C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 4258 -I Fr_TWBody_Merge.shp Fr_TWBody > Fr_TWBody.sql
}}}
    * '''Convert it into utf8'''  (as the database is posgis), in notepad, open the Fr_RWBody.sql file and convert it to utf8
Encodage> Convertir en UTF-8

And save the file as : Fr_RWBodyutf8, Fr_CWBodyutf8,...


    * '''Use psql to restore the file''' while connecting to eda2.0 ... change the port if necessary (cedric : 5433)
{{{
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -h localhost -U postgres -p 5432 -f Fr_RWBodyutf8.sql
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -h localhost -U postgres -p 5432 -f Fr_CWBodyutf8.sql
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -h localhost -U postgres -p 5432 -f Fr_GWBodyutf8.sql
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -h localhost -U postgres -p 5432 -f Fr_LWBodyutf8.sql
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -h localhost -U postgres -p 5432 -f Fr_TWBodyutf8.sql
}}}

    * '''Create schema RNABE''' et changement de schéma de la table Fr_RWBody (qui est sous public)
{{{
#!sql
--- In sql editor 
CREATE SCHEMA RNABE;
ALTER TABLE fr_rwbody SET SCHEMA rnabe;
ALTER TABLE fr_cwbody SET SCHEMA rnabe;
ALTER TABLE fr_gwbody SET SCHEMA rnabe;
ALTER TABLE fr_lwbody SET SCHEMA rnabe;
ALTER TABLE fr_twbody SET SCHEMA rnabe;
}}}

   * '''Change the srid'''
La projection du RNABE est en fait l'ETRS89 et non pas l'ETRS89-LAEA [[BR]]
Il faut donc changer le srid 4258 (ETRS89) en 3035 (ETRS89-LAEA) [[BR]]
{{{
#!sql
ALTER TABLE rnabe.fr_rwbody DROP CONSTRAINT enforce_srid_the_geom;
UPDATE rnabe.fr_rwbody SET the_geom = ST_transform(the_geom, 3035); 
ALTER TABLE rnabe.fr_rwbody ADD CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 3035);

ALTER TABLE rnabe.fr_cwbody DROP CONSTRAINT enforce_srid_the_geom;
UPDATE rnabe.fr_cwbody SET the_geom = ST_transform(the_geom, 3035); 
ALTER TABLE rnabe.fr_cwbody ADD CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 3035);

ALTER TABLE rnabe.fr_gwbody DROP CONSTRAINT enforce_srid_the_geom;
UPDATE rnabe.fr_gwbody SET the_geom = ST_transform(the_geom, 3035); 
ALTER TABLE rnabe.fr_gwbody ADD CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 3035);

ALTER TABLE rnabe.fr_lwbody DROP CONSTRAINT enforce_srid_the_geom;
UPDATE rnabe.fr_lwbody SET the_geom = ST_transform(the_geom, 3035); 
ALTER TABLE rnabe.fr_lwbody ADD CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 3035);

ALTER TABLE rnabe.fr_twbody DROP CONSTRAINT enforce_srid_the_geom;
UPDATE rnabe.fr_twbody SET the_geom = ST_transform(the_geom, 3035); 
ALTER TABLE rnabe.fr_twbody ADD CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 3035);
}}}