Extending existing sqlite databases with spatialite

[Jose Gomez-Dans]

Hi,
I have a number of sqlite tables from the past, and I would like to make them spatial. They all have a field which I can link to a shapefile. My first attempt has been to open the sqlite dbs on the GUI, load the shapefile and run the following code:

-- To make a sqlite database look like a real spatial, spalite friendly database...
-- Add a geometry column
ALTER mytable ADD COLUMN geom blob;
-- Copy the geometry over from my_grid
UPDATE mytable SET geom=( SELECT g.geom FROM "mytable" d, "my_grid" g WHERE d.grid_id=g.id) ;
-- Make it into a full graduated geotable
SELECT RecoverGeometryColumn( "mytable",geom,4326,'POLYGON',2) ;

Now, that doesn't work properly because even if I import my shapefile, the meta tables used by spatialite are not created (geometry_columns, etc). The above recipe works quite well if I export my sqlite files to a CSV, create a new db using the GUI (automatically, I  get the missing geometry_columns and friends), import the shapefile, import the CSV and link them as showed above. While I could do this, this route is error prone and tedious. Ideally, I would like to extend my current databases with a new geometry field, but what's the best way to do this?

Thanks!
Jose

[Joseph Wolter]

Jose,

If the 'Geometry' column doesn't exist try:
SELECT AddGeometryColumn(...)


Joe

[Joe]

[Alex Mandel]

If you look on the spatialite pages, theres a set of .sql files. In
order to make an existing database a spatialite db you need to run these
sql files on it. They create the spatial metadata tables and triggers
you need.

From
http://www.gaia-gis.it/spatialite/resources.html
They are the SQL scripts init_spatialite-2.3.sql

Alex

[markb]

I think what your looking for is this:

http://www.gaia-gis.it/spatialite/resources.html
init_spatialite-2.3.sql

or

http://www.gaia-gis.it/spatialite-2.4.0/index.html
init script

That should setup your database for spatial functionality, though I'm
not sure if you'll have to deal with database version issues. Then
you should be able to go into your sql statements.

-Mark

[Jose Gomez-Dans]

2009/12/15 markb <brad.m...@gmail.com>

I think what your looking for is this:

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

init_spatialite-2.3.sql

OK, this is correct. Still a problem left, but so far here's the whole procedure (hopefully for reference for somebody else):

.read init_spatialite-2.3.sql utf-8
.loadshp <path> <table> <utf-8> <srid> <column_name>
SELECT AddGeometryColumn('<my_table>', 'geom', 4326, 'POLYGON', 2);
UPDATE my_table SET geom=( SELECT g.geom FROM my_table d, my_shape g WHERE d.grid_id=g.id) ;

However, the geom column in my_table is set to one single value. The select statement shows the right results, e.g.,
spatialite> SELECT astext(g.geom) FROM my_table d, my_grid g WHERE d.grid_id=g.id limit 14;
POLYGON((8.5 63.5, 8.5 64, 9 64, 9 63.5, 8.5 63.5))
POLYGON((8.5 63.5, 8.5 64, 9 64, 9 63.5, 8.5 63.5))
POLYGON((8.5 63.5, 8.5 64, 9 64, 9 63.5, 8.5 63.5))
POLYGON((8.5 63.5, 8.5 64, 9 64, 9 63.5, 8.5 63.5))
POLYGON((8.5 63.5, 8.5 64, 9 64, 9 63.5, 8.5 63.5))
POLYGON((8.5 63.5, 8.5 64, 9 64, 9 63.5, 8.5 63.5))
POLYGON((8.5 63.5, 8.5 64, 9 64, 9 63.5, 8.5 63.5))
POLYGON((8.5 63.5, 8.5 64, 9 64, 9 63.5, 8.5 63.5))
POLYGON((8.5 63.5, 8.5 64, 9 64, 9 63.5, 8.5 63.5))
POLYGON((9 -2, 9 -1.5, 9.5 -1.5, 9.5 -2, 9 -2))
POLYGON((9 -2, 9 -1.5, 9.5 -1.5, 9.5 -2, 9 -2))
POLYGON((9 -2, 9 -1.5, 9.5 -1.5, 9.5 -2, 9 -2))
POLYGON((9 -2, 9 -1.5, 9.5 -1.5, 9.5 -2, 9 -2))
POLYGON((9 -2, 9 -1.5, 9.5 -1.5, 9.5 -2, 9 -2))

spatialite> select astext(geom) from my_table limit 14;
POLYGON((8.5 63.5, 8.5 64, 9 64, 9 63.5, 8.5 63.5))
POLYGON((8.5 63.5, 8.5 64, 9 64, 9 63.5, 8.5 63.5))
POLYGON((8.5 63.5, 8.5 64, 9 64, 9 63.5, 8.5 63.5))
POLYGON((8.5 63.5, 8.5 64, 9 64, 9 63.5, 8.5 63.5))
POLYGON((8.5 63.5, 8.5 64, 9 64, 9 63.5, 8.5 63.5))
POLYGON((8.5 63.5, 8.5 64, 9 64, 9 63.5, 8.5 63.5))
POLYGON((8.5 63.5, 8.5 64, 9 64, 9 63.5, 8.5 63.5))
POLYGON((8.5 63.5, 8.5 64, 9 64, 9 63.5, 8.5 63.5))
POLYGON((8.5 63.5, 8.5 64, 9 64, 9 63.5, 8.5 63.5))
POLYGON((8.5 63.5, 8.5 64, 9 64, 9 63.5, 8.5 63.5))
POLYGON((8.5 63.5, 8.5 64, 9 64, 9 63.5, 8.5 63.5))
POLYGON((8.5 63.5, 8.5 64, 9 64, 9 63.5, 8.5 63.5))
POLYGON((8.5 63.5, 8.5 64, 9 64, 9 63.5, 8.5 63.5))
POLYGON((8.5 63.5, 8.5 64, 9 64, 9 63.5, 8.5 63.5))

