<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 id="yui_3_16_0_ym19_1_1480793095284_37057">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? <br></div><div id="yui_3_16_0_ym19_1_1480793095284_37056"><br></div><div id="yui_3_16_0_ym19_1_1480793095284_36987" dir="ltr">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. <br></div><div dir="ltr"><br></div><div dir="ltr">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.<br></div><div dir="ltr" id="yui_3_16_0_ym19_1_1480793095284_37055"><br>Ex:</div><div dir="ltr" id="yui_3_16_0_ym19_1_1480793095284_38803"><br></div><div dir="ltr" id="yui_3_16_0_ym19_1_1480793095284_38811">SELECT gid, iso3, last_updat, area_km, name, geom<br id="yui_3_16_0_ym19_1_1480793095284_38819"> FROM as_clean_test_ogr_export_utf8<br id="yui_3_16_0_ym19_1_1480793095284_38820"> where name = 'Oakland';<br id="yui_3_16_0_ym19_1_1480793095284_38821"><br></div><div id="yui_3_16_0_ym19_1_1480793095284_36945" dir="ltr"><span id="yui_3_16_0_ym19_1_1480793095284_38901">3228;"USA";"2016-11-10";0;"Oakland";"0106000020E61000000100000001030000000100000008000000C3F5285C8FEA54C08104C58F3137454032E6AE25E4EB54C03108AC1C5A64454032E6AE25E4EB54C00F0BB5A6796F4540F0164850FCDC54C0E3C798BB9670454066F7E461A1C654C0B6847CD0B3714540F7065F984CC554C029ED0DBE3039454017D9CEF753E3 (...)"</span></div><div dir="ltr" id="yui_3_16_0_ym19_1_1480793095284_38902"><span id="yui_3_16_0_ym19_1_1480793095284_38901"><br></span></div><div dir="ltr" id="yui_3_16_0_ym19_1_1480793095284_38986"><span id="yui_3_16_0_ym19_1_1480793095284_38901"></span></div> <div class="qtdSeparateBR"><br><br></div><div class="yahoo_quoted" style="display: block;"> <div style="font-family: HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif; font-size: 16px;"> <div style="font-family: HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif; font-size: 16px;"> <div dir="ltr"><font size="2" face="Arial"> On Saturday, December 3, 2016 11:57 AM, Ruth Simm <goldfish_and_muskie@yahoo.com> wrote:<br></font></div> <br><br> <div class="y_msg_container"><div id="yiv3967456181"><div><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 clear="none"></div><div>I ran this on the shapefile once loaded in Postgres, to look at the data:</div><div id="yiv3967456181yui_3_16_0_ym19_1_1480793095284_32572"><br clear="none"></div><div dir="ltr" id="yiv3967456181yui_3_16_0_ym19_1_1480793095284_32570">SELECT gid, iso3, last_updat, area_km, name, ST_AsText(geom)<br id="yiv3967456181yui_3_16_0_ym19_1_1480793095284_32566" clear="none"> FROM as_clean_test_ogr_export_utf8<br id="yiv3967456181yui_3_16_0_ym19_1_1480793095284_32567" clear="none"> where name = 'Eastern'<br id="yiv3967456181yui_3_16_0_ym19_1_1480793095284_32568" clear="none"> and geom is NULL;<br id="yiv3967456181yui_3_16_0_ym19_1_1480793095284_32569" clear="none"></div><div dir="ltr" id="yiv3967456181yui_3_16_0_ym19_1_1480793095284_32571"><br clear="none"></div><div dir="ltr" id="yiv3967456181yui_3_16_0_ym19_1_1480793095284_32623">759;"USA";"2016-11-10";0;"Eastern";""<br clear="none"></div><div id="yiv3967456181yui_3_16_0_ym19_1_1480793095284_32632"><span></span></div> <div class="yiv3967456181qtdSeparateBR" id="yiv3967456181yui_3_16_0_ym19_1_1480793095284_33011"><div id="yiv3967456181yui_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 clear="none"></div><div>Thank you! I'm on the right path because of you!</div><div><br clear="none"></div><div>Ruth<br clear="none"></div><br clear="none"></div><div class="yiv3967456181yqt9265928126" id="yiv3967456181yqt99691"><div class="yiv3967456181yahoo_quoted" id="yiv3967456181yui_3_16_0_ym19_1_1480793095284_33085" style="display:block;"> <div id="yiv3967456181yui_3_16_0_ym19_1_1480793095284_33084" style="font-family:HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif;font-size:16px;"> <div id="yiv3967456181yui_3_16_0_ym19_1_1480793095284_33083" style="font-family:HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif;font-size:16px;"> <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 clear="none"></font></div> <br clear="none"><br clear="none"> <div class="yiv3967456181y_msg_container" id="yiv3967456181yui_3_16_0_ym19_1_1480793095284_33082"><div id="yiv3967456181"><div id="yiv3967456181yui_3_16_0_ym19_1_1480793095284_33081"><div id="yiv3967456181yui_3_16_0_ym19_1_1480793095284_33080" style="color:#000;background-color:#fff;font-family:HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif;font-size:16px;"><div>Interesting...that seems to add up. <br clear="none"></div><div><br clear="none"></div><div id="yiv3967456181yui_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="yiv3967456181qtdSeparateBR" id="yiv3967456181yui_3_16_0_ym19_1_1480793095284_33086"><br clear="none"><br clear="none"></div><div class="yiv3967456181yqt1430337685" id="yiv3967456181yqt61508"><div class="yiv3967456181yahoo_quoted" id="yiv3967456181yui_3_16_0_ym19_1_1480793095284_33091" style="display:block;"> <div id="yiv3967456181yui_3_16_0_ym19_1_1480793095284_33090" style="font-family:HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif;font-size:16px;"> <div id="yiv3967456181yui_3_16_0_ym19_1_1480793095284_33089" style="font-family:HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif;font-size:16px;"> <div dir="ltr" id="yiv3967456181yui_3_16_0_ym19_1_1480793095284_33088"><font id="yiv3967456181yui_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="yiv3967456181y_msg_container" id="yiv3967456181yui_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="yiv3967456181yqt8476231068" id="yiv3967456181yqtfd01263"><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 clear="none"><br clear="none"></div> </div> </div> </div></div></div></div></div><br><br></div> </div> </div> </div></div></body></html>