Skip to main content

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

TypeMeaningExample
Object-based modelDiscrete featuresRoad, School, Lake
Field-based modelContinuous surfaceTemperature, 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 TypeExample
PointBorewell location
LineRoad, River
PolygonVillage boundary
RasterElevation map, NDVI map

2. Table Structure

Entities become tables

Example:

Table: Road

Road_IDNameTypeGeometry

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 

PhaseFocusQuestion AnsweredOutput
ConceptualReal-world understandingWhat data is needed?ER Diagram
LogicalData structureHow should data be organized?Tables & schema
PhysicalImplementationHow 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

Comments

Post a Comment

Popular posts from this blog

Accuracy Assessment

Accuracy assessment is the process of checking how correct your classified satellite image is . 👉 After supervised classification, the satellite image is divided into classes like: Water Forest Agriculture Built-up land Barren land But classification is done using computer algorithms, so some areas may be wrongly classified . 👉 Accuracy assessment helps to answer this question: ✔ "How much of my classified map is correct compared to real ground conditions?"  Goal The main goal is to: Measure reliability of classified maps Identify classification errors Improve classification results Provide scientific validity to research 👉 Without accuracy assessment, a classified map is not considered scientifically reliable . Reference Data (Ground Truth Data) Reference data is real-world information used to check classification accuracy. It can be collected from: ✔ Field survey using GPS ✔ High-resolution satellite images (Google Earth etc.) ✔ Existing maps or survey reports 🧭 Exampl...
Post a Comment
Read more

Atmospheric Window

The atmospheric window in remote sensing refers to specific wavelength ranges within the electromagnetic spectrum that can pass through the Earth's atmosphere relatively unimpeded. These windows are crucial for remote sensing applications because they allow us to observe the Earth's surface and atmosphere without significant interference from the atmosphere's constituents. Key facts and concepts about atmospheric windows: Visible and Near-Infrared (VNIR) window: This window encompasses wavelengths from approximately 0. 4 to 1. 0 micrometers. It is ideal for observing vegetation, water bodies, and land cover types. Shortwave Infrared (SWIR) window: This window covers wavelengths from approximately 1. 0 to 3. 0 micrometers. It is particularly useful for detecting minerals, water content, and vegetation health. Mid-Infrared (MIR) window: This window spans wavelengths from approximately 3. 0 to 8. 0 micrometers. It is valuable for identifying various materials, incl...
Post a Comment
Read more

Energy Interaction with Atmosphere and Earth Surface

In Remote Sensing , satellites record electromagnetic radiation (EMR) that is reflected or emitted from the Earth. Before reaching the sensor, radiation interacts with: The Atmosphere The Earth's Surface These interactions control how satellite images look and how we interpret them. I. Interaction of EMR with the Atmosphere When solar radiation travels from the Sun to the Earth, four main processes occur: 1. Absorption Definition: Absorption occurs when atmospheric gases absorb radiation at specific wavelengths and convert it into heat. Main absorbing gases: Ozone (O₃) → absorbs Ultraviolet (UV) Carbon dioxide (CO₂) → absorbs Thermal Infrared Water vapour (H₂O) → absorbs Infrared Concept: Atmospheric Windows These are wavelength regions where absorption is very low, allowing radiation to pass through the atmosphere. Remote sensing depends on these windows. For example, satellites like Landsat 8 use visible, near-infrared, and thermal bands located in atmospheric windows. 2. Trans...
Post a Comment
Read more

REMOTE SENSING INDICES

Remote sensing indices are band ratios designed to highlight specific surface features (vegetation, soil, water, urban areas, snow, burned areas, etc.) using the spectral reflectance properties of the Earth's surface. They improve classification accuracy and environmental monitoring. 1. Vegetation Indices NDVI – Normalized Difference Vegetation Index Formula: (NIR – RED) / (NIR + RED) Concept: Vegetation reflects strongly in NIR and absorbs in RED due to chlorophyll. Measures: Vegetation greenness & health Uses: Agriculture, drought monitoring, biomass estimation EVI – Enhanced Vegetation Index Formula: G × (NIR – RED) / (NIR + C1×RED – C2×BLUE + L) Concept: Corrects for soil and atmospheric noise. Measures: Vegetation vigor in dense canopies Uses: Tropical rainforest mapping, high biomass regions GNDVI – Green Normalized Difference Vegetation Index Formula: (NIR – GREEN) / (NIR + GREEN) Concept: Uses Green instead of Red ...
Post a Comment
Read more

Change Detection

Change detection is the process of finding differences on the Earth's surface over time by comparing satellite images of the same area taken on different dates . After supervised classification , two classified maps (e.g., Year-1 and Year-2) are compared to identify land use / land cover changes .  Goal To detect where , what , and how much change has occurred To monitor urban growth, deforestation, floods, agriculture, etc.  Basic Concept Forest → Forest = No change Forest → Urban = Change detected Key Terminologies Multi-temporal images : Images of the same area at different times Post-classification comparison : Comparing two classified maps Change matrix : Table showing class-to-class change Change / No-change : Whether land cover remains same or different Main Methods Post-classification comparison – Most common and easy Image differencing – Subtract pixel values Image ratioing – Divide pixel values Deep learning methods – Advanced AI-based detection Examples Agricult...
Post a Comment
Read more