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

 back to Recipes for EDA [wiki:"CookBook Eda"]

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

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

=== 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:EDA/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\postgres\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
warning these files might be very big might sometimes open in pgAdmin, use the command batch to lauch the sql file
{{{
C:\program files\postgres\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
}}}