Code

Opened 8 months ago

Closed 4 months ago

Last modified 4 months ago

#20968 closed Bug (invalid)

Error creating Indexes on Syncdb

Reported by: burton449geo@… Owned by: nobody
Component: GIS Version: 1.5
Severity: Normal Keywords: geodjango, spatialite, indexes
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

The database is Spatialite 4.1.0

On Syncdb these errors messages are shown:

Superuser created successfully.
Installing custom SQL ...
Installing indexes ...
AddGeometryColumn() error: "no such table: geometry_columns"
CreateSpatialIndex() error: "no such table: geometry_columns"

Attachments (0)

Change History (5)

comment:1 Changed 8 months ago by leplatrem

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

Initializing the database with spatialite binary is the answer :

spatialite database.db "SELECT InitSpatialMetaData();"

comment:2 Changed 8 months ago by claudep

This should be done automatically:
https://github.com/django/django/blob/master/django/contrib/gis/db/backends/spatialite/creation.py#L102-L107

Maybe something changed in Spatialite 4.1? I cannot test right now.

comment:3 Changed 4 months ago by aaugustin

Since no one managed to reproduce the error, should I close this ticket as needsinfo?

comment:4 Changed 4 months ago by ramiro

  • Resolution set to needsinfo
  • Status changed from new to closed

Tried to reproduce this to not avail (tested with the GeoDjango tutorial project).

Environment:

Creating spatial metadata tables (per https://docs.djangoproject.com/en/dev/ref/contrib/gis/install/spatialite/#creating-a-spatial-database-for-spatialite) -- Not strictly needed but useful to get the versions of the C-level components printed to the console:

(spatialite41)ramiro@mang:~/venv/spatialite41/src/geodjango$ spatialite db.sqlite3 "SELECT InitSpatialMetaData();"
SpatiaLite version ..: 4.1.0	Supported Extensions:
	- 'VirtualShape'	[direct Shapefile access]
	- 'VirtualDbf'		[direct DBF access]
	- 'VirtualText'		[direct CSV/TXT access]
	- 'VirtualNetwork'	[Dijkstra shortest path]
	- 'RTree'		[Spatial Index - R*Tree]
	- 'MbrCache'		[Spatial Index - MBR cache]
	- 'VirtualSpatialIndex'	[R*Tree metahandler]
	- 'VirtualFDO'		[FDO-OGR interoperability]
	- 'SpatiaLite'		[Spatial SQL - OGC]
PROJ.4 version ......: Rel. 4.7.1, 23 September 2009
GEOS version ........: 3.2.2-CAPI-1.6.2
the SPATIAL_REF_SYS table already contains some row(s)
 InitSpatiaMetaData() error:"table spatial_ref_sys already exists"
0

DB schema manage.py commands:

(spatialite41)ramiro@mang:~/venv/spatialite41/src/geodjango$ PYTHONPATH=~/django/upstream python manage.py sqlall world
BEGIN;
CREATE TABLE "world_worldborder" (
    "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
    "name" varchar(50) NOT NULL,
    "area" integer NOT NULL,
    "pop2005" integer NOT NULL,
    "fips" varchar(2) NOT NULL,
    "iso2" varchar(2) NOT NULL,
    "iso3" varchar(3) NOT NULL,
    "un" integer NOT NULL,
    "region" integer NOT NULL,
    "subregion" integer NOT NULL,
    "lon" real NOT NULL,
    "lat" real NOT NULL
)
;
SELECT AddGeometryColumn('world_worldborder', 'mpoly', 4326, 'MULTIPOLYGON', 2, 1);
SELECT CreateSpatialIndex('world_worldborder', 'mpoly');

COMMIT;
(spatialite41)ramiro@mang:~/venv/spatialite41/src/geodjango$ PYTHONPATH=~/django/upstream python manage.py migrate
Operations to perform:
  Synchronize unmigrated apps: gis, sessions, admin, messages, auth, staticfiles, contenttypes, world
  Apply all migrations: (none)
Synchronizing apps without migrations:
  Creating tables...
    Creating table django_admin_log
    Creating table auth_permission
    Creating table auth_group_permissions
    Creating table auth_group
    Creating table auth_user_groups
    Creating table auth_user_user_permissions
    Creating table auth_user
    Creating table django_content_type
    Creating table django_session
    Creating table world_worldborder
  Installing custom SQL...
  Installing indexes...
Installed 0 object(s) from 0 fixture(s)
Running migrations:
  No migrations needed.

You just installed Django's auth system, which means you don't have any superusers defined.
Would you like to create one now? (yes/no): no

Also ran the GeoDjango tests without problems:

(spatialite41)ramiro@mang:~/django/upstream/tests$ PYTHONPATH=.. python runtests.py --settings=test_gis django.contrib.gis
Testing against Django installed in '/home/ramiro/django/upstream/django'
Creating test database for alias 'default'...
Creating test database for alias 'other'...
.s...s....s..s............s.......sssssssssss..........ssssss...s............s....s.......sss...s.s.s.s.......ssssss..........................................................................................................................................................................................
----------------------------------------------------------------------
Ran 302 tests in 5.177s

OK (skipped=38)
Destroying test database for alias 'default'...
Destroying test database for alias 'other'...

If you have a system-wide spatialite installation of a different version (e.g. in the case of Ubuntu 12.04 it could be the 3.0.0~beta20110817-3 .deb package) then make sure you direct GeoDjango to use the 4.1.x one using the SPATIALITE_LIBRARY_PATH setting in setting.py:

SPATIALITE_LIBRARY_PATH='/path/to/your/spatialite/4.1.x/libspatialite.so'

Closing 'needsinfo' for now. Please reopen with a similar description of your environment if you can reproduce and wish to push this further.

comment:5 Changed 4 months ago by ramiro

  • Resolution changed from needsinfo to invalid

Actually I can reproduce the issue if I don't create the spatial tables with spatialite db.sqlite3 "SELECT InitSpatialMetaData();" then the index creation fails.

So comment:1 is right. I'm wrong in the last comment because I assumed these tables were created automatically if not present. That's only true then creating the test DB to run the GeoDjango tests.

So what one needs to do is actually follow the documentation (https://docs.djangoproject.com/en/dev/ref/contrib/gis/install/spatialite/#creating-a-spatial-database-for-spatialite) because at no point it describes that step as optional.

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.