<html><body><div id="zimbraEditorContainer" style="font-family: arial, helvetica, sans-serif; font-size: 12pt; color: #000000" class="2"><div><style>body { font-family:'Times New Roman'; font-size:13px}</style></div><div>Hi Simon,<br></div><div><br data-mce-bogus="1"></div><div>I assume, you are trying to get this all done in one query, right? Did you try to throw ST_MakeValid into the mix? Like: ST_Transform(ST_Union(ST_MakeValid(ST_Transform(a.geog4326::geometry, 3857))), 4326)::geography<br data-mce-bogus="1"></div><div><br data-mce-bogus="1"></div><div>Regards,<br data-mce-bogus="1"></div><div>Birgit<br data-mce-bogus="1"></div><div><br data-mce-bogus="1"></div><div><br data-mce-bogus="1"></div><div><br></div><hr id="zwchr" data-marker="__DIVIDER__"><div data-marker="__HEADERS__"><b>Von: </b>"Simon Greener" <simon@spatialdbadvisor.com><br><b>An: </b>"PostGIS Users Discussion" <postgis-users@lists.osgeo.org><br><b>Gesendet: </b>Montag, 21. Januar 2019 06:23:10<br><b>Betreff: </b>[postgis-users] Geography/geometry<br></div><div><br></div><div data-marker="__QUOTED_TEXT__">
<div>Folks,</div><br><div>I'm in a situation where I need to ST_Union or ST_Collect some osm_county Polygon (not MultiPolygon) data for Ireland.</div><br><div>Now, because ST_Union or ST_Collect do not support geography, I cast to geometry before calling.</div><div><br><span data-mce-style="font-family: 'Courier New', monospace;" style="font-family: 'Courier New', monospace;" face="'Courier New',monospace"><strong>select min(a.osm_id) as osm_id, <br> a.name,<br> count(*) as parts, <br> ST_Union(a.geog4326::geometry)::geography as geog4326 -- or ST_Collect<br> from data.osm_county as a<br> group by a.name;</strong></span></div><div><strong><br></strong></div><div><big>Whence I get this:</big></div><div><strong><br> ERROR: lwgeom_area_spher(oid) returned area < 0.0<br> </strong></div><div><strong><br></strong></div><div><big>Investigating I get results like this:</big></div><div><strong><br>select distinct st_isvalidreason(a.geog4326::geometry) from data.osm_county as a;</strong></div><div><strong><br>"Hole lies outside shell[-10.2589459 53.9746452]"<br>etc</strong></div><div><strong><br></strong><big>I guess this is expected because geodetic lines in the source geography are being treated as straight in the cast'd geometry.</big><strong><br><br></strong><big>If I use ST_Transform to project a 4326 poly to a 3857 and then call the ST_Union aggregate, or identify a single geography that has the invalidity and execute a self-union, I get the following in both situations.</big><strong><br><br> ERROR: GEOSUnaryUnion: TopologyException: Input geom 0 is invalid: Hole lies outside shell at or near point -1148162.9982628345 7095296.1166736316 at -1148162.9982628345 7095296.1166736316<br><br></strong>I can't for the life of me work out how to complete the aggregated ST_Union on the 4326 geography data.<strong><br></strong></div><br><div>Anyone point out what I am doing wrong or give me a pointer to what I can do to achieve the aggregated union?</div><div> </div><div id="M2Signature"><div><div>Regards<br></div><div><span data-mce-style="font-family: Kunstler Script;" style="font-family: Kunstler Script;" face="Kunstler Script"><big><big><big><big>Simon</big></big></big></big></span><em><strong><br></strong></em></div><div><em><strong>--------------------------------------------------------------------------------------------------------<br></strong></em></div><div><em><strong>Spatial Advice & Solutions Architecture<br>Database Spatial Stored Procedure Designer</strong></em><br>Oracle Spatial, SQL Server, PostGIS, MySQL, ArcSDE FME<br>Awarded "2011 Oracle Spatial Excellence Award for Education and Research"<br>A: 39 Cliff View Drive, Allens Rivulet, 7150, Tas, Aust<br>W: www.spdba.com.au<br>E: simon@spdba.com.au<br>V: +61 <strong>362 396 397</strong><br>M: +61 <strong>418 396 391</strong><br>GITC Supplier: T1005<br>Skype: sggreener<br>Long: 147.20515 (147° 12' 18" E)<br>Lat: -43.01530 (43° 00' 55" S)<br>GeoHash: r22em9r98wg<br>NAC:W80CK 7SWP3</div></div></div><br>_______________________________________________<br>postgis-users mailing list<br>postgis-users@lists.osgeo.org<br>https://lists.osgeo.org/mailman/listinfo/postgis-users<br></div></div></body></html>