# Calculating minimum distance between points using PostGIS

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.

• 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: gis.stackexchange.com/questions/12515/…. Is this something in the direction of your question? Sep 13, 2013 at 6:55
• I want to do both :-)
– Carl
Sep 13, 2013 at 22:45
• 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, 2013 at 22:54

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
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;
``````