2

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

Point on line Interpolation

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:

Visualization of street lengths and width

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!

3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.