Implementing vector feature tables — examples and best practices

Vector feature tables are the heart of a GeoPackage, containing the actual geospatial features and their attributes. While the GeoPackage standard provides flexibility in how you structure these tables, following best practices ensures your data remains accessible, performant, and maintainable over time. This section explores practical implementation strategies for Danish municipal applications.

Understanding Feature Tables

A feature table in GeoPackage is a SQLite table that contains at least one geometry column and typically multiple attribute columns. Each row represents a single geographic feature with its associated properties.

Required Components

Every feature table must include:

A primary key column - Typically an integer column that uniquely identifies each feature. By convention, this is often named fid (feature ID) or id.

At least one geometry column - Contains the spatial data in GeoPackage binary format. The column name is user-defined, though geom or geometry are common choices.

Registration in gpkg_contents - The table must be registered in the GeoPackage contents table with data_type = 'features'.

Registration in gpkg_geometry_columns - Each geometry column must be registered with its coordinate system and geometry type.

Optional but Recommended Components

Attribute columns - Additional columns storing descriptive information about each feature (names, dates, classifications, measurements, etc.).

Spatial index - An R-tree spatial index dramatically improves query performance for spatial operations.

Constraints - Database constraints ensure data quality and integrity.

Practical Examples from Danish Municipalities

Let's examine several real-world scenarios and how to implement them effectively.

Example 1: Street Tree Inventory

A municipality needs to track all trees along streets for maintenance planning.

Table structure:

CREATE TABLE street_trees (
    fid INTEGER PRIMARY KEY AUTOINCREMENT,
    geom POINT NOT NULL,
    tree_id TEXT UNIQUE NOT NULL,
    species_danish TEXT,
    species_latin TEXT,
    planting_date DATE,
    trunk_diameter_cm INTEGER,
    height_m REAL,
    condition TEXT CHECK(condition IN ('Excellent', 'Good', 'Fair', 'Poor', 'Critical')),
    last_inspection DATE,
    inspector_name TEXT,
    maintenance_notes TEXT,
    street_name TEXT,
    street_number TEXT
);

Best practices demonstrated:

Primary key uses AUTOINCREMENT for automatic ID assignment
Geometry column has NOT NULL constraint to prevent empty geometries
Unique constraint on tree_id prevents duplicate entries
CHECK constraint ensures condition values are standardized
Date columns use DATE type for proper temporal queries
Descriptive column names make the schema self-documenting

Registration in system tables:

-- Register in gpkg_contents
INSERT INTO gpkg_contents (
    table_name, data_type, identifier, description,
    last_change, min_x, min_y, max_x, max_y, srs_id
) VALUES (
    'street_trees', 'features', 'street_trees',
    'Municipal street tree inventory for maintenance planning',
    datetime('now'), 541000, 6205000, 592000, 6262000, 25832
);

-- Register geometry column
INSERT INTO gpkg_geometry_columns (
    table_name, column_name, geometry_type_name,
    srs_id, z, m
) VALUES (
    'street_trees', 'geom', 'POINT', 25832, 0, 0
);

Example 2: Building Footprints with Multiple Attributes

A comprehensive building registry with detailed attributes.

Table structure:

CREATE TABLE buildings (
    fid INTEGER PRIMARY KEY AUTOINCREMENT,
    geom POLYGON NOT NULL,
    building_id TEXT UNIQUE NOT NULL,
    bbr_id TEXT,  -- Reference to Danish Building and Dwelling Register
    address TEXT,
    postal_code TEXT,
    city TEXT,
    construction_year INTEGER,
    building_type TEXT,
    usage_category TEXT,
    floor_count INTEGER,
    basement BOOLEAN,
    roof_type TEXT,
    total_area_m2 REAL,
    heated_area_m2 REAL,
    energy_label TEXT CHECK(energy_label IN ('A2020', 'A2015', 'A2010', 'B', 'C', 'D', 'E', 'F', 'G')),
    heritage_protected BOOLEAN DEFAULT 0,
    last_renovation_year INTEGER,
    owner_type TEXT CHECK(owner_type IN ('Private', 'Municipal', 'State', 'Association', 'Company')),
    created_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_date DATETIME,
    data_source TEXT,
    comments TEXT
);

