Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I have a postgres/postgis table containing a series of sequential points. I want to add a new column to the table which contains the distance between each point and the first one, and so forth until all the points are processed (the first point should have a distance of 0). Can this be done with a query or do I need to use Python or equivalent to create a loop? Many thanks

share|improve this question

migrated from stackoverflow.com Jul 22 '11 at 12:17

This question came from our site for professional and enthusiast programmers.

1  
Welcome to gis.stackexchange @william! Do your mean "distance between each point and the first one" or "distance between each point and the previous one"? Are your points numbered, indicating sequence? –  underdark Jul 22 '11 at 12:29
    
Or are you looking for a matrix, using each point as a start point and measuring the distance to each point in the layer? –  RyanDalton Jul 22 '11 at 15:11

1 Answer 1

Hallo

If assume that you have an id field called gid and that the first point has gid 1 then you can do something like:

ALTER table my_table ADD COLUMN dist DOUBLE PRECISION;

UPDATE my_table SET dist=ST_Distance(my_table.geom, b.geom) 
FROM (SELECT geom FROM my_table WHERE gid = 1) b;

That will add a column called dist and fill that column with the distance to the point with gid=1

HTH Nicklas

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.