Hello 2011/8/13 George MacKerron <g.j.macker...@lse.ac.uk>: >> On 12/08/2011 17:26, George MacKerron wrote: >>> >>> The point of the function is that you can pass it any table name >>> (along with some other parameters) and it returns rows from that >>> named table. >> >> OK, fair enough.... but what exactly are you trying to do that a simple >> SELECT won't do? >> >> Ray. >> >> -- >> Raymond O'Donnell :: Galway :: Ireland >> r...@iol.ie > > I was writing myself an easy-to-use generic nearest-neighbour function for > PostGIS that returns whole rows from the target table, using expanding search > radii to avoid calculating distances for every geometry in the table. Of > course, this will hopefully become redundant in the near future with the > introduction of k-nearest-neighbour GiST indices in PG 9.1. > > The function is as follows (I'm no PL/pgSQL guru -- comments welcome): > > create or replace function > nnrecords( > nearTo geometry > , initialDistance real > , distanceMultiplier real > , maxPower integer > , nearThings text > , nearThingsGeometryField text > , numWanted integer) > returns setof record as $$ > declare > i integer; > sql text; > enough boolean; > begin > i := 0; > while i <= maxPower loop > sql := ' select count(1) >= $5 from ' || quote_ident(nearThings) > || ' where st_dwithin($1, ' || quote_ident(nearThingsGeometryField) > || ', $2 * ($3 ^ $4))'; > execute sql into enough using > nearTo -- $1 > , initialDistance -- $2 > , distanceMultiplier -- $3 > , i -- $4 > , numWanted; -- $5 > if enough or i = maxPower then > sql := ' select * from ' || quote_ident(nearThings) > || ' where st_expand($1, $2 * ($3 ^ $4)) && ' || > quote_ident(nearThingsGeometryField) > || ' order by st_distance($1, ' || > quote_ident(nearThingsGeometryField) || ')' > || ' limit $5'; > return query execute sql using > nearTo -- $1 > , initialDistance -- $2 > , distanceMultiplier -- $3 > , i -- $4 > , numWanted; -- $5 > return; > end if; > i := i + 1; > end loop; > end > $$ language 'plpgsql' stable; >
* move non necessary rows from cycle. * use a statement FOR instead WHILE * flag STABLE is wrong, your function is VOLATILE Regards Pavel Stehule > > Please access the attached hyperlink for an important electronic > communications disclaimer: http://lse.ac.uk/emailDisclaimer > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
