Building a Smart Parking System with Streaming SQL

A smart parking system built on RisingWave turns raw IoT sensor events—entry/exit loops, magnetic sensors, camera-based LPR—into live occupancy counts, zone availability predictions, and dynamic guidance signals using standard SQL materialized views. No custom streaming code required; just PostgreSQL-compatible SQL over a Kafka event stream.

Why Smart Parking Matters for Cities

Traffic studies consistently show that 20–30% of urban congestion is caused by vehicles circling for parking. In dense city centers, the average driver spends 7–12 minutes searching for a space, generating unnecessary vehicle miles traveled, fuel burn, and emissions. The information asymmetry is the root cause: drivers do not know where spaces are available until they physically arrive and look.

Smart parking systems solve this by making occupancy data available in real time—to navigation apps, to variable message signs, and to parking operators. But the value of the data depends entirely on freshness. Occupancy data that is five minutes old is marginally useful; data that is 10 seconds old changes driver behavior. Building a pipeline that delivers occupancy data at that latency requires a streaming architecture.

The secondary benefit is operational: parking operators gain real-time visibility into utilization, enforcement needs, and revenue-per-space metrics. A downtown garage with 800 spaces and 70% average occupancy that improves to 80% through better guidance generates meaningful additional revenue without adding any physical capacity.

The Streaming SQL Approach

Parking sensors—magnetic, ultrasonic, loop, or camera-based—emit events when a space state changes: occupied or vacant. Entry/exit terminals emit transaction events when a vehicle enters or leaves. RisingWave ingests both event types from Kafka, maintains real-time space-level and zone-level occupancy counts in materialized views, and exposes them over PostgreSQL for guidance apps and operator dashboards to query.

Building It Step by Step

Step 1: Data Source

-- Space-level occupancy change events from IoT sensors
CREATE SOURCE parking_sensor_events (
    sensor_id       VARCHAR,
    space_id        VARCHAR,
    zone_id         VARCHAR,
    facility_id     VARCHAR,
    space_type      VARCHAR,  -- 'standard','accessible','ev','reserved'
    floor_level     INT,
    state           VARCHAR,  -- 'occupied','vacant'
    event_ts        TIMESTAMPTZ
) WITH (
    connector = 'kafka',
    topic = 'parking.sensors.events',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
) FORMAT PLAIN ENCODE JSON;

-- Entry/exit terminal transaction events
CREATE SOURCE parking_transactions (
    transaction_id  VARCHAR,
    facility_id     VARCHAR,
    zone_id         VARCHAR,
    terminal_id     VARCHAR,
    event_type      VARCHAR,  -- 'entry','exit'
    vehicle_type    VARCHAR,  -- 'car','motorcycle','ev','truck'
    ticket_type     VARCHAR,  -- 'hourly','daily','permit','validated'
    event_ts        TIMESTAMPTZ
) WITH (
    connector = 'kafka',
    topic = 'parking.transactions',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
) FORMAT PLAIN ENCODE JSON;

Step 2: Core Materialized View

-- Current occupancy per space (latest state from sensor events)
CREATE MATERIALIZED VIEW space_current_state AS
SELECT DISTINCT ON (space_id)
    sensor_id,
    space_id,
    zone_id,
    facility_id,
    space_type,
    floor_level,
    state,
    event_ts AS last_updated_ts
FROM parking_sensor_events
ORDER BY space_id, event_ts DESC;

-- Zone-level occupancy summary (live count)
CREATE MATERIALIZED VIEW zone_occupancy AS
SELECT
    zone_id,
    facility_id,
    space_type,
    COUNT(*)                                  AS total_spaces,
    COUNT(*) FILTER (WHERE state = 'occupied') AS occupied_spaces,
    COUNT(*) FILTER (WHERE state = 'vacant')   AS vacant_spaces,
    ROUND(
        COUNT(*) FILTER (WHERE state = 'occupied') * 100.0
        / NULLIF(COUNT(*), 0), 1
    )                                          AS occupancy_rate_pct,
    MAX(last_updated_ts)                       AS last_event_ts
FROM space_current_state
GROUP BY zone_id, facility_id, space_type;

-- Facility-level summary (rollup from zone occupancy)
CREATE MATERIALIZED VIEW facility_occupancy AS
SELECT
    facility_id,
    SUM(total_spaces)    AS total_spaces,
    SUM(occupied_spaces) AS occupied_spaces,
    SUM(vacant_spaces)   AS vacant_spaces,
    ROUND(
        SUM(occupied_spaces) * 100.0 / NULLIF(SUM(total_spaces), 0), 1
    )                    AS occupancy_rate_pct
