A database of public bodies (or organizations):
Government-run or controlled organizations or entities which may or may not have distinct corporate existence
Examples are:
- Government Ministries or Departments
- State-run Health organizations
- Police and fire departments
Data is stored in CSVs partitioned by country or region (e.g. EU). Directories are named by ISO code.
Please just add a CSV file and submit a pull request or open an issue.
The set of fields required in the CSV file can be seen in the field list on:
http://data.okfn.org/community/okfn/publicbodies (if you like JSON you look
directly at the datapackage.json file in this repo - you can also check out
the existing data in data/).
The website is a node webapp. To get it running:
-
Install node and npm (>= 0.8)
-
Get the code
git clone https://github.com/okfn/publicbodies -
Install the dependencies (make sure you are in the publicbodies directory)
npm install . -
Run the webapp:
node index.js
The list of outstanding issues is at: https://github.com/okfn/publicbodies/issues
Details of the automated data extraction to build the original database.
Data sources:
- A-Z Index of U.S. Government Departments and Agencies - http://www.usa.gov/directory/federal/index.shtml
- AskTheEU.org
- FragDenStaat.de - (private GoogleDoc) - http://www.bund.de/DE/Behoerden
- WhatDoTheyKnow.com - http://www.whatdotheyknow.com/body/all-authorities.csv
- Brazilian Government SIORG - http://www.siorg.redegoverno.gov.br/ - xml export at http://repositorio.dados.gov.br/governo-politica/administracao-publica/estrutura-organizacional/
Schema:
title
abbr
key
slug
category
parent
parent_key
description
url
jurisdiction
jurisdiction_code
source
source_url
source_description
address
contact
email
tags
created_at
updated_at
SQL
ALTER TABLE froide_de ADD COLUMN source;
ALTER TABLE froide_de ADD COLUMN source_description;
ALTER TABLE froide_de ADD COLUMN source_url;
UPDATE froide_de SET source = 'FragDenStaat / Bund Online';
UPDATE froide_de SET source_url = 'https://fragdenstaat.de/hilfe/';
UPDATE froide_de SET source_description = 'Federal-level agencies of the German government';
ALTER TABLE froide_de ADD COLUMN jurisdiction;
ALTER TABLE froide_de ADD COLUMN jurisdiction_code;
UPDATE froide_de SET jurisdiction = 'Germany';
UPDATE froide_de SET jurisdiction_code = 'DE';
ALTER TABLE ateu_en ADD COLUMN source;
ALTER TABLE ateu_en ADD COLUMN source_description;
ALTER TABLE ateu_en ADD COLUMN source_url;
UPDATE ateu_en SET source = 'Ask the EU (AccessInfo)';
UPDATE ateu_en SET source_url = 'http://www.asktheeu.org/de/help/api';
UPDATE ateu_en SET source_description = 'European-level FoI request tracker';
ALTER TABLE ateu_en ADD COLUMN jurisdiction;
ALTER TABLE ateu_en ADD COLUMN jurisdiction_code;
UPDATE ateu_en SET jurisdiction = 'European Union';
UPDATE ateu_en SET jurisdiction_code = 'EU';
ALTER TABLE wdtk_gb ADD COLUMN source;
ALTER TABLE wdtk_gb ADD COLUMN source_description;
ALTER TABLE wdtk_gb ADD COLUMN source_url;
UPDATE wdtk_gb SET source = 'What do they know? (MySociety)';
UPDATE wdtk_gb SET source_url = 'http://www.whatdotheyknow.com/help/api';
UPDATE wdtk_gb SET source_description = 'UK FoI site';
ALTER TABLE wdtk_gb ADD COLUMN jurisdiction;
ALTER TABLE wdtk_gb ADD COLUMN jurisdiction_code;
UPDATE wdtk_gb SET jurisdiction = 'United Kingdom';
UPDATE wdtk_gb SET jurisdiction_code = 'GB';
CREATE TABLE entity (
title TEXT,
abbr TEXT,
key TEXT,
slug TEXT,
category TEXT,
parent TEXT,
parent_key TEXT,
description TEXT,
url TEXT,
jurisdiction TEXT,
jurisdiction_code TEXT,
source TEXT,
source_url TEXT,
source_description TEXT,
address TEXT,
contact TEXT,
email TEXT,
tags TEXT,
created_at TEXT,
updated_at TEXT);
DELETE FROM entity;
INSERT INTO entity
(title, category, parent, description, url, jurisdiction,
jurisdiction_code, source, source_url, source_description,
address, contact, email, tags)
SELECT title, class, parent, description, url, jurisdiction,
jurisdiction_code, source, source_url, source_description,
address, contacts AS contact, email, tags FROM froide_de;
INSERT INTO entity
(title, abbr, slug, url, jurisdiction, jurisdiction_code, source,
source_url, source_description, tags, created_at, updated_at)
SELECT Name AS title, `Short name` AS abbr, `URL Name` AS slug,
`Home page` AS url, jurisdiction, jurisdiction_code, source,
source_url, source_description, Tags as tags, `Created at`
AS created_at, `Updated at` AS updated_at FROM wdtk_gb;
INSERT INTO entity
(title, abbr, slug, url, jurisdiction, jurisdiction_code, source,
source_url, source_description, tags, created_at, updated_at)
SELECT Name AS title, `Short name` AS abbr, `URL Name` AS slug,
`Home page` AS url, jurisdiction, jurisdiction_code, source,
source_url, source_description, Tags as tags, `Created at`
AS created_at, `Updated at` AS updated_at FROM ateu_en;
CREATE TABLE alias (alias TEXT, key TEXT);
DELETE FROM alias;
INSERT INTO alias (alias, key) SELECT abbr AS alias, key AS key
FROM entity WHERE abbr IS NOT NULL AND LENGTH(abbr);
INSERT INTO alias (alias, key) SELECT (SELECT ateu.Name AS alias FROM
ateu_de ateu WHERE ateu.`Home page` = ent.url) AS alias, ent.key AS key
FROM entity ent;
INSERT INTO alias (alias, key) SELECT (SELECT ateu.Name AS alias FROM
ateu_de ateu WHERE ateu.`Short name` = ent.abbr) AS alias, ent.key AS key
FROM entity ent;
INSERT INTO alias (alias, key) SELECT (SELECT ateu.Name AS alias FROM
ateu_fr ateu WHERE ateu.`Home page` = ent.url) AS alias, ent.key AS key
FROM entity ent;
INSERT INTO alias (alias, key) SELECT (SELECT ateu.Name AS alias FROM
ateu_fr ateu WHERE ateu.`Short name` = ent.abbr) AS alias, ent.key AS key
FROM entity ent;
INSERT INTO alias (alias, key) SELECT (SELECT ateu.Name AS alias FROM
ateu_es ateu WHERE ateu.`Home page` = ent.url) AS alias, ent.key AS key
FROM entity ent;
INSERT INTO alias (alias, key) SELECT (SELECT ateu.Name AS alias FROM
ateu_es ateu WHERE ateu.`Short name` = ent.abbr) AS alias, ent.key AS key
FROM entity ent;
DELETE FROM alias WHERE alias IS NULL;
CREATE TABLE types (
name TEXT,
db_url TEXT,
entity_table TEXT,
entity_key TEXT,
alias_table TEXT,
alias_text TEXT,
alias_key TEXT
);
INSERT INTO types (name, db_url, entity_table,
entity_key, alias_table, alias_text, alias_key) VALUES
('bodies', 'http://localhost:5000/pudo/publicbody',
'entity', 'key', 'alias', 'alias', 'key');
.output entities.csv
.mode csv
.headers ON
SELECT * FROM entity;