back to ["CookBook Eda"]


== How to create a postgres database from a shape file" PostgresSQl 8.4 ==


The following example applies to the creation of a database from the CCM

[http://www.bostongis.com/?content_name=postgis_tut01]
is usefull to read if the following does not work

A gui is now avalaible: [http://postgis.org/download/windows/], see "Shp2pgsql-gui" paragraph. This gui can be install as a plugin wihtin PGadmin: [http://www.postgresonline.com/journal/index.php?/archives/145-PgAdmin-III-Plug-in-Registration-PostGIS-Shapefile-and-DBF-Loader.html here]

=== 1- Install postgres and postgis ===
(Use stack builder application in postgres command menu) 

=== 2- create database CCM ===

In !PgAdmin, connect to the serveur
 right clic, create new database, use template postgis to that the database is a geodatabase
[[Image(source:data/Docs/trac/PgAmin_create_gis_database.jpg)]]

=== 3- Figure out SRID of the data ===
We'll need to match this descriptive projection to an SRID (the id field of a spatial ref record in the spatial_ref_sys table) if we ever want to reproject our data. 
Open up your pgadmin III query tool and type in the following statement, use the green arrow to launch the command
 {{{
select srid, srtext, proj4text from spatial_ref_sys where srtext ILIKE '%ETRS89%';
}}}
Now we have to find the good line, it seems to be srid='3035' since it refers to the Eu system and equal area
=== 4- Create sql database === 
Open a command batch > execute> cmd
Change directory to get to the working directory where the shapes are stored
example :
 {{{
cd C:\Documents and Settings\cedric\Mes documents\Migrateur\eda\w2000a18
}}}
The program shp2pqsql installed along postgris allows to transform from shape to .sql
type in 
 {{{
C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 3035 COAST coast > coast.sql
}}}
The coast.sql file will be created at ''C:\Documents and Settings\cedric\Mes documents\Migrateur\eda\w2000a18''
from the COAST shape file.
Be aware that these files might be very big might sometimes open in pgAdmin, use the command batch to lauch the sql file[[BR]]
-----
[[Color(,red,ENCODING TROUBLES !)]]
[[Color(, orange,While trying to connect to the whole CCM database, one encounters encoding troubles, using -W LATIN1 sets the client encoding)]]
 {{{
C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 3035 -W LATIN1 LAKES lakes > lakes.sql
}}}
[[Color(, orange,In an sql console type in :)]]
{{{
SET client_encoding to 'LATIN1';
SHOW client_encoding;
}}}
----
{{{
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d CCM -h localhost -U postgres -f catchment_w.sql 
}}}
where: -d database -h host -U user -f the sql file.
Check at the end of the process if the database is created

To dump and restore the database created by this example
{{{
C:\PostgresPlus\8.3\bin\pg_dump -F c -U postgres -p 5432 CCM>CCM.dump
REM restoration using the create database command
C:\PostgresPlus\8.3\bin\pg_restore -C -U postgres -d CCM  db.dump
}}}

However see also ["Cookbook gdal"] an the script to use with Qgis gdal , it is less efficient but with a graphical interface