Ttdfjt

How to delete certain lists from a nested list?

Did Voldemort kill his father before finding out about Horcruxes?

Sankhara meditation

Finding the package which provides a given command

Animal Shelter Management C++

Accidentally deleted python and yum is not working in centos7

Alternator dying so junk car?

Is it rude to refer to janitors as 'floor people'?

Representations of secret keys on Curve25519

When did the US colonies/states stop making their own currencies?

What are "full piece" and "half piece" in chess?

Why is Katakana not pronounced Katagana?

Do aircraft cabins have suspension?

Mechanical puzzle ID: Ring, barbell, and four-holed panel

Cine footage fron Saturn V launch's

Can a dragon's breath weapon pass through Leomund's Tiny Hut?

What is the meaning of [[:space:]] in bash?

Index Uniqueness Overhead

Do I need a 50/60Hz notch filter for battery powered devices?

Get back to US from Canada without passport

Why should I cook the flour first when making bechamel sauce?

Why are road bikes (not time trial bikes) used in many triathlons?

Why did Steve Rogers choose this character in Endgame?

Do dragons smell of lilacs?

Calculating the distance of each pixel in a raster from a point in PostGIS [closed]

Calculating distance to points in QGISHow to Create a Heatmap in PostGISSpeed up point sampling with ST_Value function in PostGIS, raster/vector overlayCalculating the distance between points and multiple lines?The ratio Pixel distance to geographical distance?How to create for each point feature an Euclidian Distance raster in ArcGISMeasuring distances from each pixel center (in raster) to each point (in vector) in PyQGIS?How to sum multi values for each pixel in PyQGIS?Why point layer result (showed as pixel centers) in this PyQGIS code rotated?River network distance from each raster cellCreate a raster layer that represents the distance from each polygon cell to the closest point in a bufferEliminating single raster pixels of flow distance raster in ArcGIS Desktop?Migratory distance from Africa?

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

0

I have a raster in PostGIS and a table of points. I want to calculate the distance of each pixel to each point.

How can I do that?

For example, if 9 pixels and 3 points, then I want to calculate 27 distances.

---

Your idea looks good. Actually I am trying to create a heatmap from a set of points that are trajectory of GPS.

I thought to first create a raster of the convex hull of the points, then calculate the distance of each pixel to each point. After that, I want to assign a degree to each pixel that represents its closeness to points. Now, I will use your idea for the middle part and at the end I create a rater from it.

Do you have any faster idea for creating heatmaps because I have really big data?

postgis raster point distance pixel

share|improve this question

edited Jul 9 at 11:38

PolyGeo♦

54.6k1717 gold badges8585 silver badges256256 bronze badges

asked Jul 8 at 4:21

miladmilad

422 bronze badges

closed as too broad by PolyGeo♦ Jul 9 at 11:40

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This post might help gis.stackexchange.com/questions/21575/…
  
  – Michael Stimson
  Jul 8 at 4:25
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Welcome to GIS SE! We're a little different from other sites; this isn't a discussion forum but a Q&A site. Please check out our short tour which emphasizes that there should be only one question asked per question.
  
  – PolyGeo♦
  Jul 9 at 11:40
  

  
  
  
  

add a comment | 

0

I have a raster in PostGIS and a table of points. I want to calculate the distance of each pixel to each point.

How can I do that?

For example, if 9 pixels and 3 points, then I want to calculate 27 distances.

---

Your idea looks good. Actually I am trying to create a heatmap from a set of points that are trajectory of GPS.

I thought to first create a raster of the convex hull of the points, then calculate the distance of each pixel to each point. After that, I want to assign a degree to each pixel that represents its closeness to points. Now, I will use your idea for the middle part and at the end I create a rater from it.

Do you have any faster idea for creating heatmaps because I have really big data?

postgis raster point distance pixel

share|improve this question

edited Jul 9 at 11:38

PolyGeo♦

54.6k1717 gold badges8585 silver badges256256 bronze badges

asked Jul 8 at 4:21

miladmilad

422 bronze badges

closed as too broad by PolyGeo♦ Jul 9 at 11:40

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This post might help gis.stackexchange.com/questions/21575/…
  
  – Michael Stimson
  Jul 8 at 4:25
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Welcome to GIS SE! We're a little different from other sites; this isn't a discussion forum but a Q&A site. Please check out our short tour which emphasizes that there should be only one question asked per question.
  
  – PolyGeo♦
  Jul 9 at 11:40
  

  
  
  
  

add a comment | 

I have a raster in PostGIS and a table of points. I want to calculate the distance of each pixel to each point.

How can I do that?

For example, if 9 pixels and 3 points, then I want to calculate 27 distances.

---

Your idea looks good. Actually I am trying to create a heatmap from a set of points that are trajectory of GPS.

I thought to first create a raster of the convex hull of the points, then calculate the distance of each pixel to each point. After that, I want to assign a degree to each pixel that represents its closeness to points. Now, I will use your idea for the middle part and at the end I create a rater from it.

