0

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

2
  • Is it possible that the first few runs through it, either function returns NULL values? Have you tried changing your UPDATE to a SELECT and reviewing what is returned for both your ST_LineInterpolatePoint and ST_LineLocatePoint functions? You might also want to limit your values based on some sort of proximity; taking the Cartesian Product of both romo2015_way and vehicle_2015_02 will end up comparing every point with every linestring for each point in vehicle_2015_02. That 2nd mention of vehicle_2015_02 (in UPDATE ... FROM) might be a problem. – John Reiser May 11 '16 at 18:17
  • I tried this now. The peculiar thing is that when I review what is returned by these functions using only SELECT I get proper results. I agree with you that it is more convenient to limit the values in the calculation. And I agree with you that the 2nd mention of the vehicle_2015_02 table makes the trouble. I changed the code as follows... – P. Poli May 13 '16 at 15:16
0

You may need to rewrite your UPDATE statement, using an explicit join.

UPDATE vehicle_2015_02 v02
SET projected_point = q.newpoint
FROM (
   SELECT vehicle_2015_02.objectid, 
          ST_LineInterpolatePoint(
              romo2015_way.st_makeline,
              ST_LineLocatePoint(
                 romo2015_way.st_makeline,
                 vehicle_2015_02.geom
           )) as newpoint
   from romo2015_way, vehicle_2015_02
) q 
where q.objectid = v02.objectid;

Apologies if this isn't 100% correct. Once you get this query working, it might be worthwhile using EXPLAIN on both, so you can see how the database handles the two queries differently.

0

I don't know why this actually works now, maybe someone could explain it, but after many tries I succeded by using the following code:

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, (SELECT * FROM vehicle_2015_02) t;

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.