[postgis-users] Geography/geometry

[Simon Greener]

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: simon at 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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20190121/07627071/attachment.html>