Understanding a complex query in pgrouting

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
gautamz07
Forum Contributor
Posts: 331
Joined: Wed May 14, 2014 12:18 pm

Understanding a complex query in pgrouting

Post by gautamz07 »

I am new to pgrouting and basically am familier with sql and postgresql, i came across the following command below:

Code: Select all

SELECT seq, id1 AS node, id2 AS edge, cost FROM pgr_dijkstra('
                SELECT gid AS id,
                         source::integer,
                         target::integer,
                         length::double precision AS cost
                        FROM ways',
                30, 60, false, false);
I am used to seeing sql statements as below:

Code: Select all

SELECT param1 , param2 
But i am thrown off the hook when i see something like:

Code: Select all

SELECT seq, id1 AS node, id2 AS edge
What's with the

Code: Select all

AS


part ?

Also is the below a subquery ?? and can somebody explain it ? I am not very clear with the below query.

Code: Select all

(' SELECT gid AS id,
         source::integer,
         target::integer,
         length::double precision AS cost
        FROM ways',
30, 60, false, false);
Thank you.

got the above query from here http://workshop.pgrouting.org/chapters/ ... h-dijkstra
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Understanding a complex query in pgrouting

Post by requinix »

gautamz07 wrote:What's with the

Code: Select all

AS


part ?
AS means an alias. Just "param1" means the column returned will be named param1. "param1 AS foo" means the column returned will be named foo. Same column, different name.
In this case, id1 and id2 don't mean much, so the author used aliases so you'd know that id1 was the node and id2 was the edge.
gautamz07 wrote:Also is the below a subquery ?? and can somebody explain it ? I am not very clear with the below query.
It isn't quite a subquery. pgr_dijkstra is a function that returns a resultset just like a SELECT subquery would. You passed it a string containing SQL code because that's how the function is designed to work, the function does whatever it does, and you SELECTed from what it returned.
User avatar
gautamz07
Forum Contributor
Posts: 331
Joined: Wed May 14, 2014 12:18 pm

Re: Understanding a complex query in pgrouting

Post by gautamz07 »

Thanks requinix ! that clears alot of my doubts ! :)
User avatar
gautamz07
Forum Contributor
Posts: 331
Joined: Wed May 14, 2014 12:18 pm

Re: Understanding a complex query in pgrouting

Post by gautamz07 »

requinix can you explain the below syntax::

Code: Select all

source::integer,
target::integer,
what is source::integer ?? what does this syntax mean , it would be great , if you can lead me to some documentation too.

Thank you.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Understanding a complex query in pgrouting

Post by requinix »

That's PostgreSQL syntax. I suggest you start learning about it...

That's a type-cast, and is the same as saying

Code: Select all

CAST(source AS integer)
CAST(target AS integer)
"source" could have been a string or an integer, but "source::integer" will definitely be an integer, and their function needs numbers and not strings.
User avatar
gautamz07
Forum Contributor
Posts: 331
Joined: Wed May 14, 2014 12:18 pm

Re: Understanding a complex query in pgrouting

Post by gautamz07 »

perfect thanks !
Post Reply