PostGIS geography returns area < 0.0

Question

I am trying to convert a geometry column to a geography column and calculate the area using this query:

SELECT dccode,SUM(CAST(St_Area(geom::geography,true) AS NUMERIC)) as Area 
       FROM  schema.table   
       GROUP BY dccode

but I am getting a strange error:

...
NOTICE:  Coordinate values were coerced into range [-180 -90, 180 90] for GEOGRAPHY
NOTICE:  Coordinate values were coerced into range [-180 -90, 180 90] for GEOGRAPHY
NOTICE:  Coordinate values were coerced into range [-180 -90, 180 90] for GEOGRAPHY
NOTICE:  Coordinate values were coerced into range [-180 -90, 180 90] for GEOGRAPHY
NOTICE:  Coordinate values were coerced into range [-180 -90, 180 90] for GEOGRAPHY


ERROR:  lwgeom_area_spher(oid) returned area < 0.0
********** Error **********

ERROR: lwgeom_area_spher(oid) returned area < 0.0

I've run this query to verify that there is no invalid geometry:

select gid, dccode, st_isvalidreason(geom) from table.schema where st_isvalid(geom) = false;

but it doesn't return anything. How can I isolate the geometry that is < 0.0?

Answer 1

I encountered this issue with geometries that spanned the antimeridian, but which were brought into PostGIS with SRS 4326. Casting to the Geography type seemed to introduce some issue with vertex ordering, although I'm not sure about the exact nature of this issue. Projecting to an appropriate antimeridian-spanning CRS for my region of interest before calculating the area addressed the issue. So: ST_Area(ST_Transform(geom, 3851)), using https://epsg.io/3851