Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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 in other threads but they were using two tables. I am using one table only.

Thank you for help! :-).

share|improve this question
    
    
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 '13 at 6:55
    
I want to do both :-) –  Carl Sep 13 '13 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 '13 at 22:54
add comment

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

I hope it will be usefull,

share|improve this answer
    
Thank you, I will try this when I am back home –  Carl Sep 13 '13 at 13:26
    
There is a last question in my head. What is meant with the vxf_id in both tables? –  Carl Sep 13 '13 at 22:45
    
I often use vxf_id for my 'id' field ; it's an habit. The post is corrected –  Benno Sep 14 '13 at 9:33
    
Thank you! Helps me a lot! –  Carl Sep 14 '13 at 11:05
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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