Polygon area in PostgreSQL differs from QGIS-CodePudding

I downloaded OpenStreetMap data and uploaded to a local PostgreSQL database with osm2pgsql. I created a new table as a result of a ST_Union function with only one record (for now).

CREATE TABLE IF NOT EXISTS m_temp.budapest_merge
(
    id text COLLATE pg_catalog."default" NOT NULL,
    st_union geometry(Polygon,3857),
    ter double precision,
    CONSTRAINT budapest_merge_pkey PRIMARY KEY (id)
)

Then I checked the area of my polygon.

SELECT ST_Area(st_union) FROM m_temp.budapest_merge

The result was 1151156216.758265 sqm which was weird for me, because I was expecting something around 520 sqkm. I opened the PostgreSQL layer in QGIS, calculated the area with the Field Calculator tool's $area function. The result was 526031443.36087507 sqm, which corresponds to my expectation and reality as well.

My question is what can be a problem here? The QGIS project is in the same projection (EPSG:3857).

Also I know that naming a geometry column st_union is not really nice, but that's not the main concern here.

CodePudding user response：

In Qgis, $area is (if defined), the ellipsoidal area, i.e. the true ground area, while area(geometry) is the planar area, i.e. highly impacted by the geometry projection.

In Postgis, st_area always use the geometry projection, so it is the equialent of QGIS area(geometry).

That being said, 3857 highly distorts distances and areas as you move away from the equator. It is wrong to use this projection for computing distances or areas.