Example 1: Duwakot-Nepal
A field survey of the vulnerability of buildings was conducted by the students of
the area Changnarayan, Duwakot in Kathmandu Valley, Nepal under theguidance of the researchers of Nepal Engineering Collage (NEC). A
georeferenced satellite image for Basemap and the check sheet for theevaluation of buildings and houses were provided to the students. Identification
numbers of buildings were noted on the check sheets and on Basemap.
Buildings in Basemap were digitized afterward by the researchers of NEC andsaved in four Shape files. Namely the surveyed area was divided in four subareas. The information noted on the check sheets were joined in fourWorksheets of MicroSoft Excel™.
Then, they are saved in a CD and sent to IISEE.
These data are used in this example.

First, four Shape files and Basemap stored in CD are browsed using QGIS. The
coordinates at right bottom shows that these are digitized on Basemap with Pixelcoordinates. Namely the .tfw file provided with Basemap was not used for the digitizingtask. Besides, the vector objects stored in Shape files are not on the area covered byBasemap.

1

There are at least two ways to define Pixel coordinates. In one way the origin (0.,0.) is
set at the upper left corner, the horizontal axis right, the vertical axis upward. In anotherway, the origin (0.,0.) is set at the lower left corner.
Basemap *.tif file without associated *.tfw file is interpreted by QGIS in the former one,
whereas it seems that the latter one was used for digitizing task when these Shape files
were made.
Second, a temporal *.tfw file is created using WordPad as shown below. This forces
QGIS to follow the latter way mentioned above.

Basemap *.tif has size 4500X2500 Pixels.
Format of .tfw file:X-scale (length for a pixel along X-axis)
X-rotation angle
Y-rotation angle-Y-scale (negative sign & length for a pixel along y-axis)
X-value of the center of the pixel at the top left corner
Y-value of the center of the pixel at the top left corner(Decimal point at the 21st column.)

Four Shape files and Basemap with the temporal *.tfw file arebrowsed using QGIS. The polygons in Shape files coincide to
the houses on Basemap.

2Transform Basemap from pixel coordinate to wgs84.

Duwakot_base_map.tif was georeferenced already when it had been created. The
corresponding *.tfw file is available and shown below.
The georeferencing information given there is
Origin:Longitude: 85.411002 deg.
Latitude: 27.706998 deg.
res: 0.000004 deg. both for longitude and Latitude
Then, the coordinates of the four corners are asshown below. ( ) denotes geographical coordinates,
whereas < > shows pixel coordinates.
(85.411, 27.707)<0.0,0.0>(85.429, 27.707)<4500.0,0.0>(85.411, 27.697)<0.0, -2500.0>(85.429, 27.697)<4500.0, -2500.0>

Transform the vector maps from pixel coordinate to wgs84.
Open GRASS with LOCATION=x-y, MAPSET=Duwakot.
Import a Shape file using “v.in.ogr”.

Repeat it for other threeshape files.

3

Confirm the imported vector
maps using Map Display.

Create “POINTS” file using WordPad.
The four corners of Basemap have the following coordinates. Values in ( , ) denotethe geographycal coordinates in wgs84, whereas those in < , > in Pixel coordintes.
(85.411, 27.707)<0.0,0.0>(85.429, 27.707)<4500.0,0.0>(85.411, 27.697)<0.0, -2500.0>(85.429, 27.697)<4500.0, -2500.0>“POINTS” file contains these values as shown below. Coordinate transform isperformed using them.

4Conduct coordinates transform using “v.transform”.
“POINTS” file created in the previous slide is used for transformation.

Repeat it for other threevector maps.

Export four vector maps to Shape file using“v.out.ogr” in order to have the interim backup files.
Exit from GRASS.

5

Load four Shape files in wgs84 together with Basemap in wgs84 on QGIS to confirm
their spacial correspondence. Remove them after confirmation.

Import four Shape files in wgs84 together to Database “valley” of PostgreSQL using“SPIT” plugin of QGIS. Don’t use the default SRID but 32767 that is same as SRID of
LOCATION=lat-long(wgs84) of GRASS

