pgsql2shp creates a dbf with 0 records when the table only has a geometry column

[winkey]

here is an output shapefile here ​https://github.com/opengdp/opengdp/raw/6b737b0377228421d442ecb289889d97dc663115/share/data/bridges.zip

following is part of a conversation in #gdal

[11:54:18] <EvenR> winkey: the DBF file isn't consistant with the SHP. It indicates 0 records
[11:59:51] <EvenR> the driver could/should perhaps emit a warning if there's an inconsistency at that level, but it would be best to know why pgsql2shp generate such file ? Perhaps your table had no attribute ?
[12:00:27] <winkey> no pkey must be the issue
[12:00:52] <winkey> it was only a geom collumn
[12:01:46] <EvenR> I think they should generate a dummy ID column with the PKID for better compatibility
[12:02:04] <EvenR> that's what the ogr shape driver does when you create a shapefile without field

[robe]

I personally HATE generating dummy ids. In fact I hate that OGR2OGR does that — drives me nuts.

However I do agree we should emit a warning.

[winkey]

creating invalid output is not good either

it seems to me any objection to creating dummy id's would be that it blindly does so and you might be expecting different behaviour

a switch to make the app generate them perhaps and a warning

dont create the dbf file without them (no idea if this is a good idea or not)

brian

Replying to robe:

I personally HATE generating dummy ids. In fact I hate that OGR2OGR does that — drives me nuts.

However I do agree we should emit a warning.

[landry]

Fwiw, i've hit that issue too on a similar usecase, and in this case is still thing there's a bug in pgsql2shp since:

• i imported a perfectly valid shapefile into postgis via shp2pgsql - it had not attributes, and the postgis table ended up with a gid attr and a the_geom attr - so far so good.
• when trying to export back that table via pgsql2shp, it generates a 'valid' shp (ie i can see the amount of features) but the dbf file is invalid/empty - thus the layer cant be open in say QGIS.

ogrinfo -al foo.shp (on an extracted shape from postgis) will give me the feature count, but wont display the list of features.. the same command on the source shape (ie before import into postgis) gives me the feature count and the list of point features in WKT.

[landry]

hexdump of the empty dbf for the source shapefile

0000000 7203 1802 0004 0000 0021 0001 0000 0000
0000010 0000 0000 0000 0000 0000 0000 0300 0000
0000020 200d 2020 0020                         
0000025

full pgsql2shp command:

pgsql2shp -r -f armoire_43 -h services -u postgis postgis "select t.* from erdf_armoires_simples_hta_2014 as t, communes_2154 as c where st_intersects(t.the_geom,c.the_geom) and c.insee_dept='43'"
Preparing table for user query... Done.
Initializing... Done (postgis major version: 1).
Output shape: Point
Dumping: XX [1 rows].

hexdump of the empty dbf file generated by the command

0000000 5f03 1a07 0001 0000 0041 000c 0000 0000
0000010 0000 0000 0000 0000 0000 0000 0000 0000
0000020 4947 0044 0000 0000 0000 4e00 0000 0000
0000030 000b 0000 0000 0000 0000 0000 0000 0000
0000040 200d 2032 2020 2020 2020 2020 0020  

[robe]

I suppose we could use ROW_NUMBER() OVER() for the key generation if no other attribute is available and emit a warning so that we have a valid DBF. Not something I want to risk doing in 2.1 though so will keep in 2.2.

[robe]

what was I thinking code altready has an interator so can probably just use that as a dummy id if no fields present.

[robe]

Milestone renamed