On Tue, Dec 15, 2009 at 03:49:52PM +0100, Sarah Hoffmann wrote: > > Hier ein Beispiel: > > > > drop view powerlineview; > > create view powerlineview AS > > SELECT ways.id, > > voltage.v as voltage, > > ways.linestring as geom > > from ways left outer join ( > > select way_id,v from way_tags > > where k='voltage') voltage on ( ways.id = > > voltage.way_id ), > > way_tags wt > > where wt.k = 'power' > > and wt.v = 'line' > > and wt.way_id = ways.id; > > Diese Anfrage stellst du aber nicht auf einer Datenbank, die den kompletten > Planeten enthält, oder? Wenn ich den SELECT-Teil auf meinem > (zugegebenermassen etwas schwachbrüstigen) Rechner laufen lasse, braucht > die Anfrage knapp 15 Minuten. (Das ist natürlich immernoch wesentlich > günstiger als ein 8-Stunden-osm2psql-Tagesupdate, weswegen ich gerade > in diese Richtung experimentiere.)
Ich habe nen bischen mehr indexe als das standard osmosis schema - IIRC sieht osmosis auf way_tags keinen index ausser auf way_id vor - ich habe da noch nen index auf "k" osm=> \d way_tags Table "public.way_tags" Column | Type | Modifiers --------+--------+----------- way_id | bigint | not null k | text | not null v | text | Indexes: "idx_way_tag_k" btree (k) "idx_way_tags_way_id" btree (way_id) Ausserdem natuerlich entsprechend indexe auf den ways.linestring - somit durchsuche ich sowieso nur entsprechend die NRW linestrings. Wenn man da nur ein "bischen" mit den indexen schludert kommt da schnell mal ein full table scan bei raus und dann brauchen wir auch mal 15 minuten: osm=> explain SELECT ways.id, voltage.v as voltage, ways.linestring as geom from ways left outer join ( select way_id,v from way_tags where k='voltage') voltage on ( ways.id = voltage.way_id ), way_tags wt where wt.k = 'power' and wt.v = 'line' and wt.way_id = ways.id and inborder(ways.linestring, 62761); QUERY PLAN ---------------------------------------------------------------------------------------------- Nested Loop Left Join (cost=924.00..92653.20 rows=7 width=807) -> Nested Loop (cost=924.00..90614.49 rows=7 width=797) -> Bitmap Heap Scan on way_tags wt (cost=924.00..90385.79 rows=22 width=8) Recheck Cond: (k = 'power'::text) Filter: (v = 'line'::text) -> Bitmap Index Scan on idx_way_tag_k (cost=0.00..924.00 rows=42267 width=0) Index Cond: (k = 'power'::text) -> Index Scan using pk_ways on ways (cost=0.00..10.38 rows=1 width=797) Index Cond: (ways.id = wt.way_id) Filter: inborder(ways.linestring, 62761) -> Index Scan using idx_way_tags_way_id on way_tags (cost=0.00..291.23 rows=1 width=18) Index Cond: (ways.id = way_tags.way_id) Filter: (way_tags.k = 'voltage'::text) Und das inborder dingelchen ist sowas: osm=> \df+ inborder List of functions Schema | Name | Result data type | Argument data types | Volatility | Owner | Language | Source code | Description --------+----------+------------------+---------------------+------------+-------+----------+----------------------------------------------------------------------------------------------+------------- public | inborder | boolean | geometry, integer | immutable | flo | sql | SELECT _ST_Intersects($1,(select border from completeborders where completeborders.id = $2)) | completeborders wiederum enthaelt vorher praeparierte multipolygon geometrien fuer die entsprechenden relations die ich so zusammenbaue: osm=> \d completeborders Table "public.completeborders" Column | Type | Modifiers ------------+-------------------+----------- id | bigint | name | character varying | complete | integer | adminlevel | integer | border | geometry | path | character varying | Check constraints: "enforce_dims_border" CHECK (ndims(border) = 2) "enforce_srid_border" CHECK (srid(border) = 4326) insert into completeborders ( id, adminlevel, name, border ) select border.id, border.adminlevel::integer, border.name, ST_BuildArea(ST_Collect(linestring)) as geom from ( select rm.id, rm.adminlevel, rm.name, w.linestring from ways w, ( select rm.relation_id as id, rm.member_id as wayid, rt1.v as adminlevel, rt2.v as name from relation_members rm, relation_tags rt1, relation_tags rt2 where rm.member_type = 'W' and rm.relation_id in ( 62761, 62771, 62607, 73347 ) and rm.relation_id = rt1.relation_id and rm.relation_id = rt2.relation_id and rt1.k = 'admin_level' and rt2.k = 'name' order by id ) rm where w.id = rm.wayid and ST_NumPoints(w.linestring)>1 ) as border group by border.id, border.adminlevel, border.name; osm=> select id,name,complete,adminlevel,path from completeborders where id = 62761; id | name | complete | adminlevel | path -------+---------------------+----------+------------+------ 62761 | Nordrhein-Westfalen | | 4 | Das mit dem "inborder" als funktion habe ich nur deshalb gemacht weil mapnik zu bloede ist sauber joins auseinanderzuhalten - d.h. mapnik geht sofort baden wenn im query mehr als eine geometry column auftaucht - Das kann man via function relativ gut verstecken ... Flo -- Florian Lohoff f...@rfc822.org "Es ist ein grobes Missverständnis und eine Fehlwahrnehmung, dem Staat im Internet Zensur- und Überwachungsabsichten zu unterstellen." - - Bundesminister Dr. Wolfgang Schäuble -- 10. Juli in Berlin
signature.asc
Description: Digital signature
_______________________________________________ Talk-de mailing list Talk-de@openstreetmap.org http://lists.openstreetmap.org/listinfo/talk-de