[postgis-users] Geography/geometry

[Martin Davis]

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 <simon at 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: 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/8b3155dd/attachment.html>