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?
    – leole
    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

1 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
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.