Help to improve a function

Question

I have this function working fine:

CREATE or replace FUNCTION pointscanner(
    IN source integer,
    IN target integer,
    IN k integer,
    IN directed boolean,
    IN quant integer
) 
RETURNS table(way geometry, name text, distance double precision, tags hstore, "operator" text, 
        admin_level text, z_order integer) AS $$
DECLARE 
    geomRoute geometry;
    routeBB box2d;
BEGIN
    SELECT INTO geomRoute ST_Union(geom) from route_agg($1, $2, $3, $4);
    routeBB := ST_Extent(geomRoute);

    RETURN QUERY
    SELECT pt.way, pt.name, ST_Distance( rt.geom, ST_Transform(pt.way,4326) ) as distance, 
        pt.tags, pt.operator, pt.admin_level, pt.z_order 
    FROM planet_osm_point pt, route_agg($1, $2, $3, $4) rt
    where routeBB && ST_Transform(pt.way,4326)
    order by distance asc limit $5;

END; $$  
LANGUAGE 'plpgsql' VOLATILE;    

But as you can see I'm calculating the route twice (route_agg) and sometimes it may be very costly. How can I improve this to run route_agg just once?

Answer 1

I'd try this (which I don't fully understand, but should be equivalent):

CREATE or REPLACE FUNCTION pointscanner
(
    IN _source integer,
    IN _target integer,
    IN _k integer,
    IN _directed boolean,
    IN _quant integer
) 
RETURNS table(way geometry, name text, distance double precision, tags hstore, "operator" text, 
        admin_level text, z_order integer) AS $$
BEGIN
    RETURN QUERY
    WITH rt AS
    (
        SELECT 
            * 
        FROM 
            route_agg(_source, _target, _k, _directed)
    ),
    xt AS
    (
         SELECT 
             ST_Extent(ST_Union(geom)) AS extent
         FROM 
             rt
    )        
    SELECT 
        pt.way, 
        pt.name, 
        ST_Distance( rt.geom, ST_Transform(pt.way,4326) ) as distance, 
        pt.tags, 
        pt.operator, 
        pt.admin_level, 
        pt.z_order 
    FROM 
        planet_osm_point pt 
        CROSS JOIN rt
        CROSS JOIN xt
    WHERE
        xt.extent && ST_Transform(pt.way, 4326)
    ORDER BY 
        distance ASC 
    LIMIT 
        _quant;
END; $$  
LANGUAGE 'plpgsql' VOLATILE;    

---

NOTE: it would be helpful to have the definition of the planet_osm_point table, and of route_agg.

Answer 2

Same to joanolo´s answer but smaller:

CREATE or REPLACE FUNCTION pointscanner
(
    IN _source integer,
    IN _target integer,
    IN _k integer,
    IN _directed boolean,
    IN _quant integer
) 
RETURNS table(
    way geometry, name text, 
    distance double precision, tags hstore, 
    "operator" text, admin_level text, 
    z_order integer)
AS $$
DECLARE 
    geomRoute geometry;
    routeBB box2d;
BEGIN
    SELECT INTO geomRoute ST_Union(geom) from route_agg($1, $2, $3, $4);
    routeBB := ST_Extent(geomRoute);

    RETURN QUERY
    SELECT 
        pt.way, 
        pt.name, 
        ST_Distance( geomRoute, ST_Transform(pt.way,4326) ) as distance, 
        pt.tags, 
        pt.operator, 
        pt.admin_level, 
        pt.z_order 
    FROM 
        planet_osm_point pt
    WHERE
        routeBB && ST_Transform(pt.way, 4326)
    ORDER BY 
        distance ASC 
    LIMIT 
        _quant;
END; $$  
LANGUAGE 'plpgsql' VOLATILE;  

Answer 3

This is the complete full version of the working function:

CREATE OR REPLACE FUNCTION public.pointscanner(IN routegeometry text, IN criteria text, IN source text)
  RETURNS TABLE(way geometry, name text, distance double precision, tags hstore, operator text, admin_level text, z_order integer) AS
$BODY$
DECLARE 
    geomRoute geometry;
    routeBB box2d;
    sql text;
BEGIN
    geomRoute = ST_GeomFromText( $1 ,4326);
    routeBB := ST_Extent(geomRoute);

    sql := 'SELECT pt.way, pt.name, ST_Distance( $1, ST_Transform(pt.way,4326) ) * 111195 as distance, pt.tags, pt.operator, pt.admin_level, pt.z_order '
     || ' FROM ' || source || ' pt where $2 && ST_Transform(pt.way,4326) and ' || criteria;

    RETURN QUERY EXECUTE sql USING geomRoute, routeBB;
END; $BODY$
  LANGUAGE plpgsql VOLATILE

As you can see, I left to te user to pass the route geometry. So I don't need to calculate it. This version is very fast.