Brazil Para Car Semas Sicar Join 2025 01

s3://trase-storage/brazil/spatial/car/pa/silver/semas_sicar_join/semas_sicar_join_2025_01.parquet

Dbt path: trase_production.main_brazil.brazil_para_car_semas_sicar_join_2025_01

Explore on Metabase: Full table; summary statistics

Containing yaml file link: trase/data_pipeline/models/brazil/spatial/car/_schema_brazil_car.yml

Model file link: trase/data_pipeline/models/brazil/spatial/car/brazil_para_car_semas_sicar_join_2025_01.sql

Dbt test runs & lineage: Test results · Lineage

Full dbt_docs page: Open in dbt docs (includes lineage graph -at the bottom right-, tests, and downstream dependencies)

Tags: brazil, car, semas, sicar, 2025

brazil_para_car_semas_sicar_join_2025_01

Description

Including here the description of * Brazil spatial CAR data * Pará (PA) spatial CAR data * Brazil Pará SICAR - SEMAS Join

Brazil spatial CAR data

The Rural Environmental Registry (CAR - Cadastro Ambiental Rural in Portuguese) is a public electronic registry that gathers georeferenced data on rural properties across Brazil.

What the dataset is

The CAR datasets are shapefiles containing polygons representing rural properties along with basic attribute information for each farm.

For some states, local authorities such as the 'Secretaria de Meio Ambiente' (Secretary of the Environment, SEMAS for its acronym in Portuguese) provide additional information, such as the CNPJ numbers associated with the properties.

CAR Registration process (more context about the data)

The Rural Environmental Registry (CAR – Cadastro Ambiental Rural in Portuguese) is a mandatory electronic public registry for all rural properties in Brazil, regardless of their size. It was created to integrate environmental data from rural lands and support efforts to monitor and combat illegal deforestation.

Registration is free and must be completed online by the property owner. With all required documents in hand, the owner fills out an electronic form on the SiCAR website. This form includes detailed information such as property boundaries, total area, the portion designated as a Legal Reserve, and other relevant environmental data. Once the form and supporting documents are submitted, they are reviewed by the state’s environmental agency. If everything is in order, the registry is validated, and the owner receives an official document confirming the property's registration.

During this validation process, the agency may approve the registration or require corrections or environmental regularisation. As a result, the registration status can be classified as "Active", "Pending", or "Cancelled". If environmental liabilities are found, the status changes from active to pending, and the owner must resolve the issues. To regularize the situation, the owner may need to join the Environmental Regularisation Program (PRA) or sign a Conduct Adjustment Agreement (TAC). Once this is done, the status can return to active.

The CAR must be updated whenever there are changes in ownership, possession, or legal reserve designation. Failure to update the registry may result in legal consequences, including environmental penalties, and potential civil or criminal liability.

How we use the dataset in Trase, for example, which SEI-PCS models it is involved in

Having the polygons of rural properties enable different spatial analysis, such as calculating distances between them and points of interest (logistics hubs, areas with deforestation), among others.

