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

Question

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!

Answer 1

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