# Calculate min distance between points in PostGIS

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.

• I'm not sure what exactly you need. Can you post some sample of your expected output? – Devdatta Tengshe Feb 25 '13 at 13:40
• @Devdatta Tengshe, added picture and comments. Thanks! – spatialhast Feb 25 '13 at 14:49
• For point 3 - why not distance between 3 and 2? – user1702401 Feb 25 '13 at 15:33
• @user1702401, Yes! for point 3 - distance between 3 and 2. Thanks! – spatialhast Feb 25 '13 at 15:52

Something like that? Be warned, not tested.

``````UPDATE my_table t1
SET dist = (SELECT ST_Distance(t1.geom, t2.geom) FROM my_table t2 WHERE t2.gid <> t1.gid ORDER BY ST_Distance(t1.geom, t2.geom) LIMIT 1)
``````
• Hey, that's nice, I guess using a select in the update forces the query to be evaluated once per record, which is exactly what you want. Worth noting though: this query is sorting the whole table for each record, so if the table you're updating is big it'll fall to bits ("for each record in my 1M record table, sort the whole table by distance and give me the closest one"). – Paul Ramsey Feb 25 '13 at 17:26
• adding to Paul's comment using `ORDER BY t1.geom <-> t1.geom` instead of `ORDER BY ST_Distance(t1.geom, t2.geom)` would improve the performance of the query. – clhenrick May 5 '14 at 15:05

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)

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.

``````WITH unfiltered AS
(
SELECT t1.id AS id1, t2.id AS id2, ST_Distance(t1.geom, t2.geom) as dist
FROM t t1, t t2 WHERE ST_DWithin(t1.geom, t2.geom, 9999) AND t1.id <> t2.id
ORDER BY t1.id, ST_Distance(t1.geom, t2.geom) ASC
)
SELECT DISTINCT ON (id1) id1, id2, dist FROM unfiltered;
``````

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.

I don't know if this will suit your needs, but you have Dijkstra algorithm available on internet.

I've done same thing but in Java.

http://en.wikipedia.org/wiki/Dijkstra%27s_algorithm

To find number of possible routes/lines/paths use Binominal coefficient which will return you every possible path.

http://en.wikipedia.org/wiki/Binomial_coefficient

I think that it's much simpler to solve this problem with JAVA, C# etc. Anyway you can make SQL script using postGIS functions combined with Dijkstra logic to return desired line/path.

Hope it helps......

I found the answers provided here very helpful and came up with a refined version. So, here's another way to accomplish this task, but without having to alter the original table.

``````SELECT t1.gid AS gid_1, t2.gid AS gid_2, ST_Distance(t1.geom, t2.geom) AS mindist
FROM table t1, table t2 WHERE t1.gid != t2.gid AND ST_Distance(t1.geom, t2.geom) != 0
ORDER BY ST_Distance(t1.geom, t2.geom)
LIMIT 1;
``````

The WHERE-statement will make sure that none of the points is compared against itself (as suggested in other answers), but also prevent the distance between points that have different GIDs, but are in the same location (which might happen for example due to multiple operators running gauges in the exact same spot) from showing up as your minimum value (which would be zero).

You can also get a distance matrix by getting rid of the second part of the WHERE-statement as well as the LIMIT part.