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:

map, with streets covered by purple polygons

Here's the query i'm running:

select sum(shape__area) from public.curbs_1;
  • 4
    Please post the actual sql you are running. It might be due to the coordinate system you are using, but it is hard to know from a picture. – John Powell Sep 19 '18 at 15:26

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.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.