Skip to main content
3 of 4
edited body
nickves
  • 11.6k
  • 3
  • 43
  • 76

The shapefile you provided seems to be valid, and the projection in the prj files states (if im not mistaken) that your coordinates of each vertex are tied to WGS84 (EPSG:4326)

Using ogr2ogr PGDump driver you can translate your shapefile to a sql querry that can be used to insert data into the database:

If you are in Windows you can open the console with MSYS Shell. It should be installed by default if you have used osgeo4w tool to install qgis.

Open your msys again and cd to your data directory, then for one file run:

ogr2ogr.exe -f PGDump -lco schema=vectors -nln name -lco geometry_name=geom -lco fid=gid -lco srid=4326 temp.sql time_1057.shp
  • the schema can be whatever you want.
  • -nln is the name postgis table that will be created.
  • the geometry_name and fid were choosen for compability reasons.
  • the srid was extracted from your prj file.

afterwards you can use psql to execute the sql file in your database. eg:

psql -h IP -U USERNAME -f temp.sql

Note that I didn't need any accompanied database files. The created table will contain data regarding the id (field name gid that we choose before) and the geometry information of the shape (field name geom). It's up to you to create and populate more fields fields.

Now since you mentioned that you have thousand of shapefiles, its a good idea to batch import them. Use again your Msys and cd to your data folder and use something like the following:

for f in $(ls *.shp); do 
      ogr2ogr.exe -f PGDump -lco schema=vectors -nln $(basename $f .shp) -lco geometry_name=geom -lco fid=gid -lco srid=4326 temp.sql $f;
      psql -h IP -U USERNAME -f temp.sql

After the operation completes, you should have a schema called vectors and within it a table for each shapefile you had

NB: There is a way to insert directly your data to the postgis, but i find if you use an intermediate file to do so, is easier to understand what's happening.

nickves
  • 11.6k
  • 3
  • 43
  • 76