I have the same question as described in this question.

I have numerous milepost addresses (like Milepost 58.3 , US 30,NJ)

Is there a way to do this in PostGIS?

  • do milepost have point or do they have m-value on on linestrring ? if point , just do re-projection , if m-value then use linear referencing. They also can have only miles from start , then you need linestring which is complete road and use linear referencing function on those. see : postgis.net/docs/reference.html#Linear_Referencing May 18, 2016 at 10:51
  • It has only m-value and no points,I have the road shapefile and today i'll try this linear referencing, thank you... May 18, 2016 at 10:59
  • in that case is just using ST_LocateAlongl(linegeom, m-value,0), something like this, assuming that name works for join. This joins lines and posts when names match and calculate point geom for them. SELECT l.name p.mvalue, st_LocateAlong(l.geom, p.mvalue,0) from line l , post p where l.name like p.name May 18, 2016 at 11:45
  • select ST_LocateAlong(a.geom, b.milepost,0) from nj.LineTable as a, nj.MilepostTable as b where a.RoadName = b.RoadName limit 100, I used this command and this works Perfectly for single record but when tried to get values of a whole column with different milepost values it did not work, so what should i add to this query ?? May 18, 2016 at 12:14

1 Answer 1

UPDATE Schema.Table1
SET Geom1 =  ST_AsEWKT(ST_LineInterpolatePoint(ST_LineMerge(ST_Force2D(a.geom)), Schema.Table2.Milepost/(St_length(a.geom)/1600)))
FROM Schema.Table1 As a
where a.RoadName = Schema.Table2.RoadName

This Query did the trick to Geocode and update the values.Thank You Guys....

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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