Obter a legislação do dre.tretas.org – formato SQLite

Posted on by

SQLite é uma biblioteca que implementa um motor de base de dados SQL, de pequeno tamanho, rápido, sem dependências externas, completo em funcionalidades, com alto desempenho e fiabilidade.

Tanto que assim é que a Biblioteca do Congresso dos EUA recomenda o formato desta base de dados para armazenamento a longo prazo de conjuntos de dados.

Ao usar este formato temos grande facilidade em filtrar, converter e conjugar com outros dados a informação disponibilizada.

Assim decidi disponibilizar os dados em bruto do dre.tretas.org em mais este formato. (Ver este post para uma descrição dos outros formatos disponíveis.)

A seguir mostro como se pode obter os dados neste formato e dou alguns exemplos de formas de explorar estes dados.

Obter o ficheiro com os dados

Os dados do dre.tretas.org são exportados todos os Domingos e disponibilizados neste endereço. Basta descarregar o ficheiro ????-??-??-DRE.sqlite3.bz2.

Este ficheiro está comprimido usando o utilitário bzip2. Deve encontrar o utilitário adequado para descomprimir o ficheiro no seu sistema.

Estrutura dos dados

São disponibilizadas três tabelas:

  • dreapp_document – meta informação dos documentos;
  • dreapp_documenttext – texto dos documentos no formato obtido no site do Diário da República Electrónico;
  • dreapp_document_connects_to – registo das ligações entre documentos. Se um documento faz referência a outro documento, essa relação é registada nesta tabela.

Tabela dreapp_document:

  • id – chave primária desta tabela, trata-se de um identificador que especifica completamente um registo;
  • conditional – documento em vigência condicional. Notar que os dados apresentados nesta coluna são aqueles que eram verdadeiros na altura em que os dados foram obtidos (ver timestamp);
  • date – data do documento;
  • doc_type – tipo do documento, por exemplo “Lei” ou “Aviso”. Notar que a nomenclatura dos documentos muda ao longo das décadas;
  • dre_pdf – URL para o PDF do documento no site oficial;
  • dr_number – número do Diário da República;
  • emiting_body – corpo emitente. Notar que a nomenclatura muda ao longo do tempo, se procurarmos por “Ministério da Saúde” não quer dizer que apanhemos todos os documentes deste emitente;
  • in_force – documento em vigência. Notar que os dados apresentados nesta coluna são aqueles que eram verdadeiros na altura em que os dados foram obtidos (ver timestamp);
  • notes – sumário do documento;
  • number – número do documento;
  • part – parte do documento. É usada uma letra ou número para identificar a parte do DR:
    • 1 – Série 1;
    • A – Presidência da República;
    • B – Assembleia da República;
    • C – Governo e Administração directa e indirecta do Estado;
    • D – Tribunais e Ministério Público;
    • E – Entidades administrativas independentes e Administração autónoma;
    • F – Regiões Autónomas;
    • G – Empresas públicas;
    • H – Autarquias locais;
    • I – Outras entidades;
    • J1 – Administração Pública – Concursos para Cargos Dirigentes;
    • J2 – Administração Pública – Alterações Excepcionais de Posições Remuneratórias;
    • J3 – Administração Pública – Relações Colectivas de Trabalho;
    • L – Contratos públicos;

    Notar que só temos disponível esta classificação para documentos a partir de 2 de Maio de 2013.

  • series – série do DR onde o documento foi publicado (1, 2 ou 3)
  • source – origem do documento, o formato deste campo mudeou ao longo do tempo, um exemplo de 2023: “Diário da República n.º 154/2023, Série I de 2023-08-09”;
  • timestamp – data e hora em que o documento foi obtido.

Tabela dreapp_documenttext:

  • id – chave primária desta tabela;
  • document_id – ligação à tabela dreapp_document;
  • timestamp – data e hora de obtenção do texto do documento;
  • text_url – URL de onde foi obtido o texto do documento. Infelizmente, na INCM, nunca houve grande preocupação de manter os URLs ao longo do tempo. Assim, estes URLs de textos mais recentes são válidos mas, os mais antigos, já não são;
  • text – texto do documento como foi obtido do site oficial.

