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.

Answer 1

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;