46

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?

Improve this question

3 Answers 3

Reset to default
66

Try shp2pgsql. The basic syntax is like:

shp2pgsql -s SRID SHAPEFILE.shp SCHEMA.TABLE | psql -h HOST -d DATABASE -U USER

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
13
  • But I do not have a table. What table and schema should I create?
    – Sam007
    Nov 21, 2012 at 23:30
  • 2
    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, 2012 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, 2012 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, 2012 at 23:59
  • 1
    yep. Try CREATE EXTENSION postgis; on the database you want to import the files into.
    – four-eyes
    Sep 11, 2017 at 8:38
8

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
6
  • 1
    the problem is I do not have a GUI. I am working on Ubuntu Server remotely.
    – Sam007
    Nov 21, 2012 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, 2012 at 23:55
  • How do I execute it
    – Sam007
    Nov 21, 2012 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, 2012 at 0:21
  • All my shp file are on the server
    – Sam007
    Nov 22, 2012 at 0:39
8

Anyone doing this in 2021 should be aware of the amazingly convenient QGIS Processing Toolbox tool 'Export to PostgreSQL':

enter image description here

Improve this answer
1
  • I'm in 2021, but don't have this tool. what version of Qgis is this? edit: nevermind, the name is changed into "Import to PostGIS"
    – sutan
    Dec 15, 2021 at 1:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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