For instance, assume that secosoilcats is a table containing column values associated with category values in a the GRASS raster file soils (cropname). In addition, assume that compyld is a table containing attribute data on the crop production capacity in the same area. In this example secosoilcats is the table associated with the GRASS raster map and grasscats is the column linking secosoilcats to the GRASS category file, the table compyld contains the attribute information to base the query/reclass on and it is linked to secosoilcats via the common column muid. To specify a query based on attribute values in the table compyld:
Specifying these conditions would insure that all rows from table compyld which satisfy the query criteria would be matched with the corresponding rows in table secosoilcats, which are then related to the spatial features in the GRASS data layer via the GRASS category values.
This syntax is adequate to accomplish 2 table joins. However, if the query demands joins between more than 2 tables it is advisable to use the -s option [see Alternate Command Line Usage] to read a user prepared query, complete with join requirements, from a file.
The user must also specify the name of the GRASS data layer containing these category values on the command line. The following example illustrates the syntax which must be used in constructing a SELECT statement for use with the -s flag.
EXAMPLE (single table)
d.what.r.pg -s sql=statsgo.sql map=secosoils
statsgo.sql:
retrieve unique (secosoilcats.all)
where secosoilcats.tlid=?
Results:
Reports all attributes from the table secosoilcats associated with the raster category value at the current mouse location the raster map secosoils.
EXAMPLE (two table join)
d.what.r.pg -s sql=statsgo.sql map=secosoils
statsgo.sql:
retrieve unique (secosoilcats.all, mapunit.all, compyld.all)
where secosoilcats.grasscat=? and secosoilcats.muid=mapunit.muid and mapunit.muid=compyld.muid
Results:
Reports all attributes from the table secosoilcats, mapunit and compyld associated with the raster category value at the current mouse location in the raster map secosoils.
Postgres modifications: Janne Soimasuo, Faculty of Forestry, University of Joensuu, Finland.
Updated to GRASS 5 by Alex Shevlakov (sixote@yahoo.com)