6

Load four PostGIS layers to QGIS using “Connect to PostGIS” button to confirm them.

Vector layer: building_ka (Attribute Data in Excel Worksheet & building_ka.shp)

Both have “GIS_NO” with that rows in both files are related.

7“building_ka” layer

csv file “att_ka.csv” modified from the given Excel Worksheet “ka”.

8Create a table to store the attributes imported from csv file.
Edit the sql batch file “mkvalley.sql” as follows.

Double “-” at the beginning of the third lines denote “This line is a comment”. Thisline is ignored at executing the sql batch file.
Open “Command Prompt” of PostgreSQL for the database “valley”, user “yokoi”
and its password.
Execute “mkvalley.sql” using “¥i” command.

Note:It is possible to achieve the same result by executing PostgreSQL commands
in “mkvalley.sql” one by one by manual typing or copy & paste from “mkvalley.sql”.

Store the attributes into the created table.
Edit the sql batch file“add_cols1ka.sql” using WordPad asshown in the top figure.
Execute it using “¥i” command as
shown in the middle figure.
¥i mysqlfile

Then, copy the attributes from
“att_ka.csv” file to table asshown in the bottom figure.
¥copy mytable FROM mycsvfile
WITH CSV HEADER

9

Confirm the columns of the table “att_ka”.
¥d mytable
Note that the primary key is set at “id1”.

Confirm the contents of the table “att_ka”.
select column1,column2 from mytable
For this slide column1=gis_id1
column2=house_owner1

Add columns to the table of the Shape file to store the attributes.
“building_ka” has geometry and ID only.
Edit the sql batch file“add_cols2ka.sql” using WordPad asshown in the top figure.

Execute it using “¥i” command as
shown in the middle figure.
¥i mysqlfile

10Confirm the columns of the table“building_ka”.
¥d mytable
Note that the primary key is set at “gid”.

Copy the corresponding attribute values from “att_ka” to “building_ka”.
Edit the sql batch file “values_ka.sql” using WordPad as shown below.

Execute it using “¥i” command as shown below.
¥i mysqlfile
Now, “building_ka”
has attributes andgeometry together.

11Confirm updated “building_ka” by connecting from QGIS and browsing “Identify
Result” dialog using “Identify Feature” button.

Change PRIMARY KEY.
Primary key is set to “gid” and changed to “serial_no” here.
Drop the constraint
PRIMARY KEY onceand set it again to“serial_no”.

Drop the columns “gid”,“cat” and “gis_no” thatare not used later.
Then, confirm“building_ka” again.

12

Export to Shape file for interim back up using “pgsql2shp”.
Quit from “Command Prompt” of PostgreSQL using “¥q”.

Pgsql2shp mydatabase mytable –f myshpfile –u myusername –P mypassword
Note that long column names are shorten automatically.

Vector layer: building_kha (Attribute Data in Excel Worksheet & building_kha.shp)

7
10

22

13

For this vector layer, Shape file has 22 “GIS_NO”, whereas Excel worksheet does
not have column entitled in it but does have the Q-th column without name that has
22 rows. Both of them skip “7”, ”10” and “22”.
Therefore, it seems probable that these can be used as ID to relate the geometryin Shape file and the attributes in Excel Worksheet.

“building_kha” layer

14csv file “att_kha.csv” modified from the given Excel Worksheet “kha”.
The column name “GIS_ID” is added to the Q-th column without name in
the three slides before.

Create a table to store the attributes imported from csv file.
Edit the sql batch file “mkvalley.sql” as follows.

Double “-” at the beginning of the third lines denote “This line is a comment”. Thisline is ignored at executing the sql batch file.
Open “Command Prompt” of PostgreSQL for the database “valley”, user “yokoi”
and its password.
Execute “mkvalley.sql” using “¥i” command.

Note:It is possible to achieve the same result by executing PostgreSQL commands
in “mkvalley.sql” one by one by manual typing or copy & paste from “mkvalley.sql”.

