5 Extract the information of locationfrom the geometry column ofPostgreSQL table

Open QGIS and load PostGIS layer “buildings” and the raster layer “Tai_wide_G”(optional just to show the basemap).

1

Click on “Identify Feature” button. Then click on one of the mark on “buildings” layer.

The information of location is shown. However, these values are not independently
stored in table but included in “the_geom” column.

Add columns for latitude and longitude to PostGIS layer.
Click on “Open Table” button.

“Attribute Table” opens. Then click on “Start Editing” button.

2

Click on “New Column” button.

“Add Atribute” dialog opens.
Type in Name “latitude” with Type “double precision.
Click on “OK”.

Click on “New Column” button of “Attribute Table” again.

“Add Atribute” dialog opens.
Type in Name “longitude” with Type “double precision.
Click on “OK”.

3

Use the horizontal slide bar to verify the addition of two new columns“latitude” and “longitude”.

Click “Stop Editing” button.
Click on “Save”.
Then click on “Close” button of “Attribute Table”.

Store the values of latitude and longitude that are extracted from “the_geom” column.
Open “Command Prompt” of PostgreSQL.
Enter the target database:
psql valley –U yokoi
Type in the password.

Extract latitude and longitude from “the_geom” column and register them into
“latitude” and “longitude” columns.
update buildings set lat=y(the_geom);
update buildings set lon=x(the_geom);4Confirm the data creation:
select id1,latitude,longitude from buildings;
Information of location is extracted from “the_geom” column and stored.
Leave from the database using “¥q”.
Close “command prompt” using “exit”.

Click on “identify feature” button and then on a mark on “buldings’layer.
Confirm the coincidence of the location in the feature list and theindicator at right bottom.

5Note: The above slides explain the way to extract (latitude, longitude) of POINT
data. It is possible to obtain the values of other coordinates. For example, thefollowing commands provide the values of UTM projection.
update buildings set x_utm=x(transform(the_geom,32645));
update buildings set y_utm=y(transform(the_geom,32645));
where “x_utm” and “y_utm” are the name of the columns that are added to thetarget table beforehand, “32645” denotes EPSG SRID code for PostGIS thatcooresponds to UTM zone 45N. The information about EPSG SRID can be shownin QGIS. “Setting” - ”Project Properties”-”Projection” tag, then ”Projected
Coordinate System” in “Spatial Reference System”, then look for your projection
system.

POINT data has its own location (latitude, longitude), whereas LINESTRING data
and POLYGON data has many pairs of (latitude, longitude) that correspond to theirnodes. These can not be stored in columns of fixed number.
Here, the information of coordinates for LINESTRING data and POLYGON data isstored in a column as a text string. It is guessed that these types are exported andimported among GIS software as files of vector layer format (e. g. Shape file) andthat rarely the coordinates of their nodes are needed.

6Select “roads” a layer of LINESTRING type.
When clicked on QGIS using “identify feature” button, the coordinates of the clicked
point is shown.

Add columns for latitude and longitude to PostGIS layer.
Click on “Open Table” button.

“Attribute Table” opens. Then click on “Start Editing” button.
Then, Click on “New Column”button.

7

“Add Atribute” dialog opens.
Type in Name “latlon” with Type “text”.
Click on “OK”.

Use the horizontal slide bar to verify the addition of two new columns“latitude” and “longitude”.

Click “StopEditing” button.

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

Store the series of (latitude, longitude) that are extracted from “the_geom” column.
Open “Command Prompt” of PostgreSQL.
Enter the target database:
psql valley –U yokoi
Type in the password.

Extract the series of pairs (latitude, longitude) from “the_geom” column andregister them into “latlon” column as a text string.
update roads set latlon=astext(the_geom);8Confirm the data creation:
select id1,latlon from roads;
Information of location of the nodes is extracted from “the_geom” column andstored as a text string in the order of longitude and latitude.
Leave from the database using “¥q”.
Close “command prompt” using “exit”.

Select “open_spaces” a layer of POLYGON type.
When clicked on QGIS using “identify feature” button, the coordinates of the clicked
point is shown.

9Add columns for latitude and longitude to PostGIS layer.
Click on “Open Table” button.

“Attribute Table” opens. Then click on “Start Editing” button.
Then, Click on “New Column”button.

“Add Atribute” dialog opens.
Type in Name “latlon” with Type “text”.
Click on “OK”.

Use the horizontal slide bar to verify the addition of two new columns“latitude” and “longitude”.
Click “StopEditing” button.

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

10Store the series of (latitude, longitude) that are extracted from “the_geom” column.
Open “Command Prompt” of PostgreSQL.
Enter the target database:
psql valley –U yokoi
Type in the password.

Extract the series of pairs (latitude, longitude) from “the_geom” column andregister them into “latlon” column as a text string.
update open_spaces set latlon=astext(the_geom);

Confirm the data creation:
select id1,latlon from open_spaces;
Information of location of the nodes is extracted from “the_geom” column andstored as a text string in the order of longitude and latitude.
Leave from the database using “¥q”.
Close “command prompt” using “exit”.

11