6

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.

9

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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