-- Add index on commonly queried fields
CREATE INDEX idx_buildings_postal ON buildings(postal_code);
CREATE INDEX idx_buildings_type ON buildings(building_type);
CREATE INDEX idx_buildings_energy ON buildings(energy_label);

Best practices demonstrated:

Reference to external registry (BBR) for data integration
Boolean columns for binary attributes
CHECK constraints enforce valid energy labels
DEFAULT values reduce data entry errors
Audit trail with created_date and updated_date
Indexes on frequently queried attributes improve performance
Comments field allows flexible annotation

Example 3: Road Network with Linear Referencing

A road centerline dataset using M dimension for maintenance tracking.

Table structure:

CREATE TABLE roads (
    fid INTEGER PRIMARY KEY AUTOINCREMENT,
    geom LINESTRINGM NOT NULL,
    road_id TEXT UNIQUE NOT NULL,
    road_name TEXT,
    road_number TEXT,  -- For numbered routes
    road_class TEXT CHECK(road_class IN ('Motorway', 'Primary', 'Secondary', 'Residential', 'Service')),
    surface_type TEXT,
    lane_count INTEGER,
    width_m REAL,
    speed_limit_kmh INTEGER,
    one_way BOOLEAN DEFAULT 0,
    bike_lane BOOLEAN DEFAULT 0,
    sidewalk BOOLEAN DEFAULT 0,
    lighting BOOLEAN DEFAULT 0,
    winter_service_priority INTEGER CHECK(winter_service_priority BETWEEN 1 AND 5),
    length_m REAL,
    municipality_code TEXT,
    ownership TEXT,
    last_survey_date DATE,
    pavement_condition_index REAL CHECK(pavement_condition_index BETWEEN 0 AND 100)
);

CREATE INDEX idx_roads_class ON roads(road_class);
CREATE INDEX idx_roads_name ON roads(road_name);
CREATE INDEX idx_roads_winter_priority ON roads(winter_service_priority);

Associated events table:

CREATE TABLE road_events (
    event_id INTEGER PRIMARY KEY AUTOINCREMENT,
    road_fid INTEGER NOT NULL,
    event_type TEXT NOT NULL,
    measure_start REAL NOT NULL,  -- M coordinate start
    measure_end REAL NOT NULL,    -- M coordinate end
    event_date DATE,
    severity TEXT,
    description TEXT,
    repair_status TEXT,
    repair_date DATE,
    cost REAL,
    FOREIGN KEY (road_fid) REFERENCES roads(fid)
);

CREATE INDEX idx_road_events_road ON road_events(road_fid);
CREATE INDEX idx_road_events_type ON road_events(event_type);

Best practices demonstrated:

LineStringM geometry enables linear referencing
Related events table links to road segments via foreign key
Measure columns store positions along the road
Separate tables for different entity types (roads vs. events)
Winter service priority for operational planning
Condition index for asset management

Example 4: Zoning Districts with Complex Rules

Land use zoning with detailed planning regulations.

Table structure:

CREATE TABLE zoning_districts (
    fid INTEGER PRIMARY KEY AUTOINCREMENT,
    geom MULTIPOLYGON NOT NULL,
    zone_id TEXT UNIQUE NOT NULL,
    zone_code TEXT NOT NULL,
    zone_name TEXT NOT NULL,
    zone_category TEXT CHECK(zone_category IN ('Residential', 'Commercial', 'Industrial', 'Mixed', 'Agricultural', 'Nature', 'Recreation', 'Public')),
    plan_id TEXT,  -- Reference to local plan
    plan_adoption_date DATE,
    max_building_height_m REAL,
    max_floor_area_ratio REAL,
    max_building_coverage_pct REAL,
    min_green_space_pct REAL,
    parking_requirement TEXT,
    noise_limit_db REAL,
    environmental_restrictions TEXT,
    heritage_considerations TEXT,
    flood_risk_zone BOOLEAN DEFAULT 0,
    area_m2 REAL,
    effective_date DATE,
    expiry_date DATE,
    legal_status TEXT CHECK(legal_status IN ('Proposed', 'Approved', 'In Force', 'Expired', 'Superseded')),
    public_hearing_date DATE,
    responsible_planner TEXT,
    contact_email TEXT,
    document_reference TEXT,
    notes TEXT
);