The CAR data for the state of Pará (PA) is utilised in the CIFF (Children's Investment Fund Foundation) project, around traceability and transparency in the cattle supply chains within Brazil.

Additionally, CAR data from the states of Mato Grosso (MT), Roraima (RO), São Paulo (SP), and Mato Grosso do Sul (MS) were used in 2025 Q1 to analyse deforestation linked to suppliers of a major slaughterhouse in Brazil.

How often the dataset is updated, and when the next update is likely to be

The SICAR (Sistema Nacional de Cadastro Ambiental Rural - National Rural Environmental Registry System) source does not indicate the frequency of data updates. However, it is possible to check the last update date for each state individually.

How to re-fetch the dataset from the original source

Manual download

There are two main sources for downloading CAR data: the SICAR website, which provides geometric information (property boundaries), and local websites, which contain additional details such as CAR IDs and CNPJs.

The SICAR data can be manually downloaded from consultapublica.car.gov.br, where users can select a state and download the "Perímetros dos imóveis" (property boundaries).

For the state of Pará, the SEMAS website (car.semas.pa.gov.br) provides CAR IDs and CNPJs, which can be merged with the geometry data to enhance the dataset. Once these datasets are combined, they allow for linking CAR data with other sources, such as the GTA (Animal Transport Guide) database, enabling more comprehensive analysis. It is important to note that accessing the SEMAS website requires a VPN, as it is only available from Brazil.

The script that is used to process/clean the dataset

For Pará data, the SICAR and SEMAS are joined in the brazil_para_car_semas_sicar_join_2025_01 dbt model (link to a specific commit, check if there is a latest version)

When the dataset was last updated, and by whom

Each state is downloaded independently. * MG SICAR data: Thais in January 2025 * MT SICAR data: Thais in January 2025 * SP SICAR data: Thais in January 2025 * RO SICAR data: Thais in January 2025 * MS SICAR data: Thais in Feburary 2025 * GO SICAR data: Thais in March 2025 * PA SICAR data: Nicolás January 2025 * PA SEMAS data: Osvaldo January 2025

Pará (PA) spatial CAR data

When the dataset was last updated, and by whom

PA SICAR data: Nicolás January 2025
PA SEMAS data: Osvaldo January 2025

Important Note

As the CIFF project is currently focused on the state of Pará, specific data manipulation processes are required for this region. Pará is one of the few states that allows the integration of CAR data with identification details, such as CNPJs, which is essential for linking CAR data with other datasets used to track animal movements between farms (GTA). As mentioned in the Brazil spatial CAR data documentation, the geometric information of CAR data is obtained from the SICAR website, while the CNPJs and CAR ID details, necessary for merging with SICAR data, are sourced from the SEMAS website.

Brazil Pará SICAR - SEMAS Join

Joins the SEMAS (Secretaria de Estado de Meio Ambiente e Sustentabilidade) and SICAR data for Pará.

The SEMAS data contains the property owner's name, property name, and CAR registration number. The SICAR data contains the property geometries and the property owner name, property name, and CAR registration number. They are joined by the CAR registration number.

A history of changes/notes of the dataset

January 2025: downloaded by Nicolás

Some numerical summaries of the joint data

There are 304,192 unique CAR records.
The top three municipalities with the highest number of CAR registrations are SÃO FÉLIX DO XINGU, MARABÁ, and NOVO REPARTIMENTO, with 10,523, 9,982, and 9,665 records, respectively.
According to data from SICAR, the average property size is 270.63 hectares, while the average reported area is 271.09 hectares. This minor discrepancy is not a concern and indicates consistency between the datasets.
SICAR database: 216,954 active CAR records; 69,892 pending verification; 15,385 cancelled; and 1,483 suspended entries.
Among the active records, the total property area is 55,952,958.24 hectares. Of this total, 2,195,489.95 hectares (3.92%) correspond to permanent preservation areas, while 24,301,093.79 hectares (43.43%) are designated as legal reserves.

More of the data can be explored through this question at Metabase: https://metabase.deforestationfree.com/question/178-pa-car-data

Details

ColumnsDepends OnModel code

Column	Type	Description
`semas_num_car`	`VARCHAR`
`semas_municipio`	`VARCHAR`
`semas_cpf_cnpj_proprietario`	`VARCHAR`
`semas_nome_proprietario`	`VARCHAR`
`semas_nome_propriedade`	`VARCHAR`
`semas_localizacao`	`VARCHAR`
`semas_area_total_propriedade`	`DOUBLE`
`semas_remanescente_vegetacao_nativa`	`DOUBLE`
`semas_reserva_legal`	`DOUBLE`
`semas_preservacao_permanente`	`DOUBLE`
`semas_area_uso_consolidado`	`DOUBLE`
`semas_area_uso_restrito`	`DOUBLE`
`semas_area_servidao_administrativa`	`DOUBLE`
`semas_compromissos_ambito_programa_regularizacao_ambiental`	`DOUBLE`
`semas_situacao`	`VARCHAR`
`sicar_cod_tema`	`VARCHAR`
`sicar_nom_tema`	`VARCHAR`
`sicar_cod_imovel`	`VARCHAR`
`sicar_mod_fiscal`	`DOUBLE`
`sicar_num_area`	`DOUBLE`
`sicar_ind_status`	`VARCHAR`
`sicar_ind_tipo`	`VARCHAR`
`sicar_des_condic`	`VARCHAR`
`sicar_municipio`	`VARCHAR`
`sicar_cod_estado`	`VARCHAR`
`sicar_dat_criaca`	`VARCHAR`
`sicar_dat_atuali`	`VARCHAR`
`sicar_geom`	`GEOMETRY`
`semas_localizacao_point`	`GEOMETRY`
`sicar_centroid`	`GEOMETRY`

Models / Seeds

source.trase_duckdb.source_brazil.para_car_semas_2025_01_17
source.trase_duckdb.source_brazil.para_car_sicar_2025_01_30

Macros
- macro.trase_duckdb.excel_extension

Sources

['source_brazil', 'para_car_semas_2025_01_17']
['source_brazil', 'para_car_sicar_2025_01_30']

No called script or script source not found.

-- The excel extension relies on the spatial extension
{{  
    config(
        pre_hook='{{ excel_extension() }}',
        materialized='external'
    ) 
}}

SELECT DISTINCT
    semas."Nº CAR" AS semas_num_car,
    semas."Município" AS semas_municipio,
    semas."CPF / CNPJ Proprietário" AS semas_cpf_cnpj_proprietario,
    semas."Nome / Razão Social Proprietário" AS semas_nome_proprietario,
    semas."Nome / Razão Social Propriedade" AS semas_nome_propriedade,
    semas."Localização" AS semas_localizacao,
    semas."Área Total Propriedade" AS semas_area_total_propriedade,
    semas."Área Remanescente Vegetação Nativa" AS semas_remanescente_vegetacao_nativa,
    semas."Área Reserva Legal" AS semas_reserva_legal,
    semas."Área Preservação Permanente" AS semas_preservacao_permanente,
    semas."Área Uso Consolidado" AS semas_area_uso_consolidado,
    semas."Área Uso Restrito" AS semas_area_uso_restrito,
    semas."Área Servidão Administrativa" AS semas_area_servidao_administrativa,
    semas."Adesão Compromissos Âmbito Programa Regularização Ambiental" AS semas_compromissos_ambito_programa_regularizacao_ambiental,
    semas."Situação" AS semas_situacao,
    sicar.cod_tema AS sicar_cod_tema,
    sicar.nom_tema AS sicar_nom_tema,
    sicar.cod_imovel AS sicar_cod_imovel,
    sicar.mod_fiscal AS sicar_mod_fiscal,
    sicar.num_area AS sicar_num_area,
    sicar.ind_status AS sicar_ind_status,
    sicar.ind_tipo AS sicar_ind_tipo,
    sicar.des_condic AS sicar_des_condic,
    sicar.municipio AS sicar_municipio,
    sicar.cod_estado AS sicar_cod_estado,
    sicar.dat_criaca AS sicar_dat_criaca,
    sicar.dat_atuali AS sicar_dat_atuali,
    sicar.geom AS sicar_geom,
    ST_POINT(
        CAST(SUBSTRING(semas_localizacao FROM POSITION('Longitude: ' IN semas_localizacao) + 11) AS DOUBLE),
        CAST(SUBSTRING(semas_localizacao FROM POSITION('Latitude: ' IN semas_localizacao) + 10 FOR POSITION('/' IN semas_localizacao) - POSITION('Latitude: ' IN semas_localizacao) - 10) AS DOUBLE)
    ) AS semas_localizacao_point,
    ST_CENTROID(sicar_geom) AS sicar_centroid
FROM {{ source('source_brazil', 'para_car_semas_2025_01_17') }} AS semas
LEFT JOIN {{ source('source_brazil', 'para_car_sicar_2025_01_30') }} AS sicar
    ON semas."Nº CAR" = sicar.cod_imovel