7 Extract Attribute Value at given POINT.
The way how to convert the information loaded as a raster layer to theattribute of a vector layer and save into a table of PostgreSQL isdescribed.
7.1 Transform Raster layer to Vector layer (POLYGON) using GRASS.
7.2 Export the transformed Vector layer (POLYGON) to PostgreSQL andPOINT data to which the attributes values will be allocated.
7.3 Add column to POINT data to store the attributes values.
7.4 Store the attributes values into the added column using PostGIS
function.

7.1 Transform Raster layer to Vector layer (POLYGON) using GRASS.
(It is necessary once to transform the raster layer to a vector layer (POLYGON)).
Open GRASS with
LOCATION=latlon(wgs84),
MAPSET=Tsukuba.
Display the raster “s11t”.

1

Convert the raster map to a vector
map.
“File”, “Map type conversion’ and“Raster to Vector map”.

Input Raster map:
S11t (example)
Name of Output Vector map:
s11v (arbitrary)
Feature typearea(Select area because POLYGON
layer is needed for 7.1)
Click “Run”.

As every grids of the input raster areconverted to squares (POLYGON) this takes
much time. Be patient until the message“r.to.vect complete” in “output-GIS.m”.

2

Export the converted vector map toPostgreSQL.
“File”, “Export” and “Vector map”.

7.2 Export the transformed Vector layer (POLYGON) to PostgreSQL andPOINT data to which attributes values will be allocated.
Check ‘export lines as polygons”.
Name of input vector map
s11v
Select feature typearea
OGR output data source
PG:host=localhost
dbname=valley user=yokoi
password=yokoi
OGR layer namecolor (arbitrary)Layer Number
1
Select OGR format
PostgreSQL
OGR layer creation option
DIM=2This takes much time.

3

Be patient until the message “…featureswritten” is shown.

Exit from GRASS.

Check the exported data using PostgreSQL.

Open “Command Prompt” of PostgreSQL.
psql valley –U yokoi
Then, type passwprd.
Check the table “color”¥d color

Geometry is stored in“wkb_geometry” column. The
converted data are stored in “value”
column. SRID is correctly set 32767.

4

Browse first ten rows of the table “color” to check the contents.
select cat,value from color order by cat limit 10;

The table “color” does not have the primary key setting that is necessary to beloaded on QGIS.
alter table only color add constraint color_pkey primary key(ogc_fid);

where “alter table” is a PostgreSQL command, “only color” denotes that thiscommand is applied only to the table “color”, “add constraint color_pkey primarykey(ogc_fid)” means that the column “ogc_fid” is selected for the primary key ofthis table and named “color_pkey”.

Check the table “color” using the command¥dNotice toIndexes;“Color_pkey” PRIMARY KEY, btree (ogc_fid)
The primary key was set.

5

Open QGIS.
Click “Add PostGIS layer” button. Then connect to “valley” database.

Select table “color” and click “Add”.

PostGIS layer “color” is loaded.
Then, the procedure is succeeded.

6

Make a POINT data layer (if you have target POINT data layer already, thisprocedure can be skipped)
Add new table “obspoints” in the database “valley”. This procedure has been
described in “2_creating_vecor_layer.ppt”.
Copy the sql batch file “D:/batch_sql/mkpoint.sql” to “C:/TEMP/mkvalley.sql”.
Edit “C:/TEMP/mkvalley.sql” using WordPad as shown below.
CREATE TABLE obspoints (id1 integer NOT NULL, CONSTRAINT
obspoints_pkey PRIMARY KEY (id1)) WITHOUT OIDS;
ALTER TABLE obspoints OWNER TO yokoi;
select AddGeometryColumn('obspoints', 'the_geom', 32767, 'POINT', 2);
where the changed parts are shown blue, SRID “32767” is set as same as that ofthe table “color”.
Execute the sql batch file “C:/TEMP/mkvalley.sql” using “¥i” command.
¥i C:/TEMP/mkvalley.sql

Check the newly created table “obspoints”.
¥d obspoints

Back to QGIS.
Connect to the table “obspoints”.

77.3 Add column to POINT data to store the attributes values.
Open “Attribute Table” by using “Open Table” button.

Click on “Start Editing” button and “New Column” button.
Set Name=“vale”, Type=“Int4”.

New column “value” is added. Click on “Stop Editing” button.

Click on “Save” button.
Then, click on “close” of “Attribute Table”.

8Digitize POINT data (refer3_Input_data_to_Vector_layers.ppt).
“Toggle Editing” button,“Capture Point” button,
Clicking on “obspoints” layer
and type in “id1”,“Toggle Editing” button,“Save”.

7.4 Store the attributes values into the added column using PostGIS function.
In “Command Prompt” of PostgreSQL.
Store data of “value” of the table “color” into the column “value” of the table“obspoints” at the locations of “obspoints”.
update obspoints set val=value from color where color.wkb_geometry&& obspoints.the_geom;This means:Update the table “obspoints” setting the column “val” equal to the column “value”
from the table “color” under the condition that is the value of “wkb_geometry”
column of the table “color” coincides with that of “the_geom” column of the table“obspoints”,
where “update” is a PostgreSQL command, && is the operator of PostGIS that
reply TRUE where two geometries share the same point or area.

9

Check the added information.
select id1,val from obspoints order by id1;

Exit from the database “valley” using “¥q”.
Close “Command Prompt” of PostgreSQL.

It can be checked using “Identify Features” button of QGIS.
Close QGIS.

10

11