3

I'm working on a project where I have a series of points, something like this:

| id   | name        | geom  |
|------|-------------|-------|
| 1    | example a   | point |
| 2    | example b   | point |
| .. 
| 641  | example bcc | point |

Given a bounding area (always rectangular), such as:

{
    "type": "Polygon",
    "coordinates": [
      [
        [
          -0.23895263671874997,
          51.390637019843744
        ],
        [
          0.06866455078125,
          51.390637019843744
        ],
        [
          0.06866455078125,
          51.59413527860747
        ],
        [
          -0.23895263671874997,
          51.59413527860747
        ],
        [
          -0.23895263671874997,
          51.390637019843744
        ]
      ]
    ]
  }

I wish to divide the area into 100 equal size segments, and calculate the total count of records for that area (the intention being when a user zooms in, the bounding box can be narrowed and more specific results provided).

For example, returning something like this:

| geom    | count |
|---------|-------|
| polygon | 26    |
| polygon | 12    |
| polygon | 8     |

I haven't been able to find any function for splitting a polygon other than by a Line, however this would require creating 18 lines (9 vertical, 9 horizontal), then looping over them. Is there a better way?

2

Here is a PostgreSQL / PostGIS function that will divide up a box2d object into equal-sized cells.

You can then use the created grid to get a count like so:

SELECT
  cell_id,
  count(*)
FROM my_points
LEFT JOIN (
  SELECT
    (create_grid(st_extent(geom), 6, 20, 4326)).* -- fill out the proper SRID here
  FROM my_points
   ) grid
ON my_points.geom && grid.geom AND st_intersects(my_points.geom, grid.geom)
GROUP BY grid.geom, cell_id;

You've probably already figured something out by now, but I hope this helps.

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.