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.

I do not understand how the operator works some times... Here is an example that works,

"SELECT ST_Distance(ST_GeomFromText('POINT(-3.16496271127842 55.9262620593642)'),ST_GeomFromWKB(a.geometry)),ST_AsText(ST_Centroid(a.geometry)) FROM <table> As a;"

and here ia an example that fails,I use a simple execute methos like this,

cc = conn.cursor()
a = ('POINT(-3.16496271127842 55.9262620593642)')
b = "SELECT ST_Distance(ST_GeomFromText(%s),ST_GeomFromWKB(a.geometry)),ST_AsText(ST_Centroid(a.geometry)) FROM <table> As a;"
cc.execute(b,a)

sometimes, the operator works fine and sometimes it does not. I have read documentation about the operators and how to use python with SQL but I have not found WHY it acts like this in a random kind of way. Does anyone have any hints and tips? Cheers, A

share|improve this question
add comment

1 Answer 1

up vote 0 down vote accepted

I think I found the reason in the docs:

For positional variables binding, the second argument must always be a sequence, even if it contains a single variable. And remember that Python requires a comma to create a single element tuple:

 cur.execute("INSERT INTO foo VALUES (%s)", "bar")    # WRONG
 cur.execute("INSERT INTO foo VALUES (%s)", ("bar"))  # WRONG
 cur.execute("INSERT INTO foo VALUES (%s)", ("bar",)) # correct
 cur.execute("INSERT INTO foo VALUES (%s)", ["bar"])  # correct

So in your case this should work:

 a = ("POINT(-3.16496271127842 55.9262620593642)",)
share|improve this answer
    
Nope, it does not work like that either.. Also, my point is ususally passed not hardcoded but loaded from the database etc. So my code looks like this but still does not work, a = "SELECT ST_Distance(ST_GeomFromText(%s),ST_GeomFromWKB(geometry)),ST_AsText(ST_Centroid(‌​geometry)) FROM cameron_toll_nodes As a WHERE name=%s;" b = (point,'exit') # point is POINT(x y) format cc.execute(a,b) Any ideas? :) –  Antony Jan 4 '12 at 17:20
    
Please update your question with full information. –  underdark Jan 4 '12 at 17:24
    
hmm, I will try your solution! I also tried another method and it worked! I re-assigned my point to a new variable and used that instead. Somethign like this newPoint = point. Right, so what should I do now? should I re-post or leave it like this? –  Antony Jan 4 '12 at 17:32
add comment

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.