# Bucharest announced utility faults

> Every utility fault and street works site currently announced in Bucharest — sewerage, gas, district heating, consumer electricity, the road surface itself and the tram and trolleybus traction network — as published on Primăria Municipiului București's interactive map. One row per announced fault: the street, the network operator responsible, the announced repair window, and the location as coordinates. The map shows only what is announced right now and keeps no archive of its own; this dataset builds that archive, hour by hour, and makes it queryable.

- Dataset id: `pmb-incidents`
- Update cadence: hourly
- Upstream source: https://hip.pmb.ro/hip/?MapGuid=72d6f523-5edb-427f-9b0e-3e03b6ba177f&SiteGuid=76c2a7a5-9eab-4a3f-9b04-fd89109b19e4
- Tags: bucharest, romania, utilities, water, gas, district-heating, roadworks, outages, infrastructure, geospatial, pmb
- Current version: ingest `01M54QHE2CXY06X14R4KZZQX2B`, snapshot 2026-10-17, generated 2026-10-17T10:49:00.986Z

## Licence and attribution

- Licence: Romanian official acts and public-interest information — outside copyright (`ro-official-acts-public-domain`)
- Licence text: https://legislatie.just.ro/Public/DetaliiDocument/7816
- Required attribution: Date despre avarii la apă și termoficare publicate de Primăria Municipiului București — hip.pmb.ro
- Notes: Legea 8/1996 privind dreptul de autor, art. 9, excludes from copyright both official texts of an administrative nature and simple facts and data. Each fault record here is the official administrative record of a public-utility incident — the announcement and repair window a public authority itself issues — and the underlying details (dates, network codes, street names, coordinates) are bare facts. They are also public-interest information under Legea 544/2001 and re-usable public-sector information under Legea 179/2022, which transposes the EU Open Data Directive. HOWEVER, and a reuser must weigh this: no explicit reuse licence is published for this data — checked on 2026-09-02, and the position is stronger than "could not find one": pmb.ro carries no copyright line anywhere, no terms-of-use page, and no legal link of any kind; the only policy documents on the site are a cookie policy and a GDPR personal-data notice, neither of which says anything about reusing published data. The map application itself (hip.pmb.ro) has a copyright panel in its interface, but it is empty. This dataset therefore rests on the legal position above rather than on any grant from Primăria Municipiului București, and anyone republishing it should make their own assessment. What it publishes is a DERIVED work: only the fault id, the announcement and repair-window dates, the network type and its code, the responsible administrator, the street name, street register code, postal number and free-text location note, and the point coordinates. No map imagery, no basemap tiles, no page markup, no site styling and no other content from pmb.ro is republished, and the connector reads only the WFS layer, never the map images. robots.txt: there is none — https://hip.pmb.ro/robots.txt returns HTTP 404, so no crawl directive covers this endpoint at all; https://www.pmb.ro/robots.txt returns HTTP 200 but serves the site's HTML shell rather than a robots file, which is also no directive. The endpoint needs no authentication and this connector sends no cookie or credential. Coordinates: the source publishes points in EPSG:3844 (Pulkovo 1942(58) / Stereo 70), Romania's national projected CRS. This connector reprojects them to WGS 84 (EPSG:4326) in pure arithmetic — inverse Oblique Stereographic (EPSG method 9809) on the Krassowsky 1940 ellipsoid, then the datum shift EPSG:15995 "Pulkovo 1942(58) to WGS 84 (19)", a seven-parameter Helmert in the Coordinate Frame rotation convention (EPSG method 9607) whose own published accuracy is 3 m; the implementation agrees with PROJ 9.5.1 to better than 1e-8 m over 400 points spanning Romania, so 3 m is the whole error budget. The original Stereo-70 easting and northing are preserved verbatim in stereo70_easting / stereo70_northing so any reuser can redo the transformation with different parameters, or with ANCPI's licensed TransDatRO grid, and compare.

Reproduce the attribution line verbatim anywhere you republish this data.

## Manifest

- Start here for how this directory works: https://data.bunnytech.app/llms.txt
- This dataset: https://data-directory.fsn1.your-objectstorage.com/public/pmb-incidents/manifest.json
- All datasets: https://data-directory.fsn1.your-objectstorage.com/public/index.json
- Human page: https://data.bunnytech.app/datasets/pmb-incidents

The manifest is the contract: file URLs, row counts, column types, min/max statistics and
example queries all live there. Re-read it to discover a new version.

## Fastest path — Parquet exports

Immutable, physically sorted snapshot exports. No catalog fetch, best row-group pruning,
safe to cache forever. Register one view per table:

```sql
INSTALL httpfs; LOAD httpfs;

CREATE OR REPLACE VIEW faults AS
  SELECT * FROM read_parquet('https://data-directory.fsn1.your-objectstorage.com/public/pmb-incidents/v/01M54QHE2CXY06X14R4KZZQX2B/faults.parquet');
```

Example queries:

