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– simpleuser001May 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...– Deepan KaviarasuMay 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– simpleuser001May 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 ??– Deepan KaviarasuMay 18, 2016 at 12:14
Add a comment
|
1 Answer
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....