pgsql2shp - Number of shapes does not match number of records

Question

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;"

Answer 1

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.

Answer 2

I just had the same problem - it seems to be something to do with the name 'gid' of the id column in the database - renaming it to 'uid' meant the export to shapefile worked properly and as expected.

Note: I also used ogr2ogr and did not have the same problem, even with the gid column, so it must be a problem with shp2pgsql