Classic ARC/INFO commands in PostGIS: NEAR

Rather than starting with the easiest or hardest function, I thought I'd start somewhere in the middle - for GIS experts I don't want to bore you, and for novices, I don't want to blow you away with something more complex. But, after this post, we'll work through some easy queries and move on to more advanced queries.

For those of you who used ARC/INFO, you probably found that the NEAR function was one of your best friends.  I used it every week to transfer attributes from points to lines, and vice-verse. An example from the Esri help manual of the Near (Analysis) as it is now called is shown below:

Reimagining the NEAR command is pretty easy to do in SQL, but I will step through it for you to make it more understandable.  Lets assume we have two vector drawings: parks and road centerlines.  We want to find the closest road to every park, and join the attributes together.  The SQL to replicate the NEAR function is:

We will actually start our review of the query at line 4.  This part of the query selects the distance between all the objects in the parks and road centerline tables using ST_Distance – we will call the result of the distance query near_dist.  Now, this part of the query selects the distance between every feature in parks and every feature in roads, resulting in a table that is NxN in size.  

Right before that in lines 2 and 3 we are selecting all columns from both tables that we are interested in. We could use *, but I don't want to grab all the columns.

You might be wondering what the values "a" and "b" are. In this case, I didn't want to keep typing parks and roadcl, so we dynamically rename them with the alias on a and b. I'll be doing that from now on.

In line 8, we are ordering the records by the ST_Distance field, which gives us an opportunity to select the minimum distance between the parks and the roads.

Now we are saving the best for last. Line 1 uses the DISTINCT ON clause. And in this case, we are getting the distinct id from the parks layer that matches the results from the query selecting the distance, the roadcl.id, and any other record. Because we are sorting the result, the first id in parks becomes our distinct record. It is a clever way of aggregating the results based on a single field.

The resulting table looks like this:

And now you have a code fragment to replicate the ARC/INFO NEAR command. Once again, I hope you can see how easy it is to replicate standard GIS functions like NEAR.  

Remember to check back to my next post on the DEFINE PROJECTION and PROJECT commands when we learn another set of SQL code that replicates an ARC/INFO function.

if you want to learn more about spatial SQL in Postgres/PostGIS, you can order my book How do I do that in PostGIS: Illustrating classic GIS tasks.