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

[Stephan Holl]

Hello Markus,

On Wed, 2 Aug 2006 14:02:50 +0200 Markus Neteler <neteler at itc.it> wrote:

> 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.

Why cannot we cast to int()?

> > 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?

Probably a correct exit without segfault and an appropriate error
meassage e.g.:  ERROR: Column 'id' is not integer

> Not sure if the "Strict affinity mode" can help. To do a round()
> cannot be the solution...


Stephan


-- 
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