From: Jan Martin <janmartin3@...>
Date: 2009-06-24T15:18:25+09:00
Subject: How to connect to SpatiaLite, a spatial enabled sqlite3 db?

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