GIS Database Design & Management: Models, Structures & Smart Systems

Core Concepts: What is a GIS Database?

A Geographical Information System (GIS) database is a specialized database designed to store, manage, and analyze spatial (geographic) and non-spatial (attribute) data. Unlike traditional databases, GIS databases must handle geometric data (points, lines, polygons) and topological relationships (adjacency, connectivity, containment). The design must ensure spatial integrity, efficient querying, and scalability for large datasets.

Why Database Design Matters in GIS

Poor design leads to:

• Slow queries (e.g., searching for all roads within 5 km of a flood zone).
• Data redundancy (e.g., storing the same land parcel multiple times).
• Inconsistencies (e.g., a river boundary mismatched between two layers).

1. GIS Data Models: Vector vs. Raster vs. TIN

The choice of data model depends on the application, scale, and analysis type. Below is a comparison table with real-world examples from Nepal and global platforms.

1.1 Vector Data: The "Drawing" Model

Vector data represents the world as geometric primitives:

• Points: 0D (e.g., a well location).
• Lines: 1D (e.g., a river or road).
• Polygons: 2D (e.g., a district boundary).

How It Works

• Each feature has:
  • Geometry: Coordinates (e.g., POINT(85.3125 27.7167)).
  • Attributes: Tabular data (e.g., land_use = "agricultural").
• Topology: Rules like "this road connects to that intersection" are stored explicitly.

Real-World Example: Pathao’s Route Optimization

Pathao uses vector network analysis to:

1. Represent Kathmandu’s roads as a graph (nodes = intersections, edges = road segments).
2. Apply Dijkstra’s algorithm to find the fastest route while avoiding traffic hotspots (e.g., Thapathali).
3. Update dynamically using real-time rider data.

graph TD
  A["Start: Thapathali"] -->|"Road Network"| B["Intersection 1: Pulchowk"]
  B --> C["Intersection 2: New Road"]
  C --> D["Destination: Lakshmi Path"]
  A["Start: Thapathali"] -->|"Traffic Hotspot"| E["Thapathali Circle"]
  E --> C["Intersection 2: New Road"]

1.2 Raster Data: The "Pixel Grid" Model

Raster data divides the world into a grid of cells, where each cell has a value (e.g., elevation, land cover type).

Key Formats

• DEM (Digital Elevation Model): Stores elevation as integers (e.g., cell_value = 1250 meters).
• Satellite Imagery: Bands for visible light, infrared (e.g., NASA Landsat).
• Thematic Rasters: Land use (e.g., 1 = forest, 2 = urban).

Real-World Example: Daraz’s Delivery Zones

Daraz uses raster-based flood risk modeling to:

1. Overlay a raster DEM (from ICIMOD) with historical flood data.
2. Identify high-risk delivery zones (e.g., areas below 1000m elevation in Kathmandu).
3. Adjust delivery routes or pricing dynamically.

1.3 TIN (Triangulated Irregular Network): The "Terrain" Model

TINs represent terrain using irregular triangles, where:

• Vertices = Sample points (e.g., survey markers).
• Triangles = Interpolated surfaces.

Advantages Over Raster/Vector

• Precision: Uses fewer points for complex terrain (e.g., mountains).
• Dynamic Analysis: Easily calculate slope, aspect, or viewshed.

Nepal Example: NTC’s Power Line Routing

The Nepal Electricity Authority (NEA) uses TINs to:

1. Model the Annapurna region’s terrain.
2. Simulate power line paths avoiding steep slopes (>30°).
3. Reduce construction costs by minimizing tunneling.

2. Geodatabase Design: The Backbone of GIS

A geodatabase is a container for GIS data, storing both spatial and attribute data in a structured way. It can be:

• File-based (e.g., .gdb in ArcGIS).
• Enterprise (e.g., PostgreSQL + PostGIS for large-scale systems).

Steps to Design a Geodatabase

1. Define the Purpose
   • Example: Track Kathmandu’s traffic congestion for smart city planning.
2. Identify Features
   • Roads (lines), intersections (points), noise pollution zones (polygons).
3. Choose Data Model
   • Vector for roads, raster for satellite imagery of traffic.
4. Design Schema
   • Tables for roads, traffic_cameras, accident_reports.
5. Set Topology Rules
   • "Roads must not overlap" or "Polygons must cover the entire city."
6. Index Spatial Data
   • Use R-trees or quadtrees for fast queries (e.g., "Find all roads within 500m of a school").

Real-World Example: Kathmandu Smart City Initiative

The Kathmandu Metropolitan City (KMC) is designing a geodatabase to:

• Store: Vector layers for buildings, raster layers for air quality (from sensors).
• Analyze: Use network analysis to reroute buses during protests (e.g., 2023 anti-corruption rallies).
• Visualize: Overlay flood risk (raster) with evacuation routes (vector).

3. Spatial Indexing: Speeding Up Queries

Without indexing, searching for features (e.g., "all hospitals in Lalitpur") would scan the entire database. Spatial indexing organizes data for faster searches.

Common Indexing Methods

Example: Ncell’s Emergency Response System

Ncell uses R-tree indexing to:

1. Store vector points for all mobile towers in Nepal.
2. Quickly locate the nearest tower to a 911 caller in remote areas (e.g., Dolpa).
3. Route emergency services via the fastest path (using vector network analysis).

4. Database Design Principles for GIS

A well-designed GIS database follows these rules:

Characteristics of Good GIS Database Design

1. Normalization
   • Avoid redundancy (e.g., store land parcel attributes once, not per survey).
