[GRASS-dev] v.extract and sqlite-db

[Markus Neteler]

On Wed, Aug 02, 2006 at 01:32:20PM +0200, Stephan Holl wrote:
> Dear devs,
> 
> playing with sqlite as attribute-storage I found out, that the ID needs
> to be an integer. I tried with this[1] sample
> spearfish-sqlite-attributedb which has a 'numeric' type for ID.
> 
> v.db.connect does not complain as long as you do not use v.extract with
> a where-statement to extract parts of your data.
> The resulting dataset is broken afterwards.
> 
> To reproduce in spearfish60:
> wget [1]
> g.copy vect=soils at PERMANENT,soils_cp
> v.db.connect -o map=soils_cp table=soils_legend
> database=./soils_legend.db driver=sqlite key=id
> 
> v.db.select soils_cp|head
> id|shortname|longname
> 0.000000|no data|no data
> 1.000000|AaB|Alice fine sandy loam, 0 to 6
> ...

Interestingly I get
v.db.select soils_cp|head -4
id|shortname|longname
0|no data|no data
1|AaB|Alice fine sandy loam, 0 to 6
2|Ba|Barnum silt loam

I am using
rpm -qf /usr/lib64/libsqlite3.so.0
sqlite-3.3.4-6

but:
 
> v.extract in=soils_cp out=soils_steep type=area where="longname LIKE
> '%steep%'"
> Load cats from the database (table = soils_legend, db
> = ./soils_legend.db). 5 cats loaded from the database
> Building topology ...
> 194 primitives registered
> Building areas:  100%
> 39 areas built
> 16 isles built
> Attaching islands:  100%
> Attaching centroids:  100%
> Topology was built.
> Number of nodes     :   171
> Number of primitives:   194
> Number of points    :   0
> Number of lines     :   0
> Number of boundaries:   164
> Number of centroids :   30
> Number of areas     :   39
> Number of isles     :   16
> Number of areas without centroid :   9
> Writing attributes ...
> Layer 1
> ERROR: Column 'id' is not integer

ok, same problem here.

DEBUG=3 prints

D3/3: table: soils_cp
D3/3: select * from soils_legend where 0 = 1
D3/3: Escaped SQL: select * from soils_legend where 0 = 1
D3/3: describe_table()
D3/3: ncols = 3
D3/3: litetype = 2
D3/3: litetype = 3
D3/3: litetype = 3
D3/3: nkcols = 3
D3/3: litetype = 2
D2/3: col: id, nkcols 0, litetype : 2, sqltype 6
D3/3: litetype = 3
D2/3: col: shortname, nkcols 1, litetype : 3, sqltype 13
D3/3: litetype = 3
D2/3: col: longname, nkcols 2, litetype : 3, sqltype 13
D3/3: Select cursor opened
D3/3: ncols = 3
D3/3: id (DOUBLE PRECISION)
ERROR: Column 'id' is not integer

Analyzing it with db.describe:

db.describe soils_legend table=soils_legend database=./soils_legend.db
table:soils_legend
description:
insert:?
delete:?
ncols:3

column:id
description:
type:DOUBLE PRECISION
len:99999
scale:0
precision:0
default:
nullok:yes
select:?
update:?
...

The problem is that SQLite is type agnostic.
http://www.sqlite.org/datatype3.html states:

"The key here is that the type is recommended, not required. Any column
 can still store any type of data, in theory."

The only solution I see (while not knowing much about it!) is
to scan the entire cat (here 'ID') column if it contains only integer values,
if so, accept it as key column for v.extract or other commands,
otherwise refuse.


> GRASS 6.1.cvs (spearfish60):
> 
> v.info soils_steep
> ERROR: Cannot open old vector soils_steep at lausanne_db on level 2
> 
> I would suggest, that the driver does not create the dataset, if the
> id-column is a non-integer.

In SQLite it's always non-integer/or non-integer is always possible.
 
> Using a table with an int id-column works fine though.

I fear that it worked by chance.

Does anyone have a suggestion how to resolve this problem?
Not sure if the "Strict affinity mode" can help. To do a round()
cannot be the solution...

Markus

> Best
> 
> 	Stephan
> 
> [1] http://mpa.itc.it/grasstutor/scripts/soils_legend.db
> 
> -- 
> GDF Hannover - Solutions for spatial data analysis and remote sensing
> Hannover Office      -     Mengendamm 16d      -     D-30177 Hannover
> Internet: www.gdf-hannover.de      -      Email: holl at gdf-hannover.de
> Phone : ++49-(0)511.39088507       -        Fax: ++49-(0)511.39088508
> 
> _______________________________________________
> grass-dev mailing list
> grass-dev at grass.itc.it
> http://grass.itc.it/mailman/listinfo/grass-dev

-- 
Markus Neteler  <neteler itc it>  http://mpa.itc.it/markus/
ITC-irst -  Centro per la Ricerca Scientifica e Tecnologica
MPBA - Predictive Models for Biol. & Environ. Data Analysis
Via Sommarive, 18        -       38050 Povo (Trento), Italy