[GRASS-dev] better mysql support

Emanuele_Conti · April 15, 2007, 8:54pm

Hi everybody,
I’m a university student and I’m doing a thesis on the integration of grass with mysql. I found that grass has a good support for postgis and I’d like to duplicate all the main features available for this db for mysql, which has geometry extensions (opengis compliant) and performs significantly better than postgres. I downloaded grass 6.3 cvs source code yet and I started to have a look to the code, but I’ve some questions:

from the grass 6 features page I understood that with postGis it’s no more necessary to pass through gdal ogr translation: is this true? Is there a direct db manipulation?
what part of the code implements the direct "Export/Import to PostGIS" (functions as v.to.db and so on) and which directories and files should I look at?
what documentation could help me and where could I find it (I googled for technical docs oriented to grass/db integration, but I did’t found much material);
is this a project that could become part of grass in any way? How could I receive support and/or feedback when necessary?

I hope to read of you soon. Thanks…

–
Emanuele Conti
Senior Student at University ROMA TRE
Department of Informatics and Automation
ROME, Italy (matr. 251318)
Mob: +39 328 2681070
Home: +39 06 39741124
e-mail: emanuele.conti@yahoo.com
y!: emanuele_c
msn: emanuele_c@yahoo.com

Glynn_Clements1 · April 16, 2007, 12:17am

Emanuele Conti wrote:

I'm a university student and I'm doing a thesis on the integration of grass
with mysql. I found that grass has a good support for postgis

GRASS doesn't specifically support PostGIS. The generic database
interface (DBMI) has a driver for PostgreSQL. Also, v.{in,out}.ogr
support whichever drivers GDAL/OGR was built with.

and I'd like
to duplicate all the main features available for this db for mysql, which
has geometry extensions (opengis compliant) and performs significantly
better than postgres. I downloaded grass 6.3 cvs source code yet and I
started to have a look to the code, but I've some questions:
- from the grass 6 features page I understood that with postGis it's no more
necessary to pass through gdal ogr translation: is this true? Is there a
direct db manipulation?
- what part of the code implements the direct "Export/Import to
*PostGIS"*(functions as
v.to.db and so on) and which directories and files should I look at?

v.to.db uses the DBMI; the PostgreSQL driver is in
db/drivers/postgres, while the MySQL driver is in db/drivers/mysql.

--
Glynn Clements <glynn@gclements.plus.com>

Paul_Kelly · April 16, 2007, 2:56am

On Sun, 15 Apr 2007, Emanuele Conti wrote:

Hi everybody,
I'm a university student and I'm doing a thesis on the integration of grass
with mysql. I found that grass has a good support for postgis and I'd like
to duplicate all the main features available for this db for mysql, which
has geometry extensions (opengis compliant) and performs significantly
better than postgres. I downloaded grass 6.3 cvs source code yet and I

As I understand it the OpenGIS vector format is a lot less featureful than the GRASS format (i.e. it doesn't store the topological relationships between vector features, which is important for a lot of vector analyses) and thus it is debateable whether it is useful to store GRASS data in that format. It would mean topology would have to be re-built from the external data any time an analysis is to be done in GRASS. My understanding is the OpenGIS format is more of a quick-access format useful for displaying data, but if it is to be edited, topological format is better for ensuring the integrity of the data (no overlapping boundaries and that kind of thing).

As far as I can remember GRASS used to directly support storage of vector geometry in PostGIS in 5.7/6.0 pre-releases, but that was removed before the release of 6.0.0 final because there were problems with it. The PostGIS support was integrated by a group of final year project students I think - there may also have been a paper written about it. It would probably be a good idea to investigate this (e.g. download an old version of GRASS and try it out etc.) and investigate what the problems were and why it was dropped, and see if you can do better with your MySQL spatial support.

Off the top of my head though, I think the shortcomings of the OpenGIS vector format for spatial analysis were a major factor. But you would need to read old mailing list posts from Radim Blazek for more insight.

Paul

started to have a look to the code, but I've some questions:
- from the grass 6 features page I understood that with postGis it's no more
necessary to pass through gdal ogr translation: is this true? Is there a
direct db manipulation?
- what part of the code implements the direct "Export/Import to
*PostGIS"*(functions as
v.to.db and so on) and which directories and files should I look at?
- what documentation could help me and where could I find it (I googled for
technical docs oriented to grass/db integration, but I did't found much
material);
- is this a project that could become part of grass in any way? How could I
receive support and/or feedback when necessary?

