If it’s just Ireland, transform to a good Irish projection after casting to geometry. For maximum awsomeness, transform to gnomonic, in which great circles are represented as straight lines… then even all the introduced points will be at the correct geographic locations. Works for smaller areas, like Ireland.
P > On Jan 20, 2019, at 9:23 PM, Simon Greener <si...@spatialdbadvisor.com> wrote: > > 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 <http://www.spdba.com.au/> > E: si...@spdba.com.au <mailto: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 <mailto:postgis-users@lists.osgeo.org> > https://lists.osgeo.org/mailman/listinfo/postgis-users > <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
