Gis Database2

GIS Database Development

Dr Abdullah Hisam Omar

Databases Before the Use of Computers Data kept in books, ledgers, card files, folders, and file cabinets
Use pin maps
Long response time
Labor-intensive
Often incomplete or inaccurate


Common Problems with Using Files
Uncontrolled duplication – Wastes space – Hard to update file all files







Inconsistent data Inflexibility Limited data sharing Poor enforcement of standards Poor programmer productivity Excessive program maintenance

Database Management System All Data

DBMS

Program 1

Queries and Reports

Program 2

Why do we need a DBMS? The user does not need to know the specific structure of the data in the database;
Different users can have their own “view” of the database to suit their own needs;


Why do we need a DBMS? (2) The DBMS will allow interfaces to be created in a variety of programming languages;
You can customize the database so that regularly used data can be retrieved faster;
Security and Statistics.


The Database Approach


Database-management system (DBMS) – Interact with the data in databases







Entity: something you collect data about Field: one characteristic of an entity Record: collection of fields that describe one occurrence of an entity Entities stored in tables – One record per row – One field per column

7

Components of a DBMS









Database engine Data dictionary Query processor Report writer Forms generator Application generator Communication Security

Sample Data Table

The Database Approach


DBMS replaced file processing approach – Reduced data redundancy – Reduced program dependence

Databases typically consist of several tables
Tables can be linked together


Advantages of a DBMS









Minimal data redundancy Data consistency Integration of data Sharing of data Enforcement of standards Ease of application development Uniform security, privacy, and integrity Data independence

Database operations Adding data,
Deleting data,
Amending data.
(data are records or files... more on this later).


Data Structure


Data model – A representation of the entities and their

relationships


Primary key – An attribute or combination of attributes – Uniquely identifies each record

Data Type


Each field is assigned a type – Text, number, date, etc.




Data types help the DBMS – Organize and sort the data – Do calculations – Allocate space




Data dictionary – A repository of information about the data – Key fields, data types, valid values, etc. 14

Database Management Systems Approaches


Models of the relationship between entities in a database – Hierarchical – Network – Relational – Object -Oriented

The Hierarchical Model

The Network Model

Database Fundamentals - Object-Oriented Model


Object-Oriented Paradigm – Programming Languages – Analysis and Design Methodologies – Databases and DBMS




O-O Advantages – More modeling power – Additional constructs – Better transition to implementation models

Database Fundamentals - Object-Oriented Model


O-O Applications – CAD, Office Information Systems, CASE

tools, GIS – Structurally complex information, specialized graphics, non-standard transactions

Database Fundamentals - Object-Oriented Model


O-O DBMS – Standard DBMS capabilities






Scheme management Query language and optimization Storage and access management Transaction management Persistence

Database Fundamentals - Object-Oriented Model


Summary – The O-O model is more flexible for

representing natural (dynamic) objects in the real world than the E-R model – The O-O model provides less of an impedance mismatch


Allows the conceptual data model and the user’s view of the application domain to be closer to the logical model and the system implementation

Database software...


Light Duty




Medium Duty




Heavy Duty

Database Tables

Database

Table1

Table2

Table3

Structure Column 1 (Field 1) Row 1 (Record 1) Row 2 (Record 2)

Value

Column 2 (Field 2)

The Relational Model







Views entities as two-dimensional tables – Records are rows – Attributes are columns Tables can be linked Supports one-to-many, many-to-many, and one-to-one relationships Entity: “Thing” in the real world with an independent existence. - An entity-an object with a physical existence-particular person, car, house or employee - An entity- an object with a conceptual existence – a company, a job, or a university course - Each entity has particular properties called attributes

Entity

Entity Name= Abu Bakar

Employee

Address= UTM Age= 55

Owner= Bakar Talib

Lot

Address= Jln 1, Tmn Pulai Age= 45

Phone= 07-2555555

Attribute

