[GIS] How to get a list of adjacent lots to a road in ArcMAP and PostGIS/QGIS

analysisarcmappostgisqgisvector

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
alt text

Best Answer

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:

alt text

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
Related Question