Do you have any faster idea for creating heatmaps because I have really big data?

postgis raster point distance pixel

share|improve this question

edited Jul 9 at 11:38

PolyGeo♦

54.6k1717 gold badges8585 silver badges256256 bronze badges

asked Jul 8 at 4:21

miladmilad

422 bronze badges

share|improve this question

edited Jul 9 at 11:38

PolyGeo♦

54.6k1717 gold badges8585 silver badges256256 bronze badges

asked Jul 8 at 4:21

miladmilad

422 bronze badges

share|improve this question

edited Jul 9 at 11:38

PolyGeo♦

54.6k1717 gold badges8585 silver badges256256 bronze badges

asked Jul 8 at 4:21

miladmilad

422 bronze badges

edited Jul 9 at 11:38

PolyGeo♦

54.6k1717 gold badges8585 silver badges256256 bronze badges

edited Jul 9 at 11:38

PolyGeo♦

54.6k1717 gold badges8585 silver badges256256 bronze badges

asked Jul 8 at 4:21

miladmilad

422 bronze badges

asked Jul 8 at 4:21

miladmilad

422 bronze badges

1 Answer
1

active

oldest

votes

3

You can use ST_PixelAsPoints to get a geometry for each point and then to a "Cartesian join", ie, a full join between the points and the pixels, eg,

