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 <[email protected]>
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: [email protected]
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
[email protected]
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: [email protected]
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
[email protected]
https://lists.osgeo.org/mailman/listinfo/postgis-users
