Calculating minimum distance between points using PostGIS

Question

I have a table (tab_Cities with cities which own more than 50000 inhabitants. The table also contains lat, long and the_geom as columns. I can already see the points via QGIS.

Now I want to write a postgresql query which gives me the minimum distance of the points but I only now how to get the distance of two points. But now I have around 60 points and I want to have the smallest distance of ALL points.

How can I do this?

I found some similar questions but they were using two tables. I am using one table only.

http://gis.stackexchange.com/questions/52792/calculate-min-distance-between-points-in-postgis — spatialhast, Sep 13 '13 at 06:50
I am not quite sure that I understand your task. Do you want to find out which two points are closest to each other? Did you read the Answer to this other post: http://gis.stackexchange.com/questions/12515/calculate-distances-between-series-of-points-in-postgis?rq=1. Is this something in the direction of your question? — leole, Sep 13 '13 at 06:55
I want to do two things, First: I want to know which of cities of all cities are closest to each other. Second: I want to know the distance from City 1 to Citiy 2,3,4,5 ..... And from City 2 to 1,3,4,5,.... And so on But this seems to be to complex for me. This is why I am asking here :-)
The link helps me a lot cause it answers my second question. Sorry, that I did not find the post before and THANK YOU to you! :-) — Carl, Sep 13 '13 at 22:54

score 3 · Answer 1 · edited Dec 21 '19 at 07:56

You can use this following queries (these operations use a temporary table):

-- add distance_min and nearest_id columns
alter table tab_Cities add column distance_min float;
alter table tab_Cities add column nearest_idpoint varchar(10);

--create temp table
DROP TABLE IF EXISTS table_temp;
select a1.gid as id_1, a2.gid as id_2, ST_Distance(a1.geom, a2.geom) as distance
INTO table_temp
    from tab_Cities a1 , tab_Cities a2
    where ST_DWithin(a1.geom, a2.geom, 5000)
    -- 5000 is like a buffer of 5000 meters around your point
    -- You can use a suitable radius for your points
    and a1.gid <> a2.gid ;

--update distance_min
update tab_Cities set distance_min = 
(
select distance 
from table_temp 
where tab_Cities.gid = table_temp.id_1 
order by distance limit 1
),
                      nearest_idpoint = 
(
select id_2 
from table_temp 
where tab_Cities.gid = table_temp.id_1 
order by distance limit 1
);
--DROP TABLE temp
DROP TABLE IF EXISTS table_temp;

Calculating minimum distance between points using PostGIS

1 Answers1