[GRASS5] grass and postgis

[Kirk R. Wythers]

On Feb 10, 2006, at 9:42 AM, Moritz Lennert wrote:

>
>
> What do you mean by building "with postgis support" ? Postgis is  
> supported via ogr. Postgresql support, however, is necessary to be  
> able to use postgresql tables as database backend.

For some reason I was thinking that postgis was a config option on  
GRASS (like postgres or mysql). I was wrong. I need to start thinking  
about postgis as simply the extension (that it is) to postgres.

>
> I think you shouldn't forget that GRASS is not a cartography  
> program but a full GIS with its own geometry format and data  
> handling. So, using postgis layers in GRASS doesn't really make  
> sense, unless it is for the occasional situation where you need to  
> access.
>
> One disadvantage of the postgis format is that it obliges you to  
> install a postgresql server...

That is correct. But once the server is up and running, it seems to  
be quite trouble free. I have not looked closely at sqlite. I know  
that others here use sqlite and perhaps that the way to go in the  
future. The choice of postgres had more to do with wanting more  
extensive sql options than are available in dbf (additionaly, I  
already had it compiled and running on my machine). Also I find  
pgadmin3 a nice gui option for atrribute editing.

> The standard format in GRASS (with its own geometry format plus dbf  
> and possibly sqlite as database backend) does not impose the need  
> for a full-fledged database server.
>
>> Perhaps the answer to my question is that I do see a speed up with  
>> spatial queries run from GRASS through db.execute (or when I run  
>> them on the database with postgreql).
>
> What exactly do you mean by spatial queries ?The query you  
> mentioned before (i.e. d.vect vector_name size=2 color=blue  
> where="species='WP' AND diam>40") is not a "spatial query" in my  
> defintion, but a simple query over the data. A spatial query would  
> imply things like "all points within a certain distance of X", or  
> "all points within area Y", etc.
>
>> You were right btw Moritz, the slow response to the command d.vect  
>> seems to be at due to the time it takes d.vect to 'draw'.
>
> Does this mean that you created indices on the variables you query  
> for in your postgres data table linked to the GRASS vector layer  
> (such as species and diam in the above query)

correct

> and this doesn't make a difference ?

not much of a difference. It is noticeable, but more so when the  
query is run in postgres (without the drawing overhead of d.vect). I  
would guess that it takes postgres 3 seconds to return the  
where="species='WP' AND diam>40 query without in indexing (and  
instantaneously with indexing), whereas it takes d.vect about 40  
seconds to display the results of the query on a map without indexing  
and maybe 35 seconds with indexing.


>
>> The same query run in postgis is quite fast.
>
> Actually you mean postgresql, since the above query doesn't use any  
> postgis features if I am not mistaking.
>
> In addition, you did not display a map, or ?
> How does running your query through db.select or v.db.select  
> compare to postgresql ? That shouldn't be any different unless the  
> GRASS postgresql introduces some slowing effects.

You're right..  running the query where="species='WP' AND diam>40  
from v.db.select seems to be about the same speed  as running the  
query from postgres

>
>> Also, if I make the query more selective so that fewer data points  
>> get returned. d.vect returns faster (I think because there is less  
>> to draw). Does that make sense to you?
>
> Obviously displaying less features will take less time. But it  
> might also be that the query runs quicker. Try to compare d.vect  
> with v.db.select and see.
>
> BTW, I don't think that this should be considered a bug. So the bug  
> report can be closed and we should continue this discussion only on  
> the list.

I agree. Thanks for all comments Moritz. I understand the role of  
postgis vs. postgresql and how they interacts with GRASS much better.

>
> Moritz
>
> _______________________________________________
> grass5 mailing list
> grass5 at grass.itc.it
> http://grass.itc.it/mailman/listinfo/grass5