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.
2
-
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
SELECT *, ST_AsText(ST_Centroid(the_geom)) FROM Table
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
