[GRASS-user] Display postgis query

GRASS GRASS user
,
1

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?

Thanks
Daniel

PS - Sory if the question is a bit fuzzy. I can try to describe it
better if needed

2

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

3

Hi Moritz,

Thanks for the help. You just showed that it's possible to do
sub-queries in sql that is, include a select statement inside the
where clause :slight_smile: I was not aware of that (I'm a new database user...)

About the dataset, I first tried using v.external and now I created a
Grass vector file. But somehow, for too complex queries, d.vect
freezes. But I'm able to use v.extract with no problem and create the
necessary files.

Now a related question. Is there a way to join a new column to the
dataset extracted by v.extract? Or will I need to do that on a second
process. Because v.extract will give me the municipal polygons but the
census information is on another table...

Thanks
Daniel

On Tue, May 10, 2011 at 12:52 PM, Moritz Lennert
<mlennert@club.worldonline.be> wrote:

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

4

On 10/05/11 20:46, Daniel Victoria wrote:

Hi Moritz,

Thanks for the help. You just showed that it's possible to do
sub-queries in sql that is, include a select statement inside the
where clause :slight_smile: I was not aware of that (I'm a new database user...)

About the dataset, I first tried using v.external and now I created a
Grass vector file. But somehow, for too complex queries, d.vect
freezes. But I'm able to use v.extract with no problem and create the
necessary files.

Now a related question. Is there a way to join a new column to the
dataset extracted by v.extract? Or will I need to do that on a second
process. Because v.extract will give me the municipal polygons but the
census information is on another table...

Two options:

- v.db.join: This will permanently fill your polygon table with the census info

- Create a view connecting your polygon ids with the census info and the use v.db.connect -o to connect your map to this view. This has the advantage that your polygon attribute table is unchanged and you can reconnect to it afterwards with v.db.connect -o, thus avoiding filling the table more and more with new data. (Maybe this could be added as an option to v.db.join ?)

Moritz