FROM zone_occupancy
GROUP BY facility_id;

-- Hourly turnover and dwell time from transaction events
CREATE MATERIALIZED VIEW parking_turnover_hourly AS
SELECT
    facility_id,
    zone_id,
    vehicle_type,
    ticket_type,
    window_start,
    window_end,
    COUNT(*) FILTER (WHERE event_type = 'entry') AS entries,
    COUNT(*) FILTER (WHERE event_type = 'exit')  AS exits
FROM TUMBLE(parking_transactions, event_ts, INTERVAL '1 hour')
GROUP BY facility_id, zone_id, vehicle_type, ticket_type, window_start, window_end;

Step 3: Alerts and Aggregations

-- Near-capacity alert: zone occupancy > 90%
CREATE MATERIALIZED VIEW near_capacity_alerts AS
SELECT
    zone_id,
    facility_id,
    space_type,
    total_spaces,
    occupied_spaces,
    vacant_spaces,
    occupancy_rate_pct,
    last_event_ts
FROM zone_occupancy
WHERE occupancy_rate_pct > 90.0;

CREATE SINK near_capacity_sink
AS SELECT * FROM near_capacity_alerts
WITH (
    connector = 'kafka',
    topic = 'parking.alerts.capacity',
    properties.bootstrap.server = 'kafka:9092'
) FORMAT PLAIN ENCODE JSON;

-- EV charger availability (special space type alert)
CREATE MATERIALIZED VIEW ev_availability AS
SELECT
    zone_id,
    facility_id,
    COUNT(*) FILTER (WHERE state = 'vacant')   AS available_ev_spaces,
    COUNT(*) FILTER (WHERE state = 'occupied') AS occupied_ev_spaces,
    COUNT(*)                                    AS total_ev_spaces,
    MAX(last_updated_ts)                        AS last_updated
FROM space_current_state
WHERE space_type = 'ev'
GROUP BY zone_id, facility_id;

-- Demand pattern: peak hour occupancy by day of week
CREATE MATERIALIZED VIEW occupancy_by_hour_of_week AS
SELECT
    zone_id,
    facility_id,
    EXTRACT(DOW  FROM window_start) AS day_of_week,
    EXTRACT(HOUR FROM window_start) AS hour_of_day,
    AVG(occupied_spaces * 100.0 / NULLIF(total_spaces, 0)) AS avg_occupancy_pct
FROM (
    SELECT
        zone_id,
        facility_id,
        window_start,
        COUNT(*) FILTER (WHERE state = 'occupied') AS occupied_spaces,
        COUNT(*)                                    AS total_spaces
    FROM TUMBLE(parking_sensor_events, event_ts, INTERVAL '1 hour')
    GROUP BY zone_id, facility_id, window_start
) hourly
GROUP BY zone_id, facility_id, day_of_week, hour_of_day;

Comparison Table

FAQ

How does the system handle sensor failures or missed events?

The space_current_state view holds the last known state per space. If a sensor stops sending, that space retains its last reported state. You can add a staleness flag by comparing last_updated_ts against NOW() in a monitoring view, and surface stale sensors on the operator dashboard for maintenance dispatch.

Can RisingWave serve the mobile app directly?

RisingWave exposes a PostgreSQL wire-protocol interface. A backend API service (Node.js, FastAPI, etc.) queries materialized views using any PostgreSQL client library and serves the results to the mobile app over HTTPS. For push-based updates, a Kafka sink can stream occupancy changes to a WebSocket gateway.

How does the demand-pattern view help operators?

The occupancy_by_hour_of_week view shows historical average occupancy by day of week and hour. Parking operators use this to set dynamic pricing, schedule staff, and plan maintenance windows during low-demand periods. The view updates continuously so it reflects recent demand shifts, not just historical baselines.

Key Takeaways

• Model parking occupancy as a stateful view over an event stream: each sensor event flips a space between occupied and vacant.
• DISTINCT ON (space_id) ORDER BY event_ts DESC in RisingWave maintains the current state of every space without a traditional database update.
• Zone and facility rollup views cascade from the space-level view, giving operators hierarchical visibility from a single space to the entire facility portfolio.
• Near-capacity and EV-availability alerts publish to Kafka sinks, feeding variable message signs, navigation app APIs, and operator mobile apps in real time.

• RisingWave Documentation
• RisingWave IoT Patterns
• Materialized Views in RisingWave