# How to calculate width and lengths of street layer in Postgis/PostgreSQL?

I was able to project the point location (ad.shp) to the nearest street (line.shp) like this.

My approach was like this. I used ST_ClosestPoint() to iterate this for every point location within a distance of 10 meters and it worked well! (Yes, suggestions for improvements are always welcome).

``````    Select  ST_ClosestPoint(line, pt) As closest_pt_line
From
(Select ad.geom As pt,
st.geom As line,
ST_Distance(ad.geom, st.geom) As d
From ad, st
Where ST_DWithin(st.geom, ad.geom, 10.0)
Order By d
) As foo;
``````

Based on this interpolated point at street layer I want to calculate the width and length of street in both direction. The scenario can be visualized as follows:

The length of street needs to be calculated in both direction based on the interpolated point and street width needs to be calculated with reference to building polygons. I am aware of ST_Length() in Postgis but it returned the length of whole linestring rather than length 1 and length 2 (as shown in figure). Any advice for how to calculate street lengths and width in PostGIS?

UPDATE:

The answer of @kttii helped me to compute street width and lengths of the segments on both side of projected point!

## 1 Answer

st_split(line,point) will produce a collection of geometries which you can then get each st_length.

st_Dwithin could be used to locate polygons and then st_distance could be used between the polygons to get the street width.

How about this statement which uses st_distance, st_endpoint and st_startpoint to return:

• the id of the street (I added this so I could identify the street)
• the distance between the line and the poly
• the distance from the interpolated point to the end point of the line
• the distance from the interpolated point to the start point of the line

```Select id, st_distance(st_pt,ad_pt) as street_to_ad_width, st_distance(st_endpoint(line),st_pt) as street_length_to_end, st_distance(st_startpoint(line),st_pt) as street_length_to_start From (Select st.id,st_snap(st_closestpoint(st_linemerge(st.geom),st_centroid(ad.geom)),ad.geom,ST_Distance(ad.geom, st.geom)*1.1) As ad_pt,st_snap(st_closestpoint(st_linemerge(st.geom),st_centroid(ad.geom)),st_linemerge(st.geom),ST_Distance(ad.geom, st.geom)*1.1) As st_pt,st_makeline(st_snap(st_closestpoint(st_linemerge(st.geom),st_centroid(ad.geom)),ad.geom,ST_Distance(ad.geom,st.geom)*1.1),st_snap(st_closestpoint(st_linemerge(st.geom),st_centroid(ad.geom)),st_linemerge(st.geom),ST_Distance(ad.geom, st.geom)*1.1)) as ad_st_line, st_linemerge(st.geom) As line, ST_Distance(ad.geom, st.geom) As d From ad Join st ON ST_DWithin(st.geom, ad.geom, 10.0) ORDER BY d ) As foo;```

st_pt being the interpolated point on the street and ad_pt being the interpolated point on the ad.shp

• I have tried that. The line needs to be cut down into two segments (until next vertex point) where point is interpolated and then length is required. Thanks for the feedback. – khajlk Jun 16 '16 at 17:43
• modified answer – kttii Jun 17 '16 at 15:54
• Thank you for your help. I tested the code. It doesn't fit to the requirements nevertheless quite useful. length_1 beocome zero in last rows which is not possible. Also, street width going in mm or cm. – khajlk Aug 1 '16 at 13:52