Postgres/PostGIS calculate area of Polygon in Square Miles

Question

Polygon in Postgres/PostGIS database. It has the spatial reference 4269 which is in degrees.

I know there is a ST_Area() to calculate it. this is my query to select the areas in the SRID units.

select st_area(geom) from counties;

I want to calculate the area in Square Miles and insert a column named Area with the correct Square Mileage for each county

*I am a new to SQL and Postgres

Answer 1

ST_Area will use the units in your coordinate system, which as you say are degrees. So you have two options:

• transform your geometries to a coordinate system that uses feet or meters
• cast your geometries as geographies. ST_Area will then perform the calculation in meters.

You then need to convert from square feet or square meters to square miles.

Your query would look something like this

ALTER TABLE counties ADD COLUMN area real;
UPDATE counties SET area = ST_Area(geom::geography) / 1609.34^2

• ziggy: awesome I will give this a try
• Petro Semeniuk: Saved a day. Thanks heaps @amball!