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

[Ruth Simm]

Interesting...that seems to add up. 

How do you query out NULL geom records though? Since the geom did import (with no NULLS in the initial shapefile) 3234 - I'm not sure how/why the geom was dropped on the export.
 

    On Saturday, December 3, 2016 11:33 AM, Jorge Gustavo Rocha <jgr at di.uminho.pt> wrote:
 

 Hi Ruth,

The records that are missing are the ones with NULL geometry.
You have: 3234 records; 13 don't have geometry = 3221 records with geometry.

Regards,

Jorge

Às 19:25 de 03-12-2016, Jorge Gustavo Rocha escreveu:
> 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

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


   
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20161203/2821ba58/attachment.html>