I have a data set (more than 50,000+) of points (addresses) and Polyline (street) shapefiles of the area of interest. The street shapefile contains Annual Daily Traffic (ADT) data of each street for a long time period (1990 - 2010) for example: 1990, 1995, 2000, 2005 and so on.

I need to select streets within 30m buffer based on address locations with highest Annual Daily Traffic (ADT). Considering the complex scenario of streets (depicted by two examples below), the closest road to the address location can be a minor road or a major road but it must contain highest ADT value. If condition is met withing 30 m with highest ADT, then I need to shift my point feature to that street location otherwise the buffer is increased to 50m. This whole process needs to be iterated for all address locations (point features)enter image description here

I am looking for some advice so that I may come to know about a suitable tool i.e., Python, PostgreSQL and PostGIS etc. because ArcGIS model builder took a lot of time for this task. Any advice for using PostGIS spatial queries for this task or integrating Python with PostGIS would be more suitable?

enter image description here

  • In the database world, "very large" has a specific meaning, and usually involves tens of millions to billions of rows. You'll probably want to find a different term to refer to a collection of shapefiles.
    – Vince
    Commented Jan 29, 2016 at 19:50
  • I don't understand what you mean by "otherwise the system searches within 50 m buffer". Commented Jan 29, 2016 at 19:53
  • In PostGIS you should think in terms of ST_DWithin (measuring distances) instead of intersecting buffers. And as said before. 50000 points is a small dataset. That is good news, isn't it :-) Commented Jan 29, 2016 at 19:56
  • @Vince and Nicklas: Well, thanks for the correction guys. Please consider that "very large" data set is reasonably small data.
    – khajlk
    Commented Jan 29, 2016 at 20:32
  • @Nicklas: From the address location, I need to find the nearest street within 30m distance buffer. If street feature is there then the address location is shifted to that location. If street feature is not within 30m range then buffer distance is increased up to 50m.
    – khajlk
    Commented Jan 29, 2016 at 20:35

2 Answers 2


Jibran, it seems that what you are looking for is a nearest neighbour selection. Since it wouldn't matter if the street is 30 or 50 meters away, you just want the closest and then snap to that one, you can do the whole thing from within postgis with the help of just two functionalities:

  1. Nearest neighbour selection
  2. Point on Line interpolation
  • 2
    For point 2 you can also use ST_ClosestPoint Commented Jan 30, 2016 at 19:17
  • Agree, that makes more sense.
    – tilt
    Commented Jan 30, 2016 at 19:20
  • @tilt and Avens: Thank you for the suggestions. Indeed, these two functionalities worked well on both vector layers. Nevertheless, my problem is closer to the nearest neighborhood analysis with some additional conditions. I have added more details to the question. The selections need to be made within 30m buffer from address locations to the street containing highest annual daily traffic (ADT) value. If condition is met within 30m buffer then the point will be shifted to that street locations. Otherwise the buffer distance is increased to 50m and process is repeated.
    – khajlk
    Commented Jan 31, 2016 at 20:49

Based on this new information, I would suggest the following setup to find the street:

SELECT point.geom As pointgeom, COALESCE( 
    (SELECT streets.geom FROM streets WHERE ST_DWihtin(points.geom, streets.geom, 30) ORDER BY streets.adt DESC LIMIT 1),
    (SELECT streets.geom FROM streets WHERE ST_DWihtin(points.geom, streets.geom, 50) ORDER BY streets.adt DESC LIMIT 1)
    ) AS streetgeom
FROM points

The magic happens in COALESCE, where the first non-null input is taken. If you would need more distances it would get more difficult with some recursive parameters but for now I think you can deal with this. The next step is the ST_Closestpoint calculation, which should be fairly simple.

Please note: this is a postgis-only solution, you can only use QGIS to check the results.

  • Thank you so much. Your answer was very helpful for my work. I am able to see the buffered streets now in 30m and 50m buffer distance. Then, I used pgsql2shp utility to convert the resulting geometry to a shapefile for visualization in QGIS. The only problem is: street shapefile contained Annual daily traffic (ADT) of ten years in ten columns and so far I am only able to extract annual daily traffic (ADT) of 2005 only (I used streets.adt2005 instead to give it a try). However, I need to select maximum ADT value from ten ADT attributes columns. Can I use "MAX" to extract maximum ADT?
    – khajlk
    Commented Feb 16, 2016 at 23:07

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.