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

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

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.

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: