back to first page ..
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: ​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

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

---

ENCODING TROUBLES ! 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

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  CCM.dump

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