4

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.

Improve this question
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
    Commented Sep 23, 2022 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
    Commented Sep 25, 2022 at 9:33

1 Answer 1

Reset to default
1

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.

Improve this answer
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
    Commented Sep 25, 2022 at 9:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.