search

rustprooflabs / pgosm-flex

PgOSM Flex provides high quality OpenStreetMap datasets in PostGIS (Postgres) using the osm2pgsql Flex output.
MIT License
100 stars 20 forks source link

Change "all" views to tables loaded via Flex style #337

Closed rustprooflabs closed 1 year ago

rustprooflabs commented 1 year ago

Closes #320

The views have turned into tables and are not always created.

Pros / Cons / Inter-tangled

Enables loading combined point table w/out requiring load of full polygon data. This can be considerable size savings when larger polygons are involved in the source data but being simplified before loading to Postgres.

vbuilding_all and vshop_all were views doing combining, probably wasn't good for general querying performance beyond small regional data. New table structure is only points, and fully indexed.

Requires duplicating a bit of logic. The negative impact is minimized by moving common logic into new helper methods. E.g. building and building_combined_point leverage common logic in building_helper.

Increased data size with layerset=everything with more data materialized to tables. Because of additional overhead not loading in default. However, this opens the opportunity to consider loading more _combined_points layers into lightweight layersets. Going to consider adding a place_combined_point layer option. Some of those polygons are huge, and if all you need is a centroid.

Additional Change Notes

Layerset: Basic

Layerset: Everything

Layerset: Minimal

rustprooflabs commented 1 year ago

Rebased and force pushed after #338.

rustprooflabs commented 1 year ago

Problem is in the shop_polygon table, that helps narrow it down. Hooray for a couple more tests (and docs about them!)

FAILED TEST: sql/shop_combined_point_osm_type_subtype_count.sql - See tmp/shop_combined_point_osm_type_subtype_count.diff
  docker exec -it pgosm /bin/bash -c "cat /app/tests/tmp/shop_combined_point_osm_type_subtype_count.diff " 
FAILED TEST: sql/shop_polygon_osm_type_subtype_count.sql - See tmp/shop_polygon_osm_type_subtype_count.diff
  docker exec -it pgosm /bin/bash -c "cat /app/tests/tmp/shop_polygon_osm_type_subtype_count.diff "
rustprooflabs commented 1 year ago

Cleaned up shops, started on POIs.

rustprooflabs commented 1 year ago

Unsure why I'm getting this error

2023-06-08 20:00:31,822:INFO:pgosm-flex:helpers:2023-06-08 20:00:31  ERROR: Failed to execute Lua function 'osm2pgsql.process_way': ./style/poi_combined_point.lua:96: Error in 'insert': Trying to add way to table 'poi_combined_point'.
2023-06-08 20:00:31,822:INFO:pgosm-flex:helpers:
2023-06-08 20:00:31,822:INFO:pgosm-flex:helpers:stack traceback:
2023-06-08 20:00:31,822:INFO:pgosm-flex:helpers:[C]: in method 'insert'
2023-06-08 20:00:31,822:INFO:pgosm-flex:helpers:./style/poi_combined_point.lua:96: in function 'poi_process_way_combined'.
2023-06-08 20:00:31,828:ERROR:pgosm-flex:pgosm_flex:Failed to run osm2pgsql. Return code: 1
Failed to run osm2pgsql. Return code: 1 - Check the log output for details
rustprooflabs commented 1 year ago

The error in https://github.com/rustprooflabs/pgosm-flex/pull/337#issuecomment-1583828340 is quite unexpected. I believe I'm doing everything the same as the building_combined_point and shop_combined_point but it's failing.

The poi_combined_point table has the geom defined as a point`.

{ column = 'geom', type = 'point', projection = srid, not_null = true},

It fails adding rows on both polygons and lines (confirmed by commenting out the polygon portion...) which use these lines respectively.

geom = object:as_polygon():centroid()

and 

geom = object:as_linestring():centroid()

The the error is Trying to add way to table 'poi_combined_point'. which makes it seem like the centroid() function is not working. It shouldn't be a way at the time of insert, it should be a centroid. Right??

rustprooflabs commented 1 year ago

Problem wasn't the geom column, it was the ids definition. :facepalm:

https://github.com/rustprooflabs/pgosm-flex/pull/337/commits/c508c2f738d9e2943c2a23b4581aab4948e92041