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 Finding minimum edge to edge distance of polygons using ArcGIS Desktop? which is 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:

  "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


1 Answer 1


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.

  a.hgt AS a_hgt,
  b.hgt AS b_hgt,
  ST_Distance(a.the_geom, b.the_geom) AS distance_between_a_and_b
  public."TestArea" AS a, public."TestArea" AS b
  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).

  • 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
    Commented Jul 8, 2011 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
    Commented Jul 8, 2011 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
    Commented Jul 8, 2011 at 22:02

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.