PostGIS query points based on distance from regular grid

Question

I have a PostGIS table with a large number of points (>150k) on a roughly regular grid -- all points are all about 5km apart. Each point has a geometry field and an associated value nr_days.

Is there a clever way to query a subset from this table on a coarser grid, say only points that are 10, 15, 20...km apart?

So far I have only found cases where people were interested in features within a certain distance rather than the other way.

Answer 1

You can use Postgres's generate_series twice, once for the x direction, once for the y, to generate any grid you like, for example,

SELECT ST_AsText(ST_MakePoint(x, y)) 
FROM 
 generate_series(0, 40, 10) as x, 
 generate_series(200, 220, 10) y;

where the first number is the start point, the second the end point and the final the step size, ie, a grid starting at (0, 200) and ending at (40, 220) with 10 meter intervals. I left the ST_AsText in so you can visualize the results, the first few rows of which are:

POINT(0 200)
POINT(0 210)
POINT(0 220)
POINT(10 200)
POINT(10 210)
POINT(10 220)

You can now use this in a query by joining on your actual table and use ST_DWithin to set the tolerance for intersection (if you have points exactly on a grid, you could use ST_Intersects).

SELECT t.* 
FROM 
 your_table AS t, 
 (SELECT ST_MakePoint(x, y) as geom 
 FROM 
 generate_series(0, 40, 10) as x, 
 generate_series(200, 220, 10) y) 
 AS grid 
WHERE ST_DWithin(t.geom, grid.geom, 1);

which will find everything from your table that is within 1m of the generated grid.

You will want to ensure that you have a spatial index on your table and, obviously, adjust the grid for your own use case.

• Geotob: Thanks, exactly what I was looking for! I had thought of a different solution, but this cut my query time from 3000ms to 100ms -- and is overall more beautiful :) I had to wrap ST_MakePoint(x, y) in ST_SetSTRID() though.
• John Powell: Yes, generate_series is really useful in conjunction with spatial functions.