1

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 – simplexio May 18 '16 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... – Deepan Kaviarasu May 18 '16 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 – simplexio May 18 '16 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 ?? – Deepan Kaviarasu May 18 '16 at 12:14
2
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, privacy policy and cookie policy

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