-- Faults announced on one street: Search street_lc, never lower(street): a function on a column cannot use parquet statistics, and street_lc also folds away the diacritics the source is inconsistent about.
```sql
ATTACH IF NOT EXISTS 'ducklake:https://data-directory.fsn1.your-objectstorage.com/catalog/catalog.ducklake' AS "datadirectory" (READ_ONLY);

SELECT start_date, end_date, network_type, administrator, street, street_number
FROM datadirectory.pmb_incidents.faults
WHERE street_lc LIKE '%eminescu%'
ORDER BY start_date DESC
```

-- How long faults lasted, by network operator: The date filter comes first because start_date is the leading sort column. last_seen_date − first_seen_date is the observed duration; announced_duration_days is what was promised.
```sql
ATTACH IF NOT EXISTS 'ducklake:https://data-directory.fsn1.your-objectstorage.com/catalog/catalog.ducklake' AS "datadirectory" (READ_ONLY);

SELECT administrator,
       count(*) AS faults,
       round(avg(announced_duration_days), 1) AS announced_days,
       round(avg(date_diff('day', first_seen_date, last_seen_date)), 1) AS observed_days
FROM datadirectory.pmb_incidents.faults
WHERE start_date >= '2026-08-01' AND start_date < '2026-09-01'
GROUP BY administrator
ORDER BY faults DESC
```

## Live lake — DuckLake catalog with time travel

Attach the published catalog read-only over plain https. This works in native DuckDB AND in
duckdb-wasm in the browser — the reader catalog resolves its data files through public https
URLs, so no credentials and no backend are involved.

```sql
INSTALL ducklake; LOAD ducklake;
ATTACH 'ducklake:https://data-directory.fsn1.your-objectstorage.com/catalog/pmb-incidents/catalog.ducklake' AS pmb_incidents (READ_ONLY); SELECT * FROM pmb_incidents.main.faults LIMIT 5;
USE pmb_incidents;
```

- Latest catalog: https://data-directory.fsn1.your-objectstorage.com/catalog/pmb-incidents/catalog.ducklake
- This exact version: https://data-directory.fsn1.your-objectstorage.com/catalog/pmb-incidents/v/01M54QHE2CXY06X14R4KZZQX2B/catalog.ducklake (sha256 035035f9a1ad15ab2be3e2ac9646318a122da14b358449ce3a1c8b8758a1a323)
- Written by DuckDB v1.5.5
- Time travel: this ingest is snapshot 21 — e.g. `SELECT * FROM pmb_incidents.faults AT (VERSION => 21)`

## Wholesale — S3 access

For bulk downloads, read the bucket prefix directly over S3 with httpfs:

```sql
INSTALL httpfs; LOAD httpfs;
CREATE SECRET hetzner (
  TYPE s3,
  ENDPOINT 'fsn1.your-objectstorage.com',
  URL_STYLE 'path',
  KEY_ID '<public-read-not-required>', SECRET ''
);
SELECT * FROM read_parquet('s3://data-directory/public/pmb-incidents/v/01M54QHE2CXY06X14R4KZZQX2B/**/*.parquet');
```

## Schema

### faults
One row per announced utility fault or planned works site in Bucharest — sewerage, gas, district heating, consumer electricity, the road surface itself and the tram and trolleybus traction network — as last published on the city's interactive map. The map shows only what is currently announced and keeps no archive; first_seen_date / last_seen_date record the window over which this connector observed each fault, which is the closest thing to a real duration the source admits of.
- 731 rows · 40 KB · write mode `upsert`
- Physically sorted by: `start_date`, `street_lc`, `fault_id`
- Primary key: `fault_id`
- Text search columns: `street`, `location_note`
- Files: 1
  - https://data-directory.fsn1.your-objectstorage.com/public/pmb-incidents/v/01M54QHE2CXY06X14R4KZZQX2B/faults.parquet (731 rows)

