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?

PolyGeo
  • 65,136
  • 29
  • 109
  • 338
milad
  • 719
  • 5
  • 11
  • This post might help https://gis.stackexchange.com/questions/21575/calculating-distance-to-points-in-qgis – Michael Stimson Jul 08 '19 at 04: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 09 '19 at 11:40

1 Answers1

4

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.

John Powell
  • 13,649
  • 5
  • 46
  • 62
  • 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 08 '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. – John Powell Jul 08 '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. – John Powell Jul 08 '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 08 '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. – John Powell Jul 09 '19 at 11:17
  • Sorry i did not know how to accept a question. Still i cannot find an accept button. – milad Jul 11 '19 at 18:25
  • It is a tick by the up/down vote buttons. Thanks – John Powell Jul 11 '19 at 20:19