Independent Enterprise GIS Project · San Francisco · Version 1.0.0
Executive Summary
A database-first enterprise GIS case study demonstrating how a large urban roadway network is structured, validated, and turned into repeatable routing and accessibility decision-support products.
Road network data rarely arrives in a state ready for analysis. Edges are disconnected, geometries need review, and a network graph has to be built and validated before any routing question can be answered reliably. This project addresses that problem directly: a PostgreSQL/PostGIS database holds the network model, pgRouting builds the routable graph, and SQL-based topology preparation turns raw San Francisco road data into a validated network of 16,081 edges and 9,644 vertices.
From that network, four decision-support map packages were produced in QGIS — covering emergency response routing, nearest-facility accessibility, emergency response coverage, and infrastructure inspection routing. Each is backed by a reproducible SQL workflow, documented QA/QC, and backup/restore validation, so the build can be re-run and independently checked rather than taken on faith.
Designed as a portfolio-grade demonstration of spatial database engineering, network topology preparation, pgRouting analysis, and enterprise-style QA/QC for transportation decision support.
Operational Decision Context
This system demonstrates a reproducible workflow for the questions routing, accessibility, and service-coverage systems are typically asked to answer.
This project provides a reproducible workflow for these decision types. It is an independently designed and validated system, not a claim of deployment by any specific agency — see Methodological Scope & Limitations below.
System Architecture
Each stage runs in PostgreSQL/PostGIS and QGIS, with SQL and Python automation supporting a reproducible build.
Core Components
Network Preparation & Topology
Before any routing query can run, the road network has to be structured, cleaned, and validated as a connected graph.
Decision-Support Map Products
Each product pairs a specific operational question with a pgRouting-based method and a QGIS cartographic output. Select an image to view it enlarged.
01 · Emergency Response
Operational QuestionWhich route reaches an emergency location fastest across the validated network?
MethodpgRouting shortest-path analysis over the PostGIS network graph.
Decision-Support ValueSupports reproducible route-time comparison for emergency-response planning.
02 · Accessibility Analysis
Operational QuestionWhich facility is nearest, and how accessible is it, from any point on the network?
MethodpgRouting nearest-facility analysis across network vertices.
Decision-Support ValueSupports facility-siting review and accessibility-gap identification.
03 · Service Coverage
Operational QuestionWhat area can be reached within a given response threshold?
MethodpgRouting service-area analysis over the routable network.
Decision-Support ValueSupports coverage review and gap identification for response planning.
04 · Infrastructure Inspection
Operational QuestionWhat is an efficient inspection sequence across a set of infrastructure locations?
MethodRoute-optimization analysis over the validated network graph.
Decision-Support ValueSupports infrastructure inspection planning and route prioritization.
Routing & Accessibility Methodology
Each stage is built as a numbered SQL step so it can be re-run and independently verified.
Organize schemas and tables in PostgreSQL/PostGIS for network storage.
Load and clean road geometries into the network data model.
Check node and edge connectivity and topological integrity across the network.
Prepare source/target vertices and cost attributes required by pgRouting.
Execute shortest-path, accessibility, service-area, and inspection-routing queries.
Review and cartographically finalize each decision-support map.
Check SQL query outputs and map results against expected network behavior.
Assemble methodology notes, QA/QC results, and the versioned release package.
QA/QC & Validation
The build follows a numbered SQL workflow with automated checks at each stage, plus manual review before release.
Includes automated database, geometry, CRS, network, and routing tests, backup/restore validation, and release acceptance testing.
Deliverables
The project is organized so a reviewer can inspect the database structure, the SQL workflow, the map outputs, and the supporting documentation.
Methodological Scope & Limitations
This project demonstrates enterprise-style GIS architecture: a database-first design in PostgreSQL/PostGIS, a validated pgRouting network graph, and reproducible routing, accessibility, and service-area workflows for San Francisco's roadway network.
It is an independently designed and validated project. It is not evidence of operational deployment by any public agency, and it is not a substitute for live dispatch, real-time traffic, road-closure, or field-condition systems. It should not be presented as a production emergency-response platform.