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

Sun Dec 4 08:06:42 PST 2016

Ruth,

Following up on Jorge's answer.   I do recall we have some logic with the import and export routines that refuse to import or export invalid geometries.
I think the policy has changed over the years and you're running a fairly old install.

I forget what the condition is, but I know we spent a lot of time discussing it.

So the one you have below that has a geometry, can you check 

ST_IsValid(geom)  on the geometry you have below?  If it returns false, then as Jorge mentioned that's the problem.  You could try fixing with ST_MakeValid.

SELECT gid, iso3, last_updat, area_km, name, ST_IsValid(geom)
  FROM as_clean_test_ogr_export_utf8
  where name = 'Oakland';

--- Ruth's email --
Does anyone know If there are NULL geoms in a table (any table - such as the one created by this shapefile) will it invalidate geometry that was valid and present? 

Reason I ask is I see missing geometries in my shapefile exports that WERE present in the original shapefile and the PostGIS import. However on export they disappear. 

Below is a feature I cannot seem to export to shapefile when I try to round-trip this table...It has geom in PostGIS...however it 'disappears' and cannot be viewed in QGIS on export.

Ex:
SELECT gid, iso3, last_updat, area_km, name, geom
  FROM as_clean_test_ogr_export_utf8
  where name = 'Oakland';

3228;"USA";"2016-11-10";0;"Oakland";"0106000020E61000000100000001030000000100000008000000C3F5285C8FEA54C08104C58F3137454032E6AE25E4EB54C03108AC1C5A64454032E6AE25E4EB54C00F0BB5A6796F4540F0164850FCDC54C0E3C798BB9670454066F7E461A1C654C0B6847CD0B3714540F7065F984CC554C029ED0DBE3039454017D9CEF753E3 (...)"

-----Original Message-----
From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Jorge Gustavo Rocha
Sent: Saturday, December 03, 2016 2:25 PM
To: Ruth Simm <goldfish_and_muskie at yahoo.com>; PostGIS Users Discussion <postgis-users at lists.osgeo.org>
Subject: Re: [postgis-users] PostGIS export - data is inexplicably truncated from 3234 records to 3221 when using 'pgsql2shp', PGAdmin, AND QGIS 2.6+

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
_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users