Hello
2011/8/13 George MacKerron <g.j.mackerron@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
>> rod@iol.ie
>
> I was writing myself an easy-to-use generic nearest-neighbour function for PostGIS that returns whole rows from the
targettable, using expanding search radii to avoid calculating distances for every geometry in the table. Of course,
thiswill 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
>