WITH pixels (x, y, geom) AS (
 SELECT x, y, geom 
 FROM 
 (SELECT (ST_PixelAsPoints(rast, 1)).* 
 FROM rast_table 
 WHERE rid = 1
)
SELECT 
 gid, x, y, ST_Distance(points.geom, pixels.geom)
 FROM pixels, points;

This assumes that your points table is called points and has an id called gid, that your raster rid is 1, so change as appropriate, and also includes the x, y of each pixel in the raster.

The comma in FROM pixels, points is just syntactic sugar for FULL OUTER JOIN. This kind of operation, without an index, will be hideously slow for large polygon/raster tables.

share|improve this answer

answered Jul 8 at 8:44

John PowellJohn Powell

11.1k44 gold badges3131 silver badges5252 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  It's not slow because of the lack of a spatial index. This query would not use it anyway. It's slow because of the large number of point there could be in a big raster.
  
  – Pierre Racine
  Jul 8 at 13:38
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Yes, of course, it is a Cartesian join. A tiled raster would help, along with a spatial index, if there were some kind of ST_DWithin type restriction on the search. However, this answers the OP's question, as stated, I think.
  
  – John Powell
  Jul 8 at 13:44
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Please ask another question and accept this one, if it has answered your original question. As regards heat maps, the standard approach is to calculate a grid and then count how many points are with x radius of the grid, see, for example, this question. The link in the answer to that question shows various ways to go about calculating a grid.
  
  – John Powell
  Jul 8 at 15:40
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I was also thinking of putting the raster in a table where every pixel is in one row of the table. In fact, I wanted to transform it to tiles of 1 pixel. Then, since each pixel is one row, if I use the distance function, I will have the distance between each point and each pixel. I am wondering if it is possible to store very big rasters in tiles of 1 pixel by pixel?? is this method less efficient?
  
  – milad
  Jul 8 at 18:14
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  See this answer regarding tile sizes of 1 pixel. If you are going to store each pixel as a point, you might as well store it in vector format. You need to accept this answer and/or ask another question. You can't keep asking new things in the comments, it isn't how this site works, take the tour if you are unsure as to why.
  
  – John Powell
  Jul 9 at 11:17
  
  

  
  
  
  

 | 
show 2 more comments

3

You can use ST_PixelAsPoints to get a geometry for each point and then to a "Cartesian join", ie, a full join between the points and the pixels, eg,

WITH pixels (x, y, geom) AS (
 SELECT x, y, geom 
 FROM 
 (SELECT (ST_PixelAsPoints(rast, 1)).* 
 FROM rast_table 
 WHERE rid = 1
)
SELECT 
 gid, x, y, ST_Distance(points.geom, pixels.geom)
 FROM pixels, points;

This assumes that your points table is called points and has an id called gid, that your raster rid is 1, so change as appropriate, and also includes the x, y of each pixel in the raster.

The comma in FROM pixels, points is just syntactic sugar for FULL OUTER JOIN. This kind of operation, without an index, will be hideously slow for large polygon/raster tables.

share|improve this answer

answered Jul 8 at 8:44

John PowellJohn Powell

11.1k44 gold badges3131 silver badges5252 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  It's not slow because of the lack of a spatial index. This query would not use it anyway. It's slow because of the large number of point there could be in a big raster.
  
  – Pierre Racine
  Jul 8 at 13:38
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Yes, of course, it is a Cartesian join. A tiled raster would help, along with a spatial index, if there were some kind of ST_DWithin type restriction on the search. However, this answers the OP's question, as stated, I think.
  
  – John Powell
  Jul 8 at 13:44
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Please ask another question and accept this one, if it has answered your original question. As regards heat maps, the standard approach is to calculate a grid and then count how many points are with x radius of the grid, see, for example, this question. The link in the answer to that question shows various ways to go about calculating a grid.
  
  – John Powell
  Jul 8 at 15:40
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I was also thinking of putting the raster in a table where every pixel is in one row of the table. In fact, I wanted to transform it to tiles of 1 pixel. Then, since each pixel is one row, if I use the distance function, I will have the distance between each point and each pixel. I am wondering if it is possible to store very big rasters in tiles of 1 pixel by pixel?? is this method less efficient?
  
  – milad
  Jul 8 at 18:14
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  See this answer regarding tile sizes of 1 pixel. If you are going to store each pixel as a point, you might as well store it in vector format. You need to accept this answer and/or ask another question. You can't keep asking new things in the comments, it isn't how this site works, take the tour if you are unsure as to why.
  
  – John Powell
  Jul 9 at 11:17
  
  

  
  
  
  

 | 
show 2 more comments

3

You can use ST_PixelAsPoints to get a geometry for each point and then to a "Cartesian join", ie, a full join between the points and the pixels, eg,

WITH pixels (x, y, geom) AS (
 SELECT x, y, geom 
 FROM 
 (SELECT (ST_PixelAsPoints(rast, 1)).* 
 FROM rast_table 
 WHERE rid = 1
)
SELECT 
 gid, x, y, ST_Distance(points.geom, pixels.geom)
 FROM pixels, points;

This assumes that your points table is called points and has an id called gid, that your raster rid is 1, so change as appropriate, and also includes the x, y of each pixel in the raster.

The comma in FROM pixels, points is just syntactic sugar for FULL OUTER JOIN. This kind of operation, without an index, will be hideously slow for large polygon/raster tables.

share|improve this answer

answered Jul 8 at 8:44

John PowellJohn Powell

11.1k44 gold badges3131 silver badges5252 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  It's not slow because of the lack of a spatial index. This query would not use it anyway. It's slow because of the large number of point there could be in a big raster.
  
  – Pierre Racine
  Jul 8 at 13:38
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Yes, of course, it is a Cartesian join. A tiled raster would help, along with a spatial index, if there were some kind of ST_DWithin type restriction on the search. However, this answers the OP's question, as stated, I think.
  
  – John Powell
  Jul 8 at 13:44
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Please ask another question and accept this one, if it has answered your original question. As regards heat maps, the standard approach is to calculate a grid and then count how many points are with x radius of the grid, see, for example, this question. The link in the answer to that question shows various ways to go about calculating a grid.
  
  – John Powell
  Jul 8 at 15:40
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I was also thinking of putting the raster in a table where every pixel is in one row of the table. In fact, I wanted to transform it to tiles of 1 pixel. Then, since each pixel is one row, if I use the distance function, I will have the distance between each point and each pixel. I am wondering if it is possible to store very big rasters in tiles of 1 pixel by pixel?? is this method less efficient?
  
  – milad
  Jul 8 at 18:14
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  See this answer regarding tile sizes of 1 pixel. If you are going to store each pixel as a point, you might as well store it in vector format. You need to accept this answer and/or ask another question. You can't keep asking new things in the comments, it isn't how this site works, take the tour if you are unsure as to why.
  
  – John Powell
  Jul 9 at 11:17
  
  

  
  
  
  

 | 
show 2 more comments

You can use ST_PixelAsPoints to get a geometry for each point and then to a "Cartesian join", ie, a full join between the points and the pixels, eg,

WITH pixels (x, y, geom) AS (
 SELECT x, y, geom 
 FROM 
 (SELECT (ST_PixelAsPoints(rast, 1)).* 
 FROM rast_table 
 WHERE rid = 1
)
SELECT 
 gid, x, y, ST_Distance(points.geom, pixels.geom)
 FROM pixels, points;

This assumes that your points table is called points and has an id called gid, that your raster rid is 1, so change as appropriate, and also includes the x, y of each pixel in the raster.

The comma in FROM pixels, points is just syntactic sugar for FULL OUTER JOIN. This kind of operation, without an index, will be hideously slow for large polygon/raster tables.

share|improve this answer

answered Jul 8 at 8:44

John PowellJohn Powell

11.1k44 gold badges3131 silver badges5252 bronze badges

WITH pixels (x, y, geom) AS (
 SELECT x, y, geom 
 FROM 
 (SELECT (ST_PixelAsPoints(rast, 1)).* 
 FROM rast_table 
 WHERE rid = 1
)
SELECT 
 gid, x, y, ST_Distance(points.geom, pixels.geom)
 FROM pixels, points;

share|improve this answer

answered Jul 8 at 8:44

John PowellJohn Powell

11.1k44 gold badges3131 silver badges5252 bronze badges

answered Jul 8 at 8:44

John PowellJohn Powell

11.1k44 gold badges3131 silver badges5252 bronze badges

answered Jul 8 at 8:44

John PowellJohn Powell

11.1k44 gold badges3131 silver badges5252 bronze badges