Tuesday, 9 October 2007

Import spatial data (shapefile) into PostgreSQL

1. Open console, execute following commands:
cd $ShapeDir
shp2pgsql -W UTF-8 <filename>.shp <schema>.<tablename> ><filename>.sql


2. Open file that has just created, insert string "WITH OIDS" before character ";" of command CREATE TABLE, and then save that file.

3. Continue to execute the command:
psql -h <hostname> -U <username> -d <databasename> -f <filename>.sql

4. Open pgAdmin3 (or phpPgAdmin), open Query Tool and input following lines:
CREATE INDEX <indexname> ON <tablename> USING gist (<spatial_attribute>);
VACUUM ANALYZE <tablename>


Ex:
shp2pgsql -W UTF-8 hcm_road.shp public.hcm_road > hcm_road.sql

psql -h 127.0.0.1 -U postgres -d hcmcity -f hcm_road.sql

CREATE INDEX hcm_road_the_geom_gist ON hcm_road USING gist (the_geom);

VACUUM ANALYZE hcm_road


* Note: if you want to export data from PostgreSQL to shapefile, using command pgsql2shp.

1 comment:

MccMsMadness said...

Thank you. I've referenced you on my blog.