Working with CNO • realestatebr

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.

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")

cno

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()

The source may repeat a work’s total area across several classifications, and it contains extreme area values. The package preserves these source records. Do not assume that summing every areas$area row produces an unbiased measure of built area.

Let’s look at CNO "900143355070". This case shows how exact duplicate rows affect a join between the constructions and areas tables.

construction_case <- cno$constructions |>
  filter(cno == "900143355070") |>
  select(cno, municipality_name, state, total_area)

area_case <- cno$areas |>
  filter(cno == "900143355070")

case <- construction_case |>
  inner_join(area_case, by = "cno") |>
  collect()

case |>
  select(
    cno,
    municipality_name,
    total_area,
    destination,
    area_type,
    complementary_area_type,
    area
  )

# A tibble: 8 × 7
#   cno          municipality_name total_area destination               area_type    complementary_area_type            area
#   <chr>        <chr>                  <dbl> <chr>                     <chr>        <chr>                             <dbl>
# 1 900143355070 LONDRINA              18719. Residencial multifamiliar Complementar Quadra Esportiva e Poliesportiva   160
# 2 900143355070 LONDRINA              18719. Residencial multifamiliar Complementar Estacionamento Térreo             3076.
# 3 900143355070 LONDRINA              18719. Residencial multifamiliar Complementar Piscina                            136.
# 4 900143355070 LONDRINA              18719. Residencial multifamiliar Principal    NA                               15347.
# 5 900143355070 LONDRINA              18719. Residencial multifamiliar Complementar Quadra Esportiva e Poliesportiva   160
# 6 900143355070 LONDRINA              18719. Residencial multifamiliar Complementar Estacionamento Térreo             3076.
# 7 900143355070 LONDRINA              18719. Residencial multifamiliar Complementar Piscina                            136.
# 8 900143355070 LONDRINA              18719. Residencial multifamiliar Principal    NA                               15347.

These duplicates are present in the original CNO areas dataset. realestatebr currently reproduces them exactly. For most use cases, these duplicate rows will lead to errors, so we advise removing them using, for instance, dplyr::distinct().

Removing the duplicate rows, in this case, would also make the sum of the individual area column from areas coincide with total_area from constructions. This will not always be the case.

construction_case <- cno$constructions |>
  filter(cno == "900143355070") |>
  select(cno, municipality_name, state, total_area)

area_case <- cno$areas |>
  filter(cno == "900143355070") |>
  distinct()

case <- construction_case |>
  inner_join(area_case, by = "cno") |>
  collect()

all.equal(unique(case$total_area), sum(case$area))
#> [1] TRUE

Future versions of realestatebr may add validation steps that remove exact duplicate rows and other noise from these tables.

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.

Municipality identifiers

municipality_tom_code is Receita Federal’s four-digit TOM code. It is not the seven-digit IBGE municipality code used by geobr and most Brazilian regional datasets. A TOM-to-IBGE crosswalk is required before joining those sources.

Raw values and snapshot versions

The tables normalize headers, dates, numeric fields, identifiers, and text encoding. They otherwise preserve the source. This includes unusual state values, historical date sentinels, area outliers, status codes, and reported category labels.

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.

cno_2026_09_13 <- query_dataset("cno", version = "2026-09-13")
close(cno_2026_09_13)

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

close(cno)