[GRASS-dev] single vs. multiple sqlite db files [was v.db.calc]

GRASS GIS GRASS GIS developer
1

Dylan:

I also agree on this matter. I think that one thing that is making
this decision particularly difficult is that we are lacking a robust
interchange format for complex vector data.

I guess we need to create v.pack, v.unpack modules. should be much easier
than the r.pack, r.unpack.

Dumping from GRASS vectors --> shapefiles -->
GRASS can sometimes lead to data loss.

(make that often)

to move forward on this I guess we need to start coding the easy way to
switch between per-map and per-mapset sqlite modes. there seems to be
enough support for both ways to warrant it.

Hamish

2

Hamish wrote:

Dylan:

I also agree on this matter. I think that one thing that is making
this decision particularly difficult is that we are lacking a robust
interchange format for complex vector data.

I guess we need to create v.pack, v.unpack modules. should be much easier
than the r.pack, r.unpack.

Unless we take "interchange" to include other GIS. I find it frustrating
that there is no established vector data format that adequately
preserves information between different systems. SQLite + WKT/WKB
should be a good and simple way to provide that. It is easy to
turn an SQLite DB into a spatial database, following these guidelines:

http://trac.osgeo.org/fdo/wiki/FDORfc16

Of course, OGR/GDAL already has support for this "standard":

http://www.gdal.org/ogr/drv_sqlite.html

but I imagine it would be more convenient if GRASS supported it
natively?

Data exchange with other GIS apps could also be managed by pumping
(WKT/WKB) SQL statements through stdin/stdout, without the need to
go through a file, at all (of course both ends would need to
support this).

In the interim, why not create a nice wrapper module that uses the OGR
SQlite driver to store/extract attributes and geometries?

SpatiaLite is just one (albeit very nice!) implementation of that
general idea, which should be fairly easy to implement in GRASS.
The biggest problem with the current SpatiaLite API re. GRASS seems
to be that it caters for 2D coordinates only. I am not sure how hard
it would be to work around that limitation.

Ben

Dumping from GRASS vectors --> shapefiles --> GRASS can sometimes lead to data loss.

(make that often)

to move forward on this I guess we need to start coding the easy way to
switch between per-map and per-mapset sqlite modes. there seems to be
enough support for both ways to warrant it.

Hamish

_______________________________________________
grass-dev mailing list
grass-dev@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/grass-dev

--
Benjamin Ducke
Senior Geospatial Consultant

Oxford Archaeology
Janus House
Osney Mead
OX2 0ES
Oxford, U.K.

Tel: +44 (0)1865 263 800 (switchboard)
Tel: +44 (0)1865 980 758 (direct)
Fax :+44 (0)1865 793 496
benjamin.ducke@oxfordarch.co.uk

------
Files attached to this email may be in ISO 26300 format (OASIS Open Document Format). If you have difficulty opening them, please visit http://iso26300.info for more information.

3

Just a quick update on my own thoughts (awful):
There is a note in the OGR SQlite driver page which states that
the SpatiaLite extenstion to SQLite databases will be fully
supported by the time GDAL 1.7.0 gets released:

http://www.gdal.org/ogr/drv_sqlite.html

So I guess here we have a good candidate for a universal data
exchange format that works out-of-the box with every application
that supports the GDAL/OGR interface. Plus SpatiaLite comes with
a very nice set of userland GUI tools to manage (spatial) data in an
SQLite DB, so what more could one ask for?

Ben

Benjamin Ducke wrote:

Hamish wrote:

Dylan:

I also agree on this matter. I think that one thing that is making
this decision particularly difficult is that we are lacking a robust
interchange format for complex vector data.

I guess we need to create v.pack, v.unpack modules. should be much easier
than the r.pack, r.unpack.

Unless we take "interchange" to include other GIS. I find it frustrating
that there is no established vector data format that adequately
preserves information between different systems. SQLite + WKT/WKB
should be a good and simple way to provide that. It is easy to
turn an SQLite DB into a spatial database, following these guidelines:

http://trac.osgeo.org/fdo/wiki/FDORfc16

Of course, OGR/GDAL already has support for this "standard":

http://www.gdal.org/ogr/drv_sqlite.html

but I imagine it would be more convenient if GRASS supported it
natively?

Data exchange with other GIS apps could also be managed by pumping
(WKT/WKB) SQL statements through stdin/stdout, without the need to
go through a file, at all (of course both ends would need to
support this).

