Using GeomFromText in Python

204 views
Skip to first unread message

Mossa

unread,
Oct 23, 2017, 9:17:58 PM10/23/17
to SpatiaLite Users
Hi guys,

I would like to ask how can I use GeomFromText in my query. I tried to write below SQL but it didn't work:

cur.execute("select distance(GeomFromText('%f',geometry)) , astext(geometry) from dallas_texas_osm_line where name='South Cooper Street'", [matchPoint])


The actual query which is successfully run in spatialite-gui.exe is:

CREATE VIEW
DisPOINTtoGEOM as
select distance(GeomFromText('POINT(-98.476332 33.675324)'),geometry) as DistancePOINTtoLINE, astext(geometry)
from TarrantRoads
where Fullname='S Cooper St'

The problem I can't represent POINT(X Y) correctly in Python; I got such below error:

line 42, in <module>
    cur.execute("select distance(GeomFromText('?',geometry)) , astext(geometry) from dallas_texas_osm_line where name='South Cooper Street'", [matchPoint])
sqlite3.OperationalError: wrong number of arguments to function distance()

Any idea could help is appreciated.

Thanks,
Mossa

br...@frogmouth.net

unread,
Oct 24, 2017, 12:32:52 AM10/24/17
to spatiali...@googlegroups.com

What is “matchPoint” in your code? Also, why are you using “%f”?

 

Perhaps you should use the MakePoint() function inside the SQL, and pass the X and Y in.

 

Brad

 

AboMaher AlHajlah

unread,
Oct 24, 2017, 10:29:45 AM10/24/17
to spatiali...@googlegroups.com
Thanks for your reply,

matchPoint is a string has the this value: POINT(-98.476332 33.675324)

I'm trying to inject it in the query using "%f".

How can I use MakePoint().


Thanks,
Mousa

--
You received this message because you are subscribed to a topic in the Google Groups "SpatiaLite Users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/spatialite-users/ruX59Iywj2s/unsubscribe.
To unsubscribe from this group and all its topics, send an email to spatialite-users+unsubscribe@googlegroups.com.
To post to this group, send email to spatialite-users@googlegroups.com.
Visit this group at https://groups.google.com/group/spatialite-users.
For more options, visit https://groups.google.com/d/optout.

David Anderson

unread,
Oct 30, 2017, 7:02:05 PM10/30/17
to SpatiaLite Users
The problem is you are trying to mix and match Python formatting with SQL parameter substitution.
First %f is for floating point values.  Matchpoint has a string and two floating point variables.
You would need to substitute a string, so use %s.
However I don't think that will work as SQL parameter substitution in Python only works in the WHERE clause part of the SQL statement.  Or that has been my experience.

Do either
Build the string, then execute
sql = "select distance(GeomFromText(%s,geometry)) , astext(geometry) from dallas_texas_osm_line where name='South Cooper Street'" %[matchPoint]
cur.execute(sql)

or

sql = cur.execute("select distance(st_makepoint(?,?),geometry), astext(geometry) from dallas_texas_osm_line where name='South Cooper Street'",[-98.476332,33.675324])





On Tuesday, October 24, 2017 at 8:29:45 AM UTC-6, Mossa wrote:
Thanks for your reply,

matchPoint is a string has the this value: POINT(-98.476332 33.675324)

I'm trying to inject it in the query using "%f".

How can I use MakePoint().


Thanks,
Mousa

On Oct 23, 2017 11:32 PM, <br...@frogmouth.net> wrote:

What is “matchPoint” in your code? Also, why are you using “%f”?

 

Perhaps you should use the MakePoint() function inside the SQL, and pass the X and Y in.

 

Brad

 

--
You received this message because you are subscribed to a topic in the Google Groups "SpatiaLite Users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/spatialite-users/ruX59Iywj2s/unsubscribe.
To unsubscribe from this group and all its topics, send an email to spatialite-use...@googlegroups.com.
To post to this group, send email to spatiali...@googlegroups.com.

Mossa

unread,
Oct 31, 2017, 1:54:37 AM10/31/17
to SpatiaLite Users
Dear Mr. Anderson,

I do appreciate your clear explanation. 

Thanks for your valuable support. It is solved now.

Best regards,
Mossa
Reply all
Reply to author
Forward
0 new messages