CREATE INDEX idx_zoning_category ON zoning_districts(zone_category);
CREATE INDEX idx_zoning_status ON zoning_districts(legal_status);
CREATE INDEX idx_zoning_plan ON zoning_districts(plan_id);

Best practices demonstrated:

MultiPolygon supports zoning districts that may include separate areas
Comprehensive planning parameters stored as attributes
Temporal validity tracking with effective and expiry dates
Legal status workflow support
Contact information for public inquiries
Document references for full legal text

Example 5: Utility Network - Water Supply

A water supply network with pipes, valves, and service connections.

Pipes table:

CREATE TABLE water_pipes (
    fid INTEGER PRIMARY KEY AUTOINCREMENT,
    geom LINESTRINGZ NOT NULL,  -- Z dimension for depth
    pipe_id TEXT UNIQUE NOT NULL,
    material TEXT CHECK(material IN ('PVC', 'PE', 'Ductile Iron', 'Concrete', 'Steel', 'Copper')),
    diameter_mm INTEGER NOT NULL,
    installation_year INTEGER,
    length_m REAL,
    depth_m REAL,
    pressure_zone TEXT,
    operational_status TEXT CHECK(operational_status IN ('Active', 'Inactive', 'Abandoned', 'Planned')),
    last_inspection DATE,
    condition_rating INTEGER CHECK(condition_rating BETWEEN 1 AND 5),
    leak_history_count INTEGER DEFAULT 0,
    replacement_priority TEXT,
    asset_value REAL,
    expected_lifetime_years INTEGER,
    gis_accuracy_m REAL
);

Valves table:

CREATE TABLE water_valves (
    fid INTEGER PRIMARY KEY AUTOINCREMENT,
    geom POINTZ NOT NULL,
    valve_id TEXT UNIQUE NOT NULL,
    valve_type TEXT CHECK(valve_type IN ('Isolation', 'Control', 'Check', 'Pressure Reducing', 'Air Release')),
    size_mm INTEGER,
    depth_m REAL,
    installation_year INTEGER,
    manufacturer TEXT,
    model TEXT,
    operational BOOLEAN DEFAULT 1,
    normally_open BOOLEAN,
    last_operated DATE,
    last_maintenance DATE,
    maintenance_interval_months INTEGER,
    critical_valve BOOLEAN DEFAULT 0,  -- Affects multiple customers if fails
    turning_direction TEXT,
    turns_to_close INTEGER,
    location_description TEXT,
    access_restrictions TEXT
);

Service connections table:

CREATE TABLE water_connections (
    fid INTEGER PRIMARY KEY AUTOINCREMENT,
    geom POINT NOT NULL,
    connection_id TEXT UNIQUE NOT NULL,
    property_id TEXT,
    address TEXT,
    meter_id TEXT,
    connection_type TEXT,
    diameter_mm INTEGER,
    installation_date DATE,
    customer_type TEXT CHECK(customer_type IN ('Residential', 'Commercial', 'Industrial', 'Public', 'Agricultural')),
    active BOOLEAN DEFAULT 1,
    meter_reading_date DATE,
    consumption_m3 REAL,
    billing_account TEXT
);

Best practices demonstrated:

Different tables for different network components
LineStringZ for pipes captures depth information
PointZ for valves includes installation depth
Comprehensive asset management attributes
Operational status tracking
Maintenance scheduling support
Critical infrastructure flagging
Customer relationship data

Best Practices for Table Design

Naming Conventions

Use clear, descriptive table names - Choose names that immediately convey the table's purpose. Prefer street_trees over trees or st_tr.

Consistent column naming - Establish and follow conventions throughout your GeoPackage:

Primary keys: fid or id
Geometry columns: geom or geometry
Foreign keys: parent_table_fid (e.g., road_fid)
Dates: suffix with _date (e.g., inspection_date)
Boolean flags: use adjectives or is_ prefix (e.g., active, is_protected)