I hope to read of you soon. Thanks...

--
Emanuele Conti
Senior Student at University ROMA TRE
Department of Informatics and Automation
ROME, Italy (matr. 251318)
Mob: +39 328 2681070
Home: +39 06 39741124
e-mail: emanuele.conti@yahoo.com
y!: emanuele_c
msn: emanuele_c@yahoo.com

nicholas.g.lawrence · April 16, 2007, 4:07am

grass-dev-bounces@grass.itc.it wrote on 16/04/2007 12:56:18 PM:

On Sun, 15 Apr 2007, Emanuele Conti wrote:
> Hi everybody,
> I'm a university student and I'm doing a thesis on the integration of

grass

> with mysql. I found that grass has a good support for postgis and I'd

like

> to duplicate all the main features available for this db for mysql,

which

> has geometry extensions (opengis compliant) and performs significantly
> better than postgres. I downloaded grass 6.3 cvs source code yet and I

Paul
As I understand it the OpenGIS vector format is a lot less featureful

than

the GRASS format (i.e. it doesn't store the topological relationships
between vector features, which is important for a lot of vector analyses)
and thus it is debateable whether it is useful to store GRASS data in

that

format.

Nick
Isn't OpenGIS planning to introduce proper topology at a later date?

My understanding was that the 'first pass' was simple, non-topological
vectors because they are simpler.

************************************************************
Opinions contained in this e-mail do not necessarily reflect
the opinions of the Queensland Department of Main Roads,
Queensland Transport or Maritime Safety Queensland, or
endorsed organisations utilising the same infrastructure.
If you have received this electronic mail message in error,
please immediately notify the sender and delete the message
from your computer.
************************************************************

Paolo_Cavallini · April 16, 2007, 5:50am

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

do you have data supporting this statement?
from my experience this is not true.
pc

Emanuele Conti ha scritto:

mysql, which has geometry extensions (opengis compliant) and performs
significantly better than postgres.

- --
Paolo Cavallini
http://www.faunalia.it/pc
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFGIw6d/NedwLUzIr4RApthAJ4iQKCYNGpB6ZXGx83Yk7WYEl4liQCgndMh
woLBvzbDwk660HYOXyN8WL0=
=GHaa
-----END PGP SIGNATURE-----

Emanuele_Conti · April 16, 2007, 8:02am

---------- Forwarded message ----------
From: Emanuele Conti <emanuele.conti@gmail.com>
Date: 16-apr-2007 10.01
Subject: Re: [GRASS-dev] better mysql support
To: Paolo Cavallini <cavallini@faunalia.it>

Something can be read from google and so on, but personally I did benchmarks based on performances and mysql server prerforms significantly better in most of circumstances; I did experiments using also ram-fs to store data for both of them, but the all-in-memory engine of mysql is the fastest and better optimized of the possibilities (always in terms of performances), followed by myisam engine with or without ram-fs; at last comes postgres…

2007/4/16, Paolo Cavallini <cavallini@faunalia.it>:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

do you have data supporting this statement?
from my experience this is not true.
pc

Emanuele Conti ha scritto:

mysql, which has geometry extensions (opengis compliant) and performs
significantly better than postgres.

Paolo Cavallini
http://www.faunalia.it/pc
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFGIw6d/NedwLUzIr4RApthAJ4iQKCYNGpB6ZXGx83Yk7WYEl4liQCgndMh
woLBvzbDwk660HYOXyN8WL0=
=GHaa
-----END PGP SIGNATURE-----

grass-dev mailing list
grass-dev@grass.itc.it
http://grass.itc.it/mailman/listinfo/grass-dev

–
Emanuele Conti
Senior Student at University ROMA TRE
Department of Informatics and Automation
ROME, Italy (matr. 251318)
Mob: +39 328 2681070
Home: +39 06 39741124
e-mail: emanuele.conti@yahoo.com
y!: emanuele_c
msn: emanuele_c@yahoo.com

