ST_Area yields total area encompased by a polygon. How do I get just the area covered by a polygon?

Question

Here's my dataset: https://aaron-bauman.carto.com/dataset/curbs_1

I want to calculate the area covered by the purple only. ST_Area seems to be yielding all the area within the purple as well because when I sum it up the value is greater than the entire area of Philadelphia.

How do I get the area of just the purple?

The dataset bunch of polygons outlining street surfaces. The polygons cover huge areas. Here's a small snippet of one polygon for example:

Here's the query i'm running:

select sum(shape__area) from public.curbs_1;

Answer 1

Geometries in CartoDB are by default stored with two different coordinate reference systems (CRS). the_geom has coordinates in Degree, while the_geom_webmercator has coordinates in units of meter or smaller depending on latitude. Both systems can be used world wide (Web Mercator excluding the pole areas), but both systems are not appropriate to measure distances or areas.

For these kind of calculations, you have to project the geometries into a CRS that is measured in meters. These projections are sufficient accurate only in a limited area on earth. As Philadelphia is located near 75°W, a usable CRS would be UTM zone 18N, which has EPSG code 32618. ST_Transform() does this conversion.

SELECT sum(st_area(st_transform(the_geom, 32618))) FROM public.curbs_1;

result: 52392349.3595709, thats around 52,4 mio m² or 52,4 km². The area of Philadelphia is around 370 km² according to Wikipedia.