Avoid special characters - Stick to alphanumeric characters and underscores. Avoid spaces, dashes, or special characters that require quoting.

Use lowercase - SQLite is case-insensitive, but lowercase is conventional and easier to type.

Example: Good: building_footprints, maintenance_date. Poor: Building-Footprints, Date Of Maintenance.

Data Types

Choose appropriate SQLite types:

TEXT for strings, codes, and identifiers
INTEGER for whole numbers and foreign keys
REAL for decimal numbers and measurements
BLOB for binary data (though geometry uses special GeoPackage format)
DATE and DATETIME for temporal data (stored as TEXT in ISO 8601 format)
BOOLEAN is stored as INTEGER (0 or 1)

Example:

construction_year INTEGER,        -- Whole number
area_m2 REAL,                     -- Decimal measurement
inspection_date DATE,             -- Temporal data
active BOOLEAN DEFAULT 1,         -- Binary flag
building_id TEXT UNIQUE NOT NULL  -- String identifier

Constraints for Data Quality

NOT NULL constraints - Require values for essential fields:

geom POLYGON NOT NULL,
building_id TEXT NOT NULL,
owner_type TEXT NOT NULL

UNIQUE constraints - Prevent duplicate identifiers:

tree_id TEXT UNIQUE NOT NULL,
asset_number TEXT UNIQUE

CHECK constraints - Enforce valid values:

condition TEXT CHECK(condition IN ('Excellent', 'Good', 'Fair', 'Poor')),
priority INTEGER CHECK(priority BETWEEN 1 AND 10),
percentage REAL CHECK(percentage >= 0 AND percentage <= 100)

FOREIGN KEY constraints - Maintain referential integrity:

FOREIGN KEY (road_fid) REFERENCES roads(fid),
FOREIGN KEY (zone_code) REFERENCES zoning_codes(code)

DEFAULT values - Provide sensible defaults:

active BOOLEAN DEFAULT 1,
created_date DATETIME DEFAULT CURRENT_TIMESTAMP,
data_source TEXT DEFAULT 'Municipal Survey'

Indexing Strategy

Always create spatial indexes - Critical for performance:

-- Most GIS tools create this automatically
-- Manual creation if needed:
CREATE INDEX idx_buildings_geom ON buildings(geom);

Index frequently queried attributes:

CREATE INDEX idx_buildings_type ON buildings(building_type);
CREATE INDEX idx_roads_class ON roads(road_class);
CREATE INDEX idx_trees_species ON street_trees(species_latin);

Index foreign keys:

CREATE INDEX idx_events_road ON road_events(road_fid);

Consider composite indexes for combined queries:

CREATE INDEX idx_buildings_type_year ON buildings(building_type, construction_year);

Don't over-index - Every index adds storage overhead and slows down inserts/updates. Only index columns that are frequently used in WHERE clauses or JOIN conditions.

Handling Temporal Data

Track data lineage:

created_date DATETIME DEFAULT CURRENT_TIMESTAMP,
created_by TEXT,
updated_date DATETIME,
updated_by TEXT,
data_source TEXT,
accuracy_date DATE  -- When the data was collected/verified

Support temporal queries:

effective_from DATE NOT NULL,
effective_to DATE,
-- Query current features: WHERE effective_from <= CURRENT_DATE AND (effective_to IS NULL OR effective_to > CURRENT_DATE)

Version control for planning scenarios:

version_id INTEGER NOT NULL,
version_description TEXT,
scenario_type TEXT,
superseded_by_fid INTEGER

Documentation Within the GeoPackage

Use table descriptions in gpkg_contents:

INSERT INTO gpkg_contents (
    table_name, identifier, description
) VALUES (
    'buildings',
    'Building Footprints',
    'Complete building footprint inventory for municipality, including BBR registry integration and energy performance data. Updated quarterly from municipal surveys and BBR synchronization.'
);

Add metadata tables for codes and classifications:

CREATE TABLE road_class_codes (
    code TEXT PRIMARY KEY,
    description TEXT NOT NULL,
    display_order INTEGER,
    color_hex TEXT,
    line_width INTEGER
);