Emanuele_Conti · April 16, 2007, 8:35am

---------- Forwarded message ----------
From: Emanuele Conti <emanuele.conti@gmail.com>
Date: 16-apr-2007 10.34
Subject: Re: [GRASS-dev] better mysql support
To: Glynn Clements <glynn@gclements.plus.com>

So, please, can anyone explain the meaning of the first to point in this feaure list of grass6, where there is a direct and explicit mention to postgis?

2007/4/16, Glynn Clements <glynn@gclements.plus.com>:

Emanuele Conti wrote:

I’m a university student and I’m doing a thesis on the integration of grass
with mysql. I found that grass has a good support for postgis

GRASS doesn’t specifically support PostGIS. The generic database
interface (DBMI) has a driver for PostgreSQL. Also, v.{in,out}.ogr
support whichever drivers GDAL/OGR was built with.

and I’d like
to duplicate all the main features available for this db for mysql, which
has geometry extensions (opengis compliant) and performs significantly
better than postgres. I downloaded grass 6.3 cvs source code yet and I
started to have a look to the code, but I’ve some questions:

from the grass 6 features page I understood that with postGis it’s no more
necessary to pass through gdal ogr translation: is this true? Is there a
direct db manipulation?

what part of the code implements the direct "Export/Import to
PostGIS"(functions as
v.to.db and so on) and which directories and files should I look at?

v.to.db uses the DBMI; the PostgreSQL driver is in
db/drivers/postgres, while the MySQL driver is in db/drivers/mysql.

–
Glynn Clements <glynn@gclements.plus.com>

–
Emanuele Conti
Senior Student at University ROMA TRE
Department of Informatics and Automation
ROME, Italy (matr. 251318)
Mob: +39 328 2681070
Home: +39 06 39741124
e-mail: emanuele.conti@yahoo.com
y!: emanuele_c
msn: emanuele_c@yahoo.com

Emanuele_Conti · April 16, 2007, 8:37am

So, can anyone explain the meaning of the first two points in this feaure list of grass6, where there is a direct and explicit mention to postgis?

2007/4/16, Paul Kelly <paul-grass@stjohnspoint.co.uk>:

On Sun, 15 Apr 2007, Emanuele Conti wrote:

Hi everybody,
I’m a university student and I’m doing a thesis on the integration of grass
with mysql. I found that grass has a good support for postgis and I’d like
to duplicate all the main features available for this db for mysql, which
has geometry extensions (opengis compliant) and performs significantly
better than postgres. I downloaded grass 6.3 cvs source code yet and I

As I understand it the OpenGIS vector format is a lot less featureful than
the GRASS format (i.e. it doesn’t store the topological relationships
between vector features, which is important for a lot of vector analyses)
and thus it is debateable whether it is useful to store GRASS data in that
format. It would mean topology would have to be re-built from the external
data any time an analysis is to be done in GRASS. My understanding is the
OpenGIS format is more of a quick-access format useful for displaying
data, but if it is to be edited, topological format is better for ensuring
the integrity of the data (no overlapping boundaries and that kind of
thing).

As far as I can remember GRASS used to directly support storage of vector
geometry in PostGIS in 5.7/6.0 pre-releases, but that was removed before
the release of 6.0.0 final because there were problems with it. The
PostGIS support was integrated by a group of final year project students I
think - there may also have been a paper written about it. It would
probably be a good idea to investigate this (e.g. download an old version
of GRASS and try it out etc.) and investigate what the problems were and
why it was dropped, and see if you can do better with your MySQL spatial
support.

Off the top of my head though, I think the shortcomings of the OpenGIS
vector format for spatial analysis were a major factor. But you would need
to read old mailing list posts from Radim Blazek for more insight.

Paul

started to have a look to the code, but I’ve some questions:

from the grass 6 features page I understood that with postGis it’s no more
necessary to pass through gdal ogr translation: is this true? Is there a
direct db manipulation?

what part of the code implements the direct "Export/Import to
PostGIS"(functions as
v.to.db and so on) and which directories and files should I look at?

