CODEWITHSUNDEEP
sqlpostgresqlpostgis
Todd
Todd

Reputation: 101

How to group points by defined count and return a bounding box for each group?

I have a large table of very irregularly spaced points. Given a user-defined bounding box, I would like to return rows of sub-bounding boxes that represent an equal number of points. The shape of the sub-boxes does not matter, as long as all points in the user-defined bounding box are represented and counted.

This is the logic I'm trying to implement:

  1. select all points where intersects user's bounding box.
  2. order all points by x value
  3. group ordered points where count <= 1000
  4. return ST_Extent of each group.

I'm not really sure where to begin, since I don't have a lot of experience with SQL and PostGIS, but something like this...?

SELECT 
    ST_Extent(geom) as extent,
    c.count
FROM 
    xyz_master as x,
    (
        SELECT
            COUNT(*) as count
        FROM
            xyz_master
    ) as c
WHERE
    c.count < 1000
GROUP BY
    extent
;

And, of course, Postgres responds with this:

ERROR:  aggregate functions are not allowed in GROUP BY
LINE 3:  ST_Extent(geom) as extent

I realize the subquery doesn't really make much sense, since it's just returning one row with a count of all points, but I have no idea where to begin.

Can anyone point me in the right direction?

Thanks.

Upvotes: 3

Views: 926

Answers (1)

Daniel Demonceau
Daniel Demonceau

Reputation: 188

Sort the table based on x, then create a different group for each 1000s. Using CEIL is one way to do it. Note that you have to replace in the following code the xmin, ymin, xmax, ymax, and srid provided by the user:

SELECT ST_EXTENT(t2.geom) extent, COUNT(*) count
FROM (
    SELECT t1.geom, ROW_NUMBER() OVER (ORDER BY ST_X(t1.geom)) row_num
    FROM xyz_master t1
    WHERE ST_INTERSECTS(ST_MakeEnvelope(xmin, ymin, xmax, ymax, srid), t1.geom)
) t2
GROUP BY CEIL(t2.row_num / 1000.0);

Upvotes: 3

Related Questions