Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

everybody. I created function search nearest point (pois) by using pg_routing as folowing

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. Thank a lot.

share|improve this question
    
please help me. I have try with ''''POINT(' || row_data.x ....)'''' or '\' but it always had same error? –  Truong Pham Oct 24 '12 at 5:17

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.