Attribute

Phone= 07-3366333

The Relational Model

ER-DIAGRAM : example Jen_Gunaan

Na_Jalan Lebar Id

Jen_Bangunan Na_Jalan

LOT Polygon G

1

Risiko

Panjang

bersebelahan

JALAN Line G T

T

Laluan

Kelas

No_Rumah

bersebelahan bersebelahan berdekatan

m

ID

No_PB

PARIT Polygon G T

Lokasi

Tekanan

PILI BOMBA Point G T

Na_Jln

Status Zon

E-R Diagram

Trkh_Servis

Taraf

Schema The structure of a database described in a formal language.
Generally stored in the data dictionary.
The term is often used to refer to a graphical depiction of the database structure.
In a RDBMS, the schema defines the tables, the fields in each table, and the relationships between fields and tables.


Example..Schema

PENGUNDI No_KP

Taraf

Nama

Kawasan

Kerja

Kerja

Gaji

Tempat

Status

Bank

Cawangan

No_Kp

ID_Sec

PEKERJAAN No_KP

AKAUN No_Akaun

Fields and Records Field: An element of a table that contains a specific item of information;
Record: A collection of data about a person, an event, etc.


First Name Last Name 1

Amanda

Huginkiss

2

I.P.

Freely

3

Al

Coholic

4

Ivona

Tinkie

RDBMS (Relational DBMS)


a type of database management system(DBMS) that stores data in the form of related tables.

Why RDBMS? Relational databases are powerful because they require few assumptions about how data is related or how it will be extracted from the database.
As a result, the same database can be viewed in many different ways.
A single database can be spread across several tables.


Relational Database

Relations

Keys

• Candidate Keys • Primary Keys • Alternate Keys

Foreign Keys A foreign key on relation A is a primary key on relation B.

What are attribute data? “Positional data are the ‘where things are’ data and attribute data the ‘what things are’ ”.




Data about our world are being produced continuously. – Satellites – automatic environmental monitoring – automated business transactions – everyday activities; purchasing, banking – research and surveys

Attribute Data Types used in GIS Data types used in a GIS include: • character strings (Idaho, Moscow) • integers (4387, -92) • floating points (56.23, 0.03) • dates (10012001) • time (0930, 1458)

Database Implementation

Database implementation is the procedure of populating the database with attribute data.

The Design Process of Database

Needs Analysis Data investigation is the “fact-finding” stage of database creation.

Consider: • data types • data quantity • data quality • attribute nature • entity nature

Conceptual Design: Modeling the Relationships between Entities


One to one; 1:1 – one visitor stays at one hotel




One to many; 1:M – one ski school teaches many visitors




Many to many; M:N – many tour companies use many hotels

Creation of the Database

(Heywood et al., 1998)

Attribute Data Modeling

(Heywood et al., 1998)

Conceptual Design Entity Relationship Model (E-R) GPS_Stn Pelan_Lokasi

GPS_Stn

1

Status

Pelan_Akui

Koordinat Geodetik

Monumen

Sketch

1

Kawalan GPS

Divide to

R_North

Mukim

Daerah

1 Pointkey

R_East

1

Tarikh Update

Kawalan Kadaster

1

Skecth

Seksyen

Lot_No

Batu Sempadan

S_Comment Status

Daerah

R_East

R_North

Koordinat Geosentrik

Mukim

WGS_Lat

Apdate Area_Unit 1

GID

RSO_Y

Parcelkey

Bearing

Plan

Distance M

has

Apdate

GID Status

Garis Sempadan

Entry_Mod

Units Class Line_Code

Adjparcel Status

WGS_Lon

Svy_Area

N

Lock_Id

CCDB_Stn

R_North

LOT Coord_Type

R_East

RSO_Y

Mukim

1

has

RSO_X

1

RSO_X

Mark_Desc

WGS_Lat WGS_Lon

Apdate Daerah

