Kein Result bei RETURN QUERY?

Hallo zusammen,

ich hänge nun schon seit ein paar Stunden an einem Problem.
Ich möchte zu einem Punkt den nächsten Nachbarn suchen (PostGIS), um das ganze performanter zu machen benutze ich ich eine BOX3D um meinen Bereich in dem der nächste Nachbar gesucht werden soll.

CREATE OR REPLACE FUNCTION my_nearest_srtm_neighbor(p_in geometry) RETURNS TABLE(id integer, height integer, point geometry, distance double precision) AS $$
DECLARE
    x double precision := ST_X(p_in);
    y double precision := ST_Y(p_in);
    mybox box3d := ('BOX3D(' || (x-0.005) || ' ' || (y-0.005) || ', ' || (x+0.005) || ' ' || (y+0.005) || ')')::box3d;
BEGIN
    RAISE NOTICE 'Point is: %', ST_AsText(p_in);
    RAISE NOTICE 'Box is: %', ST_AsText(mybox);
    
    RETURN QUERY SELECT id, height, point, ST_Distance(point, p_in)
    FROM srtm
    WHERE point && mybox
    AND ST_Distance(point, p_in) = 
    (
        SELECT MIN(ST_Distance(point, p_in)) 
        FROM srtm
        WHERE point && mybox
    );
END;
$$ LANGUAGE plpgsql;

Ich bekomme allerdings ständig 0 Zeilen zurückgeliefert. Mein Punkt (Parameter) ist ok, die Box die erstellt wird stimmt und wenn ich die Query so, also ohne PGSQL-Funktion ausführe funktioniert es.
Was lustigerweise auch funktioniert ist folgendes:

DROP FUNCTION my_nearest_srtm_neighbor(geometry);
/*TABLE(id integer, height integer, point geometry, distance double precision)*/
CREATE OR REPLACE FUNCTION my_nearest_srtm_neighbor(p_in geometry) RETURNS integer AS $$
DECLARE
    x double precision := ST_X(p_in);
    y double precision := ST_Y(p_in);
    mybox box3d := ('BOX3D(' || (x-0.005) || ' ' || (y-0.005) || ', ' || (x+0.005) || ' ' || (y+0.005) || ')')::box3d;
    t_row RECORD;
BEGIN
    RAISE NOTICE 'Point is: %', ST_AsText(p_in);
    RAISE NOTICE 'Box is: %', ST_AsText(mybox);
    
    SELECT id, height, point, ST_Distance(point, p_in)
    INTO t_row
    FROM srtm
    WHERE point && mybox
    AND ST_Distance(point, p_in) = 
    (
        SELECT MIN(ST_Distance(point, p_in)) 
        FROM srtm
        WHERE point && mybox
    );

    RETURN t_row.id;
END;
$$ LANGUAGE plpgsql;

Ich lasse mir also nicht die ganze Zeile ausgeben sondern lediglich die ID des Punktes. Das funktioniert wunderbar. Warum funktioniert mein erster Ansatz nicht?

Vielen Dank für eure Hilfe!

Viele Grüße

PS: Ich hoffe ich habe die Board-Konventionen mit CodeTags etc. eingehalten :slight_smile:

Ähm, 9.1 hat KNNGIST, KNN Gist for PostGIS in Place - Postgres OnLine Journal , solltest Du im Auge behalten …

Ansonsten: warte auf den Gisman :wink:

Andreas

Hmm, ich hab leider nur Postgres 8.4 zur Verfügung. Aber ich glaube das Problem liegt nicht an PostGIS sondern in PL/PGSQL (?).
Die Abfrage so funktioniert ja wie gesagt und auch recht performant (~40ms) bei 10 Mio Einträgen.

Danke trotzdem für den Hinweis :slight_smile:

Bzzzzt…schon da :wink:

