Shooting_Star not accepting a JOINing query

[yancho]

Hi,

anton, I tried changing the Shooting Star function like you told me to accept a WHERE clause, and am calling for it via another function.

This is how I am calling it :

      var_params_shootingstar := 
          $v$
                WHERE
                       t1.status = TRUE,
                       AND    (
                                (
                                  (
                                    SELECT
                                          z( pointn( geometryn(d3.the_geom,1), 1) ) AS z
                                          FROM streets_3d d3
                                               WHERE d3.gid = t1.gid 
                                   ) / $v$ || var_rain_cm || $v$
                                  ) >= $v$ || rain_ratio || $v$
                                ),
                        AND     ( 
                                   $v$ || var_ratio_cartype_rain || $v$ <= 10
                                ) ,
                    
           $v$;

       RAISE NOTICE 'Where clause : %', var_params_shootingstar;

       var_text_shootingstar :=
        $qt$
          SELECT gid, the_geom
              FROM shootingstar_sp_where_new
              (
                'streets',
        $qt$ || var_gid_a || $qt$,
        $qt$ || var_gid_b || $qt$,
                5000,
                'length',
                true,
                true,
	$qt$ || quote_literal(var_params_shootingstar) || $qt$
              )
        $qt$;
      
      RAISE NOTICE 'SQL Query : %',var_text_shootingstar;
  
      var_id := 0;

      FOR rec_shooting_star IN
       	    EXECUTE var_text_shootingstar

The query part which I added the WHERE clause is :

	query := 'SELECT gid, the_geom FROM ' || 
          'shortest_path_shooting_star(''SELECT t1.gid as id, t1.source::integer, ' || 
          't1.target::integer, t1.'||cost_column||'::double precision as cost, ' || 
          't1.x1::double precision, t1.y1::double precision, t1.x2::double ' ||
          't1.precision, t1.y2::double precision, t1.rule::varchar, ' ||
	  't1.to_cost::double precision ';
	  
	IF rc THEN query := query || ' , t1.reverse_cost ';  
	END IF;
	  
	query := query || 'FROM ' || quote_ident(geom_table) || ' AS t1 ' || query_text || ' AND setSRID(''''BOX3D('||
          ll_x-delta||' '||ll_y-delta||','||ur_x+delta||' '||
          ur_y+delta||')''''::BOX3D, ' || srid || ') && the_geom'', ' || 
          quote_literal(sourceid) || ' , ' || 
          quote_literal(targetid) || ' , '''||text(dir)||''', '''||text(rc)||''' ) , ' || 
          quote_ident(geom_table) || ' where edge_id = gid ' ;

	RAISE NOTICE 'Query is : %', query;

When I am running my function, this is what I am getting :

NOTICE:  SQL Query : 
          SELECT gid, the_geom
              FROM shootingstar_sp_where_new
              (
                'streets',
        36368,
        39134,
                5000,
                'length',
                true,
                true,
                '',
	'
                WHERE
                       t1.status = TRUE,
                       AND    (
                                (
                                  (
                                    SELECT
                                          z( pointn( geometryn(d3.the_geom,1), 1) ) AS z
                                          FROM streets_3d d3
                                               WHERE d3.gid = t1.gid 
                                   ) / 0.0001
                                  ) >= 1
                                ),
                        AND     ( 
                                   0 <= 10
                                ) ,
                    
           '
              )
        
NOTICE:  Query is : SELECT gid, the_geom FROM shortest_path_shooting_star('SELECT t1.gid as id, t1.source::integer, t1.target::integer, t1.length::double precision as cost, t1.x1::double precision, t1.y1::double precision, t1.x2::double t1.precision, t1.y2::double precision, t1.rule::varchar, t1.to_cost::double precision  , t1.reverse_cost FROM streets AS t1  
                WHERE
                       t1.status = TRUE,
                       AND    (
                                (
                                  (
                                    SELECT
                                          z( pointn( geometryn(d3.the_geom,1), 1) ) AS z
                                          FROM streets_3d d3
                                               WHERE d3.gid = t1.gid 
                                   ) / 0.0001
                                  ) >= 1
                                ),
                        AND     ( 
                                   0 <= 10
                                ) ,
                    
            AND setSRID(''BOX3D(630264.299796684 224548.091433566,643684.863573708 241417.300516518)''::BOX3D, -1) && the_geom', '36368' , '39134' , 'true', 'true' ) , streets where edge_id = gid 
CONTEXT:  PL/pgSQL function "give_most_fuel_friendly" line 127 at for over execute statement

ERROR: syntax error at or near "t1"
SQL state: 42601
Context: PL/pgSQL function "shootingstar_sp_where_new" line 116 at for over execute statement
PL/pgSQL function "give_most_fuel_friendly" line 127 at for over execute statement

Any idea what I can do please? I think it is a bug in the shootingstar function since it seems it doesn't accept a join to another table :|

I am all the days on IRC, so if you can try to log in so we can discuss better it would be really nice of you.

Thanks

[anton]

Hi Matthew,

Please look at the sandbox/wrappers/routing_core_smart.sql file - there you can see how do I join the table and the result of the function.

[yancho]

Thanks anton :)

I gave it a look, but its quite a long one. Do you remember what is the function that does the joining please? Thanks

On mine what I did was do a column join and did the query on the same table

Thanks for your support! :)

[daniel]

Hi Matthew,

does this help you? http://pgrouting.postlbs.org/wiki/SQLTipsAndTricks

It's probably a good idea to extend the Wiki with some SQL recipies.

[yancho]

Hi Daniel,

Yep seems it does the trick :) Thanks alot. Will use it if the need arises. What I did was make a view which connects the columns itself so I did a work around :)

Thanks

[daniel]

Spam problem

[daniel]

No, sorry, it was really fixed already.