1

I am trying to get cells names (in a sequence) by way trajectory is passing through them. For example in the photo shown below:

Blue markers represent a grid and red line represent a trajectory passing through them. (Its a 5x5 grid).

enter image description here

The result should be{C22, C21, C31, C30, C40, C41} where C21 etc are the cell names by row & column number. All it means is trajectory is starting from cell_names C22 to C21 to C31 and so on. I am using following query to retrieve this result:

select ce.cell_names
from cells ce, traj tr
where st_intersects(tr.traj_path, ce.coordinates) and tr.traj_id = 207 and ce.grid_id = 7776

Where traj_path is LINESTRING and coordinates is POLYGON.

The problem of keep cell order was solved by the answer of one previous contributor and it also works through following query:

SELECT tr.traj_id, ce.cell_id
from cells_new_york AS ce, real_traj AS tr,
ST_LineLocatePoint(tr.traj_path, ST_Centroid(ce.coordinates)) as saim
where st_intersects(tr.traj_path, ce.coordinates) and ce.grid_id = 9 and tr.traj_id = 572
order by saim

But this misses the revisited polygons. As in the figure below C22 and C21 are visited twice. But they are not captured by the query.

Can you guide me towards a relevant PostGIS function?

enter image description here

2

You should be able to order by the fraction of line-length at which each cells centroid projects onto the line:

SELECT cell_names
FROM   (
  SELECT ce.cell_names,
         ST_LineLocatePoint(tr.traj_path, ST_Centroid(ce.coordinates)) AS frac
  FROM   cells AS ce
  JOIN   traj AS tr
    ON   ST_Intersects(ce.coordinates, tr.traj_path)
) q
ORDER BY
       frac
;

Those cells need to be a regular grid. There may be rare edge cases, though, where this fails.

  • Thank you so much! It's working and I'm trying to understand why. Reading through postgis docs. Hoping it'll make sense after sometime. – Saim Mehmood Apr 1 at 22:21
  • 1
    glad it works! but one major drawback I forgot to mention: a line returning to a previously passed polygon likely produces wrong results, and that polygon is not counted twice! might be essential to your needs, so consider if this truly answers your question...,) – ThingumaBob Apr 1 at 23:41
  • Kindly let me know how I can make sure that revisited polygon gets counted twice. – Saim Mehmood Aug 8 at 16:32
  • I'm getting same result after running slightly different query. SELECT tr.traj_id, ce.cell_id from cells_new_york AS ce, real_traj AS tr, ST_LineLocatePoint(tr.traj_path, ST_Centroid(ce.coordinates)) as saim where st_intersects(tr.traj_path, ce.coordinates) and ce.grid_id = 9 and tr.traj_id = 572 order by saim – Saim Mehmood Aug 8 at 16:45
  • But this also misses revisited polygons. Which is important, as I am taking trajectories as walks of objects on grid cells i.e., polygons. Let me know if you're familiar with any other function that will do the task. – Saim Mehmood Aug 8 at 16:47

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.