I created function search nearest point (pois) by using pg_routing as following:
CREATE OR REPLACE function find_pois(inn_point text, distance int) RETURNS float[] AS --point(x y)
$$
DECLARE
source_id INTEGER;
target_id INTEGER;
row_data RECORD;
i integer;
retVal float[];
temp_length float;
BEGIN
-- search edge of source
SELECT into source_id source ,
ST_Distance(the_geom, ST_GeometryFromText(
'POINT(587314.857692619 2325407.8399756)', -1)) AS dist
FROM ways
WHERE the_geom && setsrid(
'BOX3D(587214.857692519 2325407.8399756, 587414.857692619 2325507.8399756)'::box3d, -1)
ORDER BY dist LIMIT 1;
-- get all point in 500m radius
FOR row_data IN SELECT b.gid, x(the_geom), y(the_geom) FROM station b
WHERE ST_DWithin(the_geom, 'POINT(587314.857692619 2325407.8399756)', 500.0)
LOOP
-- with each point, search shortest part
-- 1. find taget_id
SELECT into target_id target ,
ST_Distance(the_geom, ST_GeometryFromText(
'''POINT(' || row_data.x || ' ' || row_data.y || ')''', -1)) AS dist
FROM ways
WHERE the_geom && setsrid(
'''BOX3D(' || row_data.x-100 || ' ' || row_data.y-100 || ', ' || row_data.x+100 || ' ' || row_data.y+100 || ')'''::box3d, -1) --cast((cast(row_data.x as float))-100 as text)
ORDER BY dist LIMIT 1;
-- 2. tim duong di ngan nhat tu diem nguon toi diem dich
SELECT --rt.gid, ST_AsGeoJSON(rt.the_geom) AS geojson,
into temp_length sum(length(rt.the_geom))
--ways.gid
FROM ways,
(SELECT gid, the_geom FROM dijkstra_sp_delta( 'ways', source_id, target_id, 100) ) as rt
WHERE ways.gid=rt.gid;
retVal := temp_length;
END LOOP;
return retVal;
END;
$$ LANGUAGE plpgsql;
Everytime i try excute this function, i have error:
ERROR: BOX3D parser - doesnt start with BOX3D(
LINE 1: ... || ' ' || $4 || ', ' || $5 || ' ' || $6 || ')'''::box...
^
QUERY: SELECT target , ST_Distance(the_geom, ST_GeometryFromText( '''POINT(' || $1 || ' ' || $2 || ')''', -1)) AS dist FROM ways WHERE the_geom && setsrid( '''BOX3D(' || $3 || ' ' || $4 || ', ' || $5 || ' ' || $6 || ')'''::box3d, -1) ORDER BY dist LIMIT 1
CONTEXT: PL/pgSQL function "find_pois" line 27 at SQL statement
I guest there are some error on string in BOX3d but cannot fix Please help me.