Problem with ST_AREA regarding coordinate system

Question

I want to select all polygons with area more than 350 square kilometers from my initial polygons and tried ST_Area. But my polygons are in WKID: 25832 (UTM 32N) and I read that ST_Area always calculates in WGS 84 (EPSG 4326)....

I tried it like this but did not work.

DROP TABLE IF EXISTS projekt."Final_Selection";
CREATE TABLE projekt."Final_Selection" AS
SELECT (st_area/1000000), geom 
FROM projekt."New_Pot" WHERE st_area>350 ;

and like this:

DROP TABLE IF EXISTS projekt."Final_Selection";
CREATE TABLE projekt."Final_Selection" AS
SELECT (st_area(ST_Transform(geom,25832)))/10000000, geom  
FROM projekt."New_Pot" WHERE st_area>350;

Does anyone have an advice for me? Thx in advance!

Answer 1

If you want to select geometries by their size in sqm use ST_Area with geography instead of geometry:

For geography types by default area is determined on a spheroid with units in square meters.

SELECT * FROM projekt."New_Pot"
WHERE ST_Area(ST_Transform(geom,4326)::geography) >= 350000

Note: keep in mind that using ST_Area in the WHERE clause might slow down your query significantly, as the area is being calculated in query time. If it isn't a one time query, consider creating an index with the area pre-calculated, e.g.

CREATE INDEX idx_geom ON projekt."New_Pot" (ST_Area(ST_Transform(geom,4326)::geography));

See also: Calculate the percentage of polygon inside the buffer in postgresql

In case you want to query polygons within a given radius: the easiest way would be to convert your geometry column to geography and from there use ST_DWithin, which will calculate the distance in metres when used with geography parameters:

SELECT * FROM projekt."New_Pot"
WHERE 
  ST_DWithin(
      ST_Transform(geom,4326)::geography, -- your geometry converted to geography
      ST_MakePoint(1,52)::geography,      -- a given point
      350000)                             -- a 350km buffer

Related post: Getting all Buildings in range of 5 miles from specified coordinates