How to connect to SpatiaLite, a spatial enabled sqlite3 db?

[Jan Martin]

Hi all,

I am a beginner making a decision on what programing language I spent
more time to learn it.
In principle I really really like ruby.

However for a GIS project of mine I like to use spatialite, a sqlite
database with spatial extension:
http://www.gaia-gis.it/spatialite/

And it seems there is no way to interface it from ruby?

So far I implemented the backend for my GIS project both in perl and
python.
Currently it runs on apache2 as cgi using mod_python.

Frontend is html with openlayers.org javascript that queries the backend
like this:
http://mydomain.com/cgi-bin/data.py?lat="+lon+"&lon="+lat

Do yo see any way to do the backend in ruby?
Or would you suggest a totally different approach?
If so, wich?

(Please no comments on code quality, this is strictly throw-away-code.
Not for real world usage.)

#!/usr/bin/python
from mod_python import util
from pysqlite2 import dbapi2 as sqlite

def index(req):
data = util.FieldStorage(req)
lat= data['lat'];
lon= data['lon'];

DB = sqlite.connect('exif.sqlite');
DB.enable_load_extension(True);
DB.execute('SELECT load_extension("libspatialite.so")');
DBCursor = DB.cursor()

strSQL = "SELECT FromPath\n\
FROM ExifPhoto\n\
WHERE Distance(GpsGeometry,\n\
GeomFromText(\"POINT("+lat+" "+lon+")\", 4326)) =\n\
(\n\
SELECT Min(Distance(GpsGeometry,\n\
GeomFromText(\"POINT("+lat+" "+lon+")\", 4326)))\n\
FROM ExifPhoto\n\
);\n"

DBCursor.execute( strSQL );
for row in DBCursor:
filename = row[0].rpartition('/')[2];
return(filename)
DBCursor.close();
DB.close();

 

[Eric Hodel]

I am a beginner making a decision on what programing language I spent
more time to learn it.
In principle I really really like ruby.

However for a GIS project of mine I like to use spatialite, a sqlite
database with spatial extension:
http://www.gaia-gis.it/spatialite/

And it seems there is no way to interface it from ruby?

From your python code below, it seems to be plain old SQL.

def index(req):
data = util.FieldStorage(req)
lat= data['lat'];
lon= data['lon'];

DB = sqlite.connect('exif.sqlite');
DB.enable_load_extension(True);
DB.execute('SELECT load_extension("libspatialite.so")');

Looks like the ruby equivalent method to this DB.execute would be
SQLite3:atabase#execute, which should handle the same SQL syntax.

DBCursor = DB.cursor()