Ich bin noch nicht ganz fertig mit dem Lesen, aber erstmal folgende Fragen:

  1. Warum benutzt du eine 3DBox? Du benutzt keine 3D-Daten.
  2. Warum eine Boundingbox und kein Buffer? Wäre IMHO logischer, weil du dann wirklich nur die Punkte abfragst, die innerhalb einer definierten Distance liegen. Bei der Box fragst du Punkte auch ab die bis sqrt(2) deiner eigentlichen Distance entfernt sind. Und du sparst dir das erzeugen der Box.
  3. Ich würde die Distance der der Box/des Buffers als Parameter der Funktion übergeben und nicht fest einbauen.
    Ansonsten kann ich in der Abfrage selbst im Moment keinen Fehler entdecken, wie du sagst, funktioniert ja die Abfrage, nur die Rückgabe nicht so wie du willst. Ich werd morgen mal eine klene Testanwendung damit bauen, denke ich.
    Noch als Anmerkung: PostGIS2.0 kann direkt auf die SRTM-Raster zugreifen (Ich versuche im Moment genau das zum Laufen zu kriegen :wink: ) und dort z.B. an einem Punkt Höhenwerte oder andere Sachen der jeweiligen Rasterzelle abfragen.

Grüssle Uwe

Hi,

also mal der Reihe nach :slight_smile:

Ich hab mich hierbei an die PostGIS-Dokumentation gehalten: Chapter 4. Using PostGIS: Data Management and Queries
Ich habs gerade auch mit der 2D-Box versucht, die Abfrage dauert im Schnitt doppelt so lange (~110ms). Frag mich nicht warum!

Aus gleichem Grund wie bei 1.
Mir ist kein anderes Verfahren bekannt, wie funktioniert das mit dem Buffer?

Ja, kann man natürlich machen^^ Brauch ich aber im Moment nicht, kann man ja dann noch schnell ändern.

Anmerkung:
Ja toll, so langsam glaub ich echt ich muss upgraden… Es war allerdings schon ein Kampf mein Debian zu überzeugen 8.4 anstatt 8.3 zu verwenden. Ist leider squeeze. Danach hatte ich noch schöne Stunden mit der PG-Config weil die nicht korrekt gemerged hatte… Eigentlich wollte ich mir sowas nochmal ersparen… :confused:

Danke schonmal für deine Hilfe! :slight_smile:

Was ich auch gerade versucht habe war: Mehrere OUT-Parameter bestimmen und diese als SET zurückzugeben. Dann sind auch alle leer. Sehr komisch das ganze :frowning:

Viele Grüße,
Wolfgang

Guten Morgen!

Zum Buffer:
Du würdest deine Räumliche Bedingung etwa so formulieren:

...
where point && st_buffer(point, distance)
...

Allerdings musst du die Distance auch hier entsprechend des räumlichen Bezugs abgeben, also bei srid=4326 in Dezimalgrad. Aber das ist bei der Box-Methode letzlich genauso. Könnte mir auch vorstellen, das das im Falle von Punkten etwas schneller ist. Die genaue Syntax musst du noch in der Doku nach lesen, die find ich hier grad nicht.

Gruß Uwe

EDIT: Muß kurz zurückrudern mit ST_Buffer: ST_DWithin ist die bessere und schnellere Funktion: http://postgis.refractions.net/docs/ST_DWithin.html

Hab mal was zusammengebastelt, was gerade bei mir funktioniert hat.

CREATE OR REPLACE FUNCTION my_nearest_srtm_neighbor(p_in geometry) RETURNS TABLE(id integer, height numeric, point geometry, distance double precision) AS $$
DECLARE
 
BEGIN
    RAISE NOTICE 'Point is: %', ST_AsText(p_in);
    
    RETURN QUERY 
    with t1 as 
      (select a.id, a.x, a.y, a.height, a.geom, st_distance(p_in, a.geom) as dist
         from test_daten a
         where st_dwithin(p_in, a.geom, 2))
    select t1.id as id, t1.height as height, t1.geom as point, t1.dist as distance  
      from t1
      where t1.dist = (select min(dist) from t1);
