Simon, can you post one (or more) of the geographies which are being reported as invalid when you run *select distinct st_isvalidreason(a.geog4326::geometry) *?
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. On Sun, 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 <http://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 > <http://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 ArchitectureDatabase 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
