2

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. I have tried another way of converting traj_path into set of points and using st_within. But somehow I haven't figured out a way yet in which the returned cell_names would maintain a sequence in which trajectory is passing through them. I've tried couple of methods from Spatial Relationships and Measurements references as well.

Can you guide me?

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
  • 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

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.