2

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:

ST_Area(way::geography,false)

with the error depicted below

enter image description here

This option should use the faster calculation mode based on a sphere instead of spheroid.

However, if I switch to using

ST_Area(way::geography,true)

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?

EDIT :

  • 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.

  • 1
    Issues like this arise often from wrong winding order of polygon rings or geometries which are crossing the 180° meridian. Or then the geometry has rather few vertices and projecting the geometry on spheroid creates self-intersection into the geometry. Try to capture the geometry that triggers the error. – user30184 Feb 20 at 13:54
  • My bad, I actually mixed up spheroid and sphere options in my code. It is failing with the "sphere" option instead of "spheroid". See my edits to the original post, that I have updated to reflect the real situation. – Marco_B Feb 20 at 18:30
  • @user30184, do you have a suggestion how to "capture the geometry that triggers the error"? Since ST_IsValid is failing to catch this based on the test I did with the SQL CASE statement I posted, and therefor likely something like ST_IsValidReason as well, is there any other way to find out exactly which record is to blame? – Marco_B Feb 20 at 19:27
  • There must be more clever ways but I would have a try with "create table area_test as select id, ST_Area(way::geography, false) as area from … order by id". I guess that the operation stops prematurely and the suspicious id is next to the last id you can find from area_test. – user30184 Feb 20 at 21:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.