INSERT INTO road_class_codes VALUES
    ('Motorway', 'High-speed divided highway', 1, '#FF0000', 4),
    ('Primary', 'Major through roads', 2, '#FFA500', 3),
    ('Secondary', 'Regional connectors', 3, '#FFFF00', 2),
    ('Residential', 'Local streets', 4, '#CCCCCC', 1);

Create a metadata table for the GeoPackage itself:

CREATE TABLE geopackage_metadata (
    property TEXT PRIMARY KEY,
    value TEXT
);

INSERT INTO geopackage_metadata VALUES
    ('title', 'Aarhus Municipality Infrastructure Database'),
    ('version', '2.1.0'),
    ('created_date', '2025-01-15'),
    ('organization', 'Aarhus Kommune'),
    ('contact_email', '[email protected]'),
    ('coordinate_system', 'EPSG:25832'),
    ('coverage_area', 'Aarhus Municipality boundary'),
    ('update_frequency', 'Quarterly'),
    ('data_classification', 'Public');

Common Patterns and Anti-Patterns

Pattern: Separating Geometry and Attributes

Good approach - Single table with geometry and attributes together:

CREATE TABLE parcels (
    fid INTEGER PRIMARY KEY,
    geom POLYGON NOT NULL,
    parcel_number TEXT UNIQUE NOT NULL,
    owner_name TEXT,
    area_m2 REAL,
    land_use TEXT
);

Why it works: Keeps related data together, simplifies queries, maintains atomicity.

Anti-Pattern: Storing Coordinates as Text

Poor approach:

CREATE TABLE locations (
    fid INTEGER PRIMARY KEY,
    x_coordinate TEXT,
    y_coordinate TEXT,
    name TEXT
);

Why it fails: No spatial indexing, no CRS information, error-prone parsing, can't use spatial operations.

Correct approach:

CREATE TABLE locations (
    fid INTEGER PRIMARY KEY,
    geom POINT NOT NULL,
    name TEXT
);

Good approach - Separate tables with relationships:

CREATE TABLE inspections (
    inspection_id INTEGER PRIMARY KEY,
    asset_fid INTEGER NOT NULL,
    inspection_date DATE,
    inspector TEXT,
    findings TEXT,
    FOREIGN KEY (asset_fid) REFERENCES assets(fid)
);

Why it works: Avoids data duplication, supports multiple inspections per asset, maintains data integrity.

Anti-Pattern: Repeating Groups

Poor approach:

CREATE TABLE buildings (
    fid INTEGER PRIMARY KEY,
    geom POLYGON,
    owner1_name TEXT,
    owner1_percentage REAL,
    owner2_name TEXT,
    owner2_percentage REAL,
    owner3_name TEXT,
    owner3_percentage REAL
);

Why it fails: Limited to fixed number of owners, wastes space, difficult to query.

Correct approach:

CREATE TABLE buildings (
    fid INTEGER PRIMARY KEY,
    geom POLYGON NOT NULL,
    building_id TEXT UNIQUE NOT NULL
);

CREATE TABLE building_owners (
    owner_id INTEGER PRIMARY KEY,
    building_fid INTEGER NOT NULL,
    owner_name TEXT NOT NULL,
    ownership_percentage REAL,
    FOREIGN KEY (building_fid) REFERENCES buildings(fid)
);

Pattern: Lookup Tables for Controlled Vocabularies

Good approach:

CREATE TABLE land_use_types (
    code TEXT PRIMARY KEY,
    description_da TEXT NOT NULL,  -- Danish
    description_en TEXT,           -- English
    parent_code TEXT,              -- Hierarchical classification
    display_color TEXT
);

CREATE TABLE parcels (
    fid INTEGER PRIMARY KEY,
    geom POLYGON NOT NULL,
    land_use_code TEXT,
    FOREIGN KEY (land_use_code) REFERENCES land_use_types(code)
);

Why it works: Ensures consistency, simplifies updates to descriptions, supports internationalization, enables hierarchical classifications.

Performance Considerations

