Estimating road width in PostGIS?

Question

I am interested in estimating road width (distance from road center line to the nearest building on both sides) in PostGIS. I am aware of the cases/exceptions where there can be buildings on only one side of the road (or no buildings at all). Thus in the following query, it is assumed that buildings exist on both sides of the road center line within 100 meters search radius. I have two tables i.e., road st_wi and building bu_wi in PostgreSQL 9.5 database. The building table contains gid, geom and height columns for each building. The sample scenario is given as follows:

The Approach:

The approach I am adopting to estimate street width is:

• Buffer road to 1m to start
• Intersect that buffer with building polygon
• Count the number of rows
• if count = 0,increase buffer by 1m
• Stop if two buildings (on opposite sides) are selected and return that buffer distance

The Query:

So far, the following query works but have many limitations. For example, the buffer around road center line increments up to 15 meters only.

Do  Language plpgsql
$$
Declare
r integer;
Begin
    EXECUTE 'Drop table if exists t';
    EXECUTE 'Create table t (gid int, geom geometry)';

   /* buffer upto 15 meters */  
    For r in 1..15
    LOOP
        EXECUTE '
        Insert into t (gid, geom)
        Select
            b.gid,
            b.geom
        From st_wi a
        -- joining buildings to road within 100m search radius
        Left join bu_wi b on st_dwithin(b.geom, a.geom, 100)
        Where st_intersects(st_buffer(b.geom,$1), a.geom)
        and a.gid not in (select gid from t)
        and b.geom && a.geom
        group by b.gid, b.geom
        having count(a.geom) = 2'
        using r;
    END LOOP;
END;
$$;

Then:

Select * from t;

The above approach might not be the most efficient one. I am open for suggestions.

Answer 1

I'm not sure how the laborious incremental process improves on just finding the nearest building to each road segment... if the assumption is that

• each road segment will have at least one building on it
• road segments are true centerlines, so the width is some scaling of 2*building_distance

In which case finding the answer should just be

SELECT DISTINCT ON (st.gid)
  st.gid, bu.gid, ST_Distance(st.geom, bu.geom) AS dist
FROM st_wi st 
JOIN st_bu bu
ON ST_DWithin(st.geom, bu.geom, 50)
ORDER BY st.gid, ST_Distance(st.geom, bu.geom) ASC