4 Importing/Exporting Vector Layer
4.1 Convert Map Info Tab to Shape file using FWTOOLS
4.2 Import Shape file into PostgreSQL using SPIT Plugin of QGIS andExport PostgreSQL table to a shape file
4.3 Import Shape file into QGIS
4.4 Load table of PostgreSQL created by the imported Shape file on QGIS
4.5 Convert Map Info Tab, ArcInfo Coverage or other vector formats toShape file
4.6 Import PostGIS layer to GRASS and export GRASS vector map to
PostGIS
4.7 Retrieve table of PostgreSQL to Excel using ODBC and PostgreSQL
command
4.8 Import Excel worksheet to PostgreSQL

4.1 Convert Map Info Tab to
Shape file using FWTOOLS

1Examples of Map Info Tab files in ‘C:¥Cygwin¥home¥yokoi¥Kathmandu_Valley.
In this directory ‘Kathmandu_Valley’ there are three layers:
surface_kv
surface_nkv
surface_sg

Click on ‘FWTools Shell’ icon.

‘FWTools Shell’ command prompt starts.
Change directory to ‘C:¥Cygwin¥home¥yokoi’.
cd C:¥Cygwin¥home¥yokoi

2List Map Info Tab layers in ‘Kathmandu_Valley’ directry.
ogrinfo Kathmandu_Valley
there are three layers:
1: surface_kv
2: surface_nkv
3: surface_sg

Convert a layer of Map Info Tab to Shape file using ‘ogr2ogr’ command.
Shape file name for output

Directory name of input data

Layer name of input dataogr2ogr -f "ESRI Shapefile" surface_kv.shp Kathmandu_Valley surface_kv

Shape files are created3Convert other two layers to Shape file.

ogr2ogr -f "ESRI Shapefile" surface_nkv.shp Kathmandu_Valley surface_nkv
ogr2ogr -f "ESRI Shapefile" surface_sg.shp Kathmandu_Valley surface_sg‘Exit’ after completion of tasks.

4.2 Importing Shape file into
PostgreSQL using SPIT Plugin of
QGIS

4Double click on ‘Quantum GIS’ icon.
Logo of Quantum GIS appears.

Then, Quantum GIS 0.9.1 starts.

‘Plugin’ and ‘Plugin Manager’.

Put check box of ‘SPIT’ on
in ‘QGIS Plugin Manager’
dialog.
Then, click on ‘OK’.

5

‘Plugin’ , ‘Plugin Manager’, ‘SPIT’and ‘Import Shapefiles to PostGIS’.

Don’t worry about this message.
Click on ‘OK’.

Select existing connection name if you
have and ‘Edit’ or create it by clicking on‘New’.
Then, click on ‘Add’.

Select target Shape file and click on ‘Open’.

The selected file name appears.
Then, click ‘OK’.

6

The selected Shape file is imported into
PostgreSQL table.

Refer “4.4 Load table of PostgreSQL created by the imported Shape file on
QGIS” for the way to load the PostGIS layer created by the imported Shape file.

There is an alternative way to import Shape file into PostGIS using shp2pqsql
command. This command creates an interim file that is sql batch file, namely, thisincludes a series of PostgreSQL commands.
You can add more detailed control to this batch file if you have sufficientknowledge and techniques for modifying sql batch file.

7

Start PostgreSQL command Prompt.
‘Start’, ‘All Progrmas’, ‘PostgreSQL 8.2’
and ‘Command Prompt’.

Set a temporal environmental parameter DDir.
set DDir=c:¥cygwin¥home¥yokoi
Create the interim file ‘sqlsur_kv’ using ‘shp2pgsql’ command of PostgreSQL.
shp2pgsql –I %Ddir%¥surface_kv.shp sur_kv >%DDir%¥sqlsur_kv
Then, ‘Exit’.
The interim file ‘sqlsur_kv’ is created in ‘c:¥cygwin¥home¥yokoi’ directory.

Use ‘-s 4326’ option of ‘shp2pgsql’ command if it is necessary to specify the SRIDof spatial reference system (4326 corresponds to wgs84).

