Postgresql Arrays

Communities Deutschland (Germany)
1

Hallo,

viele Daten speichert die osm2pqsqlDatenbank ja in Arrays. Jetzt möchte ich gerne ein solches Array durchsuchen. Dazu habe ich folgendes gefunden:
http://www.postgresql.org/files/documentation/books/pghandbuch/html/arrays.html#AEN5006
Aber SELECT * FROM planet_osm_rels WHERE tags *= ‘w21853504’;
liefert leider keinen Erfolg.
Offenbar muss hierfür postgresql neu übersetzt werden. Oder gibt es noch einen eleganteren Weg?
Falls nein wie kann ich diese Funktion unter Ubuntu in postgresql einkompilieren?

2

installier mal das paket postgresql-contrib-9.1 oder die für dich passende version. da ist vieles drin.

eventuell auch das array?? bin mir nicht ganz sicher aber schaden kann es nicht.

Gruss
walter

p.s. Kompilieren (maken, …) brauchst du definitiv nicht. das muss in einem Paket sein.

3

Das Paket contrib war schon installiert. Leider gibt es unter Ubuntu 11.10 nicht mehr die Paketverwaltung synaptik, dass man dort genauer nachschauen könnte.

4

contrib/array scheint sehr alt zu sein, aus dem letzten Jahrtausend :wink:

Ausserdem hat es die falsche Lizenz, moeglicherweise haben sie es deshalb nicht (mehr) beigefuegt.

5

och nö und wie sucht man jetzt in Arrays? jedes einzeln abholen und dann wieder wegschmeißen, wenn es nicht den richtigen Inhalt hat?

6

SELECT * FROM planet_osm_rels WHERE ‘w21853504’ = any(members);

Grüße
Joachim

7

aber natürlich - ohne synaptic wäre ich auch verloren

geh mal ins “Dash Home” (ganz oben, ganz links) und gib synaptic ein. müsste er dann finden.

Gruss
walter

oder einfach im terminalfenster “synaptic &”

8

Klasse, das war die Lösung. Ist das irgendwo dokumentiert? Eigentlich suche ich ja noch mehr solche Dinge, wie ich nach bestimmten Eigenschaften suchen kann. Zum Beispiel dann für ÖPNV Routen, alle Wege die mit forward oder backward markiert sind. Oder welches ist der erste und welches der letzte Weg. Und aus diesem wieder den ersten oder letzen Punkt.

9

Ich habe es gefunden. Es wollte erst noch installiert werden:
https://help.ubuntu.com/community/SynapticHowto

10

http://www.postgresql.org/docs/current/static/arrays.html#ARRAYS-SEARCHING

Diese Array-Struktur ist etwas schwierig zu handhaben. Ich würde die Elemente der Relationen in einer weiteren Tabelle noch etwas breitklopfen, z. B:
Relations-ID, Position, Rolle, Kennung (Punkt, Weg, Relation), Referenz-ID

Vielleicht gibt es aber auch noch andere Möglichkeiten. Das Feld REL_OFF scheint wohl die Anzahl der Elemente anzugeben.

11

http://www.postgresql.org/docs/current/static/functions-comparisons.html

Hier steht etwas von “IN”
Das klingt doch ganz brauchbar.

expression IN (value [, ...])

wenn ich aber jetzt für expression durch Array[‘type’,‘route’] ersetze dann bekomme ich einen Syntaxfehler

select * from planet_osm_rels where ARRAY [‘type’,‘route’] in members limit 10;

Überhaupt ist es mir nicht gelungen ein sql statment mit in zu erstellen. Hat jemand ein Besipiel für ein funmktionierendes?

12

Take that
SELECT * FROM planet_osm_rels WHERE way_off in (4,5,6);

13

Danke für den Hinweis. Ich habe das ganze andersrum verstanden. Ich hatte den zu suchenden Wert vor dem IN stehen und dadurch dann den Syntaxfehler.
Es besteht also keine Möglichkeit quasie nach einem Teil Array in einem größeren Array zu suchen.
Also zum Beispiel den Array (highway,residential) in (name,hubertstraße,highway,residential,maxspeed,30)
Wichtig ist dabei, dass highway und residential aufeinander Folgen müssen, da ich nicht landuse,residential haben möchte oder highway,platform.

14 

SELECT *
FROM   (SELECT tags,
               generate_subscripts(tags, 1) AS s
       FROM    planet_osm_rels
       )
       AS foo
WHERE  tags[s]   = 'highway'
AND    tags[s+1] = 'residential';
15

Vielen Dank. Manchmal liegt das einfache doch so nah. Aber auf s+1 bin ich wieder nicht gekommen. Für ganz Sachsen dauert die Auswertung auch nur um die 5500 ms ist also recht Flott.
Jetzt ist die Frage, wie diese Fuktion implementiert sind und ob man durch eine eigene Funktion nicht schneller sein könnte. Denn man braucht ja nur jedes zweite Element zu testen und kann bei gefundener Übereinstimmung abbrechen.

16

Also die Sache ist wirklich spannend. Um dem auf den Grund zu gehen habe ich folgende Funktion erstellt:

CREATE OR REPLACE FUNCTION c_k_v(planet_osm_ways.tags%TYPE, text, text)
  RETURNS boolean AS'
    DECLARE
    BEGIN
        if array_upper($1,1)>1 THEN
            FOR i IN 1..(array_upper($1,1)) LOOP
                if ($1[i]=$2 and $1[i+1]=$3) Then
                    Return true;
                END IF;
                i:=i+1;
            END LOOP;
        END IF;
        Return false;
    END;
' LANGUAGE plpgsql

Wenn ich jetzt eine Anfrage nach dem Muster von Frank stelle, dann ist diese zeitmäßig klar im Vorteil. Wobei der Abstand sich deutlich verringert, wenn man alle Spalten aufnimmt. und nicht nur die tags.
Wenn jedoch nach zwei Straßentypen gefiltert werden soll, dann sind die Funktionen in der Ausführungszeit schon fast gleich auf.


SELECT * FROM (SELECT id, nodes, tags, pending, generate_subscripts(tags,1) AS s
FROM planet_osm_ways) as foo
WHERE tags[s] = 'highway' AND tags[s+1] = 'residential'
OR tags[s] = 'highway' AND tags[s+1] ='footway';

SELECT * FROM planet_osm_ways
WHERE c_k_v(tags,'highway','residential') OR c_k_v(tags,'highway','footway');

Die Frage ist für mich jetzt erst mal ganz klar woran liegt das?
Meint ihr es ist sinnvoll diese Funktion weiterzuentwickeln? Vielleicht mit einem Array als Übergabe das mehrere Key_Value Paare checken kann.

Die Ergebnisse sind übrigens bis auf die Spalte s von der Zeilenzahl gleich.