[GRASS-user] Display postgis query

[Daniel Victoria]

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 :) 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 at 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
>