2 Creating Vector Layers
2.1 Creation of tables of PostgreSQL with geometry column forPostGIS
2.2 Adding columns to tables of PostgreSQL through PostGIS
connection from QGIS

Premise:PostgreSQL
user
name=‘yokoi’, password=‘yokoi’(This can create new database)database
name=‘valley’ owned by user ‘yokoi’(This is connected to PostGIS)1

2.1 Creation of tables of PostgreSQLwith geometry column for PostGISClick on ‘Start’, ‘All Programs’,‘PostgreSQL’ and ‘CommandPrompt’, sequentially.

2Create Tables of PostgreSQL Database using psql command prompt
Account=Administrator

User Account=postgres
PostgreSQL DataBase Server 8.1
Internal DataBase
Superuser=postgres

Account=userPostgreSQL DataBase Client

DataBase=“TEST”
Psql Command Prompt
Table=“buildings”

Create Table

Table2

Table?

On MicroSoft Windows

Login to Database ‘valley’ as user ‘yokoi’.
psql valley –U yokoi
and the password ‘yokoi’.

3Create a new table for POINT data type connected to PostGIS.
¥i D:/batch_sql/mkpoint.sql‘D:/batch_sql/mkpoint.sql’ is the name of a sql batch file of which contents are:
CREATE TABLE buildings (id1 integer NOT NULL, CONSTRAINT buildings_pkey PRIMARY KEY (id1))
WITHOUT OIDS;
ALTER TABLE buildings OWNER TO yokoi;
select AddGeometryColumn('buildings', 'the_geom', 4326, 'POINT', 2);

where blue letters show the name of the created table. ‘4326’ denotes SRID for thewgs84. This file is located in the directory ‘batch_sql’ on ‘D:’ drive (Install CD).

Note: If you want to make a Table of ‘POINT’ type with different name:Copy ‘D:/batch_sql/mkpoint.sql’ into ‘C:/Your_Directory’,
Edit the copied file using Notepad or other editor, namely replace the blue letteredparts shown below with the new table name.
CREATE TABLE buildings (id1 integer NOT NULL, CONSTRAINT buildings_pkey PRIMARY KEY (id1)) WITHOUT OIDS;
ALTER TABLE buildings OWNER TO yokoi;
select AddGeometryColumn('buildings', 'the_geom', 4326, 'POINT', 2);

SRID=4326 means wgs84 geodetic system. If you want to use other geodetic
and/or projection system, obtain the corresponding SRID. QGIS can provide thisinformation.
Start QGIS and click ‘Setting’. Then select ‘Project Properties’ and ‘Projection Tag’.
Look for your preferable projection system in the top panel using vertical slide bar.
Then, check ‘PostGIS SRID:’4

Create a new table for LINESTRING data type connected to PostGIS.
¥i D:/batch_sql/mkline.sql‘D:/batch_sql/mkline.sql’ is the name of a sql batch file of which contents are:
CREATE TABLE roads (id1 integer NOT NULL, CONSTRAINT roads_pkey PRIMARY KEY (id1))
WITHOUT OIDS;
ALTER TABLE roads OWNER TO yokoi;
select AddGeometryColumn(‘roads', 'the_geom', 4326, ‘LINESTRING', 2);

where blue letters show the name of the created table. ‘4326’ denotes SRID forwgs84. This file is located in the directory ‘batch_sql’ on ‘D:’ drive (Install CD).

Note: If you want to make a Table of ‘LINESTRING’ type with different name:Copy ‘D:/batch_sql/mkline.sql’ into ‘C:/Your_Directory’,
Edit the copied file using Notepad or other editor, namely replace the blue letteredparts shown below with the new table name.
CREATE TABLE roads (id1 integer NOT NULL, CONSTRAINT roads_pkey PRIMARY KEY (id1)) WITHOUT OIDS;
ALTER TABLE roads OWNER TO yokoi;
select AddGeometryColumn(‘roads', 'the_geom', 4326, ‘LINESTRING', 2);

