1

I have a polyline shapefile (.shp and .shx) without .dbf. I would like to import the shp into PostGIS.

In order to do that, I created the corresponding .dbf using the r code provided by mdsumner in this post Adding Attribute data to shapefile?

The .dbf consists of just one row, while the .shp corresponds to a polyline.

Now, if I load the shp into QGIS, the attribute table lists the features ids of the polyline in read-only mode.
I know that I could save the file loaded in QGIS then I would be able to import it into PostGIS.

However I have thousands of similar shapefiles and I would like to import all of them into PostGIS. I suppose that this is not possible because the .dbf is read-only because I got this message: "dbf file (.dbf) can not be opened".

I read in another post, that it is the shx file that connects the shp to the dbf but this did not help me.

Apologies if the question is not relevant or it is not the correct way to proceed.

  • 2
    "the shx file that connects the shp to the dbf" - that's incorrect. The SHX file is an index to the location of shapes within the SHP file, in case you want to, say, go to the 100th shape directly rather than reading the 99 prior ones. – Russell at ISC Nov 20 '14 at 15:26
  • DBF should have a row for each shape. Tools should still be able to read it if it's read-only (plus you can check and change that on any OS) so more likely either the DBF is corrupt, or there is more than one feature in your shape file. Where did you get the shapefile from anyway? DBF should always be included, that's part of the standard. – Russell at ISC Nov 20 '14 at 15:29
  • Its doable - can you give a sample? – nickves Nov 20 '14 at 15:37
  • the shp was created using the api of shapelib. For some reasons, the dbf was not created. – seli Nov 20 '14 at 15:43
  • yes, of course. how could I send the files? – seli Nov 20 '14 at 15:46
4

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.

you can use head to examine the header of the newly generated file to check what is goind to happen.

head temp.sql

In the header you can find information on what in going to happen when you execute the querry. eg how its going to create the tables, if an index is going to be created and etc... the rest of the file regards actual data that are going to be inserted to the table.

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

NB1: Don't forget to VACUUM afterwards to builds table statistics for the indexes to work.

NB2: 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.

  • Thank you a lot. Very clear and complete answer. It works! – seli Nov 20 '14 at 18:09

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.