Optimal Table Size

Single large table vs. multiple smaller tables:

For similar features with same attributes: Use one table (e.g., all parcels in one table)
For different feature types: Use separate tables (e.g., separate tables for buildings, roads, parcels)
Consider partitioning very large datasets (millions of features) by municipality, year, or geographic area

Example: A nationwide dataset might be split:

buildings_region_hovedstaden
buildings_region_midtjylland
buildings_region_nordjylland
buildings_region_sjaelland
buildings_region_syddanmark

Query Optimization

Use spatial indexes effectively:

-- Good: Uses spatial index
SELECT * FROM buildings 
WHERE ST_Intersects(geom, ST_GeomFromText('POLYGON(...)', 25832));

-- Good: Filter by extent first
SELECT * FROM buildings 
WHERE min_x >= 560000 AND max_x <= 570000 
  AND min_y >= 6220000 AND max_y <= 6230000;

Combine spatial and attribute filters wisely:

-- Good: Filter attributes first if very selective
SELECT * FROM buildings 
WHERE heritage_protected = 1  -- Might reduce to 1% of records
  AND ST_Intersects(geom, @search_area);

-- Good: Filter spatially first if very selective
SELECT * FROM buildings 
WHERE ST_Intersects(geom, @small_area)  -- Might reduce to 0.1% of records
  AND building_type = 'Residential';

Batch Operations

Insert multiple features efficiently:

BEGIN TRANSACTION;
-- Multiple INSERT statements
INSERT INTO street_trees (...) VALUES (...);
INSERT INTO street_trees (...) VALUES (...);
-- ... thousands more
COMMIT;

Use prepared statements in applications to improve performance and security.

Validation and Quality Control

Built-in Validation

Geometry validation:

-- Check for invalid geometries
SELECT fid, building_id 
FROM buildings 
WHERE ST_IsValid(geom) = 0;

-- Check for empty geometries
SELECT fid, building_id 
FROM buildings 
WHERE geom IS NULL OR ST_IsEmpty(geom);

Attribute completeness:

-- Find records missing critical attributes
SELECT fid, building_id 
FROM buildings 
WHERE address IS NULL OR construction_year IS NULL;

-- Check for out-of-range values
SELECT fid, building_id, floor_count 
FROM buildings 
WHERE floor_count < 0 OR floor_count > 50;

Creating Validation Views

Summary view for quality control:

CREATE VIEW data_quality_summary AS
SELECT 
    'buildings' AS layer_name,
    COUNT(*) AS total_features,
    SUM(CASE WHEN geom IS NULL THEN 1 ELSE 0 END) AS missing_geometry,
    SUM(CASE WHEN address IS NULL THEN 1 ELSE 0 END) AS missing_address,
    SUM(CASE WHEN ST_IsValid(geom) = 0 THEN 1 ELSE 0 END) AS invalid_geometry
FROM buildings;

Real-World Implementation Workflow

Step 1: Plan Your Schema

Identify all feature types needed
Determine attributes for each feature type
Define relationships between tables
Establish naming conventions
Document data sources and update procedures

Step 2: Create Base Tables

-- Enable foreign key support
PRAGMA foreign_keys = ON;

-- Create main feature table
CREATE TABLE your_features (
    fid INTEGER PRIMARY KEY AUTOINCREMENT,
    geom GEOMETRY_TYPE NOT NULL,
    -- Add attribute columns
);

-- Register in GeoPackage system tables
INSERT INTO gpkg_contents (...) VALUES (...);
INSERT INTO gpkg_geometry_columns (...) VALUES (...);

Step 3: Add Constraints and Indexes

-- Add constraints
ALTER TABLE your_features ADD CONSTRAINT ...;

-- Create indexes
CREATE INDEX idx_your_features_attribute ON your_features(attribute);

Step 4: Create Supporting Tables

-- Lookup tables
CREATE TABLE attribute_codes (...);

-- Related data tables
CREATE TABLE related_data (...);

Step 5: Populate and Validate

-- Import data
BEGIN TRANSACTION;
-- INSERT statements or import process
COMMIT;

-- Validate
SELECT * FROM data_quality_summary;