In the interim, why not create a nice wrapper module that uses the OGR
SQlite driver to store/extract attributes and geometries?

SpatiaLite is just one (albeit very nice!) implementation of that
general idea, which should be fairly easy to implement in GRASS.
The biggest problem with the current SpatiaLite API re. GRASS seems
to be that it caters for 2D coordinates only. I am not sure how hard
it would be to work around that limitation.

Ben

Dumping from GRASS vectors --> shapefiles --> GRASS can sometimes lead to data loss.

(make that often)

to move forward on this I guess we need to start coding the easy way to
switch between per-map and per-mapset sqlite modes. there seems to be
enough support for both ways to warrant it.

Hamish

     _______________________________________________
grass-dev mailing list
grass-dev@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/grass-dev

--
Benjamin Ducke
Senior Geospatial Consultant

Oxford Archaeology
Janus House
Osney Mead
OX2 0ES
Oxford, U.K.

Tel: +44 (0)1865 263 800 (switchboard)
Tel: +44 (0)1865 980 758 (direct)
Fax :+44 (0)1865 793 496
benjamin.ducke@oxfordarch.co.uk

------
Files attached to this email may be in ISO 26300 format (OASIS Open Document Format). If you have difficulty opening them, please visit http://iso26300.info for more information.

4

Sorry for this question almost coming out from nowhere, but is this
SpatiaLite extension treating geometric data topologically ? or is it
conform to OGC simple feature definitions ?
If so, it's quite a negative point for a "universal" data exchange model
and for information preservation, isn't it ?

Bye,
Vincent

Le vendredi 08 mai 2009 à 12:08 +0100, Benjamin Ducke a écrit :

Just a quick update on my own thoughts (awful):
There is a note in the OGR SQlite driver page which states that
the SpatiaLite extenstion to SQLite databases will be fully
supported by the time GDAL 1.7.0 gets released:

http://www.gdal.org/ogr/drv_sqlite.html

So I guess here we have a good candidate for a universal data
exchange format that works out-of-the box with every application
that supports the GDAL/OGR interface. Plus SpatiaLite comes with
a very nice set of userland GUI tools to manage (spatial) data in an
SQLite DB, so what more could one ask for?

Ben

Benjamin Ducke wrote:
> Hamish wrote:
>> Dylan:
>>> I also agree on this matter. I think that one thing that is making
>>> this decision particularly difficult is that we are lacking a robust
>>> interchange format for complex vector data.
>>
>> I guess we need to create v.pack, v.unpack modules. should be much easier
>> than the r.pack, r.unpack.
>
> Unless we take "interchange" to include other GIS. I find it frustrating
> that there is no established vector data format that adequately
> preserves information between different systems. SQLite + WKT/WKB
> should be a good and simple way to provide that. It is easy to
> turn an SQLite DB into a spatial database, following these guidelines:
>
> http://trac.osgeo.org/fdo/wiki/FDORfc16
>
> Of course, OGR/GDAL already has support for this "standard":
>
> http://www.gdal.org/ogr/drv_sqlite.html
>
> but I imagine it would be more convenient if GRASS supported it
> natively?
>
> Data exchange with other GIS apps could also be managed by pumping
> (WKT/WKB) SQL statements through stdin/stdout, without the need to
> go through a file, at all (of course both ends would need to
> support this).
>
> In the interim, why not create a nice wrapper module that uses the OGR
> SQlite driver to store/extract attributes and geometries?
>
> SpatiaLite is just one (albeit very nice!) implementation of that
> general idea, which should be fairly easy to implement in GRASS.
> The biggest problem with the current SpatiaLite API re. GRASS seems
> to be that it caters for 2D coordinates only. I am not sure how hard
> it would be to work around that limitation.
>
> Ben
>
>>
>>
>>> Dumping from GRASS vectors --> shapefiles --> GRASS can sometimes
>>> lead to data loss.
>>
>> (make that often)
>>
>>
>> to move forward on this I guess we need to start coding the easy way to
>> switch between per-map and per-mapset sqlite modes. there seems to be
>> enough support for both ways to warrant it.
>>
>>
>> Hamish
>>
>>
>>
>>
>> _______________________________________________
>> grass-dev mailing list
>> grass-dev@lists.osgeo.org
>> http://lists.osgeo.org/mailman/listinfo/grass-dev
>>
>>
>
>

5

Hi Vincent,

actually, SpatiaLite seems to use its own (apparently strictly 2D)
geometry representations in the SQLite DB. I could not find any hint in
the documentation that topology is imposed on the data:

http://www.gaia-gis.it/spatialite/spatialite-manual-2.3.0.html
http://www.gaia-gis.it/spatialite/spatialite-sql-2.3.0.html

But anyway, the OGR driver does not exclusively use SpatiaLite's
storage model. You can use simple WKB/WKT tables if you want.
It seems that even support for AutoDesk's way of SQLite spatial
data storage will be supported in the future.
As long as all applications access the database via the OGR driver, it
doesn't matter which representation you prefer, they'll all work for
data exchange.

What's wrong with OGC simple feature definitions? Too simple for
your purposes?

Ben

Vincent Bain wrote:

Sorry for this question almost coming out from nowhere, but is this
SpatiaLite extension treating geometric data topologically ? or is it
conform to OGC simple feature definitions ?
If so, it's quite a negative point for a "universal" data exchange model
and for information preservation, isn't it ?

Bye,
Vincent

Le vendredi 08 mai 2009 à 12:08 +0100, Benjamin Ducke a écrit :

Just a quick update on my own thoughts (awful):
There is a note in the OGR SQlite driver page which states that
the SpatiaLite extenstion to SQLite databases will be fully
supported by the time GDAL 1.7.0 gets released:

http://www.gdal.org/ogr/drv_sqlite.html

So I guess here we have a good candidate for a universal data
exchange format that works out-of-the box with every application
that supports the GDAL/OGR interface. Plus SpatiaLite comes with
a very nice set of userland GUI tools to manage (spatial) data in an
SQLite DB, so what more could one ask for?

Ben

Benjamin Ducke wrote:

Hamish wrote:

Dylan:

I also agree on this matter. I think that one thing that is making
this decision particularly difficult is that we are lacking a robust
interchange format for complex vector data.

I guess we need to create v.pack, v.unpack modules. should be much easier
than the r.pack, r.unpack.

Unless we take "interchange" to include other GIS. I find it frustrating
that there is no established vector data format that adequately
preserves information between different systems. SQLite + WKT/WKB
should be a good and simple way to provide that. It is easy to
turn an SQLite DB into a spatial database, following these guidelines:

http://trac.osgeo.org/fdo/wiki/FDORfc16

Of course, OGR/GDAL already has support for this "standard":

http://www.gdal.org/ogr/drv_sqlite.html

but I imagine it would be more convenient if GRASS supported it
natively?

Data exchange with other GIS apps could also be managed by pumping
(WKT/WKB) SQL statements through stdin/stdout, without the need to
go through a file, at all (of course both ends would need to
support this).

In the interim, why not create a nice wrapper module that uses the OGR
SQlite driver to store/extract attributes and geometries?

SpatiaLite is just one (albeit very nice!) implementation of that
general idea, which should be fairly easy to implement in GRASS.
The biggest problem with the current SpatiaLite API re. GRASS seems
to be that it caters for 2D coordinates only. I am not sure how hard
it would be to work around that limitation.

Ben

Dumping from GRASS vectors --> shapefiles --> GRASS can sometimes lead to data loss.

(make that often)

to move forward on this I guess we need to start coding the easy way to
switch between per-map and per-mapset sqlite modes. there seems to be
enough support for both ways to warrant it.

Hamish

     _______________________________________________
grass-dev mailing list
grass-dev@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/grass-dev

--
Benjamin Ducke
Senior Geospatial Consultant

Oxford Archaeology
Janus House
Osney Mead
OX2 0ES
Oxford, U.K.

Tel: +44 (0)1865 263 800 (switchboard)
Tel: +44 (0)1865 980 758 (direct)
Fax :+44 (0)1865 793 496
benjamin.ducke@oxfordarch.co.uk

------
Files attached to this email may be in ISO 26300 format (OASIS Open Document Format). If you have difficulty opening them, please visit http://iso26300.info for more information.

6

Ben,

What's wrong with OGC simple feature definitions? Too simple for
your purposes?

Well, briefly analysing this point, I would say storing data in a non
topological way may not be a problem, as far as Grass handles the format
and finally, per se, can deal with spatial relationships between
features of a vector map (adjacency, containment, overlapping, and so
on).

My point of view on vector model (perhaps we are sliding towards a new
topic right now) is that topological structure is helpful mainly for :
* detecting digitizing errors,
* easily performing spatial analysis,
* significantly reducing map storage volumes.

But for my personal needs (particularly dealing with both space and time
variability data mapping) I admit that non-topological models can be a
good way to handle data...

Vincent