strSQL = "SELECT FromPath
FROM ExifPhoto
WHERE Distance(GpsGeometry,
GeomFromText(\"POINT("+lat+" "+lon+")\", 4326)) =
(
SELECT Min(Distance(GpsGeometry,
GeomFromText(\"POINT("+lat+" "+lon+")\", 4326)))
FROM ExifPhoto
);"

DBCursor.execute( strSQL );

Ditto.

 

[Jan Martin]

I gave it a try.
However it seems all the spatial functionality does not work.
And thats what I need.
Check the "db.execute" lines.

Any ideas?

Thanks,
Jan
#!/usr/bin/env ruby

require 'rubygems'
require 'sqlite3'

db = SQLite3:atabase.new( "exif.sqlite" )

# Does NOT work:
db.execute( "SELECT Y(GpsGeometry),
X(GpsGeometry),DateTime(GpsTimestamp), FromPath FROM ExifPhoto" ) do
|row|

# Works:
# db.execute( "SELECT DateTime(GpsTimestamp), FromPath FROM ExifPhoto"
) do |row|
p row
end
db.close

Result:

NON-WORKING:
me@home:~/python$ ruby ruby.rb
/home/me/.gem/ruby/1.8/gems/sqlite3-ruby-1.2.4/lib/sqlite3/errors.rb:62:in
`check': no such function: Y (SQLite3::SQLException)
from
/home/me/.gem/ruby/1.8/gems/sqlite3-ruby-1.2.4/lib/sqlite3/statement.rb:39:in
`initialize'
from
/home/me/.gem/ruby/1.8/gems/sqlite3-ruby-1.2.4/lib/sqlite3/database.rb:154:in
`new'
from
/home/me/.gem/ruby/1.8/gems/sqlite3-ruby-1.2.4/lib/sqlite3/database.rb:154:in
`prepare'
from
/home/me/.gem/ruby/1.8/gems/sqlite3-ruby-1.2.4/lib/sqlite3/database.rb:181:in
`execute'
from ruby.rb:8

WORKING:
me@home:~/python$ ruby ruby.rb
["2008-10-23 14:45:20", "DSCN0029.JPG"]
["2008-10-23 14:41:49", "DSCN0025.JPG"]
["2008-10-23 14:28:17", "DSCN0012.JPG"]
["2008-10-23 14:36:47", "DSCN0021.JPG"]
["2008-10-23 14:54:00", "DSCN0040.JPG"]
["2008-10-23 14:27:07", "DSCN0010.JPG"]
["2008-10-23 14:50:40", "DSCN0038.JPG"]
["2008-10-23 14:57:41", "DSCN0042.JPG"]
["2008-10-23 14:42:29", "DSCN0027.JPG"]

I am a beginner making a decision on what programing language I spent
more time to learn it.
In principle I really really like ruby.

However for a GIS project of mine I like to use spatialite, a sqlite
database with spatial extension:
http://www.gaia-gis.it/spatialite/

And it seems there is no way to interface it from ruby?

From your python code below, it seems to be plain old SQL.

def index(req):
data = util.FieldStorage(req)
lat= data['lat'];
lon= data['lon'];

DB = sqlite.connect('exif.sqlite');
DB.enable_load_extension(True);
DB.execute('SELECT load_extension("libspatialite.so")');

Looks like the ruby equivalent method to this DB.execute would be
SQLite3:atabase#execute, which should handle the same SQL syntax.

);"

DBCursor.execute( strSQL );

Ditto.

 

[Eric Hodel]

Don't top post.

I am a beginner making a decision on what programing language I
spent
more time to learn it.
In principle I really really like ruby.

However for a GIS project of mine I like to use spatialite, a sqlite
database with spatial extension:
http://www.gaia-gis.it/spatialite/

And it seems there is no way to interface it from ruby?

From your python code below, it seems to be plain old SQL.

def index(req):
data = util.FieldStorage(req)
lat= data['lat'];
lon= data['lon'];

DB = sqlite.connect('exif.sqlite');
DB.enable_load_extension(True);
DB.execute('SELECT load_extension("libspatialite.so")');

Looks like the ruby equivalent method to this DB.execute would be
SQLite3:atabase#execute, which should handle the same SQL syntax.

);"

DBCursor.execute( strSQL );

Ditto.

I gave it a try.
However it seems all the spatial functionality does not work.
And thats what I need.
Check the "db.execute" lines.

Any ideas?

Thanks,
Jan
#!/usr/bin/env ruby

require 'rubygems'
require 'sqlite3'

db = SQLite3:atabase.new( "exif.sqlite" )

# Does NOT work:
db.execute( "SELECT Y(GpsGeometry),
X(GpsGeometry),DateTime(GpsTimestamp), FromPath FROM ExifPhoto" ) do
|row|

You haven't loaded the geometry extension like you did in the python
script.

 

[Jan Martin]

Don't top post.



You haven't loaded the geometry extension like you did in the python
script.

Hi Eric,

maybe I should have stated it clearer:

I am a beginner, so I do not know how to load extensions in ruby.
Also I do not know where to get the extension from, and how to install
it.
I am not even sure it exists for ruby.

May I ask for a bit more info on how to load the SpatiaLite ruby
extension?

Thanks,
Jan

 

[Ryan Davis]

I am a beginner, so I do not know how to load extensions in ruby.
Also I do not know where to get the extension from, and how to install
it.

Eric was referring to your python code:

DB.execute('SELECT load_extension("libspatialite.so")');

that's a plain DB execute with a select statement to load spatialite.
You should be doing almost the same thing on the ruby side. The rest
of the code will prolly Just Work (or at least be much closer) after
that.

 

[Jan Martin]

Eric was referring to your python code:


that's a plain DB execute with a select statement to load spatialite.
You should be doing almost the same thing on the ruby side. The rest
of the code will prolly Just Work (or at least be much closer) after
that.

Next try:

#!/usr/bin/env ruby

require 'rubygems'
require 'sqlite3'
db = SQLite3:atabase.new( "exif.sqlite" )

#Does NOT work:
db.execute('SELECT load_extension("libspatialite.so")');
db.execute( "SELECT DateTime(GpsTimestamp), FromPath FROM ExifPhoto"
) do |row|
p row
end

db.close

Does anyone know how to enable the sqlite extension mechanism from ruby?
It seems it's switched off by default for security reasons.

Error I get with the code above:

ruby test.rb
/home/me/.gem/ruby/1.8/gems/sqlite3-ruby-1.2.4/lib/sqlite3/errors.rb:62:in
`check': not authorized (SQLite3::SQLException)
from
/home/me/.gem/ruby/1.8/gems/sqlite3-ruby-1.2.4/lib/sqlite3/resultset.rb:56:in
`check'
from
/home/me/.gem/ruby/1.8/gems/sqlite3-ruby-1.2.4/lib/sqlite3/resultset.rb:48:in
`commence'
from
/home/me/.gem/ruby/1.8/gems/sqlite3-ruby-1.2.4/lib/sqlite3/resultset.rb:38:in
`initialize'
from
/home/me/.gem/ruby/1.8/gems/sqlite3-ruby-1.2.4/lib/sqlite3/statement.rb:135:in
`new'
from
/home/me/.gem/ruby/1.8/gems/sqlite3-ruby-1.2.4/lib/sqlite3/statement.rb:135:in
`execute'
from
/home/me/.gem/ruby/1.8/gems/sqlite3-ruby-1.2.4/lib/sqlite3/database.rb:182:in
`execute'
from
/home/me/.gem/ruby/1.8/gems/sqlite3-ruby-1.2.4/lib/sqlite3/database.rb:157:in
`prepare'
from
/home/me/.gem/ruby/1.8/gems/sqlite3-ruby-1.2.4/lib/sqlite3/database.rb:181:in
`execute'
from test.rb:8

 

[Ryan Davis]

Does anyone know how to enable the sqlite extension mechanism from
ruby?
It seems it's switched off by default for security reasons.

Error I get with the code above:

ruby test.rb
/home/me/.gem/ruby/1.8/gems/sqlite3-ruby-1.2.4/lib/sqlite3/errors.rb:
62:in
`check': not authorized (SQLite3::SQLException)
from

"not authorized" via line 8 of your script (the load_extension part).
This seems more like a permissions thing. You may want to look into
that (google might be your friend here) or you may want to look in the
files/lines listed in the stack trace for a clue.