Tugas Basis Data Spasial 631o72

Applied Spatial Database Preparation of Spatial Database in PostgreSQL Import Shapefile into Spatial Databases Spatial Data Query Result's Visualization in Quantum GIS

created by : Rd Achmad Faizal P S (11/319127/TK/38258)

Department of Geodetic Engineering Faculty of Engineering Gadjah Mada University

I.

Objective -

Students are understand the concept of spatial database

-

Students can create a spatial database by importing shapefiles into spatial data tables that have an element of data geometry

II.

-

Students can convert WKB into WKT

-

Students can create a query related to the Data Definition Language (DDL)

-

Student can work with spatial query

-

Students can displaying the results on Quantum GIS

Equipment Needed -

Laptop hardware

-

PostgreSQL 9.3 software installed (with PostGIS 2.0 extension)

-

Quantum GIS software 2.4 installed

-

Notepad ++ (optional)

-

Shapefile Dataset (data source = http://planning.hawaii.gov/gis/ ) : 1. 2. 3. 4. 5.

haw_centerlines.shp (polyline) hawcntrs100.shp (polyline) pubschool.shp (point) skilled_nursing.shp (point) tsunevac.shp (polygon)

III. Practice 1. Create an spatial databases : The first thing we have to do is create a databases. It can be done in two ways, such as by using SQL query manually or by using PostgreSQL’s GUI. -

By SQL Query: On SQL editor (execute arbitrary SQL queries),

write down an SQL’s script like this: create database “databases name” ;

then execute it by clicking (

) icon.

-

By PostgreSQL GUI: On Databases, right click  New Databases, there will be an entries display, write the name of databases on Name’s field (for example bds_faizal), then click OK.

The result is there will be a new database named bds_faizal, This database isn’t yet based on spatial database. To make it become a database that ing spatial data, we have to integrating it with PostGIS extension. There are two ways: -

By right click on extension belong to working database (bds_faizal)  New Extension, then choose postgis on the name’s field, then click OK.

-

By open query file manually, on Query display (execute arbitrary SQL queries icon)  file  open, then open file postgis.sql located on C:\ProgramFiles\PostgreSQL\9.3\share\contrib\postgis-2.1\postgis.sql

Then, execute it.

Do the same thing in the same directory location for spatial_ref_sys.sql. C:\ProgramFiles\PostgreSQL\9.3\share\contrib\postgis-2.1\spatial_ref_sys.sql

We can open that sql file (postgis.sql and spatial_ref_sys.sql) by notepad++, then copy/paste it on SQL editor, it’s more efficiently I thought since we copying those file to our familiar directory. The reason why I explain the second ways (By open query file manually) above is to understand how the spatial databases extension works. PostGIS extension can ing spatial data because they provide geography_columns, geometry_columns and spatial_ref_sys table as a spatial environment to work with spatial database concept (look at picture below). -

Geometry_columns provide columns that able to add geometry data type. This is the display of geometry_columns (showed using SQL script): select * from geometry_columns;

*There are the different between PostGIS 2.0 and the late version, Geometry_columns replaces on table, but at 2.0, it replaces at views as a single column.

-

Geography_columns has similar function as geometry_columns. This is the display of Geography_columns (showed using SQL script): select * from geography_columns;

What is best to use (between geometry_columns and geography_columns)? http://gis.stackexchange.com/questions/26082/what-is-the-difference-between-geometric-andgeographic-columns : "When choosing between the geometry and geography type for data storage, you should consider what you’ll be using it for. If all you do are simple measurements and relationship checks on your data, and your data covers a fairly large area, then most likely you’ll be better off storing your data using the new geography type. Although the new geography data type can cover the globe, the geometry type is far from obsolete. The geometry type has a much richer set of functions than geography, relationship checks are generally faster, and it has wider currently across desktop and web-mapping tools."

-

Spatial_ref_sys is PostGIS include and OGC compliant database table that lists over 3000 known spatial reference systems and details needed to transform/project between them. Spatial_ref_sys provide spatial referenced (SRID) that needed to convert shapefile (.dbf) to spatial databases. This is the display of spatial_ref_sys table (showed using SQL script): select * from spatial_ref_sys;

…3911 I suggested to use the first ways, because raster_columns and raster_overview aren’t automatically added, so there are some function that can’t be used. 2. Converting shapefile to spatial databases To convert shapefile to databases, there two ways. It can be converted by using PostGIS directly or by using QGIS. These are the following stape: -

By PostGIS 1. Open PostGIS desktop (PostGIS 2.0 shapefile and DBF loader exporter)

2. On Import mod, click add file, Choose shapefile that will be converted, then click open.

3. Click on view connection detils, determine the name, , server host, and databases that will be used

4. Determine SRID based on shapefile SRID that will be converted (we can see it at their .prj file)

5. Then click Import

-

By QuantumGIS 1. Open QuantumGIS Desktop

2. Click on Databases  Spit  Import Shapefiles to PostgreSQL

3. There will be spit dialog box, click new Then, there will be new entries display, determine name, host, port, databases, name, (these are related with our postgreSQL configuration). Test connection by click Test connect until there is a successful message. Then Ok.

4. Add shapefile that will be converted by click Add, and choose them.

5. After that, determine the SRID of the shapefile. On QGIS, we can only choose one reference system in one PostGIS connections, so make sure that all converted shapefile have the same coordinate system.

6. Choose connect and enter the postgreSQL , then click ok. Shapefiles will be imported to spatial databases automatically.

These are the result of converting shapefile to databases on PostgreSQL. There will be new table based on shapefile that we’ve been import. We can see the table by using SQL script (select * from “table name”) or by right click on the selected table name  View Data  View All Rows.

We are also possible to displaying databases integrating/connected with PostGIS on QuantumGIS by using DB Manager. There are three mod in there (info, table, preview)

2.

Info Mod

1.

3.

Table Mod

Preview Mod

And displaying it visually by using layers  add postgis layers or ctrl +shift+D. Make sure that QGIS and PostGIS databases are connected by choose connect and entry PoistgreSQL .

3. Case and Resolve - Case : Do queries to show : 1) Data Modification or the application of SQL as DDL 2) Query application for : a. Converting WKB to WKT b. Showing the data type c. Showing the geometric coordinate value d. Finding the closest distance between two point e. Choosing Features with specific attributes (displayed on QGIS canvas) f. Presenting the overlay between database’s tables with raster (displayed on QGIS canvas) 3) Implementing existing spatial databases for proximity analyst and overlay analyst using SQL syntax, then display it on QGIS canvas! -

