[GIS] How to calculate area or centroid of the_geom in CartoDB

cartopostgis

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.

Best Answer

Using spatial SQL:

SELECT *, ST_Area(the_geom) FROM Table

more info

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

more info

Related Solutions

[GIS] CartoDB Convert from the_geom to google’s lat long

you could use CartoDB SQL API to get that data

http://USER.cartodb.com/api/v1/sql?q=select st_y(the_geom) as lat, st_x(the_geom) as lon from TABLE

for example, using jQuery ajax:

$.get("http://USER.cartodb.com/api/v1/sql?q=select st_y(the_geom) as lat, st_x(the_geom) as lon from TABLE", function(data) {
  for(var i = 0; i < data.rows.length; ++i) {
     var row = data.rows[i];
     console.log("point", row.lat, row.lon);
  }
});

[GIS] Centroid of Points using postgis

Your SQL query is missing one function. Add ST_GeomFromText and it will work.

SELECT ST_AsText(ST_Centroid(ST_GeomFromText(('MULTIPOINT ( -1 0, -1 2, -1 3, -1 4, -1 7, 0 1, 0 3, 1 1, 2 0, 6 0, 7 8, 9 8, 10 6 )'))));

Result:

POINT(2.307692 3.307692)

If you have a point table "mypoints" the corresponding query is

select ST_AsText(ST_Centroid(ST_Union(geometry)))
from mypoints;

ST_Union builds a multipoint feature from your points, ST_Centroid finds the centroid and ST_AsText shows the result as WKT. Add WHERE if you want to select just some points from the table to centroid calculation.

Best Answer

Related Solutions

[GIS] CartoDB Convert from the_geom to google’s lat long

[GIS] Centroid of Points using postgis

Related Question