CogniCity

Open Source GeoSocial Intelligence Framework

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

DOI for current stable release v2.0.0:

About

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 - PetaJakarta.org: 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

Tables

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 PetaJakarta.org 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.

Hydrological Infrastructure Data (pumps, floodgates, waterways) is licensed under Creative Commons Attribution-NonCommercial 4.0 International License.

* Hydrological data are available from [Research Data Australia](https://researchdata.ands.org.au/petajakartaorg/552178) (Australian National Data Service), with DOIs held by the National Library of Australia.

Dependencies

PostgreSQL version 9.2 or later, with
PostGIS version 2.0 or later

Installation

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

Restoring the schema

Create an empty database, using createdb.sql for required properties
Load the schema into the new database

psql -d cognicity -f schema.sql

Optionally, load the sample data:

for datafile in sample_data/*.sql
do
  psql -d cognicity -f $datafile
done

License

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

smart-facility/cognicity-schema