How to get a list of adjacent lots to a road in ArcMAP and PostGIS/QGIS?

Question

In the following map, I have:

• A line / road layer
• A parcel / lot layer

The Task:

• Find and count the list of lots adjacent to the road with their types (parcel_type is a field in the parcel layer)
• The data is both stored as shp files and as a postgis tables

The Issues:

• The line layer and the lot layer do not intersect i.e. the lot layer has the right of way specified in it of the actual width of the road; whereas, the road layer is just a line layer.
• a parcel might be adjacent to a road on more than one location. think of it as a large lot, with a lot within it. or lots with double frontages. (there is an example of such a lot in the picture below
• This has to be automated on all roads of the study area (roughly 20,000 segments)

Image Sample

Answer 1

This is a PostGIS query that I just tried on our data set so it may work for what you need, and I'm not much of a PostGIS expert yet so I'm 100% sure it could be done better but here goes anyway:

select DISTINCT ON (p."land_no") a."Road_asset_ID", p."land_no", p."SP_GEOMETRY"
from "roads_old_centre_lines" a
INNER JOIN "Property" p on st_intersects(st_buffer(a."SP_GEOMETRY",50), p."SP_GEOMETRY") 
where a."Road_asset_ID" = 1500
   OR a."Road_asset_ID" = 1502

It should create a buffer around the road and select the property geometry that intersects the buffer, the property land no, and the road id that was used for the buffer. It should also only select one property using DISTINCT ON (p."land_no") even if the property touches two road buffers.

This query uses a buffer of 50m but you will just need to adjust that so that cover the road reserve area.

This is what the result in QGIS looks like:

EDIT: This seems to do the count right, it's a bit slow due to having to do two runs but I'm sure someone might be able to optimize it.

SELECT road, Count(land_no) FROM 
    (select DISTINCT ON (p."land_no") a."Road_asset_ID" as road, p.land_no ,p."SP_GEOMETRY"
    from "roads_old_centre_lines" a
    INNER JOIN "Property" p on st_intersects(st_buffer(a."SP_GEOMETRY",50), p."SP_GEOMETRY") 
    where a."Road_asset_ID" = 1500 OR a."Road_asset_ID" = 1502) as Test
GROUP BY road

Answer 2

Create a buffer for each street centerline segment, wider than the standard ROW. Make sure that the buffer has the name of the street as an attribute. Then intersect your buffer layer with your parcel layer. You will then have a list of all of the street names, along with any parcel ids that intersected that particular buffer. That should also cover the double frontages. Once you have the workflow down, it should be easy to set it up in ModelBuilder.

Answer 3

With an ArcInfo level license, you can use the Buffer command with a "FLAT" end type, which allow you to get around the problem mentioned by @underdark. As @Zachary mentioned, this is a pretty easy model to create. Using the "make feature layer" will allow you to define a query (ie- Main St), and buffer the selected layer to a temp in memory layer (in_memory\road_buffer) using a distance (set as a parameter to make interactive), and use the in memory buffer to select taxlots within the chosen distance (select features by location). See below: