[GRASS-user] Advanced SQL Queries

GRASS GRASS user
1

   Two tables: 'sites' contains the ID and geographic coordinates;
'waterchem' contains the ID and water chemistry data. The latter table is a
sub-set of the former.

   I want to display only those sites for which chemistry data are available.
In postgres I write,

   SELECT DISTINCT(w.site) FROM waterchem AS w
   WHERE EXISTS (SELECT s.name FROM sites AS s
   WHERE w.site = s.name);

   Can I submit this same query within GRASS and have the returned sites
displayed by location? If so, is there a preferred method for doing so?

Rich

2

On 12/06/12 23:21, Rich Shepard wrote:

  Two tables: 'sites' contains the ID and geographic coordinates;
'waterchem' contains the ID and water chemistry data. The latter table is a
sub-set of the former.

I want to display only those sites for which chemistry data are available.
In postgres I write,

SELECT DISTINCT(w.site) FROM waterchem AS w
WHERE EXISTS (SELECT s.name FROM sites AS s
WHERE w.site = s.name);

Can I submit this same query within GRASS and have the returned sites
displayed by location? If so, is there a preferred method for doing so?

Just use everything after the first WHERE as argument for the 'where' parameter of d.vect (or any other module with a where parameter).

Moritz

3

On Wed, 13 Jun 2012, Moritz Lennert wrote:

Just use everything after the first WHERE as argument for the 'where'
parameter of d.vect (or any other module with a where parameter).

Moritz,

   Thank you.

Rich

4

Two tables: ‘sites’ contains the ID and geographic coordinates;
‘waterchem’ contains the ID and water chemistry data. The latter table is a
sub-set of the former.

I want to display only those sites for which chemistry data are available.
In postgres I write,

SELECT DISTINCT(w.site) FROM waterchem AS w
WHERE EXISTS (SELECT s.name FROM sites AS s
WHERE w.site = s.name);

Can I submit this same query within GRASS and have the returned sites
displayed by location? If so, is there a preferred method for doing so?

Just use everything after the first WHERE as argument for the ‘where’ parameter of d.vect (or any other module with a where parameter).

Sorry to butt in…
Can you have a where=“” argument that references a table other than the actual vector map’s table?

I tried with i.e. v.extract (to select records from a vector based on a related column in another, separate table, and I wasn’t successful.
?
Is there any way to connect a vector map to a PG view ?

Thanks,
Micha