I have point layer in PostGIS database. Help please with query which add for each points in layer distance in attribute table between this point and another nearest point. Thanks!
Update
Calculate distances between series of points in postgis there is similar question, but
there is in answer:
UPDATE my_table SET dist=ST_Distance(my_table.geom, b.geom)
FROM (SELECT geom FROM my_table WHERE **gid = 1**) b;
distance added for points – from gid = 1 point to another points.
For me each point object in attribute table should have distance from point to another nearest point.
Update
For example, I need add distance between point and another nearest point in attribute table for point:
for point 1 – distance between 1 and 2;
for point 2 – distance between 2 and 3;
for point 3 – distance between 3 and 2;
for point 4 – distance between 4 and 3;
for point 5 – distance between 5 and 4;
for point 6 – distance between 6 and 3.
Best Answer
It's not a simple problem, it involves some kind of forced iteration over the set of candidate points. This chapter from the workshop shows a similar problem, but not exact (your problem is slightly easier)
http://postgis.net/workshops/postgis-intro/advanced_geometry_construction.html
The nearest neighbor searching chapter from the workshop shows the tools you might use to do an index-assisted approach with some external loop driving the query
http://postgis.net/workshops/postgis-intro/knn.html
If your points have a distinct id and you know a distance tolerance (9999) they will all fall within, a self-join and use of the "DISTINCT ON" filter will get you the answer in one go.
It first gathers the candidates combinations of points, and sorts them by distance. Then the "distinct on" filter strips out just the first member of each candidate group, which conveniently is the closest, thanks to the pre-sorting.