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

= CCM2 download and load =

== Download on ccm on the web site ==
You can download the CCM2 Data at :  http://ccm.jrc.ec.europa.eu/php/index.php?action=view&id=24 [[BR]]
If you don't have a login and a password create it.[[BR]]
And select the data you want to add. Use the projection system : ETRS 1989 LAEA[[BR]]

== Change the format with !ArcGis ==

In !ArcCatalog, open the data (for example CCM21_LAEA_RiverBasins\ccm21\LAEA_RiverBasins.gdb) and clic rigth on LAEA_RiverBasins.gdb > Export > To Shapefile (multiple)[[BR]]
and choose the "Output Folder" where the data will be stored.[[BR]]

Unzip the folder.

== Append the different shape files ==
We have obtained shapefiles from different sources, folder (http://ccm.jrc.ec.europa.eu/images/CCM2_Windows2b.JPG), and it's easier to combine them into one single shapefile for ease of analysis and mapping.[[BR]]
This process is known as "merging" within ArcGIS.[[BR]]

 * Tables: Rivernodes - Riversegments- Catchments
||Layer|| Region||Rivernodes[[BR]] Nb of records||Riversegments[[BR]] Nb of records||Catchments[[BR]] Nb of records||
||2000||North||64308||63298||65395||
||2001||Western Islands||34507||32408||36613||
||2002||South||121630||119096||123104||
||2003||West||117455||116764||118265||
||2004||South West||96378||95424||96826||
||2005||Danube||141112||140998||141216||
||2006||Dnjepr||64439||64016||64668||
||2007||Balticum||140998||138443||162488||
||2008||Scandinavia||86671||82433||96189||
||2009||South East||108279||104696||110005||
||2010||Macronesia||1722||1314||1771||
||2011||Caucasus||50320||49534||50461||
||2012||Ural||33198||32850||33470||
||2013||Volga||250212||250183||250291||
||2015||Azores||94||49||118||
||2016||Northern Islands||108||58||371||
||2017||Tigris||54734||54730||54731||
||2018||Iceland||3476||2998||3662||
||After "Merge"[[BR]] Number of record expected[[BR]]Total||[[BR]][[BR]]EUROPE||[[BR]][[BR]]1369641 (ok)||[[BR]][[BR]]1349292 (ok)||[[BR]][[BR]]1409644 (ok)||

The Merge tool can be found within Data Management Tools > General > Merge. Double-clicking on the tool opens up its dialog box.[[BR]]
The input datasets are those shapefiles which you wish to merge together. Select the different shapefiles as you wish as long as they are all of the same data type. 
Add Rivernodes in : CCM21_LAEA_window2000\ccm21\LAEA_W2000.gdb\RIVERNODES  (from CCM21_LAEA_window2000  to CCM21_LAEA_window2018)

And specify a location and name for the output file which will be created : CCM2\RIVERNODES_Merge.[[BR]]

You must do the same thing with "Rivernodes", "Riversegments", "Catchments".[[BR]]



== Add data into Postgres ==


See ["CookBook shptopostgres"] the same procedure was applied to obtain : (refer to your version so far Cédric or Céline)
{{{
REM Ouvrir ce fichier avec notepad++ et copier coller les commandes dans une invite de commande
REM================================================
REM Script pour cédric (la connexion change - p 5433)
REM================================================
REM Création de la base de donnée
REM correspond au point 1 de http://62.160.92.241:8066/trac/wiki/CookBook%20shptopostgres
C:\"Program Files"\PostgreSQL\8.4\bin\psql -p 5433 -U postgres -c "CREATE DATABASE ccm21_eda  WITH OWNER = postgres  template=postgis ENCODING = 'UTF8'  LC_COLLATE = 'French_France.1252'  LC_CTYPE = 'French_France.1252' CONNECTION LIMIT = -1;"
REM on se place dans le répertoire ou sont les données
CD C:\Documents and Settings\cedric\Mes documents\Migrateur\eda\couches_SIG\coucheccm\CCM2
C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 3035 -W LATIN1 COAST coast > coast.sql
C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 3035 -W LATIN1 ISLANDS islands > islands.sql
C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 3035 -W LATIN1 LAKES lakes > lakes.sql
C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql  -s 3035 -W LATIN1 MAINRIVERS mainrivers > mainrivers.sql
C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 3035 -W LATIN1 NAMEDRIVERS namedrivers > namedrivers.sql
C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 3035 -W LATIN1 SEAOUTLETS seaoutlets > seaoutlets.sql
C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql  -s 3035 -W LATIN1 RIVERNODES_Merge rivernodes > rivernodes.sql
C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql  -s 3035 -W LATIN1 RIVERSEGMENTS_Merge riversegments > riversegments.sql
C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 3035 -W LATIN1 CATCHMENTS_Merge catchments > catchments.sql
REM changement d'encodage du client
C:\"Program Files"\PostgreSQL\8.4\bin\psql -p 5433 -U postgres -c "SET client_encoding to 'LATIN1';"

REM utilisation de psql pour la réintégration des données
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d ccm21_eda -h localhost -U postgres -p 5433 -f coast.sql
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d ccm21_eda -h localhost -U postgres -p 5433 -f islands.sql
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d ccm21_eda -h localhost -U postgres -p 5433 -f lakes.sql
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d ccm21_eda -h localhost -U postgres -p 5433 -f mainrivers.sql
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d ccm21_eda -h localhost -U postgres -p 5433 -f namedrivers.sql
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d ccm21_eda -h localhost -U postgres -p 5433 -f seaoutlets.sql
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d ccm21_eda -h localhost -U postgres -p 5433 -f rivernodes.sql
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d ccm21_eda -h localhost -U postgres -p 5433 -f riversegments.sql
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d ccm21_eda -h localhost -U postgres -p 5433 -f catchments.sql

REM================================================
REM Script pour Céline (la connexion change - p 5432
REM================================================
REM Création de la base de donnée
REM correspond au point 1 de http://62.160.92.241:8066/trac/wiki/CookBook%20shptopostgres
C:\"Program Files"\PostgreSQL\8.4\bin\psql -p 5432 -U postgres -c "CREATE DATABASE ccm21_eda  WITH OWNER = postgres  template=postgis ENCODING = 'UTF8'  LC_COLLATE = 'French_France.1252'  LC_CTYPE = 'French_France.1252' CONNECTION LIMIT = -1;"
REM on se place dans le répertoire ou sont les données
CD D:\Celine Jouanin\CCM21
C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 3035 -W LATIN1 COAST coast > coast.sql
C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 3035 -W LATIN1 ISLANDS islands > islands.sql
C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 3035 -W LATIN1 LAKES lakes > lakes.sql
C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql  -s 3035 -W LATIN1 MAINRIVERS mainrivers > mainrivers.sql
C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 3035 -W LATIN1 NAMEDRIVERS namedrivers > namedrivers.sql
C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 3035 -W LATIN1 SEAOUTLETS seaoutlets > seaoutlets.sql
C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql  -s 3035 -W LATIN1 RIVERNODES_Merge rivernodes > rivernodes.sql
C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql  -s 3035 -W LATIN1 RIVERSEGMENTS_Merge riversegments > riversegments.sql
C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 3035 -W LATIN1 CATCHMENTS_Merge catchments > catchments.sql
REM changement d'encodage du client
C:\"Program Files"\PostgreSQL\8.4\bin\psql -p 5432 -U postgres -c "SET client_encoding to 'LATIN1';"
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d ccm21_eda -h localhost -U postgres -f coast.sql
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d ccm21_eda -h localhost -U postgres -f islands.sql
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d ccm21_eda -h localhost -U postgres -f lakes.sql
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d ccm21_eda -h localhost -U postgres -f mainrivers.sql
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d ccm21_eda -h localhost -U postgres -f namedrivers.sql
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d ccm21_eda -h localhost -U postgres -f seaoutlets.sql
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d ccm21_eda -h localhost -U postgres -f rivernodes.sql
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d ccm21_eda -h localhost -U postgres -f riversegments.sql
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d ccm21_eda -h localhost -U postgres -f catchments.sql
}}}
== change schema to ccm1 ==
Which can be downloaded at source:trunk/EDACCM/ccm21_build.bat [[BR]] open this file with notepad++ and copy / paste to the command batch
{{{
CREATE SCHEMA ccm21;
ALTER TABLE riversegments SET SCHEMA ccm21;
ALTER TABLE rivernodes SET SCHEMA ccm21;
ALTER TABLE catchments SET SCHEMA ccm21;
ALTER TABLE coast SET SCHEMA ccm21;
ALTER TABLE islands SET SCHEMA ccm21;
ALTER TABLE mainrivers SET SCHEMA ccm21;
ALTER TABLE namedrivers SET SCHEMA ccm21;
ALTER TABLE seaoutlets SET SCHEMA ccm21;
ALTER TABLE lakes SET SCHEMA ccm21;
}}}
== A PLSQL function to get the upstream segments ==
=== creating indexes ===
{{{
DROP INDEX IF EXISTS ccm21.indexriversegments_wso_id;
CREATE INDEX indexriversegments_wso_id
ON ccm21.riversegments
USING btree  (wso_id);

DROP INDEX IF EXISTS ccm21.indexcatchments_wso_id; 
CREATE INDEX indexcatchments_wso_id
ON ccm21.catchments
USING btree  (wso_id);

DROP INDEX IF EXISTS ccm21.indexcatchments_wso1_id; 
CREATE INDEX indexcatchments_wso1_id
ON ccm21.catchments
USING btree (wso1_id);

DROP INDEX IF EXISTS ccm21.indexcatchments_wso2_id; 
CREATE INDEX indexcatchments_wso2_id
ON ccm21.catchments
USING btree (wso2_id);

DROP INDEX IF EXISTS ccm21.indexcatchments_wso3_id; 
CREATE INDEX indexcatchments_wso3_id
ON ccm21.catchments
USING btree (wso3_id);

DROP INDEX IF EXISTS ccm21.indexcatchments_wso4_id; 
CREATE INDEX indexcatchments_wso4_id
ON ccm21.catchments
USING btree (wso4_id);

DROP INDEX IF EXISTS ccm21.indexcatchments_wso5_id; 
CREATE INDEX indexcatchments_wso5_id
ON ccm21.catchments
USING btree (wso5_id);

DROP INDEX IF EXISTS ccm21.indexcatchments_wso6_id; 
CREATE INDEX indexcatchments_wso6_id
ON ccm21.catchments
USING btree (wso6_id);

DROP INDEX IF EXISTS ccm21.indexcatchments_wso7_id; 
CREATE INDEX indexcatchments_wso7_id
ON ccm21.catchments
USING btree (wso7_id);

DROP INDEX IF EXISTS ccm21.indexcatchments_wso8_id; 
CREATE INDEX indexcatchments_wso8_id
ON ccm21.catchments
USING btree (wso8_id);

DROP INDEX IF EXISTS ccm21.indexcatchments_wso9_id;
CREATE INDEX indexcatchments_wso9_id
ON ccm21.catchments
USING btree (wso9_id);

DROP INDEX IF EXISTS ccm21.indexcatchments_wso10_id;
CREATE INDEX indexcatchments_wso10_id
ON ccm21.catchments
USING btree (wso10_id);
}}}
Exploration done in ticket #52 [[BR]]
plsql documentation can be found there :
http://www.postgresql.org/docs/8.4/static/plpgsql.html