2

Using PostgreSQL 14.5, PostGIS 3.3 and OSM data, with Uber h3 extension for PostGIS I am trying to get all hexagons within a certain boundry of polygons.

This SQL query:

SELECT h3_polygon_to_cells(polygon(poly.way), array[]::polygon[], 8) 
FROM planet_osm_polygon AS poly
WHERE poly.admin_level = '4' LIMIT 5;

is timing out, even though there are only 6 results in total for the WHERE clause:

SELECT count(*) FROM planet_osm_polygon WHERE admin_level = '4'

--> 6

Since I am a bit rusty with SQL and new to PostgreSQL, I suspect I have a syntax error in function invocation of h3_polygon_to_cells.

New contributor
Stefano L is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
2
  • 2
    Not sure what client you are using, but you'd certainly be able to distinct between a syntax error and a timeout. I suspect that the amount of h3 cells to return is a bit too high for those admin areas and at level 8...
    – geozelot
    Sep 23 at 17:16
  • You're right, I got a logical error in my thinking there - I thought limiting the results to a low number will increase speed, but thats nonsense because the area to be calculated is massive.
    – Stefano L
    2 days ago

1 Answer 1

0

What is the area of your polygons, say

SELECT SUM(ST_Area(poly.way)) FROM planet_osm_polygon WHERE admin_level = '4'

I would suspect the H3 resolution could be too high. H3 Level 8 has average area of less than square kilometer. Admin_level 4 corresponds to state or province level, so its area could be much larger depending on the country, and then you are trying to get huge results. Try lower resolution, or don't pass explicit resolution at all.

1
  • 1
    Thanks for your help. The resulting number has 12 digits :-) I narrowed the calculation load down with this query, it gives me an empty result even though there should be some hexagons: SELECT h3_polygon_to_cells(polygon(poly.way), array[]::polygon[], 8) FROM planet_osm_polygon poly WHERE admin_level = '8' AND name = 'Jever' Admin level 8 is municipality level and Jever is big enough to have some hexagons of resolution 8 in it. Anyway, for my purpose that computation is way too slow, I guess I need to do it with application code. But would be nice to see the function working.
    – Stefano L
    2 days ago

Your Answer

Stefano L is a new contributor. Be nice, and check out our Code of Conduct.

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.