Spatialite tips?

[Mitch Chapman]

Can anyone provide tips for getting started with GeoDjango and
Spatialite?

I'm trying to do so on Mac OS X 10.5.6, using Python 2.5.2 and a
virtualenv. I've installed Django rev 10580 from svn, along with
pysqlite 2.5.5, GEOS 3.1.0, PROJ 4.6.1 and GDAL 1.6.0. I've also
downloaded the Spatialite initialization sql, init_spatialite-2.3.sql
to my project directory. But I haven't been able to figure out what
to do to initialize the database.

After much bumbling around, I've tried this as a way to get started:
$ ./manage.py shell
>>> from django.contrib.gis.db.backend.spatialite import creation
>>> creation.load_spatialite_sql("")
...
>>> ^D
$ ./manage.py syncdb

This seems to create most of the tables needed. But when I
subsequently try to load worldborders, as in the tutorial at
http://geodjango.org/docs/tutorial.html#setting-up, I get a traceback:
"OperationalError: no such table: main.idx_world_worldborders_geom".

$ ./manage.py shell
>>> from world import load
>>> load.run()
...
Failed to save the feature (id: 0) into the model with the keyword
arguments:
{'iso2': u'AG', 'name': u'Antigua and Barbuda', 'area': 44, 'region':
19, 'lon': -61.783000000000001, 'iso3': u'ATG', 'subregion': 29,
'fips': u'AC', 'lat':
...
Traceback (most recent call last):
File "<console>", line 1, in <module>
File "/path/to/my/virtualenv/website/world/load.py", line 18, in run
lm.save(strict=True, verbose=verbose)
File "/path/to/my/virtualenv/lib/python2.5/site-packages/
Django-1.1_beta_1-py2.5.egg/django/contrib/gis/utils/layermapping.py",
line 693, in save
_save()
...
"/path/to/my/virtualenv/lib/python2.5/site-packages/Django-1.1_beta_1-
py2.5.egg/django/db/backends/sqlite3/base.py", line 193, in execute
return Database.Cursor.execute(self, query, params)
OperationalError: no such table: main.idx_world_worldborders_geom

(Full traceback is available on request.)


Here's my world/models.py:
----
# This is an auto-generated Django model module created by ogrinspect.
from django.contrib.gis.db import models

class WorldBorders(models.Model):
fips = models.CharField(max_length=2)
iso2 = models.CharField(max_length=2)
iso3 = models.CharField(max_length=3)
un = models.IntegerField()
name = models.CharField(max_length=50)
area = models.IntegerField()
pop2005 = models.IntegerField()
region = models.IntegerField()
subregion = models.IntegerField()
lon = models.FloatField()
lat = models.FloatField()
geom = models.MultiPolygonField(srid=4326)
objects = models.GeoManager()

# Auto-generated `LayerMapping` dictionary for WorldBorders model
worldborders_mapping = {
'fips' : 'FIPS',
'iso2' : 'ISO2',
'iso3' : 'ISO3',
'un' : 'UN',
'name' : 'NAME',
'area' : 'AREA',
'pop2005' : 'POP2005',
'region' : 'REGION',
'subregion' : 'SUBREGION',
'lon' : 'LON',
'lat' : 'LAT',
'geom' : 'MULTIPOLYGON',
}
----


Thanks for any help!

--
Mitch

[Justin Bronn]

Mitch Chapman wrote:
> Can anyone provide tips for getting started with GeoDjango and
> Spatialite?
>
> I'm trying to do so on Mac OS X 10.5.6, using Python 2.5.2 and a
> virtualenv. I've installed Django rev 10580 from svn, along with
> pysqlite 2.5.5, GEOS 3.1.0, PROJ 4.6.1 and GDAL 1.6.0. I've also
> downloaded the Spatialite initialization sql, init_spatialite-2.3.sql
> to my project directory. But I haven't been able to figure out what
> to do to initialize the database.
>

Getting SpatiaLite to work on OS X can be tricky, but you've almost got it.

Using the following command, you can initialize a SpatiaLite database
for use with GeoDjango:

$ spatialite geodjango.db < init_spatialite-2.3.sql

Unfortunately, the SQLite binary on OS X is not compatible with
SpatiaLite because it was not compiled with the R-tree index support nor
the ability to load extensions dynamically. Thus, to get it to work
you'll have to build SQLite from source -- grab the SQLite amalgation
(latest is 'sqlite-amalgamation-3.6.13.tar.gz'), untar, and configure
like so:

$ CFLAGS="-I/usr/include -DSQLITE_ENABLE_RTREE=1" \
LDFLAGS="-L/usr/lib -liconv" ./configure

Setting these flags enables the R-tree index and links into the OS X
iconv library.

Afterwards, you'll also need to rebuild pysqlite so that is linked to
the new SQLite. In the pysqlite 2.5.5 directory that was untared,
change setup.cfg to the following:

[build_ext]
#define=
include_dirs=/usr/local/include
library_dirs=/usr/local/lib
libraries=sqlite3
#define=SQLITE_OMIT_LOAD_EXTENSION

Then run `python setup.py install`.

Obviously, this information needs to be in the documentation.

Regards,
-Justin

[Mitch Chapman]

Thanks! This should be easy, as I am running with virtualenv builds of
sqlite 3.6.13 and pysqlite-2.5.5. (Sorry for omitting that.)

Looks like I screwed up on spatialite installation, though. Do I need
both libspatialite and spatialite-tools?

[Mitch Chapman]

On May 6, 10:00 am, Justin Bronn <jbr...@gmail.com> wrote:

> Getting SpatiaLite to work on OS X can be tricky, but you've almost got it.
>
> Using the following command, you can initialize a SpatiaLite database
> for use with GeoDjango:
>
>      $ spatialite geodjango.db < init_spatialite-2.3.sql
>

Something is still amiss. I still get the same traceback as before,
apparently due to a missing index.

$ rm database/website.db
$ spatialite database/website.db < init_spatialite-2.3.sql
$ ./manage.py syncdb
...

$ ./manage.py shell
>>> from world import load; load.run()

SpatiaLite version ..: 2.3.0 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.0-CAPI-1.5.0

Failed to save the feature (id: 0) into the model with the keyword
arguments:
{'iso2': u'AG', 'name': u'Antigua and Barbuda', 'area': 44,
'region': 19, 'lon':

...
File "/path/to/virtualenv/lib/python2.5/site-packages/
Django-1.1_beta_1-py2.5.egg/django/db/backends/sqlite3/base.py", line

193, in execute
return Database.Cursor.execute(self, query, params)
OperationalError: no such table: main.idx_world_worldborders_geom

To be a little more complete than in the original post, this is with
sqlite-amalgamation-3.6.13
libspatialite-amalgamation-2.3.0
spatialite-tools-2.3.0
pysqlite-2.5.5

--
Mitch

[Justin Bronn]

> To be a little more complete than in the original post, this is with
> sqlite-amalgamation-3.6.13
> libspatialite-amalgamation-2.3.0
> spatialite-tools-2.3.0
> pysqlite-2.5.5

Make sure `/usr/local/bin` comes in your PATH before `/usr/bin` and
recompile SpatiaLite. I think your problems may be caused by SpatiaLite
using Mac's SQLite.

-Justin

[Mitch Chapman]

Good suggestion! /usr/local/bin does indeed come first -- or rather
my virtualenv's bin comes first.

I missed this diagnostic message produced by ./manage.py syncdb:
Installing custom SQL for world.WorldBorders model
updateTableTriggers: "no such module: rtree"

libspatialite's sqlite3.c module initializes the rtree module from
within openDatabase. The spatialite utility uses libspatialite's
definition of openDatabase; I've confirmed this by adding debug
printf's :-)

GeoDjango does not initialize rtree from within openDatabase, since it
can load the extension only on an established database connection.

Near as I can tell, the only other way to initialize the rtree module
(i.e. to invoke sqlite3RtreeInit) is to invoke libspatialite's
sqlite3_extension_init. But it appears that the definition of
SQLITE_CORE at the top of libspatialite's sqlite3.c prevents that
function definition from being compiled. So the rtree module never
gets initialized when libspatialite is loaded as an extension module.

Does any of this sound plausible?

[Mitch Chapman]

On May 7, 9:22 am, Mitch Chapman <mchapman87...@gmail.com> wrote:
> Near as I can tell, the only other way to initialize the rtree module
> (i.e. to invoke sqlite3RtreeInit) is to invoke libspatialite's
> sqlite3_extension_init.  But it appears that the definition of
> SQLITE_CORE at the top of libspatialite's sqlite3.c prevents that
> function definition from being compiled.  So the rtree module never
> gets initialized when libspatialite is loaded as an extension module.

Oops, scratch that. The definition for sqlite3_extension_init is in
libspatialite's spatialite.c source file. But that definition does
not appear to initialize the RTree module.

[Mitch Chapman]

On May 7, 9:52 am, Mitch Chapman <mchapman87...@gmail.com> wrote:
> Oops, scratch that.  The definition for sqlite3_extension_init is in
> libspatialite's spatialite.c source file.  But that definition does
> not appear to initialize the RTree module.

Problem solved: I had not compiled SQLite3 with the RTree module
enabled. (http://www.sqlite.org/rtree.html)
$ export CPPFLAGS=-DSQLITE_ENABLE_RTREE=1
$ ./configure --prefix=${PREFIX}
$ make
$ make install

Thanks for all the help!

[Justin Bronn]

Mitch Chapman wrote:
> Problem solved: I had not compiled SQLite3 with the RTree module
> enabled. (http://www.sqlite.org/rtree.html)
> $ export CPPFLAGS=-DSQLITE_ENABLE_RTREE=1
> $ ./configure --prefix=${PREFIX}
> $ make
> $ make install
>

That was in my first reply :) Except I also had LDFLAGS for linking to
iconv, but that may no longer be necessary (maybe due to improvements
after beta). Glad you got it working.

-Justin

[Mitch Chapman]

On May 7, 11:43 am, Justin Bronn <jbr...@gmail.com> wrote:
> That was in my first reply :)

Yep. About once a day I can be heard muttering "I gotta learn to
read" :)