CCDB_Stn

Koordinat Geodetik R_East

Negeri

Control

1

Serial M

RSO_Y

Divide to

Pelan_Akui

Negeri

RSO_X

R_North

M

Pelan_Lokasi

GID

R_North

Status

CCDB_Stn

1

WGS_Lat WGS_Lon

Monumen

Control

GPS_Stn

Koordinat Geosentrik

1

GPS_Stn

RSO_Y

RSO_X

Negeri 1

Seksyen

Pointkey

WGS_Lon

R_East

Tarikh_Bina

Coincide

WGS_Lat

Line_Type Tnode

Fnode

GPS_Stn Pelan_Lokasi

GPS_Stn

Geodetic Coordinates

1

Status

Pelan_Akui

Monumen

Sketch

WGS_Lon

R_East 1

GPS Control

Tarikh_Bina

Divide to

R_North

Mukim

Daerah

1 Pointkey

R_East

R_North

RSO_X

RSO_Y

Status

CCDB_Stn

1

Tarikh Update

Cadastral Cadastre

1

WGS_Lat WGS_Lon

Monumen

Control by

GPS_Stn

Geocentric Coordinates

1

GPS_Stn

RSO_Y

RSO_X

Negeri 1

Seksyen

Coincide

WGS_Lat

1

CCDB_Stn

Geodetic Coordinates

WGS_Lat

Divide to

WGS_Lon R_North

RSO_X

R_East Negeri

M

Pelan_Lokasi Skecth

Seksyen

Daerah

Pelan_Akui

Apdate Negeri

Pointkey

Daerah

Lot_No

GID

Boundary Mark

S_Comment Status

Serial M

R_East

R_North

Apdate

N

Area_Unit 1

GID

Parcelkey

Distance M

GID Status

Boundary Line

Units Class Line_Code

Adjparcel Status

WGS_Lon

Bearing

Plan has

Apdate Lock_Id

RSO_Y

Svy_Area

LOT Coord_Type

WGS_Lat RSO_X

1

has

CCDB_Stn

R_North Mukim

Mark_Desc

Geocentric Coordinates

R_East

Mukim

Control by

1

RSO_Y

1

Entry_Mod

Line_Type Tnode

Fnode

Logikal Design • Translation of the conceptual design to database.

• Based on selected DBMS •Example of logical design in MapInfo 5.0

Struktur jadual dalam perisian MapInfo

LOGICAL DESIGN Master Data List Feature Dataset

Object Class Name

Object Class Alias

Type

Geometry

DCDB

Sto

Batu Sempadan

Simple

Point

Bdy

Garis Sempadan

Simple

Polyline

Lot

Parcel

Simple

Polygon

Rujukan Geodetik

GPS

Stesen GPS

Simple

Point

CCDB

Kawalan Kadaster

Simple

Point

None

GPSgeod

Koordinat Geodetik GPS

Table

None

GPSgeos

Koordinat Geosentrik GPS

Table

None

CCDBgeod

Koordinat Geodetik CCDB

Table

None

CCDBgeos

Koordinat Geosentrik CCDB

Table

None

FIELD FOR FEATURE CLASS LOT

STO BRY -POINTKEY -APDATE -MARK_DESC -SERIAL -COORD_TYPE -R_EAST -R_NORTH - S_COMMENT -STATUS -GID

(Primary Key) -APDATE -PARCELKEY -BEARING -DISTANCE -UNITS -CLASS -LINE_CODE -LINE_TYPE -ENTRY_MOD -PLAN -FNODE -TNODE -ADJPARCEL -STATUS

-NEGERI -DAERAH -MUKIM -SEKSYEN -LOT -SVY_AREA -AREAUNIT -APDATE -STATUS -LOCK_ID -GID

