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

Discussion in 'Ruby' started by Jan Martin, Jun 24, 2009.

  1. Jan Martin

    Jan Martin Guest

    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();
    --
    Posted via http://www.ruby-forum.com/.
     
    Jan Martin, Jun 24, 2009
    #1
    1. Advertising

  2. Jan Martin

    Eric Hodel Guest

    On Jun 23, 2009, at 23:18, Jan Martin wrote:
    > 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::Database#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.
     
    Eric Hodel, Jun 24, 2009
    #2
    1. Advertising

  3. Jan Martin

    Jan Martin Guest

    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::Database.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"]

    Eric Hodel wrote:
    > On Jun 23, 2009, at 23:18, Jan Martin wrote:
    >> 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::Database#execute, which should handle the same SQL syntax.
    >
    >> );"
    >>
    >> DBCursor.execute( strSQL );

    >
    > Ditto.


    --
    Posted via http://www.ruby-forum.com/.
     
    Jan Martin, Jun 24, 2009
    #3
  4. Jan Martin

    Eric Hodel Guest

    Don't top post.

    On Jun 24, 2009, at 00:46, Jan Martin wrote:
    > Eric Hodel wrote:
    >> On Jun 23, 2009, at 23:18, Jan Martin wrote:
    >>> 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::Database#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::Database.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.
     
    Eric Hodel, Jun 24, 2009
    #4
  5. Jan Martin

    Jan Martin Guest

    Eric Hodel wrote:
    > Don't top post.
    >
    > On Jun 24, 2009, at 00:46, Jan Martin wrote:
    >>>>
    >>>> DB.enable_load_extension(True);

    >>
    >>
    >> require 'rubygems'
    >> require 'sqlite3'
    >>
    >> db = SQLite3::Database.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.


    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

    --
    Posted via http://www.ruby-forum.com/.
     
    Jan Martin, Jun 24, 2009
    #5
  6. Jan Martin

    Ryan Davis Guest

    On Jun 24, 2009, at 02:16 , Jan Martin wrote:

    > Eric Hodel wrote:
    >> You haven't loaded the geometry extension like you did in the python
    >> script.

    >
    > 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.
     
    Ryan Davis, Jun 24, 2009
    #6
  7. Jan Martin

    Jan Martin Guest

    Ryan Davis wrote:
    >
    > 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.


    Next try:

    #!/usr/bin/env ruby

    require 'rubygems'
    require 'sqlite3'
    db = SQLite3::Database.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


    --
    Posted via http://www.ruby-forum.com/.
     
    Jan Martin, Jun 24, 2009
    #7
  8. Jan Martin

    Ryan Davis Guest

    On Jun 24, 2009, at 02:50 , Jan Martin wrote:

    > 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.
     
    Ryan Davis, Jun 24, 2009
    #8
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Shi Mu
    Replies:
    1
    Views:
    635
    Magnus Lycka
    Oct 13, 2005
  2. wallge
    Replies:
    14
    Views:
    724
    wallge
    Jan 30, 2007
  3. paul
    Replies:
    1
    Views:
    338
    Victor Bazarov
    Oct 1, 2005
  4. Jeffrey 'jf' Lim
    Replies:
    5
    Views:
    508
    Jeffrey 'jf' Lim
    Apr 9, 2007
  5. SunSw0rd
    Replies:
    4
    Views:
    278
    SunSw0rd
    Jul 2, 2009
Loading...

Share This Page