Hfrhyu

Has there ever been an airliner design involving reducing generator load by installing solar panels?

Why are electrically insulating heatsinks so rare? Is it just cost?

prove that the matrix A is diagonalizable

A reference to a well-known characterization of scattered compact spaces

Is there a hemisphere-neutral way of specifying a season?

Can a virus destroy the BIOS of a modern computer?

How to take photos in burst mode, without vibration?

I'm flying to France today and my passport expires in less than 2 months

When a company launches a new product do they "come out" with a new product or do they "come up" with a new product?

Brothers & sisters

What mechanic is there to disable a threat instead of killing it?

Took a trip to a parallel universe, need help deciphering

How can I prevent hyper evolved versions of regular creatures from wiping out their cousins?

Why is the 'in' operator throwing an error with a string literal instead of logging false?

Can a rocket refuel on Mars from water?

1960's book about a plague that kills all white people

Why is consensus so controversial in Britain?

Today is the Center

Forgetting the musical notes while performing in concert

Where does SFDX store details about scratch orgs?

What's the difference between 'rename' and 'mv'?

What reasons are there for a Capitalist to oppose a 100% inheritance tax?

What is the intuition behind short exact sequences of groups; in particular, what is the intuition behind group extensions?

Why is Collection not simply treated as Collection<?>

Maintaining Order of Cell Names (Postgres Postgis)

Postgres/postgis cursorsConfused about PostGIS and Postgres terminologyidentify subgraphs within planet_osm_line tableHow to insert Google Maps API Lat/Long into PostgreSQL Postgis GEOMETRY(GEOMETRY, 4326)Is it possible to perform spatial join in PostGIS and keep all features?Optimising a very large point in polygon queryPostgres/postgis st_makepointOrder in PostGis multipointsObtaining Each Unique Area of Overlapping Polygons in Postgres 9.6/Postgis 2.3PostGIS (Postgres 9.6) - order bounding box query results by date (large table)

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

2

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).

The result should beC22, 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?

postgis

share|improve this question

edited Apr 1 at 18:46

Saim Mehmood

asked Apr 1 at 18:13

Saim MehmoodSaim Mehmood

1155

add a comment | 

2

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).

The result should beC22, 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?

postgis

share|improve this question

edited Apr 1 at 18:46

Saim Mehmood

asked Apr 1 at 18:13

Saim MehmoodSaim Mehmood

1155

add a comment | 

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).

The result should beC22, 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?

postgis

share|improve this question

edited Apr 1 at 18:46

Saim Mehmood

asked Apr 1 at 18:13

Saim MehmoodSaim Mehmood

1155

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

share|improve this question

edited Apr 1 at 18:46

Saim Mehmood

asked Apr 1 at 18:13

Saim MehmoodSaim Mehmood

1155

share|improve this question

edited Apr 1 at 18:46

Saim Mehmood

asked Apr 1 at 18:13

Saim MehmoodSaim Mehmood

1155

asked Apr 1 at 18:13

Saim MehmoodSaim Mehmood

1155

asked Apr 1 at 18:13

Saim MehmoodSaim Mehmood

1155

1 Answer
1

active

oldest

votes

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.

share|improve this answer

edited Apr 1 at 20:11

answered Apr 1 at 19:26

ThingumaBobThingumaBob

6,3701424

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "79"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fgis.stackexchange.com%2fquestions%2f317401%2fmaintaining-order-of-cell-names-postgres-postgis%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

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.

share|improve this answer

edited Apr 1 at 20:11

answered Apr 1 at 19:26

ThingumaBobThingumaBob

6,3701424

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  
  

  
  
  
  

add a comment | 

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.

share|improve this answer

edited Apr 1 at 20:11

answered Apr 1 at 19:26

ThingumaBobThingumaBob

6,3701424

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  
  

  
  
  
  

add a comment | 

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.

share|improve this answer

edited Apr 1 at 20:11

answered Apr 1 at 19:26

ThingumaBobThingumaBob

6,3701424

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
;

share|improve this answer

edited Apr 1 at 20:11

answered Apr 1 at 19:26

ThingumaBobThingumaBob

6,3701424

answered Apr 1 at 19:26

ThingumaBobThingumaBob

6,3701424

answered Apr 1 at 19:26

ThingumaBobThingumaBob

6,3701424