[postgis-users] Geography/geometry

[Simon Greener]

The shapefile (from OSM) can be accessed here:

https://www.dropbox.com/s/jbptp3ycmc8cqzi/gis_osm_county_a_free_1.rar?dl=0

The command used to load is:

ogr2ogr -overwrite -progress -skipfailures -a_srs "EPSG:4326" ^
   -f "PostgreSQL" PG:"dbname='gisdb' host='localhost' port='5432' user='postgres'  ^
   gis_osm_county_a_free_1.shp -nln data.osm_county -nlt POLYGON ^
-lco LAUNDER=YES -lco GEOMETRY_NAME=geog4326 -lco GEOM_TYPE=geography -lco FID=ID -lco DIM=2 -lco SPATIAL_INDEX=YES

Am I missing something with the ogr2ogr command for the load?

When a geography is casted to geometry for use in the aggregate form of ST_Union (or other commands such as ST_Within) is the processing done assuming the data is projected even when geodetic?

regards
Simon

> 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,
>>       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



-- 
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/20190122/be6f0230/attachment.html>