END;
$$ LANGUAGE plpgsql;

Prinzipieller Unterschied ist, das ich erst mit dem With-statement erst eine Untermenge der Punkte bilde, auf der dann die eigentliche Distance-Abfrage stattfindet. Die Begrenzung findet jetzt mit ST_DWITHIN statt, was die Box erspart. Ich spiel halt gern rum :wink:
Bei mir hat das aber erst funktioniert, als ich die Ausgabespalten so benannt habe wir sie in der Ausgabe-Tabelle heißen sollen, d.h. es könnte bei dir zu Problemen geführt haben, das die ST_Distance Anweisung einen falschen Namen für die Ausgabe generiert.

Viele Grüße und schön Sonntag noch :wink:

Uwe

PS: Die Testdaten hatte ich so generiert:

create table test_daten (
  id integer primary key,
  x integer,
  y integer,
  height numeric);


select addgeometrycolumn('public', 'test_daten', 'geom', -1, 'POINT', 2); 


insert into test_daten (id, x, y, height, geom)
select x.x*1000+y.y as id, x.x, y.y, random()*100 as height, 
       st_geometryfromtext('POINT('||x.x||' '||y.y||')', -1) as geom 
  from generate_series(0,100) as x(x), generate_series(0,100) as y(y)

Hi,

leider funktioniert deine Funktion bei mir nicht. Ich erhalte folgenden Fehler:

ERROR:  syntax error at or near "$2"
LINE 1: ...ere st_dwithin( $1 , a.geom, 2)) select t1.id as  $2 , t1.he...
                                                             ^
QUERY:   with t1 as (select a.id, a.x, a.y, a.height, a.geom, st_distance( $1 , a.geom) as dist from test_daten a where st_dwithin( $1 , a.geom, 2)) select t1.id as  $2 , t1.height as  $3 , t1.geom as  $4 , t1.dist as  $5  from t1 where t1.dist = (select min(dist) from t1)
CONTEXT:  SQL statement in PL/PgSQL function "my_nearest_srtm_neighbor" near line 13

********** Fehler **********

ERROR: syntax error at or near "$2"
SQL Status:42601
Kontext:SQL statement in PL/PgSQL function "my_nearest_srtm_neighbor" near line 13

Das gleiche erhalte ich auch wenn ich bei meiner Funktion ST_Distance(…) AS distance machen will. :frowning:

Woran kann denn das schon wieder liegen?

Viele Grüße,
Wolfgang

Du kannst Dinge wie Tabellennamen nicht als Parameter übergeben. Oder aber dann das Query als dynamisches SQL EXECUTen.


Andreas

Hi,

das mache ich doch garnicht oder?
Ich übergebe eig. nur einen Punkt.

Viele Grüße,
Wolfgang

Für Dich noch mal:

ERROR:  syntax error at or near "$2"
LINE 1: ...ere st_dwithin( $1 , a.geom, 2)) select t1.id as  $2 , t1.he...
                                                             ^

Andreas

Benutze es doch einfach auf wie deine eigene Funktion, da steht es doch auch keine $… drin.

Gruß Uwe

PS: die 2 in

