Skip to main content
added 90 characters in body
Source Link
joanolo
  • 13.6k
  • 8
  • 39
  • 66

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
    WHERE
        (SELECT 
            ST_Extent(ST_Union(geom)) 
       CROSS FROMJOIN xt
            rtWHERE
        )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.

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)
    )   
    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
    WHERE
        (SELECT 
            ST_Extent(ST_Union(geom)) 
        FROM 
            rt
        ) && 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.

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.

Source Link
joanolo
  • 13.6k
  • 8
  • 39
  • 66

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)
    )   
    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
    WHERE
        (SELECT 
            ST_Extent(ST_Union(geom)) 
        FROM 
            rt
        ) && 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.