lonvia
commented
5 months ago This is as good as it gets with the current SQLAlchemy debugging capabilities. The code is at https://github.com/osm-search/Nominatim/blob/33c0f249b1a9bc716bcb89078c4a766766cbe94f/nominatim/api/logging.py#L83
PRs to improve welcome. Please note that any solution must work with SQLAlchemy 1.4 and 2.x. Closing here as "not planned".
Describe the bug All of the lookup search queries on the debug page when rendered using the python backend are mangled, both wherever there's an array and in all IN expressions. IN expressions should not have brackets, and arrays need to have the keyword ARRAY prefaced. These are especially noticeable wherever the weigh_search() function is called, as it takes a number of arrays as parameters. Example: (search term was "white house. 20500")
SELECT placex.place_id, placex.osm_type, placex.osm_id, placex.name, placex.class, placex.type, placex.address, placex.extratags, placex.housenumber, placex.postcode, placex.country_code, placex.wikipedia, placex.parent_place_id, placex.rank_address, placex.rank_search, placex.linked_place_id, placex.admin_level, ST_AsEWKB(placex.centroid) AS centroid, ST_AsEWKB(ST_Expand(placex.geometry, 0::INTEGER)) AS bbox, placex.importance, 0.09090909090909091 + weigh_search(search_name.name_vector, [[0.0,[584765]]]::VARCHAR, 0.7090909090909091) + CASE WHEN (placex.postcode IN (['20500', '205 00', '20-500'])) THEN 0.0 ELSE coalesce((SELECT min(ST_Distance(location_postcode.geometry, search_name.centroid)) AS min_1 FROM location_postcode WHERE location_postcode.postcode IN (['20500', '205 00', '20-500'])), 2.0) END AS accuracy FROM placex, search_name WHERE placex.place_id = search_name.place_id AND (search_name.name_vector && [584765]::INTEGER[]) AND search_name.address_rank > 9::SMALLINT AND placex.linked_place_id IS NULL AND placex.indexed_status = 0::SMALLINT ORDER BY (0.09090909090909091 + weigh_search(search_name.name_vector, [[0.0,[584765]]]::VARCHAR, 0.7090909090909091) + CASE WHEN (placex.postcode IN (['20500', '205 00', '20-500'])) THEN 0.0 ELSE coalesce((SELECT min(ST_Distance(location_postcode.geometry, search_name.centroid)) AS min_1 FROM location_postcode WHERE location_postcode.postcode IN (['20500', '205 00', '20-500'])), 2.0) END) - CASE WHEN (search_name.importance > 0::INTEGER) THEN search_name.importance ELSE 0.75001 - CAST(search_name.search_rank AS FLOAT) / CAST(40::INTEGER AS NUMERIC) END, accuracy LIMIT 20You can see the weigh_search function parameters are missing ARRAY, and IN has brackets when it shouldn't.
The PHP backend has proper queries, as a note.
To Reproduce Put in any address, then click on the "debug output" link, or just go here and scroll down until you find the lookup queries.
Software Environment (please complete the following information): Whatever nominatim.openstreetmap.org is using
Hardware Configuration (please complete the following information): Whatever nominatim.openstreetmap.org is using
Postgresql Configuration: Whatever nominatim.openstreetmap.org is using
Nominatim Configuration: Whatever nominatim.openstreetmap.org is using