Resolve : 1) A data definition language or data description language (DDL) is a syntax similar to a computer programming language for defining data structures, especially database schemas. There are some kind of modification that can be implemented, such as: - Add Columns - Remove Columns - Add Constraint - Remove Constraint - Change Default Values - Change Columns Data Type - Rename Columns - Rename Tables These are the explanation of DDL by SQL by using SQL editor on PostgreSQL, practically:

-

Add Columns : SQL command: ALTER TABLE “table name” ADD COLUMN “column name” data_type; In this case, I will add “ownership” field on skilled_nursing table. The SQL syntax will be like this:

So, there will be a new column named “ownership” like picture below:

-

Remove Columns SQL command: ALTER TABLE “table_name” DROP COLUMN “column name”; In this case, I will delete some of unused column from skilled_nursing table. The SQL syntax will be like this:

So, this is skilled_nursing table after execution, some of selected columns are removed:

- Add Constraint: SQL constraints are used to specify rules for the data in a table. The specify rules are NOT NULL, UNIQUE , PRIMARY KEY , FOREIGN KEY , CHECK SQL command will be depended on specify rules : ALTER TABLE “table_name” ADD [specify rules]; In this case, I’ll set set phone_no as Unique from skilled_nursing table. The SQL syntax will be like this:

So, the phone_no column will be unique and it can’t be duplicate value in the same field. - Remove Constraint: SQL command will be depended on specify rules : ALTER TABLE “table_name” DROP [specify rules];

In this case, I will remove the constraint that has been given before to phone_no field as a UNIQUE value. The SQL syntax will be like this:

After execution, the phone_no column is no longer set as unique constraint, so it is possible to be duplicated in the same field. -

Changing a Column's Data Type SQL command: ALTER TABLE “table_name” ALTER COLUMN “column name” TYPE data type; In this case, I will change ownership data type become varchar(15). The SQL syntax will be like this:

So, the data type of ownership column will be set become varchar(15), look at picture below:

-

Change Default Values SQL command: ALTER TABLE “table_name” ALTER COLUMN “column name” SET DEFAULT “default value”; In this case, I will set the default values of ownership as government. The SQL syntax will be like this:

So, the ownership column will be added ‘government’ as default value. This doesn't affect any existing rows in the table, it just changes the default for future INSERT commands. -

Rename Column SQL command: ALTER TABLE ‘Table_name’ RENAME COLUMN ‘old column name’ TO ‘new column name’; In this case, I will rename column become _name column. The SQL syntax will be like this:

So, if we see the picture below, field name will be change to _name.

-

Rename Table SQL command: ALTER TABLE “table_name” RENAME TO “new table name”; In this case, I will rename skilled_nursing table to be medical_center table. The SQL syntax will be like this :

So, the result is, skilled_nursing table will rename to medical_center table. Column and their value will not change.

2) These are the application of query to resolve some cases such as: a. Converting WKB to WKT Well-known text (WKT) is a text markup language for representing vector geometry objects on a map, spatial reference systems of spatial objects and transformations between spatial reference systems. A binary equivalent, known as well-known binary (WKB) is used to transfer and store the same information on databases, such as PostGIS, Microsoft SQL Server and DB2. The formats were originally defined by the Open Geospatial Consortium (OGC) and described in their Simple Feature Access and Coordinate Transformation Service specifications. These are the ways to convert WKB to WKT format by using SQL query: This is the basic SQL function related with WKB and WKT 1. bytea WKB = ST_AsBinary(geometry); 2. text WKT = ST_AsText(geometry); 3. geometry = ST_GeomFromWKB(bytea WKB, SRID); 4. geometry = ST_GeometryFromText(text WKT, SRID); For a single value , we can use the 3rd and 4th function for conversation between WKB and WKT. For Example (from single value WKT to WKB):

But, in this case I want to convert all WKB geometry values from one table (pubschools) into new table. The SQL query will be like this (assumed that geotable table has been made):

b.

Showing the data type We are already knew that the data type of geometry for example above is POINT, by looking at its statement.

Defined As POINT

These are another WKT form, for LINESTRING and POLYGON 2.

LINESTRING

1.

POLYGON

c. Showing the geometric coordinate values Geometric coordinate values are showed after data type. For the example above, we can directly know the geometric coordinates .

Defined As POINT’s geometric Coordinates

For advance and interoperability purposes , we can export those table into other ASCII format such as (.CSV), so the geometric coordinate are more easy to be defined. These are following steps to export it: -

On query window, select File  Export

-

Do setting as needs, then choose the file directory and name to be saved

This is the (.csv) format of table that has been exported, opened by notepad++ :

d. Finding the closest distance between two point To find the closest distance between two geospatial object (geometry data type), we can use the function below: ST_Distance(geometry1,geometry2) For Example :

The GEOMETRY type calculates meaningless Cartesian/planar distance between two geospatial object. If we use GEOGRAPHY type, so the distance will be calculated by using great circle/orthodromic distance. In this case, I want to calculate the closet distance between two location that is 2900 Pali Hwy hospital and Aliamanu Elementary School. So, the SQL query will be like this:

Base on the result of SQL queries, the closest distance between those locations is 7672.74513788208 meters (planar distance). Comparing with GIS software (ArcGIS) distance calculate function :

e. Choosing Features with specific attributes (displayed on QGIS canvas) To answer this question, I’ll use QGIS’s SQL interface because the result will be displayed directly on QGIS canvas. It means that we can do spatial query on QGIS with consequence that it must be connected with databases on PostGIS before. These are the step to do queries on QGIS: 1. Open QGIS 2. If we never make an databases connection and importing PostGIS databases before, so we made it first (just like the answer no. 1 above about showing PostGIS databases on QGIS). In this situation, assumed that I used a databases that have been made before as the answer of question no.1 (tugas_bds databases).

3. Select Databases  DB Manager  DB

4. It will be DB Manager windows, connecting it with PostGIS databases by clicking on refresh icon first, then choose PostGIS  databases to connected (tugas_bds)

5. Entries the PostgreSQL’s name and

6. Then the databases will be connected, after that, click SQL window Icon to begin spatial queries in QGIS .

7. Do queries to find specific attribute. In this case, I will do SQL query to find contours where the values (elevation) are located between 1000 until 8000 meter (msl). The SQL query will be like this :

Then select on Execute icon or F5 8. So the table as result of that query will be like this:

