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.
library(realestatebr)
library(dplyr)
cno <- query_dataset("cno")
cnoThe 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)