[GRASS-user] Re: grass-user Digest, Vol 47, Issue 30

[Richard Chirgwin]

>
> Message: 3 Date: Thu, 11 Mar 2010 00:40:35 -0800 (PST) From: Gary 
> Nobles <garynobles at yahoo.com> Subject: [GRASS-user] Re: SQL and GRASS 
> To: grass-user at lists.osgeo.org Message-ID: 
> <1268296835486-4714753.post at n2.nabble.com> Content-Type: text/plain; 
> charset=us-ascii Hi Richard, Thankyou for your help, I have been using:
>> > d.vect  SiteGrid3 where "SquareNumber<>0 "
>> > or
>> > v.db.select map=SiteGrid3 column=square_no where=<>0
>>     
>
> however as you see in my example in my initial post I have a second table:
>
>   
>> > grass SiteGrid3  (one)            Pottery table (many)
>> > |cat|SquareNumber|            |SquareNumber|Type|count|
>> > |1   |1                 |            |1                  |mug |12
>> > |2   |2                 |            |1                  |mug |14
>> > |3   |3                 |            |1                  |mug |3
>> > it continues...
>>     
>
> d.vect  SiteGrid3 where "SquareNumber<>0 " is great for table1 (left) but I
> want to query a non spatial table, the pottery table. So I want to display
> all squares where Pottery.Type ='mug' 
>
> and also:
>
> display: select SiteGrid3.SquareNumber, Pottery.SUM(count) from SiteGrid3,
> Pottery
>
> I may need an inner join so like this
>
> select SiteGrid3.SquareNumber, Pottery.SUM(count) 
> from SiteGrid3, Pottery
> Inner JOIN SiteGrid3
> ON Pottery.SquareNumber=siteGrid3.SquareNumber
> where (possible where statement)
>
> so displaying data based on a complex sql statement
>
> I hope this clarifies my problem
>
> Thanks again
>
> Gary

I think so, but it's easier to understand what you're doing if you paste 
the whole command ...

The Grass-GIS database driver doesn't implement support for the full 
MySQL command set. In particular, I don't think joins are supported.

The option would be either to set a value in the original table with an 
ordinary query in MySQL; or, you could attach the second table (Pottery) 
to layer 2 of the map, and query it directly for display.

In the second case, there will be some MySQL work anyhow, to relate 
Pottery to SiteGrid3 on category.

I'm pretty certain a complex join would end up beyond the Grass-GIS 
database driver, though.

Richard Chirgwin