5

I have a MultiLineString postgis layer in a db. The layer is created with the following...

-- Table: public.pipe_clip_102737

-- DROP TABLE public.pipe_clip_102737;

CREATE TABLE public.pipe_clip_102737
(
  geom geometry(MultiLineString,102737),
  gid integer NOT NULL DEFAULT nextval('pipe_clip_102737_gid_seq'::regclass),
  CONSTRAINT pipe_clip_102737_pkey PRIMARY KEY (gid)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.pipe_clip_102737
  OWNER TO jcfcqrso;

-- Index: public.pipe_clip_102737_geom_idx

-- DROP INDEX public.pipe_clip_102737_geom_idx;

CREATE INDEX pipe_clip_102737_geom_idx
  ON public.pipe_clip_102737
  USING gist
  (geom);

The layer displays correctly in both ArcGIS and QGIS when connecting directly to the db.

However will not display when I try to export to shp via cmd line or via the PostGIS DBF and Shapefile Loader utility.

pgsql2shp -f package/pipe_clip3.shp -h xxxxxxxxxxxxxxxxxxx -u xxxxx -P xxxxxxxx routing "SELECT gid, geom FROM pipe_clip_102737"

Both methods dump 1727 records as a shp. But cannot be displayed. ArcGIS displays:

"Error opening feature class. Number of shapes does not match number of records."

and QGIS seems to be aware of the extents of the shp but will not draw features and table is empty.

Why can I successfully display db table but not export a usable shp using either method?

EDIT: The issue seems to be related to the cloumn name gid. As mentioned by @tinlyx renaming the gid column while running the cmd seem to fix the problem.

pgsql2shp -f myshape.shp -h xx.xx.xx.xx -u myuser -P xxxxx mydb "SELECT gid as myid, geom FROM mytable;"
6

This error often means that your shapefile (generated by pgsql2shp) is corrupted. The ESRI FAQ suggested common causes such as editing the .DBF file with Excel.

In your case, it's possible that some of the geometries in PostGIS is invalid or NULL. Without actual data, it's difficult to tell. But you may want to check that all geometries in the table are valid with ST_IsValid (and not NULL) in PostGIS before exporting them to a shapefile.

Also, in the following GIS.SE question, there is a script to check whether the number of shapes and attribute entries match:

How to programmatically check if the number of shapes = number of table records?

-- Update --

@wtgeographer From your comments, it seems that the attributes in the DBF file isn't correctly written. I've done some quick experiments to verify:

I exported a table to a shapefile extracting only the gid, geom columns, just as you did. In my system (QGIS 2.14, Linux, PostGIS 2.3), pgsql2shp dumped the all the rows; the .shp file is the right size, but QGIS shows 0 records/shapes when the shapefile is loaded.

I suspect this is a bug in handling empty attribute table, because if I exported one non-trivial field such as name (text), then QGIS can display all the geometry and attributes in the shapefile.

-- Update 2 --

@wtgeographer. You may have to file a bug report and ask the developers to come up with a solution for the empty .DBF problem. I think the gid field is probably considered special and handled differently. In my experiments, the exported shapefile works even if I rename/copy the gid field using "SELECT gid as myid, geom FROM mytable" in the pgsql2shp command line.

  • 2
    Shapefile supports empty geometries but pgsql2shp may write them wrong. Without test data it is hard to say. I suggest to use ogd2ogr and compare the results. – user30184 May 6 '17 at 21:12
  • After following recommendations from @tinlyx here is what I have learned. ST_IsValid() returns true on all records. Also after running pyshp example in the referenced GIS.SE question. I learn that the exported shp file has the following: Number of shapes: 17237 Number of records: 0. FYI I only have id and geom columns in this table. Should there be special handling of this? – wtgeographer May 7 '17 at 1:55
  • additional testing involved adding a dummy text column to the table and exporting with the new column. Running the pyshp script once more I now get the expected. Number of shapes: 17237 Number of records: 17237 How can properly export a table containing only id and geometry? – wtgeographer May 7 '17 at 2:32

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.