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:[email protected]] On Behalf Of Jorge Gustavo Rocha Sent: Saturday, December 03, 2016 2:25 PM To: Ruth Simm <[email protected]>; PostGIS Users Discussion <[email protected]> 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 > [email protected] > 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 [email protected] http://lists.osgeo.org/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/mailman/listinfo/postgis-users
