PostgreSQL database schema for the CogniCity framework
Switch branches/tags
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Type Name Latest commit message Commit time
Failed to load latest commit information.


Open Source GeoSocial Intelligence Framework

cognicity-schema: PostgreSQL/PostGIS Schema for CogniCity data.

DOI for current stable release v2.0.0: DOI


CogniCity-schema is the PostgreSQL/PostGIS database schema for the CogniCity Framework. The schema contains the tables required for data input by cognicity-reports and data output using cognicity-server.

For a comprehensive overview of CogniCity, including the database schema see Chapter 2 of:

"White Paper - Assessing the Role of Social Media for Civic Co‑Management During Monsoon Flooding in Jakarta, Indonesia", 2014. Holderness T & Turpin E. ISBN 978-1-74128-249-8


Base Schema

Table Name Description
tweet_reports Confirmed tweet reports of flooding
tweet_reports_unconfirmed Unconfirmed tweet reports of flooding
nonsptial_tweet_reports Confirmed tweet reports of flooding missing geolocation metadata
all_users Encrypted hash of all related Twitter usernames
tweet_users Encrypted hash of user names who have submitted confirmed reports
tweet_invitees Encrypted hash of users have been been sent an invitation
nonspatial_tweet_users Encrypted hash of users who have submitted confirmed reports missing geolocation metadata

Sample Data

The following tables are also included as samples of ancillary data used for map overlays and report aggregation, as part of the project. SQL files for these tables and included data can be found in the sample_data folder.

Table Name Description
jkt_city_boundary Boundaries of Jakarta’s five municipalities
jkt_subdistrict_boundary Boundaries of Jakarta’s municipal sub-districts (‘Kecamatan’)
jkt_village_boundary Boundaries of Jakarta’s municipal villages (‘Kelurahan’)
jkt_rw_boundary Municipal boundaries of Jakarta’s municipal RW districts (‘Rukun-Warga’)
pumps Locations of water pumps in Jakarta
floodgates Locations of floodgates in Jakarta
waterways Locations of waterways in Jakarta

Sample Data Licenses

Jakarta's municipal boundaries are licensed under a Creative Commons Attribution-ShareAlike 4.0 International License. Creative Commons Licence
Hydrological Infrastructure Data (pumps, floodgates, waterways) is licensed under Creative Commons Attribution-NonCommercial 4.0 International License. Creative Commons Licence
* Hydrological data are available from [Research Data Australia]( (Australian National Data Service), with DOIs held by the National Library of Australia.



  • The PostgreSQL database server must be running with a UTF-8 character set.

Restoring the schema

  1. Create an empty database, using createdb.sql for required properties
  2. Load the schema into the new database
psql -d cognicity -f schema.sql
  1. Optionally, load the sample data:
for datafile in sample_data/*.sql
  psql -d cognicity -f $datafile


The schema is released under the GPLv3 License. See License.txt for details.