1

I wanted to calculate the number of points that fall within some distance of a point inside a PostgreSQL/PostGIS window function, and I ran into a problem I don't understand. The core of the problem is as follows:

This simple query finds the number of points within 2500 m of the point with ID 863040 (the point geometries are in UTM Zone 48, SRID 32648):

WITH onepoint AS (SELECT id, geom FROM mytable WHERE id = 863040),
     allpoints AS (SELECT geom AS points FROM mytable)
SELECT op.id,
 sum(ST_DWithin(op.geom, allp.points,2500)::int)
FROM onepoint op, allpoints allp

but if I try the same query, this time collecting the geometries of "allpoints" and then re-dumping them, the query fails:

WITH onepoint AS (SELECT id, geom FROM mytable WHERE id = 863040),
     allpoints AS (SELECT ST_Collect(geom) AS points FROM mytable)

SELECT op.id,
 sum(ST_DWithin(op.geom, (ST_Dump(allp.points)).geom,2500)::int) 
FROM onepoint op, allpoints allp

> ERROR: set-valued function called in context that cannot accept a set

I needed to collect and dump to be able to use ST_DWithin inside the window function, but my plans have all come to naught because of this error.

  • Could you buffer op.geom by 2500, then clip allp.points then sum the ST_Dump output? – Liam G Jun 15 '17 at 3:10
2

I am not really sure what you are trying to do and what you expect from the functions. But what you see is that you cannot aggregate (sum function) and expand a collection in that way. I don't know exactly why, but allowing it would open a worm can of possible strange combinations.

If I guess what you want, you want to know how many points is in 2500 meters to that specific point and then continue working with the data set. Ok, but why do you want to collect the points? You can do this with a proper windowing function, but I think I would continue the path you have started and do something like:

WITH  npoints_in_range 
( 
    select count(*) number_of_points from mytable a inner join mytable b on st_dwithin(a.geom, b.geom where a.id = 863040)
)
SELECT select mytable.*, number_of_points from mytable, npoints_in_range

and if you want the points collected in some stage just collect them there:

WITH  npoints_in_range 
( 
    select count(*) number_of_points from mytable a inner join mytable b on st_dwithin(a.geom, b.geom where a.id = 863040)
),
collected_points as
(
   select st_collect(geom) col from mytable
)
SELECT select * from npoints_in_range, collected_points
  • I appreciate the answer. I should have been clearer that my question is not really how to get around the problem, but rather to understand why the collecting and dumping doesn't work. I would be happy to explain the background but it would require so much editing of the original question that it will look quite different and I'm not sure if I should start a new question instead. – John Jun 15 '17 at 21:00
  • It seems the question involves using sum as a window-function instead of an aggregate (note the missing GROUP BY). This requires a different answer, although I agree with this answer being more likely to be what the questioner is looking for. – tilt Jun 16 '17 at 11:16
1

The problem lies in trying to use the set-returning ST_Dump inside another function that doesn't know how to deal with this. You should see the result of ST_Dump as another table (the set) with two columns 'path' and 'geom'. Some functions can deal with this kind of output but in your example obviously not. The dump here is expected to be done in a subselect or with clause first, after which you can use it in other functions.

The query that does this looks like:

WITH 
onepoint AS (
  SELECT id, geom FROM mytable WHERE id = 863040
),
allpoints AS (
  SELECT ST_Collect(geom) AS points FROM mytable
)
,dumpedpoints AS (--this will generate a table with point geometries
  SELECT (ST_Dump(allp.points)).geom FROM allpoints
)
SELECT op.id,
 sum(ST_DWithin(op.geom, dp.geom,2500)::int) 
FROM onepoint op, dumpedpoints dp

I agree with the other answer that this query makes no sense, since you are first collecting something which you are undoing with a Dump right afterwards.

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.