# 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
st.geom As line,
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!

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.

```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;```