# PostGIS: calculate area of intersection of EPSG:27700 polygons?

I have two tables in Postgres, both of type `geometry(MultiPolygon,27700)` and I would like to find any that intersect with an area of more than 100 square metres.

I know how to get any that intersect above an arbitrary threshold:

``````SELECT *
FROM aTable a
JOIN bTable b
WHERE ST_Area(ST_Intersection(a.geom, b.geom)) > 1;
``````

However, the area returned here is in (I think) decimal degrees. How can I ensure it is always more than 100 square metres?

Perhaps if I do something like:

``````WHERE ST_Area(ST_Transform(ST_Intersection(i.wkb_geometry, w.wkb_geometry), 3857)) > 100
``````

that would work, since the unit of EPSG:3857 is metres?

I've read this and this but I don't know what projection to pick.

St_area returns cartesian area for geometries and geodesic areas for geographies.

If your coordinate system is in lat/lan, simply type cast your geometry to geography and your result will be in sq meters

`ST_Area(ST_Intersection(a.geom, b.geom)::geography)`

EDIT: For projected data, st_area() will always return areas in the linear unit of the projection itself. EPSG 27700 is the British National Grid projection. It is a Transverse Mercator projection based on the OSGB36 datum. Its linear unit is meter. More info here.

The function

`ST_Area(ST_Intersection(a.geom, b.geom))`

should give you directly an area in sq meters. If you want to double check, test this

`ST_Area(ST_Transform(ST_Intersection(a.geom, b.geom),4326)::geography)`

This transforms your coordinate from BNG to WGS84 and calculates the geodesic area. Results should be somewhat similar to the simple st_area() call above.

• Thanks! Unfortunately I get `ERROR 1: ERROR: Only lon/lat coordinate systems are supported in geography.`. Table a has geometry of type `geometry(Polygon,27700)` and table b has geometry of type `geometry(MultiPolygon,27700)`. Commented Jan 14, 2018 at 3:06
• Maybe I need to transform the intersection before casting it to geography? `ST_Area(ST_Transform(ST_Intersection(a.geom, b.geom), 3857)::geography)` -- nope, that gives the same error. Commented Jan 14, 2018 at 3:10
• Sorry, I wasn't familiar with the British National Grid and assumed from your question that it was in lat/lon. It is a Transverse Mercator projection and its linear unit is in meters. a simple st_area() call should do. I edited my answer as well. Commented Jan 14, 2018 at 4:31