NAME

d.site.pg - Display select sites from a database query. (GRASS-RDBMS Interface Display Program)

SYNOPSIS

d.site.pg
d.site.pg help
d.site.pg [sql=file] tab=name coordx=name coordy=name [cats=name] [where=name] [map=name] [plot=color,icon,size]
 

ALTERNATE

d.vect.pg -s help
d.vect.pg -s sql=filename [map=name] [plot=color,icon,size]

DESCRIPTION

Query sites in a PostgreSQL database using an SQL file or by specifying conditions directly.  Optionally, plot display or send output to a sites_list "map".

d.site.pg displays select point location information returned from a database query. The site locations returned from the database query are displayed as icons in the active frame. The user controls the color, size and icon used in the graphic output. As option, a site list of the database output is generated and placed in the current mapset/location.

COMMAND LINE OPTIONS

Parameters:

tab=databasetablename
Table containing X, Y coordinate values.
coordx=database_x_columnname
Column containing x coordinate values.
coordy=database_y_columnname
Column containing y coordinate values.
where=SQLwhereclause
SQL "where" clause which specifies the query criteria to be used in subsetting the database. The information specified in the where option must indicate the column(s) to be used, the operators to be used in the evaluation and the values which the data in the column will be evaluated against.
For example, if you want to select only those records from the table well where the value for depth is either 58 or 75 the following could be entered:
well.depth = 58 or well.depth = 75

 

 

To select all wells in an area where the value for well is between 50 and 120:

well.depth > 50 and well.depth < 120

 

 

To select all wells of depth greater than 75 where the value for owner is not equal to SMITH:

well.depth > 75 and well.owner != 'SMITH'

If the database column used as the selection criteria is a character field then the associated value must be placed in quotes. To determine the data types associated with columns in the currently selected database use the g.column.pg command with the -v flag.

In addition to the operators presented in the examples above a range of relational operators including and, or, etc. are supported.

Queries more complex are best implemented using a prepared SQL file.

map=list
Name of sites list to output


cats=name

Name of the category field to be assigned in the new site list
plot=color,icon,size
Colors: red, orange, yellow, green, blue, indigo, violet, magenta, brown, gray, white, black
Icon: diamond, box, plus, x
Size: 1-9
Default: gray, x, 3
sql=filename
SQL statements specifying well formed selection criteria

 

ALTERNATE COMMAND LINE USAGE

The alternate command line usage is provided to simplify the process of retrieving information from more than one table in the query criteria. The alternate command line structure is selected using the the [-s] flag on the command line. When using this flag the user must include the name of a text file on the command line as well. This file must include a complete, well formed SQL SELECT statement specifying the query criteria to retrieve values which correspond to category values in a GRASS data layer.

Flag:

-s
SQL select statements are input from a prepared file

Parameters:

map=list
Name of sites list to output
plot=color,icon,size
Colors: red, orange, yellow, green, blue, indigo, violet, magenta, brown, gray, white, black
Icon: diamond, box, plus, x
Size: 1-9
Default: gray, x, 3

 
sql=filename
SQL statements specifying well formed selection criteria

 
 
EXAMPLE:

1. d.site.pg tab=izb coordx=xcoor coordy=ycoor where="how_big~'small'" plot=white,diamond,9

Result: displays only 'small' huts in white color diamonds size 9

2. d.site.pg -s sql=izb3.sql map=huts_good

izb3.sql is:
select xcoor,ycoor,owner from izb where how_good ~ 'good' and izb_info.name=name and izb_info.rec_id=num

Result: displays only 'good' huts (and only huts which have 'name' coinciding with that taken from a table izb_info, linked on the hut number) and creates site map called huts_good with categories taken from the database field 'owner'

BUGS

None known.

NOTE

This program requires the Postgres database software.

SEE ALSO

g.column.pg, g.select.pg, g.stats.pg, g.table.pg, d.rast.pg, d.vect.pg, d.what.r.pg, d.what.s.pg, d.what.v.pg, r.reclass.pg, r.rescale.pg, v.reclass.pg

AUTHOR

Original Informix SQL-tools: James A. Farley, Wang Song, and W. Fredrick Limp University of Arkansas, CAST

Postgres modifications: Janne Soimasuo, Faculty of Forestry, University of Joensuu, Finland.

Updated to GRASS 5 by Alex Shevlakov (sixote@yahoo.com)