PostGIS Cookbook - Second Edition

Improving proximity filtering with KNN – advanced

In the preceding recipe, we wanted to answer the simple question of which are the nearest 10 locations to a given point. There is another simple question with a surprisingly sophisticated answer. The question is how do we approach this problem when we want to traverse an entire dataset and test each record for its nearest neighbors?

Our problem is as follows: for each point in our table, we are interested in the angle to the nearest object in another table. A case demonstrating this scenario is if we want to represent address points as building-like squares rotated to align with an adjacent road, similar to the historic United States Geological Survey (USGS) quadrangle maps, as shown in the ...