How can I use mod_spatialite library

[Mossa]

Dear folks,

I have this nightmare issue. I am trying using spatialite library with Python. I followed the below link's instruction how can I successfully load extension(mod_spatialite):

https://pythongisandstuff.wordpress.com/2015/11/11/python-and-spatialite-32-bit-on-64-bit-windows/

I am successfully setting up the environment and the extension has loaded correctly after I tested the following piece of code:

import sqlite3

with sqlite3.connect(':memory:') as conn:

    conn.enable_load_extension(True)

    conn.execute("SELECT load_extension('mod_spatialite')")

The problem I am facing is that I cannot use any of spatialite functions such as Glength, Buffer, AsText, ...etc.

Below is an example of the SQL query:

cur.execute("SELECT distinct(FULLNAME), Glength(Geometry) FROM TarrantRoads")

I got the following error:

    cur.execute("SELECT distinct(FULLNAME), Glength(Geometry) FROM TarrantRoads")

sqlite3.OperationalError: no such function: Glength

I do not know how I can call spatialite functions in SQL after I successfully load mod_spatialite successfully.

Any help or suggestion is highly appreciated.

Thanks,

Mossa

[br...@frogmouth.net]

How are you getting from conn to cur? Can you show the whole sample?

 

Brad

 

[Mossa]

Yes, sure...

Please have a look below code:

import sqlite3

from sqlite3 import Error

import sys

if __name__ == '__main__':
 print (' main working')

 with sqlite3.connect(':memory:') as conn:
 conn.enable_load_extension(True)

 #conn.execute("CREATE VIRTUAL TABLE testrtree USING rtree(id,minX,maxX,minY,maxY);")

 conn.execute("SELECT load_extension('mod_spatialite')")

 conn.execute("SELECT InitSpatialMetaData()")
 database = "/Users/mousa/Documents/Tarrant.sqlite"

 vs = str([i for i in conn.execute("""SELECT spatialite_version()""")])
 vgeos = str([i for i in conn.execute("""SELECT geos_version()""")])
 print('Spatialite {} (GEOS {})'.format(vs.strip("()[]',"), vgeos.strip("()',[]")))

 try:

 conn = sqlite3.connect(database)

 print("SQLite : %s" % (sqlite3.sqlite_version))
 print("Thi is test")

 except Error as e:
 print(e)

 cur = conn.cursor()

 cur.execute("SELECT distinct(FULLNAME), Glength(Geometry) FROM TarrantRoads")

 rows = cur.fetchall()
 print(rows[30])

[br...@frogmouth.net]

The formatting is a bit messed up, but I take it that the spatialite_version part:

vs = str([i for i in conn.execute("""SELECT spatialite_version()""")])
vgeos = str([i for i in conn.execute("""SELECT geos_version()""")])
print('Spatialite {} (GEOS {})'.format(vs.strip("()[]',"), vgeos.strip("()',[]")))

is OK, but this part:

cur = conn.cursor()
cur.execute("SELECT distinct(FULLNAME), Glength(Geometry) FROM TarrantRoads")

is where you get the error.

If so, that is expected - you haven't loaded the extension onto that connection.

Brad

[Jukka Rahkonen]

Hi,

What does it print for you about the Spatialite and GEOS versions?

-Jukka Rahkonen-

> --
> You received this message because you are subscribed to the Google
> Groups "SpatiaLite Users" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to spatialite-use...@googlegroups.com.
> To post to this group, send email to
> spatiali...@googlegroups.com.
> Visit this group at https://groups.google.com/group/spatialite-users.
> For more options, visit https://groups.google.com/d/optout.

[Mossa]

Yes, the first part is just to show the version of spatialite I am using and version of geos.

For the second part, How can I load the extension to the connection I am using? Is not enough to call below line of code:

[Mossa]

It gives me the correct version of spatialite and GEOS, too. Which means the extension is working but when I tried to use any of spatialite functions, I got an error...

I believe I missed something but I don't know what is it.

[br...@frogmouth.net]

Your code changes the connection (conn variable), but only loads the extension on the first (memory) connection.
The second connection (to the database) doesn't have it loaded.

This isn't a spatialite problem, but in how you are using the sqlite connections.

Brad

[Mossa]

Thanks for your reply,

Could you please give me some examples how I load the extension in the connection and utilize spatialite functions without changing the connection.

I didn't see such complete example using mod_spatialite and execute SQL with spatialite functions.

[mj10777]

On Friday, 13 October 2017 08:41:53 UTC+2, Mossa wrote:

Yes, sure...

Please have a look below code:

import sqlite3

from sqlite3 import Error

import sys

if __name__ == '__main__':
 print (' main working')


 with sqlite3.connect(':memory:') as conn:

Connect here directory to your Database, instead of ':memory:'

 conn.enable_load_extension(True)
 #conn.execute("CREATE VIRTUAL TABLE testrtree USING rtree(id,minX,maxX,minY,maxY);")
 conn.execute("SELECT load_extension('mod_spatialite')")
 conn.execute("SELECT InitSpatialMetaData()")

Note needed, if  'Tarrent.sqlite' is a spatialite Database

 database = "/Users/mousa/Documents/Tarrant.sqlite"

skip this 

 vs = str([i for i in conn.execute("""SELECT spatialite_version()""")])
 vgeos = str([i for i in conn.execute("""SELECT geos_version()""")])
 print('Spatialite {} (GEOS {})'.format(vs.strip("()[]',"), vgeos.strip("()',[]")))

 try:

 conn = sqlite3.connect(database)

skip this 

 print("SQLite : %s" % (sqlite3.sqlite_version))
 print("Thi is test")

 except Error as e:
 print(e)

 cur = conn.cursor()

skip this 

 cur.execute("SELECT distinct(FULLNAME), Glength(Geometry) FROM TarrantRoads")

use 'conn' 

 rows = cur.fetchall()

use 'conn'  

 print(rows[30])

That should work, since the spatialite_version seems to work

- which version of spatialite are you using?

Mark 

[Mossa]

I got below error:

C:\Python27\python.exe C:/Users/mossa/PycharmProjects/SpatialIntegration/TestSpatialite.py

 main working

Traceback (most recent call last):

  File "C:/Users/mossa/PycharmProjects/SpatialIntegration/TestSpatialite.py", line 18, in <module>

    rows = conn.fetchall()

AttributeError: 'sqlite3.Connection' object has no attribute 'fetchall'

InitSpatiaMetaData() error:"table spatial_ref_sys already exists"

Process finished with exit code 1

The code is:

import sqlite3

from sqlite3 import Error

import sys

if __name__ == '__main__':
 print (' main working')

 with sqlite3.connect('/Users/mousa/Documents/Tarrant.sqlite') as conn:
 conn.enable_load_extension(True)

 conn.execute("SELECT load_extension('mod_spatialite')")
 conn.execute("SELECT InitSpatialMetaData()")

 conn.execute("SELECT distinct(FULLNAME), Glength(Geometry) FROM TarrantRoads")

 rows = conn.fetchall()

 print(rows[30])

[Mossa]

The versions of spatialite and Geos are:

Spatialite u'4.3.0 (GEOS u'3.5.0-CAPI-1.9.0 r4084)

[mj10777]

On Friday, 13 October 2017 09:28:33 UTC+2, Mossa wrote:

I got below error:

C:\Python27\python.exe C:/Users/mossa/PycharmProjects/SpatialIntegration/TestSpatialite.py

 main working

Traceback (most recent call last):

  File "C:/Users/mossa/PycharmProjects/SpatialIntegration/TestSpatialite.py", line 18, in <module>

    rows = conn.fetchall()

AttributeError: 'sqlite3.Connection' object has no attribute 'fetchall'

InitSpatiaMetaData() error:"table spatial_ref_sys already exists"

Process finished with exit code 1

The code is:

import sqlite3

from sqlite3 import Error

import sys

if __name__ == '__main__':
 print (' main working')


 with sqlite3.connect('/Users/mousa/Documents/Tarrant.sqlite') as conn:
 conn.enable_load_extension(True)
 conn.execute("SELECT load_extension('mod_spatialite')")
 conn.execute("SELECT InitSpatialMetaData()")

Sorry, not firm in python 

 cur = conn.cursor()

is needed since 'fetchall' is for a cursor

[Mossa]

Thanks, there is no error when I execute the code; however, also there are no results in cur neither conn while the same SQL provides a several tables.

I think I figure it out:

I use  

cur.execute("SELECT distinct(FULLNAME), Glength(Geometry) FROM TarrantRoads")

instead of 

conn.execute("SELECT distinct(FULLNAME), Glength(Geometry) FROM TarrantRoads")

and it works fine and fetches the results.

Thank you all for the help...

Mossa.

[a.fu...@lqt.it]

Hi Mossa,

you'll find a practical example within the Cookbook:
http://www.gaia-gis.it/gaia-sins/spatialite-cookbook/html/python.html

(it's based on pyspatialite instead of pysqlite, but the overall logic
is almost identical except for the "load_extension" step):

bye Sandro

[AboMaher AlHajlah]

Thanks Furieri,

I have visited this site but when I saw it using different library I thought it may be different than my installed library...

I will give it a try and now I believe it will work with me...

Best regards,

Mossa

--
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/93eFMPty05k/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.