Step 6: Document

-- Update metadata
UPDATE gpkg_contents SET description = '...' WHERE table_name = 'your_features';

-- Add metadata table entries
INSERT INTO geopackage_metadata VALUES ('last_update', CURRENT_DATE);

Summary

Implementing effective vector feature tables in GeoPackage requires careful planning and attention to detail. By following these best practices, Danish municipalities can create robust, performant, and maintainable geospatial databases that serve their needs for years to come. Key takeaways include choosing appropriate geometry types, using constraints for data quality, creating proper indexes, documenting your schema, and following consistent naming conventions throughout your GeoPackage.

Knowledge Check Quiz

Test your understanding of implementing vector feature tables in GeoPackage:

Question 1: What are the three essential components that every GeoPackage feature table must have?

a) Geometry column, attribute columns, and spatial index
b) Primary key, geometry column, and registration in gpkg_contents
c) Primary key, attribute columns, and foreign keys
d) Geometry column, spatial index, and CHECK constraints

Question 2: You're creating a table for a sewer network where you need to track the depth of pipes. Which combination is most appropriate?

a) LINESTRING geometry with depth stored as an attribute
b) LINESTRINGZ geometry using the Z coordinate for depth
c) LINESTRINGM geometry using the M coordinate for depth
d) LINESTRING geometry with a separate depth_m column

Question 3: Which of the following is an anti-pattern that should be avoided?

a) Using separate tables for different feature types
b) Storing x and y coordinates as TEXT columns instead of using geometry
c) Creating indexes on frequently queried attributes
d) Using foreign keys to link related tables

Question 4: For a building registry where some buildings have multiple owners, what is the best database design approach?

a) Create columns owner1_name, owner2_name, owner3_name in the buildings table
b) Store all owner names in a single TEXT column separated by semicolons
c) Create a separate building_owners table with a foreign key to buildings
d) Use a JSON column to store an array of owners

Question 5: What is the correct way to register a feature table in a GeoPackage?

a) Only create the table - GeoPackage handles registration automatically
b) Insert records into both gpkg_contents and gpkg_geometry_columns
c) Only insert a record into gpkg_geometry_columns
d) Create a special .gpkg.xml metadata file

Question 6: Which constraint would you use to ensure that a condition_rating column only accepts values between 1 and 5?

a) UNIQUE constraint
b) FOREIGN KEY constraint
c) CHECK constraint
d) NOT NULL constraint

Question 7: What is the recommended naming convention for a foreign key column that references the fid in a roads table?

a) foreign_key
b) roads_reference
c) roads_fid
d) fk_roads

Question 8: For a table with 500,000 features that is frequently queried by both spatial location and building_type attribute, what indexes should you create?

a) Only a spatial index on the geometry column
b) Only an attribute index on building_type
c) Both a spatial index and an attribute index on building_type
d) A composite index combining geometry and building_type

Question 9: When should you use MultiPolygon geometry instead of Polygon?

a) Always - MultiPolygon is more flexible
b) Only when features may consist of multiple non-contiguous areas
c) When you need better performance
d) When working with Danish coordinate systems

Question 10: What is the purpose of creating lookup tables for controlled vocabularies (like land use types)?

a) To increase database size and complexity
b) To ensure consistency, simplify updates, and support internationalization
c) To improve spatial query performance
d) To meet GeoPackage specification requirements

Question 11: Which SQL statement pattern provides the best performance for batch inserting thousands of features?

a) Individual INSERT statements executed one at a time
b) Multiple INSERT statements wrapped in a single BEGIN TRANSACTION...COMMIT block
c) Creating the features in a temporary table first
d) Using UPDATE statements instead of INSERT

Question 12: For tracking when data was created and last modified, which columns should you include in your feature table?

a) date_stamp TEXT
b) created_date DATETIME and updated_date DATETIME
c) timestamp BLOB
d) No temporal columns are needed

Question 13: You need to store energy efficiency ratings (A2020, A2015, A2010, B, C, D, E, F, G) for buildings. What's the best approach?

