[GRASS-user] Display postgis query

[Moritz Lennert]

On 10/05/11 02:32, Daniel Victoria wrote:
> Hi all,
>
> I have a polygon map of municipalities and a separate table with
> census data, like soy production per year (10 years of data) and other
> data, for each municipality. What I'd like to do is to be able to
> display a vector of a given query, for instance: all municipalities
> that had soy production in 2005 above a certain level. Or display a
> map where the polygon colors are a function of soy production in a
> given year (cartograms).
>
> Now, the problem is that, since I have 10 years of data and several
> variables (soy, corn, GDP, population), I have the municipal map in
> one table and the census data in a view with 2 million records and
> both can be linked by municipal code.
>
> I know that d.vect will let me put a where clause but, can I do a
> query looking at another table (view). Or is it easier to create a
> temp vector with the data I want (v.select or db.select maybe) and
> then display the temp vect?

You can use subselect clauses in the where, and in these subselect 
clauses you can call other tables. Here's an example with the North 
Carolina demo set in a mapset with a PostgreSQL backend:

g.copy vect=comm_colleges,mycolleges
g.copy vect=hospitals,myhospitals

#create a table
v.distance -a from=mycolleges to=myhospitals upload=cat,dist 
table=dist_colleges_hospitals col=to_cat,dist

#example subselect in d.vect using the dist_colleges_hospitals table
d.vect myhospitals where="cat in (select to_cat from 
dist_colleges_hospitals where from_cat=1 and dist<100000)"

#the same thing with a (in this case redundant) join clause
d.vect myhospitals where="cat in (select myhospitals.cat from 
myhospitals JOIN dist_colleges_hospitals ON myhospitals.cat=to_cat where 
from_cat=1 and dist<100000)"

But the above is for GRASS maps with attribute tables in PostgreSQL. In 
the title of you message you speak of a PostGIS table, i.e. with the 
geometries in PostgreSQL, not in GRASS. I imagine you access this table 
via v.external ? Don't know what impact this might have, but just give 
it a try.

A long while ago there was a discussion about implementing an even more 
arbitrary SQL access to data, and I implemented a proof-of-concept 
version of d.vect.chart allowing this [1], but we never took this any 
further.

Moritz

[1] http://lists.osgeo.org/pipermail/grass-dev/2006-October/026625.html