This is after the update statement. All the rows in my_grid share the same geom value. I'd be grateful if anyone could point out what I'm doing wrong!

Thanks!
Jose

[markb]

Your Update statement isn't correct (see http://www.sqlite.org/lang_update.html).
Your expression in parentheses is requesting multiple records, but
expressions in SQLite truncate the recordset to the first record
returned. Therefore, your inserting the first record over and over
again. Maybe this? (but I often use temp tables instead, since SQLite
doesn't allow FROM clauses in UPDATE statements; plus the indexes will
work better):

UPDATE my_table SET geom = (SELECT g.geom FROM my_shape As g WHERE
g.id = my_table.grid_id);

see this link:
http://sqlite.phxsoftware.com/forums/p/1708/7238.aspx

Good Luck
-Mark


On Dec 15, 4:30 pm, Jose Gomez-Dans <jgomezd...@gmail.com> wrote:
> 2009/12/15 markb <brad.markf...@gmail.com>

>
> > I think what your looking for is this:
> >http://www.gaia-gis.it/spatialite/resources.html
> > init_spatialite-2.3.sql
>
> OK, this is correct. Still a problem left, but so far here's the whole
> procedure (hopefully for reference for somebody else):
>

> *.read init_spatialite-2.3.sql utf-8

> .loadshp <path> <table> <utf-8> <srid> <column_name>
> SELECT AddGeometryColumn('<my_table>', 'geom', 4326, 'POLYGON', 2);
> UPDATE my_table SET geom=( SELECT g.geom FROM my_table d, my_shape g WHERE
> d.grid_id=g.id) ;

> *

> However, the geom column in my_table is set to one single value. The select
> statement shows the right results, e.g.,

> *spatialite> SELECT astext(g.geom) FROM my_table d, my_grid g WHERE
> d.grid_id=g.id limit 14;*

> POLYGON((8.5 63.5, 8.5 64, 9 64, 9 63.5, 8.5 63.5))
> POLYGON((8.5 63.5, 8.5 64, 9 64, 9 63.5, 8.5 63.5))
> POLYGON((8.5 63.5, 8.5 64, 9 64, 9 63.5, 8.5 63.5))
> POLYGON((8.5 63.5, 8.5 64, 9 64, 9 63.5, 8.5 63.5))
> POLYGON((8.5 63.5, 8.5 64, 9 64, 9 63.5, 8.5 63.5))
> POLYGON((8.5 63.5, 8.5 64, 9 64, 9 63.5, 8.5 63.5))
> POLYGON((8.5 63.5, 8.5 64, 9 64, 9 63.5, 8.5 63.5))
> POLYGON((8.5 63.5, 8.5 64, 9 64, 9 63.5, 8.5 63.5))
> POLYGON((8.5 63.5, 8.5 64, 9 64, 9 63.5, 8.5 63.5))
> POLYGON((9 -2, 9 -1.5, 9.5 -1.5, 9.5 -2, 9 -2))
> POLYGON((9 -2, 9 -1.5, 9.5 -1.5, 9.5 -2, 9 -2))
> POLYGON((9 -2, 9 -1.5, 9.5 -1.5, 9.5 -2, 9 -2))
> POLYGON((9 -2, 9 -1.5, 9.5 -1.5, 9.5 -2, 9 -2))
> POLYGON((9 -2, 9 -1.5, 9.5 -1.5, 9.5 -2, 9 -2))
>

> *spatialite> select astext(geom) from my_table limit 14;*

[Jose Gomez-Dans]

Hi,
Thanks for your reply.

2009/12/15 markb <brad.m...@gmail.com>

Your Update statement isn't correct (see http://www.sqlite.org/lang_update.html).
Your expression in parentheses is requesting multiple records, but
expressions in SQLite truncate the recordset to the first record
returned.  Therefore, your inserting the first record over and over
again.  

Maybe that's what was happening...
 

Maybe this? (but I often use temp tables instead, since SQLite
doesn't allow FROM clauses in UPDATE statements; plus the indexes will
work better):

UPDATE my_table SET geom = (SELECT g.geom FROM my_shape As g WHERE
g.id = my_table.grid_id);

I had tried this, and it is really slow. Now, the only difference I see in your query and mine is that I do the where in the opposite direction, and that I specify aliases for both tables. The result is completely different, so there has to be something I'm missing here!

UPDATE my_table SET geom=( SELECT g.geom FROM my_table d, my_shape g WHERE d.grid_id=g.id) ;
 

I think I'll try creating a temporary table now, and see how that works.

Many thanks!
Jose

[Jose Gomez-Dans]

Hi,

2009/12/16 Jose Gomez-Dans <jgome...@gmail.com>

I think I'll try creating a temporary table now, and see how that works.

In case someone looks for this in the future, it turns out that I didn't have an index on the my_shape table, so Mark's suggestion worked very well once the index was in place. Creating a new table is of course as fast and produces the same results.

Many thanks!

Jose

[markb]

Jose,
The difference between our two Update queries is that your subquery
does a full table inner join, requesting all records, for each and
every record in my_table. My example pulls grid_id from the Update
table itself, and forces the subquery to only return one matching
record for each record in the Update table. The order of the Where
comparison shouldn't matter, and the aliasing is entirely optional.
This should clarify that "something" you were missing.

-Mark

On Dec 16, 7:16 am, Jose Gomez-Dans <jgomezd...@gmail.com> wrote:
> Hi,
>
> 2009/12/16 Jose Gomez-Dans <jgomezd...@gmail.com>