# PostGIS query points based on distance from regular grid

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.

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.

• 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. – Geotob Jan 18 '15 at 10:35
• Yes, generate_series is really useful in conjunction with spatial functions. – John Powell Jan 18 '15 at 10:44