I was able to create a Postgresql database and then I executed,

createlang plpgsql geospecies
psql -d geospecies -f postgis.sql
psql -d geospecies -f spatial_ref_sys.sql

So now can I directly import the shapefiles? Would this be the right command,

ogr2ogr -f PostgreSQL PG:"host=server_ip user=username dbname=dbname password=password" yourshapefile.shp;

I am not sure how to structure the database, would it not need a table? I will eventually need to add a bunch load of shapefiles and so is there a command to do that?


Try shp2pgsql. The basic syntax is like:


I always find this cheatsheet from http://www.bostongis.com useful. If you scroll down a little, you will find simple examples on how to load data.

Hope it helps.

| improve this answer | |
  • But I do not have a table. What table and schema should I create? – Sam007 Nov 21 '12 at 23:30
  • 1
    If you take a look at the cheatsheet, (-d|-a|-c|-p) are the flags for tables. If you didn't specify anything, it will choose -c by default (creates a new table and populates it). As for SRID, I don't know - that depends on the projection of your data. If you have the .proj file that came with the shapefiles, you can check it's projection at prj2epsg.org/search. Hope it helps.. – user8723 Nov 21 '12 at 23:37
  • thanks for the reply @Haziq. I am only confused as to where the -c comes? I tried shp2pgsql -s 4326 /var/www/geo/shapefile/PimaPine.shp -c public.Pima | psql -h localhost -d geospecies -U smaranh but I am getting an error dpaste.com/834563 – Sam007 Nov 21 '12 at 23:52
  • I think the -c should goes after -s 4326, and before /var/www/geo/shapefile/PimaPine.shp.. The syntax is shp2pgsql [OPTIONS] shapefile [schema.]table. Does changing the position of -c still effects in error? – user8723 Nov 21 '12 at 23:59
  • Nope still the same error. It might be also facing the ROLLBACK error dude to this, gis.stackexchange.com/questions/41807/… – Sam007 Nov 22 '12 at 0:01

Ahh! Can't comment yet!

What Haziq posted would be the correct commandline way to do it Sam.

TABLE is just whatever you want to name it and would be the name to use when calling it from a query, the table will be generated upon import.

If you become lazy like me, I just use the awesome SPIT (Shapefile to PostGIS Import Tool) available as a QGIS plugin to import all my shapefiles now. Assuming you are using QuantumGIS as you said your were using OpenGeo earlier.

| improve this answer | |
  • 1
    the problem is I do not have a GUI. I am working on Ubuntu Server remotely. – Sam007 Nov 21 '12 at 23:53
  • SPIT will work remotely, I do all the time from work to my home DB, you just have to fill out the connection information as you did to login on psql. – RomaH Nov 21 '12 at 23:55
  • How do I execute it – Sam007 Nov 21 '12 at 23:59
  • Well I was assuming you are taking local shp files and putting them on your remote server. You would start QGIS and use the SPIT plugin on your local machine, connect your remote server and upload. – RomaH Nov 22 '12 at 0:21
  • All my shp file are on the server – Sam007 Nov 22 '12 at 0:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.