15

Edit the sql batch file“add_cols1kha.sql” using WordPad
as shown in the top figure.

Execute it using “¥i” command as
shown in the middle figure.
¥i mysqlfile

Confirm the table “att_kha”.

Then, copy the attributes from “att_kha.csv” file to table as shown in the bottom figure.
¥copy mytable FROM mycsvfile WITH CSV HEADER

16

Add columns to the table of the Shape file to store the attributes.
“building_kha” has geometry and ID only.
Edit the sql batch file“add_cols2kha.sql” using WordPad
as shown in the top figure.

Execute it using “¥i” command as
shown in the middle figure.
¥i mysqlfile

Confirm the columns of the table “building_kha”.
¥d mytable
Note that the primary key is set at “gid” and srid(the_geom) is set “-1”. The
latter means that the information of coordinates used at georeferencing is not
attached to the table yet.

17

Copy the corresponding attribute values from “att_kha” to “building_kha”.
Edit the sql batch file “values_kha.sql” using WordPad as shown below.

Execute it using “¥i” command as shown below.
¥i mysqlfile
Now, “building_kha”
has attributes andgeometry together.

Confirm updated “building_kha” by connecting from QGIS and browsing “Identify
Result” dialog using “Identify Feature” button.

18

Change PRIMARY KEY.
Primary key is set to “gid” and changed to “serial_no” here.
Drop the constraint
PRIMARY KEY onceand set it again to“serial_no”.

Drop the columns “gid”,“cat” and “gis_no” thatare not used later.
Then, confirm“building_kha” again.

Export to Shape file for interim back up using “pgsql2shp”.
Quit from “Command Prompt” of PostgreSQL using “¥q”.

Pgsql2shp mydatabase mytable –f myshpfile –u myusername –P mypassword
Note that long column names are shorten automatically.

19

Vector layer: building_ga (Attribute Data in Excel Worksheet & building_ga.shp)

For this vector layer, both of Shape file and Excel worksheet have the column
entitled “GIS_NO”. The former has 24 rows but “11”, “17” and “18” are duplicatedand then in total 21 rows. The latter has 21 rows.
Therefore, it seems probable that these “GIS_ID” relates the geometry in Shapefile and the attributes in Excel Worksheet.

20

“building_ga” layer. GIS_NO is shown together. “11”,“17” and “18” are duplicated.

The duplicated rows are deleted from “building_ga”. The selection of one of the
duplicated rows to be deleted is “Try and Error” checking the result of delete using
QGIS.
Delete the row “gid”=10 (“GIS_NO”=11)delete from building_ga where gid=10;
Delete the rows “gid”=22, 23 (“GIS_NO”=17, 18)delete from building_ga where gid>21;

21

csv file “att_ga.csv” modified from the given Excel Worksheet “ga”.
The rows of the column entitled “GIS_ID” that have not values can not be
used. Then, “serial_no”=“92”, “93”, “94”, “96” are not available.

Create a table to store the attributes imported from csv file.
Edit the sql batch file “mkvalley.sql” as follows.

Double “-” at the beginning of the third lines denote “This line is a comment”. Thisline is ignored at executing the sql batch file.
Open “Command Prompt” of PostgreSQL for the database “valley”, user “yokoi”
and its password.
Execute “mkvalley.sql” using “¥i” command.

Note:It is possible to achieve the same result by executing PostgreSQL commands
in “mkvalley.sql” one by one by manual typing or copy & paste from “mkvalley.sql”.

22Edit the sql batch file“add_cols1ga.sql” using WordPad
as shown in the top figure.

Execute it using “¥i” command as
shown in the middle figure.
¥i mysqlfile

Confirm the table “att_ga”.

Then, copy the attributes from “att_ga.csv” file to table as shown in the bottom figure.
¥copy mytable FROM mycsvfile WITH CSV HEADER

23