9. We can load it as a new layer, but before we load it, QGIS will ask for unique integer value as a primary key and it must be integer numbers. We can use gid column for it, but it wouldn’t be in sequence number because it will following its original values from hawcntrs100 ‘s table. As primary key that define the unique value of its data, we have to make it unique and sequential. So, as a solution, I’ll use SQL aggregate function (row_number) and it must be following by (over).

So the right SQL query and its answer will be like this:

10. To load it as a new layer, click on mark (load as new layer) below. Entries for column with unique integer values = ‘id’ and geometry column = ‘the_geom’. Then click Load now!

11. So, there will be new layer on QGIS canvas (see pictures below). This layer represented the query’s result that displayed on QGIS directly. Hawcntrs100’s contours have been filtered by query (wrote above) defined as elev, only showing the contour’s lines between 1000 and 8000 meter (msl). This is an example of SQL query implementation to find specifics attributes (elevation) that have been qualified.

1.

The visualization of ‘elev’ layer (as queries result)

2.

Comparison with hawcntrs100 (before querying )

f.

Presenting the overlay between database’s tables with raster (displayed on QGIS canvas) by using openLayer plugin. Quantum GIS provide many plugin to be installed and upgrade the QGIS performance to manage geospatial data. OpenLayer plugin is a QGIS plugin embedding OpenLayers functionality, by using this plugin, we can add raster data from servers or Web Map Service to be used directly on QGIS canvas and it depended by inserted shapefiles and its covering coordinates. Web Map Service (WMS) is OGC standard that provides a simple HTTP interface for requesting geo-ed map images from one or more distributed geospatial databases. A WMS request defines the geographic layer(s) and area of interest to be processed. These are the following steps: 1. Install OpenLayer Plugin -

Open QGIS  Plugin  Manage and install plugins..

-

On plugins display windows, there will be many plugins provided by developers. Searching for openLayer plugin, and install Plugin.

-

After the installation, we can use OpenLayer plugin by select on web toolbar then OpenLayer plugin and displaying map from web map service provided 2.

2. Insert spatial databases into layer canvas In this case, I will displaying the overlay between haw_centerlines databases with raster data provided by map service such as Bing Map, MapQuest, Google map, Open Street Map, and Apple map - On DB Manager’s SQL Windows. Write down the SQL syntax below, it will minimalize the unused column, and load as new layer.

-

So, it will be like this

3. Then, add raster map layer from OpenLayer plugins. - Select on web toolbar  Openlayer Plugins  Then choose layer based on its provider (for example, bing map  bing aerial). These are the example of overlaying between database table and raster from many map service provider: 

From Bing Map (Bing Aerial)



From Google Map (Google Satellite)



MapQuest ( MapQuest Open Aerial)

The best result is the raster map provided by Bing Map, the map resolution is still good and the raster’s tiles are still provided well until high level of zooming.

3) Implementing existing spatial databases for proximity analyst and overlay analyst using SQL syntax, then display it on QGIS canvas! - Proximity Analyst : Proximity Analysis is an analytical technique used to determine the relationship between a selected spatial object and its neighbors. 1. ST_Buffer In this case, I’ll use ST_buffer to make a buffer area with specified distance (100 meter) around the roads from haw_centerlines. The SQL syntax and the result will be like this:

The Buffer area (100 meter) around haw_centerlines

2. ST_Dwithin In this case, I’ll use ST_DWithin to find the medical_center that are in within 100 meter around road (haw_centerline). The SQL syntax and its results will be like this:

The medical_center that are within 100 meter around haw_centerlines It also can be proved by displaying 100 m buffer area.

-

Overlay Analyst : Overlay Analyst is an analytical technique used to identifying the overlap between two or more selected spatial object. 1. ST_Contains In this case, I will find every public schools (pubschools) that are inside the tsunami evacuation zone (tsunevac). The SQL syntax and the result will be like this:

Every pubschools inside tsunami evacuation zone, it also can be proved by displaying tusnevac as comparison

2. ST_Intersects In this case, I will identifying every road (haw_centerlines) and public schools (pubschools) intersect with tsunami evacuation zone with MAPNUM = 1. The SQL syntax and its result will be like this:

Every pubschools and haw_centerlines intersected with tsunavec with mapnum = 2 (yellow area)

IV.

References

http://postgis.net/docs/manual-2.1/ http://workshops.boundlessgeo.com/postgis-intro/ http://www.postgresql.org/docs/8.4/static/ http://gis.stackexchange.com/