[postgis-users] PostGIS export - data is inexplicably truncated from 3234 records to 3221 when using 'pgsql2shp', PGAdmin, AND QGIS 2.6+

[Jorge Gustavo Rocha]

Hi Ruth,

I've imported your data from shp to postgis, with ogr2ogr, like this:
ogr2ogr -overwrite -f "PostgreSQL" PG:"host=localhost user=geobox 
dbname=geotuga password=geobox" -a_srs EPSG:4326 -nlt MULTIPOLYGON -nln 
usa as_clean_test_ogr_export_utf8.shp

Then, I run the following query:

select count(*), st_isvalid(wkb_geometry) from usa
group by st_isvalid(wkb_geometry);

The results is:

  count | st_isvalid
-------+------------
     13 |
   3218 | t
      3 | f

So, I would say that you have invalid geometries that are causing this 
problem.

I hope it helps.

Regards,

Jorge

Às 18:44 de 03-12-2016, Ruth Simm escreveu:
> Hi,
>
> I'm having a serious data export problem with the PGAdmin widget and
> pgsql2shp (QGIS too). The problem lies within converting a PostGIS table
> to shapefile. *I put in 3234 feature counts...and I get back 3221.*
>
> I get limited information from the error reports (see below...QGIS gives
> me the most information...but it is minimal). The errors (when
> available) reference ogr....but not much else. Ironically, the only way
> I've found to actually export the *full* record set is by using ogr2ogr.
>
> I'm not sure why round-tripping with the PGAdmin tool is failing so bad.
> It seems to be able to import the initial 3234 - but cannot export the
> full set.
>
> I do notice the Polygon/Multipolygon converting happening...but seeing
> that many of the tables "Multipolygons" are just single Polygons added
> into the Multipolygon bucket...I'm not sure if that has anything to do
> with it.
>
> Very perplexed. If anybody can pull my dataset and repo this (or tell me
> how they got around it - using anything BUT ogr2ogr) I'd appreciate it!!!!
>
> Here are my specs:
>
> PostgreSQL 9.2.4 (on a Linux server)
> pgsql2shp 2.1.1
> PostGIS 2.0 Shapefile and DBF Loader Exporter (PGAdmin tool)
> QGIS 2.6.0 - Brighton (on windows...we've tested using more up-to-date
> versions too)
>
> Here is the data <https://spaces.hightail.com/space/ihoE7> (2 MB
> unzipped on a cloud drive).
>
> Here is what I do...
>
> 1) Load the shapefile into Postgres using the PGAdmin tool (works
> fine...notably it is loaded as a MULTIPOLYGON[2] EVEN THOUGH the
> Shapefile type is Polygon)
>
> *3234 record counts*
>
> Importing with configuration: as_clean_test_ogr_export_utf8, public,
> geom, H:\as_clean_test_ogr_export_utf8.shp, mode=c, dump=1, simple=0,
> geography=0, index=1, shape=1, srid=4326
> Shapefile type: Polygon
> PostGIS type: MULTIPOLYGON[2]
> Shapefile import completed.
> Connecting:  host=pgsqlgis-repos port=5432 user=local-dev
> password='*********'
>
> 2) Try to export the table tp shapefile... (this doesn't work for the
> following..and notably they are all trying to export it as a Polygon)
>
> UPON Export (using >pgsql2shp)
>
> H:\>pgsql2shp -f E:/as_clean_test_ogr_export_utf8.shp -h <hostname> -u
> <username> -p <port> -P <password> <postgrestable>
> "as_clean_test_ogr_export_utf8"
>
> Initializing...
> Done (postgis major version: 2).
> Output shape: Polygon
> Dumping: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX*[3221 rows]*.
>
> QGIS (Save As in QGIS 2.6.0)
>
> Export to vector file failed.
> Error: Feature write errors:
> Feature geometry not imported (OGR error: )
> Feature geometry not imported (OGR error: )
> Feature geometry not imported (OGR error: )
> Feature geometry not imported (OGR error: )
> Feature geometry not imported (OGR error: )
> Feature geometry not imported (OGR error: )
> Feature geometry not imported (OGR error: )
> Feature geometry not imported (OGR error: )
> Feature geometry not imported (OGR error: )
> Feature geometry not imported (OGR error: )
> Feature geometry not imported (OGR error: )
> Feature geometry not imported (OGR error: )
> Feature geometry not imported (OGR error: )
> *Only 3221 of 3234 features written.*
>
> Export Using PostGIS 2.0 Shapefile and DBF Loader Exporter
>
> *3221 record counts*
>
> Exporting with configuration: as_clean_test_ogr_export_utf8, public,
> as_clean_test_ogr_export_utf8
> Done (postgis major version: 2)
> Output shape: Polygon
> Table export completed.
>
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users
>

J. Gustavo
-- 
Jorge Gustavo Rocha
Departamento de Informática
Universidade do Minho
4710-057 Braga
Tel: +351 253604480
Fax: +351 253604471
Móvel: +351 910333888
skype: nabocudnosor