Option to use spatialite

[michael groble]

I'd like to use a spatialite amalgamation and took a stab at modifying
the code so it will be used if found given the --with-sqlite3-include
and --with-sqlite3-lib options.

https://github.com/michael-groble/sqlite3-ruby/commit/83fe22df0cc0efc1d67596ce313aa60f700ae375

Before sending the pull request, I'm wondering if there are any
concerns or suggestions of doing this an alternate way. I initially
thought I'd add a --with-spatialite option to trigger this, but I
really don't understand gem that well to know how options are defined.

Comments or pointers would be welcome.

[Aaron Patterson]

I don't think this commit is necessary. You can load the spatialite
extensions without adding any extra code to the sqlite3 gem. Here is
an example program that uses the spacialite test database from here:

http://www.gaia-gis.it/spatialite-2.3.1/resources.html

require "sqlite3"

db = SQLite3::Database.new File.expand_path("~/Downloads/test-2.3.sqlite")
db.enable_load_extension 1
db.load_extension '/usr/local/lib/libspatialite.dylib'

p db.execute(<<-eosql)
SELECT name, peoples, AsText(Geometry)
from Towns
where peoples > 350000
order by peoples DESC
eosql

Two things to note about this program: 1) It's possible that your
sqlite3 installation doesn't support extension loading, and if that's
the case you'll get an error on the `enable_load_extension` method
call, and 2) your path to the spatialite extension may be different
than mine (since I'm on OS X and installed with homebrew).

Hope that helps!

--
Aaron Patterson
http://tenderlovemaking.com/

[Jon]

> I don't think this commit is necessary. You can load the spatialite
> extensions without adding any extra code to the sqlite3 gem. Here is
> an example program that uses the spacialite test database from here:
>
> http://www.gaia-gis.it/spatialite-2.3.1/resources.html

Win7 32bit with the existing sqlite3 gem also likes the example...

C:\Users\Jon\Documents>ripl
>> RUBY_DESCRIPTION
=> "ruby 1.9.3p143 (2012-02-24 revision 34791) [i386-mingw32]"
>> ENV['PATH'] += 'C:/devlibs/libiconv/bin;C:/devlibs/libgeos/bin;C:/devlibs/libproj/bin'
=> ...
>> require 'sqlite3'
=> true
>> SQLite3::VERSION
=> "1.3.5"
>> db = SQLite3::Database.new File.expand_path('test-2.3.sqlite')
=> #<SQLite3::Database:0x23d9318>
>> db.enable_load_extension 1
=> #<SQLite3::Database:0x23d9318>
>> db.load_extension 'C:/devlibs/libspatialite/bin/libspatialite-1.dll'
SpatiaLite version ..: 2.3.1 Supported Extensions:
- 'VirtualShape' [direct Shapefile access]
- 'VirtualText' [direct CSV/TXT access]
- 'VirtualNetwork [Dijkstra shortest path]
- 'RTree' [Spatial Index - R*Tree]
- 'MbrCache' [Spatial Index - MBR cache]
- 'VirtualFDO' [FDO-OGR interoperability]
- 'SpatiaLite' [Spatial SQL - OGC]
PROJ.4 Rel. 4.6.1, 21 August 2008
GEOS version 3.1.1-CAPI-1.6.0
=> #<SQLite3::Database:0x23d9318>

>> p db.execute(<<-eosql)
| SELECT name, peoples, AsText(Geometry)
| from Towns
| where peoples > 350000
| order by peoples DESC
| eosql

[["Roma", 2546804, "POINT(788703.57 4645636.3)"], ["Milano", 1256211, "POINT(514820.49 5034534.56)"],...

Jon

---
Fail fast. Fail often. Fail publicly. Learn. Adapt. Repeat.
http://thecodeshop.github.com | http://jonforums.github.com/
twitter: @jonforums

[michael groble]

My install didn't support extension loading which is what got me
started down this path in the first place.

Homebrew makes both both the amalgamation and the shared library which
is why it seemed to make sense to
try the standalone. Is it generally expected that the extension
should always work?

On Mar 2, 11:11 am, Aaron Patterson <aaron.patter...@gmail.com> wrote:
> On Thu, Mar 1, 2012 at 7:34 PM, michael groble
>

[Vitor George]

Hi everybody,

How can I enable extensions in my sqlite3 install?

I'm using the correct path to the library but I'm getting the
"undefined method `enable_load_extension'..." error, so I suppose my
installation don't have support for extensions.

Thanks,

Vitor George
| mapaslivres.org
| @mapaslivres


On Mar 8, 1:32 pm, michael groble <michael.gro...@mindspring.com>
wrote:

[vitor]

Hi,

I think I found the problem.

The default sqlite 3 install on Mac OS X doesn't support extensions, so an alternate install is need.

My alternate install is in /usr/local folder, so the problem can be solved by:

gem uninstall sqlite3

gem install sqlite3 -- --with-sqlite3-dir=/usr/local

Vitor George

| mapaslivres.org

| @mapaslivres

[Tom Kaniuch]

[Marek Piechut]

Hi.

In case anybody still struggling with this. I've found a solution.

Looks like gem is linking original sqlite library supplied with osx and this one does not have extensions support compiled in.

What i've done is:

- Install sqlite and spatiality with home-brew

brew install sqlite libspatialite spatialite-tools

- Install sqlite3 gem with explicitely pointing to brew compiled sqlite:

sudo gem install sqlite3 -- --with-sqlite3-include=/usr/local/opt/sqlite/include --with-sqlite3-lib=/usr/local/opt/sqlite/lib 

You can then load extensions just like in Aaron post and everything works fine.

Marek.