8Note: This interim file ‘sqlsur_kv’ is a batch file of PostgreSQL that includes aseries of PostgreSQL commands to create a new table in PostgreSQL and store
the data. ‘sqlsur_kv’ is an ASCII text file that can be browsed using, for example,
WordPad.

Set environmental parameters:
Username
Password
Database

set PGUSER=yokoi
set PGPASSWORD=yokoi
set PGDATABASE=valley

Execute the batch file ‘sqlsur_kv’ in ‘c:¥cygwin¥home¥yokoi’
psql –f %DDir%¥sqlsur_kv

sqlsur_kv is successfully imported to PostgreSQL

9

Confirm creation of new table ‘sqlsur_kv’ in Database ‘valley’.
Connect to Database valley
psql –U yokoi
Browse table list¥d
Check the table ‘sqlsur_kv’.
Then, ‘¥q’ and ‘exit’.

Export a table of PostgreSQL to a shape file using the command “pgsql2shp”.
Open “Command Prompt” of PostgreSQL.
Then use the command “pgsql2shp”.
pgsql2shp mydatabase mytable –f myshpname –u myusername–P mypassword –g mygeometryfield
where

mydatabase: Name of the database that contains the table to be exported,
mytable: Name of the table to be exported,
myshpname: Shape file name for output without .shp extension,
myusername: Username of the database,mypassword: Password of the database,mygeometryfield: Geometry column to be exported (can be skipped).

104.3 Import Shape file into QGIS

Double click on ‘Quantum GIS’ icon.
Logo of Quantum GIS appears.

Then, Quantum GIS 0.9.1 starts.

11‘Setting’ and ‘Project Properties’.

In ‘Project Properties’ dialog, select‘Projection’ tag and put the checkbox of ‘Enable on the fly projection’on. Then, click on ‘OK’.

Click on ‘Add a Vector Layer’ button.

Select ‘surface_kv’ in ‘c:¥cygwin¥home¥yokoi’ and click on ‘Open’.

12‘surface_kv’ is loaded on QGIS.

Click right button on ‘surface_kv’, then left button on Properties.

13

Click ‘General’ Tag

Check Spatial Reference System

Select ‘Continuous Color’.

Select Classification field ‘Sur’.
Select color for the minimum value.

Select color for the maximum value.

Then, click on ‘Apply’ and ‘OK’.

14

Close QGIS using ‘File’ and ‘Exit’.

4.4 Load table of PostgreSQL
created by the imported
Shape file on QGIS

15

Double click on ‘Quantum GIS’ icon.
Logo of Quantum GIS appears.

Then, Quantum GIS 0.9.1 starts.

Click on ‘Add a PostGIS Layer’ button.

Click on ‘New’ in ‘Add PostGIS’ dialog.

Type in the necessary information:
Name:
Connection to valley (Arbitrary)Host:
localhost (Fixed)
Database: valley (For this example)
Port:5432 (Fixed)
Username: yokoi (For this example)
Password: yokoi (hidden; For this example)
Then, click on ‘OK’.

16

Click on ‘Connect’.
All the tables in ‘valley’ are displayed.

Select ‘”public”, “surface_kv’’ (the geom)
and click on Add.

‘surface_kv’ vector layer is loaded.

17‘Setting’ and ‘Project Properties’.

In ‘Project Properties’ dialog, select‘Projection’ tag and put the checkbox of ‘Enable on the fly projection’on. Then, click on ‘OK’.

Click right button on ‘surface_kv’, then left button on Properties.

18

Click ‘General’ Tag

Check Spatial Reference System

Select ‘Continuous Color’.

Select Classification field ‘Sur’.
Select color for the minimum value.

Select color for the maximum value.

Then, click on ‘Apply’ and ‘OK’.

19

Close QGIS using ‘File’ and ‘Exit’.

4.5 Convert Map Info Tab, ArcInfo
Coverage or other vector formats toShape file20

GRASS can import and export vector data of various formats. Map Info Tab,
ArcInfo Coverage and Shape among them. (Refer A2_Supproted_Data_formats).
The way of conversion is:+ Create Location-Mapset of GRASS adequately for the target vector data,+ Start GRASS with this Location-Mapset,+ Import the target vector data into a vector layer of GRASS ,+ Export this layer to Shape file.
Here, two examples are shown. One Map Info Tab, another ArcInfo Covarage.
Both are of latitude-longitude coordinates of wgs84.

