[GRASS5] [bug #4075] (grass) grass and postgis issue

[Moritz Lennert]

Hi Kirk,

On Thu, February 9, 2006 21:23, Kirk R. Wythers wrote:
>
> On Feb 9, 2006, at 1:59 PM, Moritz Lennert wrote:
>
>> GRASS doesn't need the geometry since it is stored in the GRASS
>> format, so
>> why should it keep this column. What is it you want to do exactly:
>> import
>> a postgis map into GRASS, or view a postgis map with GRASS ? See
>> v.external it it is the latter.
>>
>> As for indexing, this obviously first of all depends on the database
>> backend that you use for GRASS maps (e.g. the dbf backend wouldn't
>> support
>> indexing). You seem to use postgresql as backend, so indexing would be
>> possible. However, most of the indexes are on the geometry column,
>> so they
>> would not be needed for a map in GRASS format. Why do you want these
>> indexes ?
>>
>>
>> Moritz
>
>
> Thanks for the reply Moritz. This all started out as a "can I speed
> this querry up" exercise. Perhaps the fundamental issue is my not
> completely understanding how GRASS makes use of the postgis extension
> to postgresql. You are correct in that I am using psotgresql for
> database management. I have an attribute table of little over a
> million lines of data in a postgres table that is associated with a
> grass vector. The table contains point data based on simple xy utm
> coordinates.
>
> This allows me to run a query like:
>
> d.vect vector_name size=2 color=blue where="species='WP' AND diam>40"
>
> I would guess that it takes d.vect about a minute to draw the results
> of this query. I have never been able to quite figure out how GRASS
> works with the postgis extension. All I was trying to do was speed
> things up a bit, so I added the postgis geometry and spatial indexing
> to the posgresql table. When I discovered that GRASS then didn't
> behave as I expected (g.copy, g.rename), I thought that there were
> three possible explanations: 1. a bug, 2. I didn't have postgis
> configured properly, 3. (most likely) user error, or user not
> understanding how something was supposed to work.
>
> Any enlightenment in that last area is much appreciated.
>

I will probably be stating the obvious here , but just for the archives:
Your vector data can be stored in different formats: internal GRASS
format, postgis, shape file, etc. GRASS can use these formats in two
different ways: either you import a non-GRASS format file into GRASS
format (v.in.ogr) or you can view it with v.external, which doesn't import
the file, but makes it usable for grass commands.

So, if you import into GRASS vector format, the geometry is stored in the
GRASS format, even if the data is in a postgres table (geometry and data
are handled separately in GRASS. This means that it is no longer a postgis
vector layer, but a GRASS vector layer. Postgis columns in the table or
spatial indices will have no impact as the geometry is not in postgres.
So, adding the geometry column and indices to your postgres data table
will not speed up the process of displaying the map, as d.vect only calls
upon the geometry which is stored in the internal GRASS vector format.

In your case, it would be interesting to see whether the delay of drawing
the results of your d.vect command is due to the geometry, or to the where
clause which doesn't touch upon geometry. The first thing I would try is
to create indices (with the command CREATE INDEX in psql) within your
postgres data table on the columns you query for, i.e. species and diam
(and possibly the combination of both if you will often query for the two
together).

If this doesn't help, then it might be d.vect which is "slow" (a million
points is a large amount of data...) and in order to speed things up it
would be necessary to check whether there is any way to optimise d.vect.
But this is far out of my field of competence.

Hope this helps,

Moritz