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?

  • This post might help gis.stackexchange.com/questions/21575/… Jul 8 '19 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 '19 at 11:40

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 
           (SELECT (ST_PixelAsPoints(rast, 1)).* 
        FROM rast_table 
       WHERE rid = 1
    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.

  • 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. Jul 8 '19 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. Jul 8 '19 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. Jul 8 '19 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 '19 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. Jul 9 '19 at 11:17

Not the answer you're looking for? Browse other questions tagged or ask your own question.