2

Is it possible to calculate area or centroid of the_geom in cartoDB?

It would not be a huge time saver, and I am doubtful that it is an option but figured I would ask.

  • It's definitely possible with SQL. Do you want to calculate it for a single row, or to create a new column with that information for each geometry in your table? – cholmes Jan 24 '14 at 23:52
  • Are you open to a PostGIS solution? – PolyGeo Jan 25 '14 at 0:25
  • sorry I am just now getting back to this. I'm open to PostGIS purely because I need to learn as much as I can about it. if you have a solution using PostGIS I'd love to have it on file. – user26190 Jan 30 '14 at 18:56
2

Using spatial SQL:

SELECT *, ST_Area(the_geom) FROM Table

more info

SELECT *, ST_AsText(ST_Centroid(the_geom)) FROM Table

more info

1

warrieka's answer is correct but there are other ways to do them that might be useful.

Area

Area calculations are going to be in whatever the projection of the geometry is though. So for area, distance, length type functions it can be a lot more useful to cast them as geography so your results are in meters (or square meters).

SELECT ST_Area(the_geom::geography) area_sqm FROM tablename

Center

Centroid of geometries are just as you expect. However, if you have interested shapes in your geoms, sometimes they can fall outside of your actual polygons! If you just want a point that is inside your geometry and similar to center, you can use PointOnSurface

SELECT ST_PointOnSurface(the_geom) the_geom FROM tablename
  • Andrew, is there a way to show lon and lat coordinates instead of the point for the centroid? – user26190 Feb 3 '14 at 20:25
  • also, is there a way to cast Area in feet rather than meters? a substitute command for "geography"? Thanks again! – user26190 Feb 3 '14 at 21:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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