[postgis-users] Geography/geometry

[Birgit Laggner]

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" <simon at spatialdbadvisor.com> 
An: "PostGIS Users Discussion" <postgis-users at 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: 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 

_______________________________________________ 
postgis-users mailing list 
postgis-users at lists.osgeo.org 
https://lists.osgeo.org/mailman/listinfo/postgis-users 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20190121/041a92ec/attachment.html>