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

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

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