I am trying to analyze GPS data of a parade on a fixed route with a certain variance due to the urban surrounding (canyoning). I'd like to project the GPS waypoints to the nearest points on the route underlying linestrings. I add a new column in the same table in which the data is located and update this with the calculated data. But during the first few queries I get for all projected waypoints the projection of the first point. Only if I repeat the query a few times the desired output is generated. Because I'm a beginner in programming I can not figure out what seems to be the trouble.
SELECT AddGeometryColumn ('vehicle_2015_02','projected_point',4326,'POINT',2, false); UPDATE vehicle_2015_02 v02 SET projected_point = ST_LineInterpolatePoint( romo2015_way.st_makeline, ST_LineLocatePoint( romo2015_way.st_makeline, vehicle_2015_02.geom )) FROM romo2015_way, vehicle_2015_02;
vehicle_2015_02: table with GPS waypoints+timestamp as points
projected_point: new column in table "vehicle_2015_02" as points
romo2015_way: table with route as points