<div dir="ltr">Simon, can you post one (or more) of the geographies which are being reported as invalid when you run
<strong>select distinct st_isvalidreason(a.geog4326::geometry) </strong>?<div><br></div><div>It's certainly possible for reprojection to introduce invalidities, due to geodesic/straight line issue. I'm actually surprised this doesn't show up more often as an issue.</div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Sun, Jan 20, 2019 at 9:23 PM Simon Greener <<a href="mailto:simon@spatialdbadvisor.com">simon@spatialdbadvisor.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><u></u>
<div><div>Folks,</div><div><br></div><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><div><br></div><div>Now, because ST_Union or ST_Collect do not support geography, I cast to geometry before calling.</div><div><br><font face="'Courier New',monospace"><strong>select min(a.osm_id) as osm_id, <br> <a href="http://a.name" target="_blank">a.name</a>,<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 href="http://a.name" target="_blank">a.name</a>;</strong></font></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><div><br></div><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="gmail-m_5658254532139036195M2Signature"><div><div>Regards<br></div><div><font face="Kunstler Script"><big><big><big><big>Simon</big></big></big></big></font><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: <a href="http://www.spdba.com.au" target="_blank">www.spdba.com.au</a><br>E: <a href="mailto:simon@spdba.com.au" target="_blank">simon@spdba.com.au</a><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></div>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></blockquote></div>