Skip to contents

The Cadastro Nacional de Obras (CNO) is a relational dataset. One table describes each registered construction work, while three child tables record areas, CNAE activities, and responsibility periods. realestatebr queries these tables lazily with DuckDB, so filtering and aggregation happen before data enter R memory.

realestatebr publishes CNO as a curated snapshot layer. It normalizes headers, types, and text, and fixes a small set of mechanical source defects (described in Raw values and snapshot versions) while retaining valid but anomalous values such as area outliers and unusual state labels.

Open the dataset

Install the optional query dependencies once.

install.packages(c("DBI", "dbplyr", "duckdb"))

Open all related tables through one catalog.

The catalog contains four lazy tables.

Table Grain
constructions One row per CNO registration
areas One row per reported area classification
cnaes One row per CNAE registration
responsibilities One row per responsibility period

All four tables use the twelve-digit character column cno as their join key. Keeping the tables in the same catalog ensures that joins run inside one DuckDB connection.

Filter before collecting

The object returned by a dplyr pipeline remains lazy. collect() executes the query and returns its result as an in-memory tibble.

constructions_sp <- cno$constructions |>
  filter(
    state == "SP",
    registration_date >= as.Date("2025-01-01")
  ) |>
  select(cno, municipality_name, registration_date, status_code)

constructions_sp

constructions_sp |>
  collect()

Select only the columns needed by the analysis. DuckDB can then avoid reading unused Parquet columns over the network.

Join constructions and areas

Each construction can have several area rows. The following query returns residential area records for construction works in São Paulo.

residential_areas <- cno$areas |>
  filter(destination == "Residencial unifamiliar") |>
  select(cno, construction_category, structure_type, area_type, area)

sp_residential <- constructions_sp |>
  inner_join(residential_areas, by = "cno") |>
  collect()

A work’s area is often reported across several rows: one row for the principal area and additional rows for each complementary area. Classificatory repetition therefore remains after snapshot cleaning: the repeated total_area-inflating duplicates are removed, but summing rows across classifications still differs from constructions$total_area in general. sum() demonstrates the join pattern, not a general cleaning rule.

The snapshot build also drops exact duplicate rows in areas. Each removal is counted in the snapshot manifest, as described in Raw values and snapshot versions.

Combine several child tables safely

Joining areas and cnaes directly can multiply rows. A work with two area rows and three CNAE rows produces six joined rows. Aggregate each child table to one row per CNO before combining them when the analysis needs one row per work.

area_summary <- cno$areas |>
  group_by(cno) |>
  summarise(
    area_records = n(),
    reported_area = sum(area, na.rm = TRUE),
    .groups = "drop"
  )

cnae_summary <- cno$cnaes |>
  group_by(cno) |>
  summarise(
    cnae_records = n(),
    .groups = "drop"
  )

construction_summary <- cno$constructions |>
  filter(state == "SP") |>
  left_join(area_summary, by = "cno") |>
  left_join(cnae_summary, by = "cno") |>
  collect()

The appropriate area aggregation depends on the research question. sum() above demonstrates the relational pattern; it is not a general cleaning rule.

Join to geobr

constructions includes code_muni, the seven-digit IBGE municipality code joined from the Receita Federal TOM-to-IBGE table, so CNO joins directly to geobr and most Brazilian regional datasets without an extra crosswalk. The original four-digit municipality_tom_code is retained for provenance.

work_counts <- cno$constructions |>
  group_by(code_muni) |>
  summarise(works = n()) |>
  collect()

# geobr::read_municipality(code_muni = ...) accepts these codes directly,
# so spatial joins need no extra crosswalk.

code_muni is NA for works declared abroad and for the rare TOM codes missing from that table. Among construction rows with a nonempty domestic TOM code, the build aborts when more than 0.1% have no IBGE match. Rows with the exterior TOM code (9707) are excluded from this threshold.

Raw values and snapshot versions

The tables normalize headers, dates, numeric fields, identifiers, and text encoding, trim surrounding whitespace, and fix these mechanical source defects:

Source defect Published fix
Sentinel dates 1900-01-01 Converted to NA in every date column
Invalid Plus Codes (e.g. 00000000+00) Converted to NA; valid codes stored in upper case
Exact duplicate rows in areas Dropped; each removal counted in the manifest
Whitespace-only text Converted to NA

Valid but anomalous values are retained. This includes unusual state values, area outliers, status codes, and reported category labels. The manifest records sentinel-date replacements, invalid Plus Codes, unmatched municipalities, and removed duplicate area rows, along with source and published row counts. Whitespace trimming and whitespace-to-NA conversions are not counted.

query_dataset("cno") resolves "latest" once and pins that immutable snapshot while the catalog remains open. Supply a published version explicitly when an analysis must be reproducible.

# Version identifiers are snapshots published by the build workflow; the date
# below is illustrative. Check
# https://github.com/viniciusoike/realestatebr/releases for published versions.
cno_pinned <- query_dataset("cno", version = "2026-10-15")
close(cno_pinned)

The current schema introduced code_muni and the defect fixes, so pinned snapshots published before it still carry the sentinel dates, duplicate areas rows, and TOM-only municipality identifiers. Pin a version from this schema onward when reproducibility matters; package versions up to 1.2.0 resolve those older snapshots.

Close the catalog after the final query. Any lazy tables obtained from it become invalid after close().

close(cno)