First, GRASS is started with Location=lat-long(wgs84), Mapset=user and theregion setting is arranged for that whole earth is covered.

21

Convert Map Info Tab to Shape:‘File’, ‘Import’, ‘Vector map’ and ‘Various formats using OGR’.

Type in the Map Info Tab file ( .tab) name with its path and output vector map name.
Then, click on ‘Run’.

Start importing. Thistakes much time.

Completed.

22

Export the vector layer ‘world_border’to s shape file using ‘File’, ‘Export’,‘Vector map’ and ‘Various formats
using OGR’.

On ‘v.out.ogr’ dialog, set as
follows:
Name of Input Vector map
world_border
Feature Typecentroid, line, boundary
OGR output datasource
world_borders_shp
OGR layer nameworld_borders
and leave others as default.
These parameters should beselected case by case.

Start exporting.

Completed.

Note: GRASS automatically an additional attribute ‘CAT’ at importing a vector datafor its inner usage. If imported vector data has the same attribute name, a batting
error takes place and it is necessary to change the attribute name at importing to
GRASS. ‘v.in.ogr’ dialog provides this functionality. QGIS provides the way to list
the attribute used in vector data for some formats including Map Info Tab.

23

Check the attributes used in Map Info
Tab data.
Start QGIS and load the target Map
Info Tab data.
Open ‘Layer Properties’ dialog byclicking the target layer in Legend.
Then, select ‘Metadata’ tag.
The attributes are listed as shown left.
In this example, attribute name ‘CAT’ is
not used.

CAT

If the attribute name ‘CAT’ isused in the vector data, it isnecessary to use thefunctionality of ‘List of column
names…’ of ‘v.in.ogr’ dialog.
At the beginning ‘CAT’ usedby GRASS should be written
and then the attribute namesof the vector data are listed.

Used by GRASS

24

Directory ‘world_borders_shp’ is created.

Shape file ‘world_borders.shp’ is created in
it with its associate files.

Start QGIS to check the created shape file.
Click on ‘Add a Vector Layer’ button.

Select the created shape file‘world_boders.shp’ in the directory‘c:¥cygwin¥home¥yokoi¥world_borders_shp’.

‘world_boders.shp’ is loaded and displayed.
‘File’ and ‘Exit’.

25

Convert ArcInfo Covarege to Shape:
ArcInfo Coverage is converted to Shape file using ‘ogr2ogr’.

Start FWTOOLS by clicking on this icon.

List the attributes using ‘orginfo’ command.
orginfo c:¥cygwin¥home¥yokoi¥Administration_boundary
where the data are stored in the directory ‘Administration_boundary’ under‘c:¥cygwin¥home¥yokoi’. Five attributes (columns) are used, i. e., ARC, CNT,
LAB, PAL, TXT.

Conversion:ogr2ogr –f “ESRI Shapefile”
c:¥cygwin¥home¥yokoi¥Administration_boundary.shpc:¥cygwin¥home¥yokoi¥Administration_boundary PAL
Input data is stored in ‘c:¥cygwin¥home¥yokoi¥Administration_boundary ‘. ‘PAL’
file among them is used for conversion. Output is ‘Administration_boundary.shp’.
In ‘c:¥cygwin¥home¥yokoi’ as shown below.

Exit from FWTools.

26

Confirm by loading the output Shape file in QGIS.

4.6 Import PostGIS layer to GRASS
and export GRASS vector map to
PostGIS27

There are three tables in the database ‘valley’ in PostgreSQL, namely, ‘buildings’,‘roads’ and ‘open_spaces’ that are created in ‘2_Creating_Vector_Layers’ andedited in ‘3_Input_Data_To_Vector_Layers’. These are used for examples.
These are digitized in Basemap that are exported from Location=lat-long(wgs84)
and Mapset=Tsukuba.
Import them from PostgreSQL to the same Location-Mapset. Start GRASS.