Add columns to the table of the Shape file to store the attributes.
“building_ga” has geometry and ID only.
Edit the sql batch file“add_cols2ga.sql” using WordPad
as shown in the top figure.

Execute it using “¥i” command as
shown in the middle figure.
¥i mysqlfile

Confirm the columns of the table “building_ga”.
¥d mytable
Note that the primary key is set at “gid” and srid(the_geom) is set “-1”. The
latter means that the information of coordinates used at georeferencing is not
attached to the table yet.

24

Copy the corresponding attribute values from “att_ga” to “building_ga”.
Edit the sql batch file “values_ga.sql” using WordPad as shown below.

Execute it using “¥i” command as shown below.
¥i mysqlfile
Now, “building_ga”
has attributes andgeometry together.

Confirm updated “building_ga” by connecting from QGIS and browsing “Identify
Result” dialog using “Identify Feature” button.

25

Change PRIMARY KEY.
Primary key is set to “gid” and changed to “serial_no” here.
Drop the constraint
PRIMARY KEY onceand set it again to“serial_no”.

Drop the columns “gid”,“cat” and “gis_no” thatare not used later.
Then, confirm“building_ga” again.
Oops! Column “id” must
be delete.

Export to Shape file for interim back up using “pgsql2shp”.
Quit from “Command Prompt” of PostgreSQL using “¥q”.

Pgsql2shp mydatabase mytable –f myshpfile –u myusername –P mypassword
Note that long column names are shorten automatically.

26

Vector layer: building_gha (Attribute Data in Excel Worksheet & building_gha.shp)

For this vector layer, both of Shape file and Excel worksheet have the column
entitled “GIS_NO”. The former has 20 rows but the latter’s “GIS_NO” column isempty completely.
Unfortunately, there is any hint to find the relation of these two. The column
entitled “Picture No.” has 20 data whereas others have 26.
Therefore, it is assumed that the row number of Excel Worksheet minus 1 might
coincide to “GIS_NO” of Shape file.

27

“building_gha” layer. GIS_NO is shown together.

csv file “att_gha.csv” modified from the given Excel Worksheet “gha”.
The column entitled “GIS_ID” is added and filled based on theassumption mentioned in the two slides before.
The rows of the column entitled “GIS_ID” that have not values can not be
used. Then, “serial_no”=“95”, “97”, “98”, “99”, “100” are not available.

28Create a table to store the attributes imported from csv file.
Edit the sql batch file “mkvalley.sql” as follows.

Double “-” at the beginning of the third lines denote “This line is a comment”. Thisline is ignored at executing the sql batch file.
Open “Command Prompt” of PostgreSQL for the database “valley”, user “yokoi”
and its password.
Execute “mkvalley.sql” using “¥i” command.

Note:It is possible to achieve the same result by executing PostgreSQL commands
in “mkvalley.sql” one by one by manual typing or copy & paste from “mkvalley.sql”.

Edit the sql batch file“add_cols1gha.sql” using WordPad
as shown in the top figure.

Execute it using “¥i” command as
shown in the middle figure.
¥i mysqlfile

29

Confirm the table “att_gha”.

Then, copy the attributes from “att_gha.csv” file to table as shown in the bottom figure.
¥copy mytable FROM mycsvfile WITH CSV HEADER

Add columns to the table of the Shape file to store the attributes.
“building_gha” has geometry and ID only.
Edit the sql batch file“add_cols2gha.sql” using WordPad
as shown in the top figure.

Execute it using “¥i” command as
shown in the middle figure.
¥i mysqlfile

30Confirm the columns of the table “building_gha”.
¥d mytable
Note that the primary key is set at “gid” and srid(the_geom) is set “-1”. The
latter means that the information of coordinates used at georeferencing is not
attached to the table yet.

Copy the corresponding attribute values from “att_ga” to “building_ga”.
Edit the sql batch file “values_gha.sql” using WordPad as shown below.

Execute it using “¥i” command as shown below.
¥i mysqlfile
Now, “building_gha”
has attributes andgeometry together.

31

