Hi Simon, 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
Regards, Birgit Von: "Simon Greener" <si...@spatialdbadvisor.com> An: "PostGIS Users Discussion" <postgis-users@lists.osgeo.org> Gesendet: Montag, 21. Januar 2019 06:23:10 Betreff: [postgis-users] Geography/geometry Folks, I'm in a situation where I need to ST_Union or ST_Collect some osm_county Polygon (not MultiPolygon) data for Ireland. Now, because ST_Union or ST_Collect do not support geography, I cast to geometry before calling. select min(a.osm_id) as osm_id, a.name, count(*) as parts, ST_Union(a.geog4326::geometry)::geography as geog4326 -- or ST_Collect from data.osm_county as a group by a.name; Whence I get this: ERROR: lwgeom_area_spher(oid) returned area < 0.0 Investigating I get results like this: select distinct st_isvalidreason(a.geog4326::geometry) from data.osm_county as a; "Hole lies outside shell[-10.2589459 53.9746452]" etc I guess this is expected because geodetic lines in the source geography are being treated as straight in the cast'd geometry. 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. 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 I can't for the life of me work out how to complete the aggregated ST_Union on the 4326 geography data. Anyone point out what I am doing wrong or give me a pointer to what I can do to achieve the aggregated union? Regards Simon -------------------------------------------------------------------------------------------------------- Spatial Advice & Solutions Architecture Database Spatial Stored Procedure Designer Oracle Spatial, SQL Server, PostGIS, MySQL, ArcSDE FME Awarded "2011 Oracle Spatial Excellence Award for Education and Research" A: 39 Cliff View Drive, Allens Rivulet, 7150, Tas, Aust W: www.spdba.com.au E: si...@spdba.com.au V: +61 362 396 397 M: +61 418 396 391 GITC Supplier: T1005 Skype: sggreener Long: 147.20515 (147° 12' 18" E) Lat: -43.01530 (43° 00' 55" S) GeoHash: r22em9r98wg NAC:W80CK 7SWP3 _______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
_______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