‘File’, ‘Import’, ‘Vector map’ and ‘Various formats using GDAL’.

‘v.in.ogr’ dialog opens.
Type in the information of
database and table at ‘OGR
datasource name’.
PG:host=localhost
dbname=valley user=yokoi
password=yokoi
Name for output vector map
Roads
Leave others as default.
Click on ‘Run’.

28

Importing data from PostgreSQL.
The imported vector layer can be checked bydrawing it in GRASS or from QGIS.

Copy the imported vector layer‘roads’ to ‘roads_new’.
‘File’, ‘Manage maps andVolumes’ and ‘Copy maps’.

‘g.copy’ dialog opens.

Fill here (from,to)29

Export GRASS vector layer to PostgreSQL table.

‘File’, ‘Export’, ‘Vector map’ and‘Various formats using OGR’.

‘v.out.ogr’ dialog opens.
Type in the information of
database and table at ‘Name of
input vector map’.
roads_new
Feature Typeline
OGR output datasource
PG:host=localhost
dbname=valley user=yokoi
password=yokoi
OGR layer nameRoads_new
OGR format
PostgreSQL (Selection)
Leave others as default.
Click on ‘Run’.

30

Start exporting.

Completed.

The exported PostgreSQL table (PostGIS layer) can be checked from QGIS.

4.7 Retrieve table of PostgreSQL to Excel

31

Retrieve table of PostgreSQL to Excel through ODBC.

Change ODBC Setting first:
Open “Control Panel” and click on “Administrative
Tools”.

Click on “Data Sources (ODBC)“ODBC Data Source Administrator” opens.
Select “PostgreSQL Unicode” and click on“Configure”.

Change “Database”, “User Name” and“Password” for the target Database. For
this example:
Database= valley
User Name= yokoi
Password= yokoi
Click on “Save”. Then click on “OK”.

32

Open Excel, Select “Data”-”Import External Data”-”New Database Query”“Choose Data Source” dialog
appears.
Select “PostgreSQL Unicode” andclick on “OK”.

“Query Wizard –Choose Columns”
opens. Select the target column.
For this example “buildings”.
Click on “>”33The columns of “buildings” areshown in the right.
Click on “Next” .

“Query Wizard –Filter Data” opens.
It is possible to specify the filter toselect rows if necessary. Click“Next” if any filter is not necessary.

“Query Wizard –Sort Order” opens.
It is possible to specify the order
how to the data. Click “Next” if any
sorting is not necessary.

“Query Wizard –Finish” opens.
Select “Return Data to Microsoft
Excel”.
Click “Finish”.

34

“Import Data” dialog opens.
It is possible to return the data in a New
worksheet or Existing work sheet. For thelatter case it is possible to specify the topleft cell where the returned data are stored.
Click “OK”.

The returned data (table“buildings”) are stored in theselected worksheet.
Never modify the column“the_geom”. This column has
the information of geometryof the vector layer.

Export table of PostgreSQL to a CSV format file (An alternative
way).
Open “Command Prompt” of
PostgreSQL.
Enter the target Database:
psql valley –U yokoi

Then, enter the password.

35

Use “¥copy” command to export the table to a csv format file:¥copy table_name to c:¥Path¥table_name.csv with csv header

Output CSV file name with path
Table name in database“buildings.csv” is created in “c:¥TEMP”.

Note: In CSV format file each rows of the PostgreSQL table stored in lines anddata are delimited by “,”. CSV format file can be opened by Excel.

36

Never modify the column“the_geom”. This column has
the information of geometryof the vector layer.

4.8 Import Excel worksheet to PostgreSQL

37

A way to store Excel worksheet to PostgreSQL ( Use CSV file as interim product).
Store the target Excel worksheet into a csv format file using “File” and “Save as”.
Open “Command Prompt” of PostgreSQL.
Enter the target Database:
psql valley –U yokoi
It is necessary to set a table to that the data are imported from a shape file.
Refer 8_Import_csv_file_&_add_geometry.ppt for detail.
Then import the csv format file:¥copy table_name from c:¥temp¥table_name.csv with csv header
input CSV file name with path
Table name in database38