Hi,
to see what keys there are for rendering on my bicycle map, I query the db for %cycle%:
SELECT key, count(*) FROM ( SELECT (each(tags)).key FROM planet_osm_line ) AS stat WHERE key LIKE '%cycle%' GROUP by key ORDER by count DESC
As this is rather slow, I was trying to take the %cycle% to the inner query. But how can I trigger the keys in 'tags'? And would that be really faster?
Thanks for any hint, Thomas
Thomas Ineichen wrote:
to see what keys there are for rendering on my bicycle map, I query the db for %cycle%:
I think the problem is that the planner is doing a sequential scan first to get all keys.
In case you find a fast way to get all distinct keys fist you can use this filtered with your %LIKE% as an input to your following query counting the number of keys.
Are all your your keys starting with cycle or is it a substring search for keys? In the first case the new hstore import switch might help.
Stephan
Hello,
Select skeys,count(*) from ( SELECT skeys(tags) FROM planet_line) AS stat WHERE skeys LIKE '%cycle%' GROUP by skeys ORDER by count DESC
Runs 10 minutes. I think for a single analyses is this duration ok and is as far as I know not faster to make. Every normal index fails if you have a "%" at the beginning.
Greetings Kolossos
Results: "bicycle";486815 "cycleway";133509 "motorcycle";93431 "cycleway:right";1376 "bicycle:oneway";972 "cycleway:left";770 "cycleway:surface";588 "cycleway_red";557 "cycleway:width";548 "cycleway:buffer";470 "cycleway_green";445 "cycleway:comfort";425 "cycleway_blue";412 "cycleway_ref";292 "oneway:bicycle";291 "cycleway_yellow";269 "cycleway_name";200 "ramp:bicycle";186 "source:bicycle";182 "source:cycleway";151 "class:bicycle";136 "source_ref:bicycle";87 "cycleworth";76 "cycleworth_mtb";70 "source_ref:cycleway";66 "track.bicycle";66 "bicycle:oneway:checked";60 "bicycle:backward";44 "cycleway.type";38 "cycle_direction";36 "class:bicycle:commute";32 "class:bicycle:mtb";30 "bicycle:hybrid";29 "path:right.bicycle";28 "bicycle:legal";28 "surface:bicycle";24 "bicycletype";24 "width:cycleway";22 "bicycles";22 "bus_lane:bicycle";19 "bicycle:footway";19 "bicycle:forward";19 "life_cycle";17 "usability:bicycle";17 "bicycle:tourer";15 "lifecycle";14 "cycleway:oneway";13 "cycleway:right:width";13 "cycleway:left.oneway";13 "cycleway:right:buffer";13 "cycleway:right:surface";13 "cycleway:right:comfort";13 "cycleway:left:surface";12 "cycleway:left:buffer";12 "cycleway:segregated";12 "cycleway:left:comfort";12 "cycleway.surface";12 "cycleway:left:width";12 "def:highway:motorway:bicycle";12 "motorcycle:backward";12 "access:bicycle";11 "cycleway:moped_B";11 "path:left.bicycle";10 "description:bicycle";9 "cycleway:forward";9 "bicycle:mtb";8 "bicycle:left";8 "footway:right:bicycle";8 "cycleway:backward";8 "motorcycle:(11:00-12:00,_18:00-20:00)";8 "seasonal:winter:bicycle";7 "cycleworth_touring";7 "motorcycle:oneway";7 "path.bicycle";7 "footway:left:bicycle";6 "cyclemap";6 "bicycle:note";6 "cycleworth:rr";6 "path:left.bicycle:forward";6 "path:left.bicycle:backward";6 "bicycle_racks";6 "access:motorcycle";5 "motorbicycle";5 "cyclepath";5 "note:cycleway";5 "maxspeed:motorcycle";5 "class:bicycle:non_experienced";4 "cycleworth:mtb";4 "cycle";4 "note:bicycle";4 "surface:cycleway";4 "lane.bicycle";4 "bicycle_guide";4 "hour_on:bicycle";3 "access:bicycle[Mo-Su][21-09]";3 "track:bicycle";3 "hour_off:bicycle";3 "access:motorcycle:backward";3 "pavement:right:bicycle";3 "cycleroad";3 "date_on:bicycle";3 "motorcycle:hour_off";3 "ref:bicycle";3 "access:bicycle[1900-0900]";3 "pavement:left:bicycle";3 "bicycle:right";3 "motorcycle:hour_on";3 "date_off:bicycle";3 "footway.bicycle";3 "class:bicycle:mtb=";2 "oneway:cycleway";2 "footway:left.bicycle";2 "motercycle";2 "opposite_direction:motorcycle";2 "cycleway:hazard:right";2 "footway:bicycle";2 "cycleway:moped";2 "motor_cycle";2 "cyclestreet";2 "old_cycleway";2 "bicycle_difficulty";2 "motorcycle:time";2 "cycleway:type";2 "oneway:motorcycle";2 "cycle_track";2 "cycleway:name";2 "motorcycle:forward:time{7:00-9:00;16:00-18:00}";1 "maxspeed:motorcycles";1 "bicycle:(Sa_18:30-10:00)";1 "bicycle:buffer";1 "parking:lane:right:motorcycle";1 "tracktype:bicycle";1 "cycleway:right.type";1 "sidewalk:right:bicycle";1 "motorcycle:day_on:2";1 "class:bicycle:mtb:technical";1 "cycleway:left.type";1 "cycleway.oneway";1 "motorcycle:forward";1 "motorcycle:hour_on:2";1 "was:bicycle";1 "cycleway:lan";1 "ferry:motorcycle";1 "bicycle:hour_off";1 "cycleway:forwaed";1 "motorcycle:day_on:1";1 "motorcycle:day_off:1";1 "motorcycle:day_off:2";1 "motorcycles";1 "bicycle:class";1 "cycler";1 "traffic:density:Sa:summer:13:forward:bicycle";1 "bicycle:one";1 "cycleway_name:en";1 "Bicycle";1 "life_cycle:start_date";1 "motorcycle:hour_off:2";1 "motorcycle:hour_off:1";1 "oneway:cycle";1 "cycleway:segrated:right";1 "old_bicycle";1 "bicycle:recumbent";1 "bicycle:hour_on";1 "cyclelane";1 "sidewalk:left:bicycle";1 "bicycle:risk_level";1 "motorcycle:hour_on:1";1 "bicycle:(Su-Fr_18:30-10:30)";1 "motorcycle:opening_hours";1 "ferry:bicycle";1 "cyclewat";1