3

In my postgres database i have a set of points(lat, long) and i created the_geom index for that. Now i have to find the centroid of these points.

I Google a lot and i find that for closed objects(polygon etc) it possible to find the centroid. I am not sure how to find the centroid of points using postgis.

Also i found this:

SELECT ST_AsText(ST_Centroid('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 )'));

But i didn't get any thing from this.

EDIT:

My requirement is:

Consider i have 5 places latlong, and i have to find which is center of this 5 places.

Help me to solve this. Thanks in advance.

7

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.

  • See the edited answer. – user30184 Jun 2 '14 at 13:47
  • ST_Centroid calculates the centre of mass which is the weighted average of the points en.wikipedia.org/wiki/Center_of_mass and en.wikipedia.org/wiki/Centroid – user30184 Jun 2 '14 at 14:10
  • My requirement is: Consider i have 5 places latlong, and i have to find which is center of this 5 places. This is my exact requirement.. Please Guide me to solve this.. – Subburaj Jun 2 '14 at 14:11
  • Tell me how you can select the 5 places. I would prefer a SQL WHERE clause but it if you know how to write it then you would have solved it all. So tell it in words, if you have 5000 points in your database table, how can you find those five which you are interested in? Are they the five latest observations by timestamp or sth? – user30184 Jun 2 '14 at 14:15
  • 1
    Then it will be simply select ST_AsText(ST_Centroid(ST_Union(geometry))) from mypoints where certain_conditions='my_rules'; It is plain SQL and if you are going to play with PostgreSQL or any other database you must just learn a bit of it. – user30184 Jun 2 '14 at 14:30

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.