I am running into an issue with using the PostGIS ST_Area function against a dataset loaded with osm2pgsql.
Setup: PostgreSQL 11.2/PostGIS 2.5.1 osm2pgsql 0.96
When I attempt to run the ST_Area function against polygons stored in WGS1984 lat/long using Geometry(!) storage (so not the default Web Mercator mostly used in conjunction with osm2pgsql, but using the EPSG 4236, and also not using PostGIS Geography geometry storage), the following command fails:
with the error depicted below
This option should use the faster calculation mode based on a sphere instead of spheroid.
However, if I switch to using
which is also the default and uses a spheroid for the calculation instead of a simple sphere, it succeeds. I suspect this may be a bug I need to report, but does anyone else have remarks about why using a sphere might cause a failure versus a spheroid not?
- I have needed to make a major correction to this question, as I accidentally mixed up the spheroid and sphere options. Instead of failing with spheroid, it fails with sphere ("use_spheroid=false"). I have now edited both title and post to reflect this.
- user30184 is right that this dataset, which is the Geofabrik "Oceania" extract, borders the dateline. However, the objects in OSM are cut up along the dateline, but I do see a number of them lying against the dateline. These may cause the issue with the sphere based calculation, although i am still trying to wrap my head around why. It is hard to visualize this.
- Correcting winding order (if even needed because I suspect osm2pgsql already properly takes care of this) and testing for validity using a command like:
CASE WHEN ST_IsValid(ST_ForcePolygonCW(way)) THEN ST_Area(ST_ForcePolygonCW(way)::geography,true) ELSE NULL END AS area_calc
did not solve this issue.
Also replacing it with CCW instead of CW (I read somewhere that sometimes CCW is used for decimal degrees data), did not solve the issue. In both cases the errors remained the same as displayed in the image, so the faster sphere based calculation failed.