[GRASS-stats] Loading a point-vector table with 466 columns

[Roger Bivand]

On Mon, 25 May 2009, Even Rouault wrote:

> (Sorry, I'm not subscribed to the GRASS-stats list, so I've emailed directly
> to the people having taken part to the discussion)

Hi Even,

Thanks for tackling the 2D/3D issue in the GRASS read driver - the fixes 
look appropriate.

The SQLite issue is, I believe, on the GRASS side - GRASS can use a number 
of databases for storing attribute data, but geometries are stored 
natively. This means that GRASS is using its own SQLite handling, I think 
shown in OGRGRASSLayer::SetAttributes() in the file you've seen in the 
2D/3D fix for determining their types, and in OGRGRASSLayer::GetFeature() 
for copying them from GRASS structures to OGR structures. I think that the 
OGR driver is limited by GRASS's own driver, but since I don't use SQLite, 
I cannot reproduce the problem.

I'll add grass-stats to the CC for completeness.

Roger

>
> About the OGR sqlite performance, I've not read all the posts of the thread,
> but I couldn't reproduce any performance related problem.
>
> Here's a simple OGR python script that creates a 1000 row x 1000 column sqlite
> db :
>
> #!/usr/bin/python
> import ogr
>
> ds = ogr.GetDriverByName('SQLite').CreateDataSource('testhuge.db')
> lyr = ds.CreateLayer('test')
> for i in range(1000):
>    ds.ExecuteSQL("ALTER TABLE test ADD COLUMN att%d VARCHAR" % i)
> ds = None
>
> ds = ogr.Open('testhuge.db', update = 1)
> lyr = ds.GetLayerByName('test')
> for i in range(1000):
>    feat = ogr.Feature(lyr.GetLayerDefn())
>    for j in range(1000):
>        feat.SetField(j, 'val%d' % j)
>    lyr.CreateFeature(feat)
>
> ds = None
>
> (takes a few seconds to run)
>
>
> Then "time ogrinfo -ro -al -q testhuge.db  > /dev/null"
>
> --> real	0m3.903s
>
> So 4 seconds, not 30 minutes... Maybe it's due to the fact how GRASS uses the
> OGR output ?
>
> Actually, by reviewing the code, there might *maybe* a performance problem if
> the FID or geometry columns of the table were at the end of the column list
> (see lines 308-313 and 339-344 in ogr/ogrsf_frmts/sqlite/ogrsqlitelayer.cpp,
> latest GDAL SVN head trunk), that could cause loop over each column name
> before finding them. But I couldn't really evaluate the impact it could have.
> (by default OGR creates these fields as the first ones)
>
> Even
>
>

-- 
Roger Bivand
Economic Geography Section, Department of Economics, Norwegian School of
Economics and Business Administration, Helleveien 30, N-5045 Bergen,
Norway. voice: +47 55 95 93 55; fax +47 55 95 95 43
e-mail: Roger.Bivand at nhh.no