Geographic DBMS

A Relational Database Model stores data in the form of tables.
Each table contains:

• Rows → Individual records

• Columns → Attributes or fields

Tables can be connected using common fields called keys.

Terminologies

1. Table

A collection of related data arranged in rows and columns.

Example:

ID	Name	Population
1	Palakkad	130000
2	Thrissur	315000

---

2. Row (Record)

Represents one feature or entry.

👉 Example
"Palakkad" is one record.

---

3. Column (Field / Attribute)

Represents one property of data.

👉 Example
Population is a field.

---

4. Primary Key

A unique field used to identify each record.

👉 Example
ID column.

---

5. Foreign Key

A field used to connect two tables.

 Example in GIS (QGIS / ArcGIS)

Suppose you have:

Table 1: District Boundary Layer

District_ID	District_Name
1	Palakkad
2	Malappuram

---

Table 2: Rainfall Data

District_ID	Rainfall
1	2200 mm
2	2800 mm

👉 Both tables share District_ID

Using this field, GIS joins rainfall data to district maps.

Where Used in GIS

• Attribute tables of shapefiles

• Linking census data

• Spatial analysis with external datasets

Geographic DBMS Extensions

A Geographic DBMS Extension adds spatial capability to normal databases.

Normal database → Stores text and numbers
Geographic DBMS → Stores maps, shapes, coordinates

---

✅ Why It Is Needed

GIS deals with spatial objects like:

• Points

• Lines

• Polygons

• Raster images

A normal database cannot store or analyse them properly.

---

✅ Examples of Geographic DBMS Extensions

Database	GIS Extension
PostgreSQL	PostGIS
Oracle	Oracle Spatial
SQL Server	Spatial Extension

---

✅ Important Terminologies

1. Geometry

Shape of spatial feature.

Examples:

• Point → School location

• Line → Road

• Polygon → District boundary

---

2. Spatial Index

Improves speed of spatial queries.

Example:
Finding nearest hospital quickly.

---

3. Spatial Query

Query based on location.

Example:
"Find all villages within 5 km from river."

---

✅ Example in QGIS

QGIS uses PostGIS database.

👉 You can store:

• Road network

• Land use layers

• Satellite data

👉 Example Query:
Find all agricultural lands near rivers.

---

✅ Example in ArcGIS

ArcGIS uses:

• File Geodatabase

• Enterprise Geodatabase

They store spatial data with advanced indexing and topology rules.

---

3. SQL in GIS

✅ Simple Meaning

SQL (Structured Query Language) is used to:

✔ Retrieve data
✔ Filter data
✔ Analyse data
✔ Update data

---

✅ Basic SQL Commands

1. SELECT → Get data

Example:

SELECT * FROM districts;  

👉 Shows all districts.

---

2. WHERE → Filter data

Example:

SELECT * FROM districts  WHERE Population > 200000;  

👉 Shows only high population districts.

---

3. JOIN → Combine tables

Example:

SELECT districts.Name, rainfall.Amount  FROM districts  JOIN rainfall  ON districts.ID = rainfall.ID;  

---

4. SQL in GIS (Spatial SQL)

GIS uses special spatial functions.

---

✅ Spatial SQL Terminologies

1. ST_Within

Checks if one feature lies inside another.

Example:
Find houses inside flood zone.

---

2. ST_Intersects

Checks if two features overlap.

Example:
Find roads crossing rivers.

---

3. ST_Distance

Finds distance between two objects.

Example:
Distance from school to hospital.

---

✅ Example in QGIS (Using DB Manager or Expression Builder)

SELECT *  FROM landuse  WHERE ST_Area(geometry) > 10000;  

👉 Finds large land parcels.

---

✅ Example in ArcGIS (Select by Attributes)

"Population" > 100000  

---

✅ Example (Select by Location)

👉 Select villages within 2 km from river.

ArcGIS automatically runs spatial SQL behind the scene.

---

5. Real Life GIS Workflow Example

Suppose you want to analyse Flood Risk Areas

Step 1 – Store Data

Store rivers, rainfall, and land use in spatial database.

---

Step 2 – Use Relational Model

Link rainfall table with district boundaries.

---

Step 3 – Use Geographic DBMS

Store spatial shapes of rivers and villages.

---

Step 4 – Use SQL

Query villages near river and heavy rainfall zones.

---

6. Difference Summary

Concept	Purpose
Relational Database	Organizes attribute data
Geographic DBMS Extension	Stores spatial objects
SQL in GIS	Queries and analyses data

---

7. Simple Concept Connection

👉 Relational Database
Stores attribute tables.

👉 Geographic DBMS
Stores spatial data.

👉 SQL
Controls and analyses both.

---

8. Example From Your Field (Geography / GIS Research)

You can use these concepts in:

• Landslide mapping

• Urban expansion studies

• Climate data analysis

• Crime mapping

• Poverty mapping