# Estimating road width in PostGIS?

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
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.

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
``````
• Thank you for your feedback. I agree with your suggestions. Somehow, I wanted to figure out PLPGSQL way to programmatically increment buffer from road center line until two buildings are selected. Oct 1, 2017 at 8:46