The ARC/INFO NEAR Function – Revisited

I can’t help myself, once I get started writing SQL, I always go on tangents, because its so cool to try out new things.  In our last post, we looked at how SQL can easily recreate the  NEAR function in ArcGIS.  But the important thing to realize about SQL is that it can be adapted so easily.

For instance, in the NEAR function, you get what you ask for – the NEAREST feature to another feature.  But, what if you actually wanted to know the total frequency of some attribute and its nearest neighbor. For instance, lets say you have electric meter boxes, each with an attribute of X number of meters in them.  Or maybe apartment buildings, and the total number of units as a frequency. You might want to populate the nearest street with the total number of meters or apartment units in each feature.  Its pretty much the same function as before, but we simply add another parent query to sum up the frequencies.

The example dataset has a drawing of lines and points for you to try this out on. Lets assume again that we have points and lines, and points have an attribute called f which represents some frequency attribute.  They both have unique IDs (lineID, PT).  The following query will select the nearest neighbor line for each point in the inner portion of the query, and the outer portion sums the frequency attribute.

SELECT sum(f) AS sumfreq, lineid FROM     (SELECT min(dist) AS mindist, first(f) AS f, First(lines.[LineID]) AS lineid,points.pt       FROM (SELECT lineid, f, pt,distance(points.[Geom (I)],lines.[Geom (I)]) AS dist             FROM points, lines ORDER BY dist)        GROUP BY points.pt       ) GROUP BY lineid

So here, we aren’t just limited to the NEAR function and then have to perform other operations to get the frequencies.  We just bury the sum of the frequencies right in the query. To further illustrate the flexibility, swap out sum(f) with Count(pt)  to determine the total number of points that are nearest to the line.  Want to get even more flexible, change the inner query with a WHERE clause like this:

 (SELECT lineid, f, pt,distance(points.[Geom (I)],lines.[Geom (I)]) AS dist              FROM points, lines WHERE point.type = “120 Volts”  ORDER BY dist )

The takeaway less is that once you have built your initial query you can make all kinds of changes to it.  That really is the power of SQL!