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.

My question is about Postgres/PgRouting syntax, when I wrote query like this:

SELECT * FROM shortest_path('SELECT gid  AS id,
      source::int4 AS source,
      target::int4 AS target,
      cost::float8 AS cost,
      reverse_cost::float8 AS reverse_cost
from network where country='USA'  ',                           
257027,
276521,
true,
true)

I got error:

ERROR:  syntax error at or near "USA"
LINE 6:  from network where country='USA'  ',             
ERROR: syntax error at or near "USA"
SQL state: 42601
Character: 226

So, I create new field in the network table countryInt and I updated field like this:

Update network set countryInt =1 where country='USA'

So my query is:

SELECT * FROM shortest_path('SELECT gid  AS id,
    source::int4 AS source,
    target::int4 AS target,
    cost::float8 AS cost,
    reverse_cost::float8 AS reverse_cost
from network where countryInt=1 ',                           
257027,
276521,
true,
true)

And now it works. But what if I have to put some string in condition, quotation marks are problem?

How to resolve this error?

Thank you in advance.

share|improve this question

1 Answer 1

up vote 5 down vote accepted

Escape the single quote with another single quote:

SELECT * FROM shortest_path('SELECT gid  AS id,
      source::int4 AS source,
      target::int4 AS target,
      cost::float8 AS cost,
      reverse_cost::float8 AS reverse_cost
from network where country=''USA''  ',                           
257027,
276521,
true,
true)
share|improve this answer
    
Steve, thank you, thank you, thank you ...This was my nightmare –  Route Maker Feb 10 '12 at 7:44

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.