init_spacialite.sql primary key error

[AdamG]

Hello - I'm new to this group and new to SpaciaLite; I currently use
Postgres, PostGIS and Django, and am looking forward to experimenting
with SpatiaLite.

I'm having difficulties with the installation, however. Running
init_spatialite.sql yields a primary key error at row 24. I found this
file at http://www.gaia-gis.it/spatialite-2.2/index.html. I've tried
the .sql from 2.0,2.1,and 2.2 and all result in the same constraint
violation.

I saw this error mentioned in a comment on one of the tutorials, but
there was no response.

Has anyone else encountered and solved this issue?

Thanks in advance,
Adam

[NiceDogE]

How did you create the new SQLite database....was it using the
SpatiaLite GUI / SpatiaLite CLI? Or was it through a SQLite GUI or
SQLite CLI?

If you are using the SpatiaLite GUI/CLI to create a new DB file, then
you don't need to run the Init_spatialite.sql script separately, this
is automatically done for you whenever you create a new DB......re-
running it I suspect would give constraint violations as the tables
the script is trying to populate are already pre-populated.

You only ever need to run Init_spatialite.sql if you have created the
DB using SQLite instead.

On Sep 12, 1:35 am, AdamG <agree...@gmail.com> wrote:
> Hello - I'm new to this group and new to SpaciaLite; I currently use
> Postgres, PostGIS and Django, and am looking forward to experimenting
> with SpatiaLite.
>
> I'm having difficulties with the installation, however. Running
> init_spatialite.sql yields a primary key error at row 24. I found this

> file athttp://www.gaia-gis.it/spatialite-2.2/index.html. I've tried

[a.fu...@lqt.it]

Hi,
just a few points to be kept in mind.

The 'init_spatialite.sql' script simply creates the
metadata tables 'geometry_columns' and 'spatial_ref_sys',
inserting all the EPSG defs (SRIDs) into this latest one
table.

Metadata tables are required in order to fully support
Geometries and Spatial operators, so you have to create
(and properly feed) them each time you'll create a new
database (if you already use PostGIS you are surely
already familiar with such metadata tables).

But starting since v.2.3.0 the GUI tools 'spatialite-gui'
and 'spatialite-gis' silently create and feed the metadata
tables during the database creation phase:
so running the 'init_spatialite.sql' script as a separate
step isn't any longer required.

Quite obviously, if you try to execute this script on an
already initialized DB you'll get some error, because you
are actually attempting to re-create an already existing table,
and/or to insert duplicate primary keys into 'spatial_ref_sys'.

Hint: check your DB; if it already contains the 'geometry_columns'
and 'spatial_ref_sys' tables there is no need at all to
run the 'init_spatialite.sql' script, because any initialization
task has been already performed by the GUI tool.

bye,
Sandro

[Adam Green]

Thanks to all for the replies - after reading, the error makes complete sense. I think the instructions I was reading must have been dated. A cursory check of spatial_ref_sys shows that all of the SRIDs in init_spacialite are present, hence the pk error.

Thanks again,
Adam