FIELD FOR TABLE CCDBgeod GPSgeod -OBJECT_ID -GPS_STN -WGS_LAT -WGS_LON -RSO_Y -RSO_X -R_NORTH -R_EAST GPSgeos -OBJECT_ID -GPS_STN -WGS_LAT -WGS_LON -RSO_Y -RSO_X -R_NORTH -R_EAST

(Foreign Key)

(Foreign Key)

CCDBgeod -OBJECT_ID -CCDB_STN Key) -WGS_LAT -WGS_LON -RSO_Y -RSO_X -R_NORTH -R_EAST CCDBgeos -OBJECT_ID -CCDB_STN Key) -WGS_LAT -WGS_LON -RSO_Y -RSO_X -R_NORTH -R_EAST

(Foreign

(Foreign

Physical Design


Proses pemilihan struktur penyimpanan data dan laluan capaian bagi fail pangkalan data untuk mendapatkan persembahan yang baik.




Semua pangkalan data akan disimpan dalam personal geodatabase masing-masing mengikut negeri dan storan setiap personal geodatabase adalah seperti berikut :Melaka – 132 MB Wilayah Persekutuan – 214 MB

 

Rekabentuk Fizikal • proses pemilihan struktur penyimpanan dan laluan capaian • penyimpanan fail merujuk kepada jumlah data • ruang storan berdasarkan bilangan entiti Jadual 1.0 : Anggaran ruang storan

Jenis Jadual Yang Dibentuk Jadual Bangunan Jadual Pili Bomba Jadual Jalan Jadual Parit

Jumlah Storan (Bait) 3033 685 395 1

FASA PEMBANGUNAN PANGKALAN DATA








Memasukkan data spatial Menyimpan data Pemprosesan Geografi (Geoprocessing) Memasukkan data atribut Memaparkan maklumat Mengurus sistem Menyelenggaraan sistem

DATA SPATIAL


Kemasukan data melalui :

 

Papan kekunci; Pengimbasan; Pertukaran format fail.




Geoprocessing yang telah dijalankan :



“Pembersihan” data input; Pembinaan topologi; Menjalankan projection; Pembersihan topologi; Percantuman peta.



   

DATA ATRIBUT




Kemasukan data melalui



Papan kekunci Pertukaran format fail



Physical Database

cont……

Example Here’s an example of a chart showing the relationships between flat files in a sample relational database for food suppliers* in Microsoft Access

* This comes from an MS ACCESS sample database

* This comes from an MS ACCESS sample database

SQL (Structured Query Language)




A database access language used in querying, updating, and managing relational databases. SQL has a common core that, generally, is available in all product implementations of the language. It has become a de facto standard because it is widely used and recognized by the industry as being standard.

Structured Query Language (SQL)




SQL is used to communicate with a database. It was developed to facilitate the querying of relational databases. SQL statements are used to perform tasks such as update data in a database, or retrieve data from a database. Advantages – simplicity – English-like style – wide application

Relational Operators


Frequently used for attribute data query

• equal to ( = ) • greater than ( > ) • less than ( < ) • greater than or equal to ( >= ) • less than or equal to ( <= ) • not equal to ( != )

Boolean Operators Connectors

And
Or
Not
Xor (Exclusive Or)


(Heywood et al., 1998)

GIS Query


Aspatial – questions about the attributes of features – can be performed by database software alone





prompts and query builder

Spatial – answers the question, “Where is something?” – associated with location; distances, areas,

perimeters – usually presented in map form; computer screen

SQL: Example


Here is a simple query that displays data from Table1: – SELECT Table1.[First Name], Table1.[Last Name] – FROM Table1 – ORDER BY Table1.[First Name];




This will generate a query that shows the fields First Name and Last Name from Table1, ordering them by the First Name.

SQL Continued


This language can get quite complicated: – SELECT DISTINCTROW

Customers.CustomerID, Customers.CompanyName, Customers.City, Customers.Country – FROM Customers RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID – WHERE (((Orders.OrderDate) Between #1/1/95# And #12/31/95#));