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
height columns for each building. The sample scenario is given as follows:
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
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; $$;
Select * from t;
The above approach might not be the most efficient one. I am open for suggestions.