<html><head></head><body><div style="color:#000; background-color:#fff; font-family:HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif;font-size:16px"><div>Jorge-</div><div><br></div><div>I ran this on the shapefile once loaded in Postgres, to look at the data:</div><div id="yui_3_16_0_ym19_1_1480793095284_32572"><br></div><div dir="ltr" id="yui_3_16_0_ym19_1_1480793095284_32570">SELECT gid, iso3, last_updat, area_km, name, ST_AsText(geom)<br id="yui_3_16_0_ym19_1_1480793095284_32566"> FROM as_clean_test_ogr_export_utf8<br id="yui_3_16_0_ym19_1_1480793095284_32567"> where name = 'Eastern'<br id="yui_3_16_0_ym19_1_1480793095284_32568"> and geom is NULL;<br id="yui_3_16_0_ym19_1_1480793095284_32569"></div><div dir="ltr" id="yui_3_16_0_ym19_1_1480793095284_32571"><br></div><div dir="ltr" id="yui_3_16_0_ym19_1_1480793095284_32623">759;"USA";"2016-11-10";0;"Eastern";""<br></div><div id="yui_3_16_0_ym19_1_1480793095284_32632"><span></span></div> <div class="qtdSeparateBR" id="yui_3_16_0_ym19_1_1480793095284_33011"><div id="yui_3_16_0_ym19_1_1480793095284_33052">So...you are definitely right - the export fails because there is null geoms! On import it looks like a record is created leaving the geom null..but after it fails.</div><div><br></div><div>Thank you! I'm on the right path because of you!</div><div><br></div><div>Ruth<br></div><br></div><div class="yahoo_quoted" id="yui_3_16_0_ym19_1_1480793095284_33085" style="display: block;"> <div style="font-family: HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif; font-size: 16px;" id="yui_3_16_0_ym19_1_1480793095284_33084"> <div style="font-family: HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif; font-size: 16px;" id="yui_3_16_0_ym19_1_1480793095284_33083"> <div dir="ltr"><font size="2" face="Arial"> On Saturday, December 3, 2016 11:39 AM, Ruth Simm <goldfish_and_muskie@yahoo.com> wrote:<br></font></div> <br><br> <div class="y_msg_container" id="yui_3_16_0_ym19_1_1480793095284_33082"><div id="yiv0757187786"><div id="yui_3_16_0_ym19_1_1480793095284_33081"><div style="color:#000;background-color:#fff;font-family:HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif;font-size:16px;" id="yui_3_16_0_ym19_1_1480793095284_33080"><div>Interesting...that seems to add up. <br clear="none"></div><div><br clear="none"></div><div id="yui_3_16_0_ym19_1_1480793095284_33079">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.<br clear="none"></div><div><span></span></div> <div class="yiv0757187786qtdSeparateBR" id="yui_3_16_0_ym19_1_1480793095284_33086"><br clear="none"><br clear="none"></div><div class="yiv0757187786yqt1430337685" id="yiv0757187786yqt61508"><div class="yiv0757187786yahoo_quoted" style="display:block;" id="yui_3_16_0_ym19_1_1480793095284_33091"> <div style="font-family:HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif;font-size:16px;" id="yui_3_16_0_ym19_1_1480793095284_33090"> <div style="font-family:HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif;font-size:16px;" id="yui_3_16_0_ym19_1_1480793095284_33089"> <div dir="ltr" id="yui_3_16_0_ym19_1_1480793095284_33088"><font id="yui_3_16_0_ym19_1_1480793095284_33087" size="2" face="Arial"> On Saturday, December 3, 2016 11:33 AM, Jorge Gustavo Rocha <jgr@di.uminho.pt> wrote:<br clear="none"></font></div> <br clear="none"><br clear="none"> <div class="yiv0757187786y_msg_container" id="yui_3_16_0_ym19_1_1480793095284_33092">Hi Ruth,<br clear="none"><br clear="none">The records that are missing are the ones with NULL geometry.<br clear="none">You have: 3234 records; 13 don't have geometry = 3221 records with geometry.<br clear="none"><br clear="none">Regards,<br clear="none"><br clear="none">Jorge<br clear="none"><div class="yiv0757187786yqt8476231068" id="yiv0757187786yqtfd01263"><br clear="none">Às 19:25 de 03-12-2016, Jorge Gustavo Rocha escreveu:<br clear="none">> Hi Ruth,<br clear="none">><br clear="none">> I've imported your data from shp to postgis, with ogr2ogr, like this:<br clear="none">> ogr2ogr -overwrite -f "PostgreSQL" PG:"host=localhost user=geobox<br clear="none">> dbname=geotuga password=geobox" -a_srs EPSG:4326 -nlt MULTIPOLYGON -nln<br clear="none">> usa as_clean_test_ogr_export_utf8.shp<br clear="none">><br clear="none">> Then, I run the following query:<br clear="none">><br clear="none">> select count(*), st_isvalid(wkb_geometry) from usa<br clear="none">> group by st_isvalid(wkb_geometry);<br clear="none">><br clear="none">> The results is:<br clear="none">><br clear="none">> count | st_isvalid<br clear="none">> -------+------------<br clear="none">> 13 |<br clear="none">> 3218 | t<br clear="none">> 3 | f<br clear="none">><br clear="none">> So, I would say that you have invalid geometries that are causing this<br clear="none">> problem.<br clear="none">><br clear="none">> I hope it helps.<br clear="none">><br clear="none">> Regards,<br clear="none">><br clear="none">> Jorge<br clear="none">><br clear="none">> Às 18:44 de 03-12-2016, Ruth Simm escreveu:<br clear="none">>> Hi,<br clear="none">>><br clear="none">>> I'm having a serious data export problem with the PGAdmin widget and<br clear="none">>> pgsql2shp (QGIS too). The problem lies within converting a PostGIS table<br clear="none">>> to shapefile. *I put in 3234 feature counts...and I get back 3221.*<br clear="none">>><br clear="none">>> I get limited information from the error reports (see below...QGIS gives<br clear="none">>> me the most information...but it is minimal). The errors (when<br clear="none">>> available) reference ogr....but not much else. Ironically, the only way<br clear="none">>> I've found to actually export the *full* record set is by using ogr2ogr.<br clear="none">>><br clear="none">>> I'm not sure why round-tripping with the PGAdmin tool is failing so bad.<br clear="none">>> It seems to be able to import the initial 3234 - but cannot export the<br clear="none">>> full set.<br clear="none">>><br clear="none">>> I do notice the Polygon/Multipolygon converting happening...but seeing<br clear="none">>> that many of the tables "Multipolygons" are just single Polygons added<br clear="none">>> into the Multipolygon bucket...I'm not sure if that has anything to do<br clear="none">>> with it.<br clear="none">>><br clear="none">>> Very perplexed. If anybody can pull my dataset and repo this (or tell me<br clear="none">>> how they got around it - using anything BUT ogr2ogr) I'd appreciate<br clear="none">>> it!!!!<br clear="none">>><br clear="none">>> Here are my specs:<br clear="none">>><br clear="none">>> PostgreSQL 9.2.4 (on a Linux server)<br clear="none">>> pgsql2shp 2.1.1<br clear="none">>> PostGIS 2.0 Shapefile and DBF Loader Exporter (PGAdmin tool)<br clear="none">>> QGIS 2.6.0 - Brighton (on windows...we've tested using more up-to-date<br clear="none">>> versions too)<br clear="none">>><br clear="none">>> Here is the data <<a rel="nofollow" shape="rect" target="_blank" href="https://spaces.hightail.com/space/ihoE7">https://spaces.hightail.com/space/ihoE7</a>> (2 MB<br clear="none">>> unzipped on a cloud drive).<br clear="none">>><br clear="none">>> Here is what I do...<br clear="none">>><br clear="none">>> 1) Load the shapefile into Postgres using the PGAdmin tool (works<br clear="none">>> fine...notably it is loaded as a MULTIPOLYGON[2] EVEN THOUGH the<br clear="none">>> Shapefile type is Polygon)<br clear="none">>><br clear="none">>> *3234 record counts*<br clear="none">>><br clear="none">>> Importing with configuration: as_clean_test_ogr_export_utf8, public,<br clear="none">>> geom, H:\as_clean_test_ogr_export_utf8.shp, mode=c, dump=1, simple=0,<br clear="none">>> geography=0, index=1, shape=1, srid=4326<br clear="none">>> Shapefile type: Polygon<br clear="none">>> PostGIS type: MULTIPOLYGON[2]<br clear="none">>> Shapefile import completed.<br clear="none">>> Connecting: host=pgsqlgis-repos port=5432 user=local-dev<br clear="none">>> password='*********'<br clear="none">>><br clear="none">>> 2) Try to export the table tp shapefile... (this doesn't work for the<br clear="none">>> following..and notably they are all trying to export it as a Polygon)<br clear="none">>><br clear="none">>> UPON Export (using >pgsql2shp)<br clear="none">>><br clear="none">>> H:\>pgsql2shp -f E:/as_clean_test_ogr_export_utf8.shp -h <hostname> -u<br clear="none">>> <username> -p <port> -P <password> <postgrestable><br clear="none">>> "as_clean_test_ogr_export_utf8"<br clear="none">>><br clear="none">>> Initializing...<br clear="none">>> Done (postgis major version: 2).<br clear="none">>> Output shape: Polygon<br clear="none">>> Dumping: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX*[3221 rows]*.<br clear="none">>><br clear="none">>> QGIS (Save As in QGIS 2.6.0)<br clear="none">>><br clear="none">>> Export to vector file failed.<br clear="none">>> Error: Feature write errors:<br clear="none">>> Feature geometry not imported (OGR error: )<br clear="none">>> Feature geometry not imported (OGR error: )<br clear="none">>> Feature geometry not imported (OGR error: )<br clear="none">>> Feature geometry not imported (OGR error: )<br clear="none">>> Feature geometry not imported (OGR error: )<br clear="none">>> Feature geometry not imported (OGR error: )<br clear="none">>> Feature geometry not imported (OGR error: )<br clear="none">>> Feature geometry not imported (OGR error: )<br clear="none">>> Feature geometry not imported (OGR error: )<br clear="none">>> Feature geometry not imported (OGR error: )<br clear="none">>> Feature geometry not imported (OGR error: )<br clear="none">>> Feature geometry not imported (OGR error: )<br clear="none">>> Feature geometry not imported (OGR error: )<br clear="none">>> *Only 3221 of 3234 features written.*<br clear="none">>><br clear="none">>> Export Using PostGIS 2.0 Shapefile and DBF Loader Exporter<br clear="none">>><br clear="none">>> *3221 record counts*<br clear="none">>><br clear="none">>> Exporting with configuration: as_clean_test_ogr_export_utf8, public,<br clear="none">>> as_clean_test_ogr_export_utf8<br clear="none">>> Done (postgis major version: 2)<br clear="none">>> Output shape: Polygon<br clear="none">>> Table export completed.<br clear="none">>><br clear="none">>><br clear="none">>><br clear="none">>><br clear="none">>> _______________________________________________<br clear="none">>> postgis-users mailing list<br clear="none">>> <a rel="nofollow" shape="rect" ymailto="mailto:postgis-users@lists.osgeo.org" target="_blank" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br clear="none">>> <a rel="nofollow" shape="rect" target="_blank" href="http://lists.osgeo.org/mailman/listinfo/postgis-users">http://lists.osgeo.org/mailman/listinfo/postgis-users</a><br clear="none">>><br clear="none">><br clear="none">> J. Gustavo<br clear="none"><br clear="none">J. Gustavo<br clear="none">-- <br clear="none">Jorge Gustavo Rocha<br clear="none">Departamento de Informática<br clear="none">Universidade do Minho<br clear="none">4710-057 Braga<br clear="none">Tel: +351 253604480<br clear="none">Fax: +351 253604471<br clear="none">Móvel: +351 910333888<br clear="none">skype: nabocudnosor<br clear="none"></div><br clear="none"><br clear="none"></div> </div> </div> </div></div></div></div></div><br><br></div> </div> </div> </div></div></body></html>