SRID=4326 means wgs84 geodetic system. If you want to use other geodetic
and/or projection system, obtain the corresponding SRID. QGIS can provide thisinformation.
Start QGIS and click ‘Setting’. Then select ‘Project Properties’ and ‘Projection Tag’.
Look for your preferable projection system in the top panel using vertical slide bar.
Then, check ‘PostGIS SRID:’5Create a new table for POLYGON data type connected to PostGIS.
¥i D:/batch_sql/mkpolygon.sql‘D:/batch_sql/mkpolygon.sql’ is the name of a sql batch file of which contents are:
CREATE TABLE open_spaces (id1 integer NOT NULL, CONSTRAINT open_spaces_pkey PRIMARY KEY(id1)) WITHOUT OIDS;
ALTER TABLE open_spaces OWNER TO yokoi;
select AddGeometryColumn(‘open_spaces', 'the_geom', 4326, 'POLYGON', 2);

where blue letters show the name of the created table. ‘4326’ denotes SRID forwgs84. This file is located in the directory ‘batch_sql’ on ‘D:’ drive (Install CD).

Note: If you want to make a Table of ‘POLYGON’ type with different name:Copy ‘D:/batch_sql/mkpolygon.sql’ into ‘C:/Your_Directory’,
Edit the copied file using Notepad or other editor, namely replace the blue letteredparts shown below with the new table name.
CREATE TABLE open_spaces (id1 integer NOT NULL, CONSTRAINT open_spaces_pkey PRIMARY KEY (id1)) WITHOUT
OIDS;
ALTER TABLE open_spaces OWNER TO yokoi;
select AddGeometryColumn(‘open_spaces', 'the_geom', 4326, 'POLYGON', 2);

SRID=4326 means wgs84 geodetic system. If you want to use other geodetic
and/or projection system, obtain the corresponding SRID. QGIS can provide thisinformation.
Start QGIS and click ‘Setting’. Then select ‘Project Properties’ and ‘Projection Tag’.
Look for your preferable projection system in the top panel using vertical slide bar.
Then, check ‘PostGIS SRID:’6Confirm the creation of these three tables using ‘¥d’ command.

Add columns to Tabkes of PostgreSQL Database using QGIS
Account=Administrator

User Account=postgres
PostgreSQL DataBase Server 8.1
Internal DataBase
Superuser=postgres

Account=user
PostgreSQL DataBase Client

DataBase=“TEST”
Table=“buildings”

QGIS
Add Columns to Table

Table2

Table?

On MicroSoft Windows7

2.2 Adding columns to tables ofPostgreSQL through PostGIS
connection from QGIS

1. Connecting vector layers (QGIS) to Tables (PostgreSQL)
Double click on ‘Quantum GIS’ icon.
Logo of Quantum GIS appears.

Then, Quantum GIS 0.9.1 starts.

8

Double click on ‘Add a PosGIS Layer’.

‘Add PostGIS Tables’ dialog opens.
Click on ‘New’.

‘Create a New PostGIS connection’ dialog opens.
Type in:Name (of connection): ‘connect to valley’ (arbitrary),
Host: ‘localhost’ (fixed),
DataBase: ‘valley’ (target database),
Port: ‘5432’ (fixed),
Username: ‘yokoi’ (User of PostgreSQL)
Password: ‘yokoi’ (Password of above user).
Then, click on ‘Test Connect’.

9Click ‘OK’ if successfully connected.

Then, click on ‘OK’.

Click ‘Connect’.

Then, three tables appear here.

10Select all these three tables andclick on ‘Add’.

11Add Columns to Table ‘buildings’ (‘POINT’ type)(Example:GESI Method)
Column Name

Data TypePrimary Key

Remarks

id1

Int4oIndex of buildings

Bulding_Type

Int4

Quality_Design

Int4

Quality _Construction Int4
Quality_Materials

Int4

Vulnerability_Rate

Int4

Vulnerability_Type

Varchar(30)Damage_GradeInt4

the_geomgeometry

Geometry column

Select ‘buildings’ layer.

12Click on ‘Open Table’ button.

‘Attribute Table’ dialog opens.

Click on ‘Start Editing’ button.

Click on ‘New Column’ button.

‘Add Attribute’ dialog opens.
Type in Name ‘building_type’.
Select Type ‘Int4’.
Then, click on ‘OK’.

13

New column ‘building_type’ is added in the table.

Repeat the same procedure for all other columns to be added.
Then, click on ‘Stop Editing’ button.

Then, save the changes by clicking on ‘Save’.

Then, click ‘Close’.

14Add Columns to Table ‘roads’ (‘LINESTRING’ type)
Column Name

Data TypePrimary Key

Remarks

GidInt4oIndex of roads

Road_Category

Int4

Pavement

Int4

Road_Name

Varchar(30)Notetext

the_geomgeometry

Geometry column

Set table columns for ‘roads’ by the same procedure as ‘buildings’ case.

15

Registering attributes of open space from Basemap or its
alternative (Example for polygon data)
Column Name

Data Type Primary KeyRemarks

GidInt4

Index of roads

oOpen_Space_Category Int4Manager

text

Owner

text

Open_Space_Name

text

Notetext

the_geomgeometry

Geometry column

Set table columns for ‘open_spaces’ by the same procedure as ‘roads’ case.

16

Confirm the additionof the columns using‘¥d buildings’
command.

Confirm the additionof the columns using‘¥d roads’ command.

Confirm the additionof the columns using‘¥d open_spaces’
command.

Unfortunately, connections are not stored in ‘Project’.
Exit from QGIS by ‘Files’ and ‘Exit’.
Exit from ‘psql Command Prompt’ using ‘¥q’ and ‘exit’.
Note: Addition of columns to tables of PostgreSQL can be performed using PostgreSQL
command “alter table”, also.
alter table mytable add column mycolumn mytype;where mytable: Name of the target table,
mycolumn: Name of the new column,
mytype: Data Type of the new column (char, Int4, float, double_precision, etc..

17