Tabela dreapp_document_connects_to:

  • id – chave primária desta tabela;
  • from_document_id – ligação à tabela dreapp_document. Identifica o documento que faz a citação;
  • to_document_id – ligação à tabela dreapp_document. Identifica o documento citado.

Exemplos

Os exemplos seguintes foram executados usando a ferramenta ‘sqlite3’. Pode-se obter essa ferramenta aqui.

1. Obter uma lista dos “Anúncios de Procedimento” do Município de Portalegre no mês de Agosto de 2018:

A seguinte query:

select
    id, doc_type, number, date
from
    dreapp_document dd
where
    dd.emiting_body = 'Município de Portalegre'
    and dd.date between '2018-08-01' and '2018-08-31'
    and dd.doc_type = 'Anúncio de procedimento';

Vai devolver:

3452156|Anúncio de procedimento|7108/2018|2018-08-30
3453649|Anúncio de procedimento|7131/2018|2018-08-31

Notar que o SQLite permite apresentar os dados usando outros formatos, por exemplo JSON, ou CSV. Assim podemos facilmente importar dados para uma folha de cálculo, assim que tivermos a query adequada.

2. Listar os documentos que citam um dado documento

Por exemplo, os documento que citam o documento com id=2631150, são dados pela query:

select
    dd.id, dd.doc_type, dd.number, dd.date, dd.series
from
    dreapp_document_connects_to ct
    left join dreapp_document dd on dd.id = ct.from_document_id
where
    ct.to_document_id = 2631150
order by
    dd.date

Os dados devolvidos são (em formato CSV):

2684215,"Despacho Normativo",7/2016,2016-08-02,2
2741631,"Resolução do Conselho de Ministros",55/2016,2016-09-27,1
3057144,Decreto-Lei,98/2017,2017-08-10,1
3065631,Portaria,259-A/2017,2017-08-21,1
3137131,Portaria,330-A/2017,2017-10-31,1
3159131,Portaria,359-B/2017,2017-11-21,1
3298635,Portaria,95/2018,2018-04-06,1
3534631,Portaria,300-A/2018,2018-11-22,1
3861132,Portaria,328/2019,2019-09-24,1
4325310,"Aviso (extrato)",19105/2020,2020-11-23,2
4852177,Portaria,117-A/2022,2022-03-21,1
5700277,Aviso,7088/2024/2,2024-04-02,2

3. Número de leis publicadas por ano, desde 1 de Janeiro de 2000

select
    strftime('%Y', date) as "Ano",
    count(1) as "Número"
from
    dreapp_document dd
WHERE
    dd.date >= '2000-01-01'
    and dd.doc_type = 'Lei'
GROUP BY
    strftime('%Y', date)
ORDER BY
    strftime('%Y', date)

O resultado em JSON é:

[{"Ano":"2000","Número":43},
{"Ano":"2001","Número":121},
{"Ano":"2002","Número":38},
{"Ano":"2003","Número":113},
{"Ano":"2004","Número":57},
{"Ano":"2005","Número":63},
{"Ano":"2006","Número":60},
{"Ano":"2007","Número":73},
{"Ano":"2008","Número":70},
{"Ano":"2009","Número":122},
{"Ano":"2010","Número":60},
{"Ano":"2011","Número":69},
{"Ano":"2012","Número":70},
{"Ano":"2013","Número":87},
{"Ano":"2014","Número":102},
{"Ano":"2015","Número":172},
{"Ano":"2016","Número":46},
{"Ano":"2017","Número":118},
{"Ano":"2018","Número":71},
{"Ano":"2019","Número":124},
{"Ano":"2020","Número":87},
{"Ano":"2021","Número":110},
{"Ano":"2022","Número":32},
{"Ano":"2023","Número":85},
{"Ano":"2024","Número":31}]

Nota, para escolher o modo de output do sqlite3 usar, por exemplo:

  • .mode csv
  • .mode json
  • .mode table
  • etc…

Consultar o manual do sqlite3 para mais informação.

Deixe um comentário