Confirm updated “building_gha” by connecting from QGIS and browsing “Identify
Result” dialog using “Identify Feature” button.

Change PRIMARY KEY.
Primary key is set to “gid” and changed to “serial_no” here.
Drop the constraint
PRIMARY KEY onceand set it again to“serial_no”.

Drop the columns “gid”,“cat” and “gis_no” thatare not used later.
Then, confirm“building_gha” again.

32

Export to Shape file for interim back up using “pgsql2shp”.
Quit from “Command Prompt” of PostgreSQL using “¥q”.

Pgsql2shp mydatabase mytable –f myshpfile –u myusername –P mypassword
Note that long column names are shorten automatically.

Open GRASS with LOCATION=lat-long(wgs84), MAPSET=Duwakot.
Import table “building_ka” from PostgreSQL to GRASS using “v.in.ogr”.
Option “-o” means that the coordinate system of
the current LOCATION (for this example, wgs84(srid=32767)) is used.

Repeat it for other three layers.

33

Confirm four imported vector maps using “Map Display”.

Combine four vector maps to one“building_Duwakot” using “v.patch”.

34Extract the centroids from “building_Duwakot” and change the type to “POINT”
and store them in “building_Duwakot_point”.

Export vector map “building_Duwakot_point” from GRASS to PostGIS layer“building_Duwakot_point”.

35Confirm the imported table “building_Duwakot_point”.

Note that Primary Key is not set, the geometry is stored in the column“wkb_geometry” and srid(wkb_geometry) is set at 32767.

Add Primary Key constraint to the column “serial_no”.
Then, confirm the table “building_Duwakot_point” again.

Note that Primary Key is set at “serial_no”.

36Confirm updated “building_Duwakot_point” by connecting from QGIS and browsing“Identify Result” dialog using “Identify Feature” button.

Export vector map “building_Duwakot” from GRASS to PostGIS layer“building_Duwakot_polygon” but only polygons.

Exit from GRASS.

37

Add Primary Key constraint to the column “ogc_fid”.
Then, confirm the table “building_Duwakot_polygon” again.

Note: As “serial_no” has duplicated data, it can not be Primary Key.

Find the duplicated data of “serial_no” using
select ogc_fid,serial_no from building_Duwakot_polygon;

Duplication is found at ogc_fid=66 and 68. Besides, null data is found at ogc_fid=67.
Then, remove these rows as follows.

38

Drop constraint Primary
Key that was set at“ogc_fid”.
Then,
add
constraint
Primary Key to “serial_no”.
Confirm that Primary Key
is set at “serial_no”.

On QGIS “building_Duwakot_point” is loaded. Open “Attribute Table” using “Open
Table” button.

39

Add new columns “latitude” and “longitude” using“Start Editing” and “New Column” button. Then savethe change using “Stop Editing” and “Save” button.
Then, “Close” of “Attribute Table”.

Extract the values of longitude and latitude from “wkb_geometry” column.
update building_Duwakot_point set longitude=x(wkb_geometry);
update building_Duwakot_point set longitude=x(wkb_geometry);
Then confirm the update using
select serial_no,longitude,latitude from building_Duwakot_point;

Drop unnecessary columns from “building_Duwakot_point” and“building_Duwakot_polygon” .
alter table building_Duwakot_point drop column ogc_fid;
alter table building_Duwakot_point drop column cat;40

Confirm the table.

Rename the shorten
column names of“building_Duwakot_poi
nt”.

Rename the shorten
column names of“building_Duwakot_polygon”.

41

Confirm PostGIS layer “building_Duwakot_point” by connecting from QGIS andbrowsing “Identify Result” dialog using “Identify Feature” button.

Back up to shape files

Confirm the backuped “building_Duwakot_point.shp” by connecting from QGIS andbrowsing “Identify Result” dialog using “Identify Feature” button. Long column
names are shorten again.

Quit from PostgreSQL
using “¥q”. Exit from“Command Prompt” of
PostgreSQL .

42

43