Hi. I'm working on the look-and-listen-map (toolserver project lalm), a web map portal for the Blind and Visually Impaired. That is not a rendering application like the several mapnik stylesheets running on the toolserver, but I agreed with the Admins, that it might be worth in respect of capacities of the toolserver to use the mapnik style nevertheless.
Today I finally got database access and looked deeper into the schema. Most queries in my application rely on the osm_id present in all tables (and index, too), so that's not a problem. Some queries will later on require geometric queries, but here I can combine the postprocessed tables (planet_nodes, planet_line, planet_polygon...) for filtering and the non-processed ones for fetching the values - so that's okay, too.
My problem lies, where mapnik does ugly tricks for rendering: I have a node-id and want to get all ways this node is part of.
That would be possible, querying planet_ways, as planet_ways contains a field nodes, that is a integer-array; but as far as I know there is no performant way to query an array-field of a database on containing a particular element.
Currently my code is based on a different database schema, but I have a query with the following characteristics, that I need with the osm_mapnik-scheme:
IN: node_id (int) OUT: a list of way_id, where the corresponding way contains the node with id node_id.
A second question would be, if errors would occur, when I combine e.g. planet_ways with planet_line in a query, and a matching row in planet_ways is pending. Usually the intermediate tables are only accessed by the import tool, so I would do unconventional things here, but am I right? If the planet_ways-row is pending, the data of this table is not in sync with the data in planet_line.
regards Peter
On 12/20/2011 08:45 AM, Peter Wendorff wrote:
Hi. I'm working on the look-and-listen-map (toolserver project lalm), a web map portal for the Blind and Visually Impaired. That is not a rendering application like the several mapnik stylesheets running on the toolserver, but I agreed with the Admins, that it might be worth in respect of capacities of the toolserver to use the mapnik style nevertheless.
Today I finally got database access and looked deeper into the schema. Most queries in my application rely on the osm_id present in all tables (and index, too), so that's not a problem. Some queries will later on require geometric queries, but here I can combine the postprocessed tables (planet_nodes, planet_line, planet_polygon...) for filtering and the non-processed ones for fetching the values - so that's okay, too.
My problem lies, where mapnik does ugly tricks for rendering: I have a node-id and want to get all ways this node is part of.
That would be possible, querying planet_ways, as planet_ways contains a field nodes, that is a integer-array; but as far as I know there is no performant way to query an array-field of a database on containing a particular element.
Currently my code is based on a different database schema, but I have a query with the following characteristics, that I need with the osm_mapnik-scheme:
IN: node_id (int) OUT: a list of way_id, where the corresponding way contains the node with id node_id.
The mapnik updater has to do that as well. As a moved node needs to recreate the geometry of the way that contains that node.
There is a GIN index on the planet_ways table that allows to do this.
The query osm2pgsql runs for this is "UPDATE %p_ways SET pending = true WHERE nodes && ARRAY[$1] AND NOT pending;"
You don't want the update or pending part, but the "WHERE nodes && ARRAY[osm_id]" should work for you.
A second question would be, if errors would occur, when I combine e.g. planet_ways with planet_line in a query, and a matching row in planet_ways is pending. Usually the intermediate tables are only accessed by the import tool, so I would do unconventional things here, but am I right? If the planet_ways-row is pending, the data of this table is not in sync with the data in planet_line.
Yes, that is correct. But that should only be for a fairly short period of time.
Kai
regards Peter
Maps-l mailing list Maps-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/maps-l
Hi. Thanks for that hint, but I fear, it's not completely correct what you wrote...
I want to ask, if a specific value is contained in an array. Your query would create an array, I think (that's correct for update, but wrong for select). Nevertheless: thanks for the hint - I read through the documentation for GIN indices [1] and Arrays [2] again.
I think, to get all way-ids, that are connected to a specific node with id node_id I would have to do sth. like:
SELECT osm_id AS way_id FROM planet_ways WHERE nodes && node_id = ANY(nodes)
yes, that should be possible. Thanks again.
regards Peter
[1] http://www.postgresql.org/docs/9.0/static/gin.html [2] http://www.postgresql.org/docs/8.2/static/arrays.html
Am 20.12.2011 17:15, schrieb Kai Krueger:
The query osm2pgsql runs for this is "UPDATE %p_ways SET pending = true WHERE nodes&& ARRAY[$1] AND NOT pending;"
You don't want the update or pending part, but the "WHERE nodes&& ARRAY[osm_id]" should work for you.
Hi
Am 20.12.2011 19:29, schrieb Peter Wendorff:
Thanks for that hint, but I fear, it's not completely correct what you wrote...
I want to ask, if a specific value is contained in an array. Your query would create an array, I think (that's correct for update, but wrong for select).
Have you actually tried that?
http://www.openstreetmap.org/browse/node/311596461
osm_mapnik=> select id from planet_ways where nodes && ARRAY[311596461]; id ----------- 28320526 131908054 (2 rows)
osm_mapnik=> explain analyze select id from planet_ways where nodes && ARRAY[311596461]; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on planet_ways (cost=6259.91..1647787.21 rows=581665 width=4) (actual time=0.145..0.167 rows=2 loops=1) Recheck Cond: (nodes && '{311596461}'::integer[]) -> Bitmap Index Scan on planet_ways_nodes (cost=0.00..6114.49 rows=581665 width=0) (actual time=0.114..0.114 rows=2 loops=1) Index Cond: (nodes && '{311596461}'::integer[]) Total runtime: 0.219 ms (5 rows)
ARRAY[311596461] creates an array, the && operator compares it against the nodes array.
Peter