# Sorting polygons into a n x n spatial array

I have a square group of contiguous polygons: By using the centroid coordinates of each polygon, I want to assign each polygon's spatial sequence in a row by row fashion: I have tried this PostgreSQL statement:

``````select id,st_centroid(geom) as center
from work.qtrs_in_section
order by st_y(st_centroid(geom)),st_x(st_centroid(geom));
``````

It does give me groups of sorted y-values, but the x-values are not sorted in each row group: Ideas?

• The first step in this answer generates the numbering scheme you want. You just need to strip off the "`sde.`" naming. – Vince Jan 5 '20 at 14:01
• A great answer, Vince, thanks. – Rudy Stricklan Jan 5 '20 at 16:25

## 1 Answer

As the centroids are not exactly (to the atomic level) aligned in a perfect North-South / East-West grid, you need to allow some displacement: if the points longitude/latitude are within X meters (cm, km, whatever) from each other, consider they are aligned.

For rectangular cell, don't bother with the centroid but you can use directly the min X or Y coordinate. Else you can replace `st_minY(geom)` by `st_y(st_centroid(geom))`

``````select *,
ROW_NUMBER() OVER(
ORDER BY round(ST_MinY(geom)/50) desc,
ST_MinX(geom) asc
) as newID
FROM Grid
``````

Note that I haven't used `floor` or `ceil` but I have rounded the coordinates divided by my distance of tolerance. This is done so `199.99` and `200.00`, as well as `149.99` and `150.00` are considered the same, but `100.01` and `199.99` are considered different.

You may need to play a bit with this tolerance to find the perfect one if the grid cells have different sizes.

• Perfect! Plus another SQL technique added to my knowledgebase. – Rudy Stricklan Jan 5 '20 at 16:24
• Technically, you only need one `round` operator, since once the rows are binned, binning the last ORDER BY term (columns) is unnecessary. – Vince Jan 5 '20 at 17:06
• @Vince Good catch, thanks! I have edited accordingly – JGH Jan 6 '20 at 1:55