Geographic Database Design in GIS

Geographic database design means planning how spatial data (maps + attributes) will be stored in a GIS system.

It is done in three main phases:

1. Conceptual Design → What data is needed?

2. Logical Design → How should data be structured?

3. Physical Design → How will it be implemented in software?

Conceptual Database Design (The "WHAT" Phase)

🔹 Meaning

This is the high-level planning stage.
It focuses on understanding real-world geographic features and their relationships.

It is independent of any software (not linked to PostgreSQL, ArcGIS, etc.).

---

🔹 Key Terminologies

• Entity → A real-world object
  Example: River, Road, Building, Village

• Attribute → Information about an entity
  Example:

  • River → Name, Length

  • Road → Type, Width

• Relationship → How entities are connected
  Example:

  • Road crosses River

  • Village located near River

• ER Diagram (Entity-Relationship Diagram)
  A diagram that shows entities, attributes, and relationships.

---

🔹 Object-based vs Field-based Model

Type	Meaning	Example
Object-based model	Discrete features	Road, School, Lake
Field-based model	Continuous surface	Temperature, Elevation, Rainfall

✔ In your rainfall or TWI analysis work, rainfall is a field model (continuous surface).
✔ In urban footprint extraction (Palakkad project), buildings are object model.

---

🎯 Goal of Conceptual Design

Define:

• What data is needed?

• What features exist?

• How are they related?

Logical Database Design (The "HOW" – Abstract Phase)

🔹 Meaning

Now we convert the conceptual idea into a structured data model.

Still independent of specific software, but more technical.

---

🔹 Key Terminologies

1. Spatial Data Types (Geometry Types)

Geometry Type	Example
Point	Borewell location
Line	Road, River
Polygon	Village boundary
Raster	Elevation map, NDVI map

---

2. Table Structure

Entities become tables

Example:

Table: Road

Road_ID	Name	Type	Geometry

---

3. Primary Key

A unique ID for each feature.

Example:
Road_ID → uniquely identifies each road.

---

4. Foreign Key

Links one table to another.

Example:
Village table contains District_ID to connect with District table.

---

5. Normalization

Organizing tables to:

• Avoid duplication

• Reduce redundancy

• Improve data integrity

Example:
Instead of repeating district name in every village record → create a separate district table.

---

6. Topology (Spatial Relationships)

Defines spatial rules like:

• Connected to

• Adjacent to

• Within

• Contains

• Intersects

Example:

• Road must be connected at junctions

• Building must be inside municipal boundary

In your GIS work, topology helps avoid:

• Gaps

• Overlaps

• Duplicate boundaries

---

🎯 Goal of Logical Design

Create:

• Tables

• Fields

• Keys

• Spatial relationships

• Clean data structure

Physical Database Design (The "HOW" – Technical Phase)

🔹 Meaning

Now the database is implemented in a real GIS-enabled DBMS.

Examples:

• PostgreSQL + PostGIS

• Oracle Spatial

• ArcGIS Geodatabase

• SpatiaLite

---

🔹 Key Terminologies

1. Data Types

Example in PostGIS:

geometry(Point, 4326)  geometry(Polygon, 32643)  

• GEOMETRY → planar coordinates

• GEOGRAPHY → earth-based spherical coordinates

---

2. Spatial Index

To make spatial queries fast.

Example:

• R-Tree Index

• GiST Index (PostGIS)

Used for:

• Finding nearest road

• Intersect queries

• Buffer analysis

---

3. SQL Implementation

Example:

CREATE TABLE roads (    road_id SERIAL PRIMARY KEY,    name VARCHAR(50),    type VARCHAR(20),    geom GEOMETRY(LineString, 4326)  );  

---

4. Optimization

Includes:

• Indexing

• Clustering

• Storage tuning

Improves:

• Query speed

• Performance

• Large dataset handling

---

🎯 Goal of Physical Design

Create:

• Real tables

• Spatial columns

• Indexes

• Efficient storage

Summary 

Phase	Focus	Question Answered	Output
Conceptual	Real-world understanding	What data is needed?	ER Diagram
Logical	Data structure	How should data be organized?	Tables & schema
Physical	Implementation	How to implement in DBMS?	SQL tables & indexes

Simple Real Example (Village Mapping Project)

Step 1 – Conceptual

Identify:

• Village

• Road

• River

• Relationships

---

Step 2 – Logical

Create tables:

• Village table

• Road table

• River table

Define:

• Primary keys

• Geometry types

• Topology rules

---

Step 3 – Physical

Implement in:

• QGIS Geopackage

• PostGIS database

Create:

• Spatial index

• Constraints

• SQL structure