2

I have a square group of contiguous polygons:

enter image description here

By using the centroid coordinates of each polygon, I want to assign each polygon's spatial sequence in a row by row fashion:

enter image description here

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:

enter image description here

Ideas?

2
  • 1
    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
0

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.

3
  • Perfect! Plus another SQL technique added to my knowledgebase. – Rudy Stricklan Jan 5 '20 at 16:24
  • 1
    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

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.