ST_Subdivide only returning a single polygon when given a valid polygon as input

Question

I am trying to divide the bounding box of a country into smaller 1km square polygons to be able to speed up my queries against millions of points (get all polygons within a radius of a selection then only query against points that are within those polygons)

I am using the following query to divide the bounding_box of the country but I only get 1 row returned from this query

SELECT (ST_Subdivide(bounding_box, 1000)) FROM ref_countries WHERE name = 'United Kingdom';

When I expected many rows of smaller polygons

I have checked that the geometry type is a polygon using:

SELECT ST_GeometryType(bounding_box) FROM ref_countries WHERE name = 'United Kingdom'

which returns "ST_Polygon"

I have also checked that the geometry is valid using:

SELECT ST_IsValid(bounding_box) FROM ref_countries WHERE name = 'United Kingdom'

which return "true"

I have also tried using ST_DUMP (ST_Dump(ST_Subdivide(bounding_box, 10000))).geom as this seemed to be a possible soultion, but still no luck.

Answer 1

ST_Subdivide "brakes" a big polygon into several smaller, irregular, ones. In your code, you instruct that each smaller polygon could have up to 1000 vertices. Since you are feeding it with a bounding box, so 4 vertices, the source polygon is way below the vertices threshold and is therefore returned untouched.

You may instead want to use ST_SquareGrid to create a regular grid within the specified bounds.

Answer 2

The concept of subdividing is to reduce vertex counts - a bounding box is by definition the least complex Polygon with 4 corners and 5 vertices with no further optimization possible.

You want to ST_SubDivide the actual country Polygon instead. PostGIS' GIST index implementation will then use the bounding boxes of each subdivision for candidate lookups.

---

That being said, a highly efficient polygon gridding approach for PIP queries is not directly implemented in PostGIS.