back to first page ..
back to CookBook Eda
back to RNABE

In ArcGis merge the different layers

To find : if it's possible to do it in Postgre (not in Qgis) ?

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:\Celine Jouanin\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)

  --- 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
Il faut donc changer le srid 4258 (ETRS89) en 3035 (ETRS89-LAEA)

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);