Columns:
- `fault_id` BIGINT NOT NULL — The fault's id in the City Hall database (the source's `id` field). The primary key, and stable across fetches. Note that the WFS feature id in the same payload ("v_hip_avarii.fid-…") is NOT stable — it is regenerated on every request — so this is the only usable identifier. (range: "523979" … "527509") (~607 distinct)
- `announced_date` DATE — The day the fault was announced (source field `data_anunt`) — the Europe/Bucharest calendar day, since that is the source's own clock.
- `announced_at` TIMESTAMPTZ — The announcement as an instant, exactly as the source sends it in `data_anuntx`. It is midnight of the announcement day in Romanian local time expressed in UTC, which is why 31.08.2026 appears here as 2026-08-30T21:00:00Z. Filter on announced_date, not on this.
- `start_date` DATE — The day works are announced to start (source field `data_inceput`), the Europe/Bucharest calendar day. This is the leading sort column, so a date range on it is the cheapest filter this table has. (range: "2026-08-30" … "2026-10-16")
- `end_date` DATE — The day works are announced to finish (source field `data_sfarsit`), the Europe/Bucharest calendar day. It is the network operator's own estimate, not a confirmed completion date.
- `announced_duration_days` INTEGER [days] — The announced length of the works in whole days, derived by this connector as end_date − start_date. A single-day job is 0. Null when either date is missing.
- `network_type` VARCHAR — Which network is affected, in the source's own words. Six values were observed on 2026-09-02, with their network_type_id in brackets: Termoficare (4, district heating), Reţea stradală (8, the road surface itself), Canalizare (2, sewerage), Gaze (3, gas), Electricitate-consumatori (7, electricity to consumers) and Transport in comun cu tractiune electrica (33, the tram and trolleybus traction network). The set is the source's own lookup table and may grow.
- `network_type_id` INTEGER — The numeric code behind network_type (source field `id_z_tip_retele`, a foreign key into the City Hall's `v_z_tip_retele` lookup). Group on this rather than on the text when you want a grouping that cannot be broken by a spelling change.
- `administrator` VARCHAR — The network operator responsible, as free text — "Apa Nova - Directia Retele Canal - Zona 2", "Distrigaz Sud - Sectoarele 1 si 2", "C.M.T.E.B SA - Sectia Distributie Sector 3 - Titan". The source usually appends a sub-unit, and often a trailing dash left over from an empty field.
- `street` VARCHAR — The street being worked on, verbatim from the source field `artera`.
- `street_lc` VARCHAR — street, lowercased and accent-folded, for case- and accent-insensitive search. Query this, not lower(street) — a function on a column cannot use parquet statistics. It also papers over the source mixing the legacy cedilla forms ş/ţ with the correct ș/ț. (range: "aleea argedava" … "strada zizin")
- `street_id` BIGINT — The street's code in the City Hall street register (source field `id_v_ar_artere`). The safest way to gather every fault on one street, because it does not depend on spelling.
- `street_number` VARCHAR — The postal number or range, as the source gives it in `nr_postal`, e.g. "63-65". Absent for about a quarter of faults.
- `location_note` VARCHAR — A free-text refinement of the location (source field `localizare`), e.g. "x Viitorului", where "x" means "at the junction with". Absent for more than half of the faults.
- `location_note_lc` VARCHAR — location_note, lowercased and accent-folded, for search. Query this, not lower(location_note).
- `latitude` DOUBLE [degrees] — Latitude of the fault in WGS 84 (EPSG:4326), in degrees. Computed by this connector from the source's Stereo-70 coordinates — see the licence notes for the method and its accuracy.
- `longitude` DOUBLE [degrees] — Longitude of the fault in WGS 84 (EPSG:4326), in degrees. Computed the same way as latitude.
- `stereo70_easting` DOUBLE [m] — The easting (Y axis) in metres exactly as published, in the Romanian national projected CRS EPSG:3844 (Pulkovo 1942(58) / Stereo 70). Kept unmodified so a reuser can redo the reprojection with different parameters or a licensed grid.
- `stereo70_northing` DOUBLE [m] — The northing (X axis) in metres exactly as published, in EPSG:3844. Beware: GeoJSON writes the pair as [easting, northing] while the GML form of the same response writes [northing, easting]; these two columns are named explicitly so the two can never be confused.
- `first_seen_date` DATE NOT NULL — The first run day (UTC) on which this fault appeared on the map. Maintained automatically by the upsert loader — never overwritten, only widened.
- `last_seen_date` DATE NOT NULL — The most recent run day (UTC) on which the map still carried this fault. With first_seen_date it gives the observed duration of the works, which is not necessarily the announced start_date – end_date window.


## Performance notes

- There are no indexes in a lakehouse. The physical sort order plus Parquet row-group min/max
  statistics ARE the index. Predicates on the leading sort columns prune whole row groups;
  everything else is a full column scan.
- `faults` prunes on `start_date` → `street_lc` → `fault_id`.
- `ILIKE '%needle%'` cannot prune: it decompresses that whole column. Prefer prefix predicates
  on the lowercased search companion columns where they exist.
- Every file URL under `/v/<ingestId>/` is immutable. Cache it as aggressively as you like.
- Files are never rewritten in place — a new ingest writes a new version path. Re-read the
  manifest to pick up updates rather than polling the file URLs.
- Pull only the columns you need. Parquet is columnar and the reader range-GETs what it reads.

## Do not

- Do not scrape https://data.bunnytech.app HTML. Every number on those pages comes from the manifest above.
- Do not hit the upstream source (https://hip.pmb.ro/hip/?MapGuid=72d6f523-5edb-427f-9b0e-3e03b6ba177f&SiteGuid=76c2a7a5-9eab-4a3f-9b04-fd89109b19e4) in bulk. This mirror exists so you do not have to.
- Do not read DuckLake internal data files with a plain Parquet reader. They come with
  positional delete files, and ignoring those silently returns deleted rows. Use the ducklake
  extension, or use the published exports above.
- Do not republish without the attribution line, and do not imply endorsement.