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'm learning how to use PostGIS and spatial databases for analysis. What I am trying to do is perform a calculation to get the distance for the nearest polygon in a file, using the edge calculation, rather than vertices.

Using this answer from Paul Ramsey to a similar question:

CREATE TABLE mytable_distances AS SELECT a.id, b.id, ST_Distance(a.geom::geography, b.geom::geography) as distance FROM mytable a, mytable b;

I am attempting to apply it to my spatial database. I don't understand the structure of this query though. I think CREATE TABLE mytable_distances AS creates a table to store the result but after this part I'm lost. Are a and b column names? If so, why would I specify two columns to calculate this?

My table is called TestArea and I have experimented with some basic queries successfully:

SELECT 
  "TestArea".hgt
FROM 
  public."TestArea"
WHERE
  "TestArea".area > 100

The structure of the database in PGAdmin III is as follows, with my table called TestArea. I'm not sure what the nearest neighbor calculation should look like using my column headers (all of these objects are polygons).

enter image description here

share|improve this question
add comment

1 Answer 1

up vote 8 down vote accepted

a and b are alias table names to the same table. This is effectively a T1 CROSS JOIN T2 in DB-speak. This allows a self-join to say "how close one part is to another" in a single table.

SELECT 
  a.hgt AS a_hgt,
  b.hgt AS b_hgt,
  ST_Distance(a.the_geom, b.the_geom) AS distance_between_a_and_b
FROM 
  public."TestArea" AS a, public."TestArea" AS b
WHERE
  a.gid < b.gid AND a.area > 100 AND b.area > 100

You might want to add another WHERE clause to limit the number of rows, e.g., add AND ST_Distance(a.the_geom, b.the_geom) < 1000.0 so that all distances are less than a kilometer (if you have projected UTM).

share|improve this answer
    
Thanks for your answer Mike. I used it, and now understand what is going on. Maybe I should ask this as a new question, but is there a way of optimizing the calculation? For example if I have 100 polygons that is 10,000 calculations. I happen to have 1,000,000 polygons and am wondering how to scale down the calculation. –  djq Jul 8 '11 at 20:02
1  
that's exactly why I introduced the last ST_Distance filter in the WHERE clause .. to say that "we don't want to find distances between anything else beyond a certain distance". Were you expecting to find the [one] closest area to another? This would be indeed a slightly different query. –  Mike T Jul 8 '11 at 21:53
    
I thought this would only not record the values above a certain level, but that it would still calculate them. Hence my subsequent question....gis.stackexchange.com/q/11979/803 –  djq Jul 8 '11 at 22:02
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.