[color=#333333][font=monospace]st_dwithin( $1 , a.geom, 2)[/font][/color]

muß du noch in den gewünschten Abstand ändern oder auch als Parameter übergeben.

Hi,

@Andres: Ich verstehe das Problem leider immer noch nicht.

@Uwe: Ich verwende keine $, diese erscheinen lediglich in der Fehlerausgabe.

Hier nocheinmal mein Problem und die Funktion so wie ich sie ausprobiert hatte:

Gegeben: Tabelle srtm(id serial, point geometry, height integer)
Problemstellung: Ich möchte zu einem Punkt den nächsten Punkt in dieser Tabelle finden. Zusätzlich darf die Distanz zu diesem Punkt einen gegebenen Threshold nicht überschreiten.

Lösung:
-Ansatz 1: Funktion (siehe 1.Post) die mir lediglich die ID des Punktes aus der srtm-tabelle zurückgibt --> funktioniert
-Ansatz 2: Funktion die mir das vollständige Tupel + die Distanz zu diesem Punkt zurückgibt --> funktioniert nicht (keine Fehler, lediglich 0 Zeilen in Ausgabe bei gleicher Query)

Hier noch einmal die Funktion die ich von Uwe übernommen hatte (leicht abgeändert auf meine Tabelle):

DROP FUNCTION my_nearest_srtm_neighbor(p_in geometry);
CREATE OR REPLACE FUNCTION my_nearest_srtm_neighbor(p_in geometry) RETURNS TABLE(id integer, height numeric, point geometry, distance double precision) AS $$
DECLARE
 
BEGIN
    RAISE NOTICE 'Point is: %', ST_AsText(p_in);
    
    RETURN QUERY 
    with t1 as 
      (select a.id, a.height, a.point, st_distance(p_in, a.point) as dist
         from srtm a
         where st_dwithin(p_in, a.point, 2))
    select t1.id as id, t1.height as height, t1.geom as point, t1.dist as distance  
      from t1
      where t1.dist = (select min(dist) from t1);
END;
$$ LANGUAGE plpgsql;

Wenn ich das ausführe erhalte ich folgende Fehlermeldung:

ERROR:  syntax error at or near "$2"
LINE 1: ...re st_dwithin( $1 , a.point, 2)) select t1.id as  $2 , t1.he...
                                                             ^
QUERY:   with t1 as (select a.id, a.height, a.point, st_distance( $1 , a.point) as dist from srtm a where st_dwithin( $1 , a.point, 2)) select t1.id as  $2 , t1.height as  $3 , t1.geom as  $4 , t1.dist as  $5  from t1 where t1.dist = (select min(dist) from t1)
CONTEXT:  SQL statement in PL/PgSQL function "my_nearest_srtm_neighbor" near line 13

********** Fehler **********

ERROR: syntax error at or near "$2"
SQL Status:42601
Kontext:SQL statement in PL/PgSQL function "my_nearest_srtm_neighbor" near line 13

Zur Vollständigkeit: DBMS ist
PostgreSQL Version: “PostgreSQL 8.4.9 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.3.real (Debian 4.3.2-1.1) 4.3.2, 64-bit”
PostGIS Version: “POSTGIS=“1.5.1” GEOS=“3.2.0-CAPI-1.6.0” PROJ=“Rel. 4.6.1, 21 August 2008” LIBXML=“2.6.32” USE_STATS”

Ich hoffe mit diesen Infos könnt ihr mehr anfangen :slight_smile:

Viele Grüße & nochmals Danke,
Wolfgang

Hallo Wolfgang,

Das liegt an den Namen deiner out-Parameter, die heissen wie in der Funktion angesprochene Spalten.
Bezeichne in der Funktion genauer was du meinst (srtm.point, …) oder benenne deine out-Parameter anders, dann klappts.

PG 8.4 setzt hier bei jedem Vorkommen von “point” die Variable point ($2) ein. Da steht aber nichts drin bei Aufruf, deswegen bekommst du keine Daten. Auch wird die Erstellung einer Funktion abgelehnt, wenn Variablen an Stellen hinersetzt werden sollen, wo keine hingehören.

Seit 9.0 ist das nicht mehr so. Es wird gemeckert bei Unklarheiten: (“ERROR: column reference point is ambiguous … DETAIL: It could refer to either a PL/pgSQL variable or a table column”) und nicht überall stumpf ersetzt (-> “… as point”).
Genauer findest du das hier:PostgreSQL: Documentation: Manuals: PL/pgSQL Under the Hood.