what documentation could help me and where could I find it (I googled for
technical docs oriented to grass/db integration, but I did’t found much
material);

is this a project that could become part of grass in any way? How could I
receive support and/or feedback when necessary?

I hope to read of you soon. Thanks…

–
Emanuele Conti
Senior Student at University ROMA TRE
Department of Informatics and Automation
ROME, Italy (matr. 251318)
Mob: +39 328 2681070
Home: +39 06 39741124
e-mail: emanuele.conti@yahoo.com
y!: emanuele_c
msn: emanuele_c@yahoo.com

–
Emanuele Conti
Senior Student at University ROMA TRE
Department of Informatics and Automation
ROME, Italy (matr. 251318)
Mob: +39 328 2681070
Home: +39 06 39741124
e-mail: emanuele.conti@yahoo.com
y!: emanuele_c
msn: emanuele_c@yahoo.com

Paul_Kelly · April 16, 2007, 9:14am

On Mon, 16 Apr 2007, Emanuele Conti wrote:

So, can anyone explain the meaning of the first two points in this feaure
list <http://grass.itc.it/grass60/index.php#features> of grass6, where there
is a direct and explicit mention to postgis?

I'm not sure. The first point explicitly says the interface to PostGIS is through OGR. PostGIS export/import does indeed have a bullet point to itself - but note it does say export/import - meaning that the geometry is still converted to/from GRASS native format. I'm not sure why its there. Note that the page is for 6.0, which is quite old now. It may be a confusion over the direct PostGIS support which has been recently removed then, or it may simply refer to importing/exporting from/to PostGIS via OGR.

This message may be interesting to you about the limitations of the old (GRASS 5.7) PostGIS support:
http://grass.itc.it/pipermail/grass5/2003-October/012808.html
In particular, it implies that the reason it was slow was because of the way GRASS queried PostGIS, not PostGIS itself. So if you query MySQL spatial in the same way, you would not get any speed improvement because the bottleneck was the interface between GRASS and PostGIS. Or, looking at it another way, if you get it to work fast with MySQL then it would probably also work fast with PostGIS.

As is probably obvious here, I only understand most of the issues involved at a superficial level but am trying to point out where you should read around to get more information.

Paul

Paolo_Cavallini · April 16, 2007, 11:07am

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

This should be related to mysql being non-ACID, something we often
cannot afford in GIS.
Performance != speed, especially in geographic contexts. PostGIS is far
more powerful than the MySQL spatial.
All the best.
pc

Emanuele Conti ha scritto:

Something can be read from google and so on, but personally I did
benchmarks based on performances and mysql server prerforms
significantly better in most of circumstances; I did experiments using
also ram-fs to store data for both of them, but the all-in-memory engine
of mysql is the fastest and better optimized of the possibilities
(always in terms of performances), followed by myisam engine with or
without ram-fs; at last comes postgres...

2007/4/16, Paolo Cavallini <cavallini@faunalia.it
<mailto:cavallini@faunalia.it>>:

do you have data supporting this statement?
from my experience this is not true.
pc

Emanuele Conti ha scritto:

mysql, which has geometry extensions (opengis compliant) and performs
significantly better than postgres.

- --
Paolo Cavallini
http://www.faunalia.it/pc
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFGI1jf/NedwLUzIr4RAoanAJ9ntwL5nHqK0TnLfKodnJ/HEPKYEQCdEyPI
Ved//u4ii0YaJEmca9I+fDM=
=u+0w
-----END PGP SIGNATURE-----

Glynn_Clements1 · April 16, 2007, 2:16pm

Emanuele Conti wrote:

So, please, can anyone explain the meaning of the first to point in this
feaure list <http://grass.itc.it/grass60/index.php#features> of grass6,
where there is a direct and explicit mention to postgis?

That refers to the use of OGR, via v.external or v.{in,out}.ogr

  http://grass.itc.it/grass60/manuals/html60_user/v.external.html
  http://grass.itc.it/grass60/manuals/html60_user/v.in.ogr.html
  http://grass.itc.it/grass60/manuals/html60_user/v.out.ogr.html

--
Glynn Clements <glynn@gclements.plus.com>