2. Topological Integrity
   • Ensure features respect real-world rules (e.g., "a river cannot intersect itself").
3. Metadata Standards
   • Use ISO 19115 for describing datasets (e.g., "This layer was created in 2023 by KMC").
4. Scalability
   • Support growth (e.g., adding 1000 new buildings to Kathmandu’s database).
5. Backup & Recovery
   • Versioning (e.g., track changes to forest cover over 20 years).

5. Spatial Analysis Techniques in GIS Databases

Databases enable advanced spatial analysis, such as:

A. Overlay Analysis

Combines multiple layers to answer questions like:

• "Which areas in Bhaktapur are both flood-prone and have poor drainage?"
  • Overlay: Flood risk (raster) + Drainage network (vector).

B. Network Analysis

Models connected systems (e.g., roads, rivers, power grids).

• Example: NTC’s power outage prediction
  • Input: Vector lines for power lines + raster terrain.
  • Output: "Outages are 30% more likely on slopes >20° due to tree falls."

C. Terrain Analysis (Using DEMs/TINs)

Calculates:

• Slope: Critical for landslide risk (e.g., ICIMOD’s Himalayan hazard maps).
• Viewshed: Where a tower (e.g., Ncell’s signal mast) is visible.
• Aspect: Sun exposure for agriculture (e.g., Nepal’s terai vs. mountain farming).

6. Real-World Applications in Nepal

Case Study 1: eSewa’s Property Registration

• Database Design:
  • Vector polygons for land parcels (stored in a PostgreSQL/PostGIS geodatabase).
  • Attributes: Owner name, land use, tax status.
• Spatial Analysis:
  • Detects illegal subdivisions by comparing parcel boundaries to master maps.
• Impact: Reduced land disputes by 40% in Kathmandu Valley.

Case Study 2: ICIMOD’s Glacier Monitoring

• Data Model:
  • TINs for Himalayan glaciers (from satellite + field surveys).
  • Raster layers for snow cover (from Sentinel-2).
• Analysis:
  • Predicts glacial lake outburst floods (GLOFs) by modeling water flow paths.
• Example: Warned authorities in Solarha Glacier (Dolpa) of a 2022 flood risk.

Case Study 3: Daraz’s Last-Mile Delivery Optimization

• Database:
  • Vector roads + raster elevation (from OpenStreetMap).
  • Points for delivery hubs and rider locations.
• Network Analysis:
  • Finds the fastest route avoiding one-way streets and congestion.
• Result: 25% faster deliveries in Pokhara.

Exam Tip: How to Score Full Marks

Common Pitfalls to Avoid

1. Confusing Raster/Vector/TIN
   • Wrong: "Raster is better for roads." → Right: "Vector is better for roads because it stores exact coordinates."
2. Ignoring Topology
   • Wrong: "A river is just a line." → Right: "A river has topology rules (e.g., must not intersect itself)."
3. Vague Examples
   • Wrong: "GIS is used in agriculture." → Right: "Nepal’s Department of Agriculture uses raster NDVI (Normalized Difference Vegetation Index) to identify drought-stricken areas in the Terai region."

High-Scoring Strategies

• Compare with a Table: For questions like "vector vs. raster," always include a 3-column table with advantages/disadvantages/applications.
• Use Nepal Examples: Examiners love local context. Mention:
  • eSewa (vector databases for property).
  • NTC (raster/terrain analysis for power lines).
  • ICIMOD (TINs for glaciers).
• Draw a Mermaid Diagram: For processes like:
  • "Steps in geodatabase design."
  • "How network analysis works in Pathao."
• Link to Real Analysis: For questions on spatial analysis, describe:
  • Overlay: "Flood risk (raster) + schools (vector) → identify safe evacuation routes."
  • Terrain: "DEM (raster) → calculate slope for landslide risk in Dolakha."

Worked Example: Designing a GIS Database for Kathmandu Traffic

Question: "Design a geodatabase for a smart traffic management system in Kathmandu. Include data models, indexing, and one analysis technique."

Solution

1. Data Models

   • Vector:
     • Roads (lines, with attributes: speed_limit, traffic_camera_id).
     • Intersections (points, with signal_status).
   • Raster:
     • Traffic density (from Google Maps API or KMC sensors).
     • Elevation (DEM for hilly areas like Kageshwori).

2. Database Schema

   erDiagram
     ROADS ||--o{ TRAFFIC_CAMERAS : "has"
     ROADS ||--o{ ACCIDENTS : "records"
     TRAFFIC_CAMERAS }|--|| REAL_TIME_DATA : "streams"
     REAL_TIME_DATA {
       int timestamp
       float congestion_index
       string direction
     }

3. Indexing

   • R-tree for roads (fast queries like "find all roads near Thapathali").

4. Analysis Technique: Hotspot Detection

   • Overlay raster traffic density with vector accident points.
   • Identify high-risk intersections (e.g., Kantipath during festivals).

5. Real-World Tie-In

   • Pathao could use this database to:
     • Avoid congested routes.
     • Predict delays and adjust rider earnings dynamically.

Key Formulas & Shortcuts

Summary Checklist

Before the exam, ensure you can:

• Compare vector/raster/TIN with a table and Nepal/global examples.
• Explain geodatabase design steps with a Mermaid ER diagram.
• Describe spatial indexing (R-tree, quadtree) and its use in Ncell’s emergency system.
• Link overlay analysis to flood risk mapping in Bhaktapur.
• Explain terrain analysis using DEMs/TINs (e.g., ICIMOD’s glacier work).
• Design a database schema for a real scenario (e.g., KMC traffic system).