a) TEXT column with no constraints
b) INTEGER column with numeric codes
c) TEXT column with CHECK constraint listing valid values
d) BOOLEAN column for each possible rating

Question 14: What does the AUTOINCREMENT keyword do in a primary key definition?

a) Automatically creates spatial indexes
b) Ensures unique IDs are assigned automatically and never reused
c) Speeds up geometry processing
d) Enables foreign key relationships

Question 15: Which approach is best for documenting what data is contained in your GeoPackage?

a) Include a README.txt file alongside the GeoPackage file
b) Use descriptive table and column names only
c) Add descriptions in gpkg_contents and create a metadata table
d) Documentation is not necessary for GeoPackage files

Answer Key

b) Primary key, geometry column, and registration in gpkg_contents - These are the three essential components. While spatial indexes are highly recommended, they're not strictly required.
b) LINESTRINGZ geometry using the Z coordinate for depth - The Z dimension is specifically designed for elevation/depth information, making it the most appropriate choice.
b) Storing x and y coordinates as TEXT columns instead of using geometry - This anti-pattern prevents spatial indexing and operations. Always use proper geometry columns.
c) Create a separate building_owners table with a foreign key to buildings - This normalized approach supports any number of owners and maintains data integrity.
b) Insert records into both gpkg_contents and gpkg_geometry_columns - Feature tables must be registered in both system tables to comply with the GeoPackage standard.
c) CHECK constraint - CHECK constraints are used to enforce valid value ranges: CHECK(condition_rating BETWEEN 1 AND 5).
c) roads_fid - This naming convention clearly indicates which table the foreign key references: parent_table_fid.
c) Both a spatial index and an attribute index on building_type - Spatial and attribute queries benefit from separate indexes on the respective columns.
b) Only when features may consist of multiple non-contiguous areas - Use MultiPolygon when a single feature naturally consists of separate areas (like islands), otherwise use Polygon.
b) To ensure consistency, simplify updates, and support internationalization - Lookup tables provide controlled vocabularies, making data management easier and supporting multiple languages.
b) Multiple INSERT statements wrapped in a single BEGIN TRANSACTION...COMMIT block - This dramatically improves performance by reducing disk writes while maintaining data integrity.
b) created_date DATETIME and updated_date DATETIME - These columns provide a clear audit trail of when data was entered and modified.
c) TEXT column with CHECK constraint listing valid values - This ensures only valid energy labels can be entered while maintaining readability: CHECK(energy_label IN ('A2020', 'A2015', ...)).
b) Ensures unique IDs are assigned automatically and never reused - AUTOINCREMENT guarantees SQLite will never reuse deleted IDs, which is important for maintaining data integrity.
c) Add descriptions in gpkg_contents and create a metadata table - Self-documenting GeoPackages with internal metadata are most reliable since the documentation travels with the data.

Updated on Jan 16, 2026

Foundations of GeoPackage

Vector Features

Raster Data and Tiles

Metadata and Relationships

Understanding Feature Tables

Required Components

Optional but Recommended Components

Practical Examples from Danish Municipalities

Example 1: Street Tree Inventory

Example 2: Building Footprints with Multiple Attributes

Example 3: Road Network with Linear Referencing

Example 4: Zoning Districts with Complex Rules

Example 5: Utility Network - Water Supply

Best Practices for Table Design

Naming Conventions

Data Types

Constraints for Data Quality

Indexing Strategy

Handling Temporal Data

Documentation Within the GeoPackage

Common Patterns and Anti-Patterns

Pattern: Separating Geometry and Attributes

Anti-Pattern: Storing Coordinates as Text

Pattern: Normalized Related Data

Anti-Pattern: Repeating Groups

Pattern: Lookup Tables for Controlled Vocabularies

Performance Considerations

Optimal Table Size

Query Optimization

Batch Operations

Validation and Quality Control

Built-in Validation

Creating Validation Views

Real-World Implementation Workflow

Step 1: Plan Your Schema

Step 2: Create Base Tables

Step 3: Add Constraints and Indexes

Step 4: Create Supporting Tables

Step 5: Populate and Validate

Step 6: Document

Summary

Knowledge Check Quiz

Answer Key