Data visualization platform to present data on flood risk points and flood mitigation projects in Bangkok
Software Terms of Reference (TOR)
The Open Contracting Partnership is seeking a software development team to develop a data visualization platform for the Bangkok Metropolitan Administration (BMA). The objective of this platform is to improve internal oversight & supervision of flood projects, improve internal efficiency by reducing administrative burdens from data sharing, and improve communications of flood risks and mitigation activities to citizens. The long term goal of this project is to enhance the internal decision-making process of the BMA regarding flood-related procurement. The platform should facilitate the dissemination of information pertaining to flood-related procurement, cultivating an environment of trust and collaboration internally within BMA and supporting and encouraging active participation and trust among citizens.
About us
The Open Contracting Partnership (OCP) is a silo-busting collaboration between government, business, civil society and technologists to transform government contracting and drive systemic change using open data and radically-improved transparency for users. We work across sectors and along the whole process of government contracting to use open data and improved oversight to save governments money and time, deliver better goods and services to citizens, deter corruption and create a better business environment for all.
Background
Context of this project
Bangkok faces an existential threat from flooding and this is expected to worsen, putting about half the city at risk by 2050: Bangkok sits on average 1.5m above sea level and is sinking at about 1.5cm a year. Flood management is therefore a top priority. Bangkok Metropolitan Administration (BMA) needs new flood infrastructure projects to help protect residents and communities from high tides and floods. In 2023, BMA had an approved water management budget of approximately THB 11.7 billion (USD 350 million) where USD 110 million was allocated for flood management. BMA needs to ensure that these funds are spent effectively and that flood projects meet the most urgent needs.
As part of OCP's Impact Accelerator Program (LIFT), OCP is working with BMA to strengthen the city's resilience to flooding by increasing the efficiency and effectiveness of its flood management strategies, policies and processes.
To achieve this, BMA has been working on upgrading its eGP system (e-GP Phase II), which will increase the availability of data, making it easier for the public and businesses to view BMA procurement data. OCP has been advising this process and working with BMA on identifying additional data sources that could help track flood-related spending.
As a pilot, OCP has already supported BMA to build a dashboard to visualize procurement and non-procurement data in a mapping format that overlays BMA data sets related to flood-prone areas to identify risk areas and link with allocated budget.
To build on these efforts, one of the project's outputs is a data visualization platform that will use the available data sources to disclose information on flood-related spending, projects and risks. The data visualization platform will be used by internal users in BMA (to support coordination, oversight and decision-making) and external users (to enhance transparency and public participation).
Responsibilities
The vendor’s main responsibilities are to:
- Finalize the tool design
- Conduct interviews with BMA and other relevant users to validate suggested user stories (see User stories) and identify new ones to include in the functionalities of the data visualization platform.
- Explore data sources to inform the design and development of the analytical applications (see Data below).
- Tool development
- Import (transform and clean) data from the selected data sources into the database used by the proposed technology (see Data).
- Develop user interfaces for the analytical applications (see User stories).
- Create a process to regularly update the data at a frequency to be agreed upon with the BMA team
- Deploy the tool in a server provided by BMA, including any configuration needed for that purpose.
- Knowledge transfer
- Deliver at least one training event for project partners and relevant stakeholders on how to use the tool, along with a use guide documentation.
- Prepare developer documentation and perform knowledge transfer to hand off the project to the project team.
- Provide technical administration and support services for major performance issues or bugs for 12 months after the final delivery of the application.
The BMA’s main responsibilities are to:
- Assist in conducting user research
- Provide a list of relevant personnel for interviews
- Provide access to data
- Prepare the data in a machine readable format
- Provide access to all datasets intended for visualization using either API / Web Services or other agreed access methods
- Establish methods for data updates on agreed schedule
- Provide data dictionaries and other relevant documentation for all datasets
The OCPs main responsibilities are to:
- Provide staging server
- Acceptance-testing and sign-off
Activities
- Design and Discovery
- Research and analysis of existing systems and processes
- Identification of user needs and requirements
- Development of conceptual design and prototypes
- User testing and feedback
- Iteration on the design based on feedback
- Build
- Database development
- Development of ETL process to consume data from various sources and import it into database on a defined schedule (e.g. once a day)
- Backend development (e.g. internal API endpoints to serve data to frontend)
- Frontend development
- Testing and quality assurance
- Documentation
- Deployment of the system to production
- Publish and Institutionalize
- Knowledge transfer to BMA’s department responsible for platform maintenance
- Technical administration and support services for major performance issues or bugs for 12 months after the final delivery of the application.
To ensure the quality, correctness, and maintainability of the software, OCP might approve:
- Database structure (e.g. entity-relationship model)
- Web routes and API endpoints (e.g. HTTP verbs, URL paths, status codes)
- Software dependencies
The specifications of activities will be agreed between the consultants and OCP. Consultants are expected to follow an agile process of iterative development, that prioritizes demonstrations and working code, and that allows for changes in implementation details.
The activities can include calls or workshops with partners, organized with OCP’s assistance, in order to, for example: elaborate user needs; refine or validate the specifications of features; and/or conduct usability testing.
The consultants will meet with OCP’s Senior Data Support Manager and Senior Asia Manager, and Representative of Deputy Governor of Bangkok, Mr. Kunanop Lertpraiwan, on a weekly basis, and with the Head of Technology on a biweekly or monthly basis to discuss progress updates and to agree on goals and tasks for the following period.
Users
This data visualization platform is intended to be used by several groups of users, including but not limited to:
- Role: BMA Decision-Makers (Executives, Planners)
- Responsibilities:
- Setting overall flood risk management strategies.
- Tracking progress against specific policies and projects.
- Allocating resources and approving budgets.
- Monitoring budget spending.
- Evaluating the effectiveness of flood management efforts.
- Needs:
- High-level overviews of flood risk status across the city.
- Summaries of spending and project progress.
- Comparisons of flood risk and management efforts across districts.
- Progress reports on projects/activities and achievement of performance indicator goals.
- Responsibilities:
- Role: BMA Flood Risk Management Officers
- Responsibilities:
- Monitoring and assessing flood risks across Bangkok.
- Prioritizing areas for intervention and mitigation.
- Planning and implementing flood management projects.
- Coordinating with other departments and agencies.
- Needs:
- Detailed maps of flood risk points.
- Dashboards summarizing project statuses, budgets, and timelines.
- Tools for analyzing historical trends and identifying patterns in flood risk.
- Responsibilities:
- Role: BMA Procurement Officers
- Responsibilities:
- Managing procurement processes for flood management projects.
- Ensuring transparency and compliance in procurement.
- Tracking spending and project progress.
- Needs:
- Dashboards visualizing procurement data (spending, contracts, suppliers).
- Tools for comparing procurement data across projects and categories.
- Alerts for delays or issues in the procurement process.
- Responsibilities:
- Role: Civil Society Organizations (NGOs, Community Groups)
- Responsibilities:
- Advocating for effective flood risk management policies.
- Raising awareness about flood risks and mitigation strategies.
- Engaging with communities on flood preparedness.
- Needs:
- Access to comprehensive flood risk data and analyses.
- Tools for visualizing and communicating flood risk information to the public.
- Responsibilities:
- Role: General Public
- Needs:
- Easy-to-understand information on flood risks in their neighborhoods.
- Access to flood maps and information on mitigation efforts.
- Needs:
User stories
An initial proposal for the data visualization platform features is described below. Additional dashboards and indicators can be included as agreed by OCP and BMA.
Must-have
- Maps and charts presenting data on flood risk points and flood mitigation projects, including but not limited to:
- Distribution of flood risk points by:
- Status
- Risk / problem type
- District and subdistrict
- Managing organization / department
- Distribution of flood mitigation projects by:
- Status
- Budget
- Procurement category / how the problem is being addressed
- Procurement method
- Managing organization / department / procuring entity / buyer
- Supplier
- Related flood risk point
- Amount of money required, available, and spent by:
- Flood risk point
- Flood mitigation project
- Managing organization / department
- District and subdistrict
- Fiscal year
- Distribution of flood risk points by:
- Data visualizations / dashboards presenting data on flood related procurement processes, including but not limited to:
- Total number of procedures
- Total number of procuring entities
- Total number of unique bidders
- Total number of awarded suppliers
- Total number of procedures by year or month
- Total value awarded
- Proportion of single bid tenders
- Mean number of bidders per tender
- Number of new bidders in a system
- Mean number of unique suppliers per buyer
- Average duration of tendering period (days)
- Percent of contracts that exceed budget
- Percent of contracts completed on time
Could-have
- Geolocated visualization to explore flood related complaints and the progress in their resolution based on Traffy Fondue data, including but not limited to
- Distribution of flood related complaints by
- District and subdistrict
- Status
- Year, month, week
- Responsible agency
- Response time
- Distribution of flood related complaints by
- Functionality for users to submit feedback
- Data export to CSV, XLSX, JSON format and export of the reports to PDF format.
Won't-have
- Open Contracting Data Standard (OCDS) data publication
Non-functional requirements
Compatibility. The tool should work on the last two versions of the most common internet desktop and mobile browsers: Chrome, Safari, Firefox, and Edge.
Performance. The screens should load (including all components and images) within 1 second and support up to 1,000 users at the same time.
Data
This project will cover data from several data sources including but not limited to:
Title: Flood Risk Points (locations) dataset
Description: Locations of Flood Risk Points (identifier, longitude, latitude)
Maintainer: Drainage Information System Division (database management). GIS Department (database publication as a web service)
Format: SHP / GeoJSON
Access mode: Web Service
Mandatory or optional: Mandatory
Title: Flood Risk Points (attributes) dataset
Description: Attributes of Flood Risk Points (identifier, status, district, subdistrict, responsible organization, problem description and data dictionary)
Maintainer: Drainage Information System Division (database management). GIS Department (database publication as a web service)
Format: SHP / GeoJSON, Google Spreadsheets
Access mode: Web Service (Data Dictionary)
Mandatory or optional: Mandatory
Title: Flood Mitigation Projects dataset
Description: Details of Flood Mitigation Projects related to each Flood Risk Point (flood risk point identifier, project identifier, status, road, area, activity description, budget year, amount of money budgeted, amount of money spent, year works started, year works finished)
Maintainer: Information System Division
Format: Google Spreadsheets
Access mode: Web Service, Google Spreadsheets
Mandatory or optional: Mandatory
Title: Flood Related Procurement dataset
Description: Flood Related Procurement Procedures from updated eGP BMA (project identifier, procuring entity, tender title, tender period, tender value, procurement category, item classification, procurement method, award value, contract value, supplier,
contract period, status, budget year, budget, budget money, number project on eGP, district, group, status number, status detail, risk number, longitude, latitude)
Maintainer: eGP
Format: JSON API or Google Spreadsheets
Access mode: Google Spreadsheets
Mandatory or optional: Mandatory
Title: Canals dataset (locations)
Description: Locations of canals (identifier, geometry, canal identifier, district id and text, canal type, length, the beginning of the canal and the end of the canal, canal width, canal depth, subdistrict id, flood prevention, owner, year, shape length and data dictionary)
Maintainer: GIS Department
Format: SHP / GeoJSON
Access mode: Web Service (Data Dictionary)
Mandatory or optional: Optional
Title: Flood Related Complaints
Description: Public complaints on flood related issues in Bangkok area submitted via Traffy Fondue platform (identifier, timestamp, longitude, latitude, description, tag, status,
responsible agency, response time)
Maintainer: Traffy Fondue
Format: JSON API / CSV
Access mode: Public API, bulk download (Data Sample)
Mandatory or optional: Optional
Additional datasets can be considered for visualization upon agreement with BMA and OCP, if their use is supported by user stories discovered during the user research phase.
All datasets might require additional data wrangling and cleaning, for instance, adjusting data structure (e.g. extracting only certain parts of the spreadsheet), reconciling district and subdistrict names, converting values from strings to numbers, parsing date values, etc.
Deliverables
The project will follow an agile development methodology, with deliverables broken down into incremental stages to ensure regular progress and allow for flexibility. Each stage will include a working version of the platform with specific features and functionalities implemented. The following are the key deliverables:
- Inception Report: A comprehensive report outlining the project's understanding, including:
- Refined user stories and requirements based on stakeholder interviews
- Detailed data source analysis and integration plan
- Proposed technology stack and architecture
- Low-fidelity wireframes or mockups illustrating the basic layout and functionality of the core features
- Project timeline with milestones and deadlines for each stage
- UI/UX design
- High-fidelity prototypes or interactive mockups
- Design specifications for maps and dashboards
- Maps and charts presenting data on flood risk points and flood mitigation projects
- Data pipeline for ingesting and processing (transforming, cleaning) data on flood risk points and flood mitigation projects
- Interactive map visualizing flood risk points with filtering and search capabilities
- Dashboard summarizing flood management project data
- Data and reports export functionality
- Data visualizations / dashboards presenting data on flood related procurement processes
- Data pipeline for ingesting and processing (transforming, cleaning) data on flood related procurement
- Dashboards with interactive charts for data exploration
- Data and reports export functionality
- Refinement and User Testing
- Implementation of any additional features or functionalities identified during the research phase
- Iterative design refinements based on user testing and feedback
- Implementation of any additional features or functionalities identified during testing
- Testing and bug fixing
- Final Delivery
- Fully functional and tested data visualization platform deployed on BMA's server
- Comprehensive user guide and documentation
- Training session for BMA staff on platform usage and maintenance
- Support and Maintenance
- Technical support for bug fixes and performance issues for a specified period
- Knowledge transfer to BMA's team for long-term maintenance and enhancements
Technical considerations
As OCP’s primary language, Python is strongly preferred for backend components that are expected to be improved over time. BMA team uses both Windows and Linux servers for its services, Oracle and PostgreSQL for databases, and PHP programming language for web development.
It should be possible for another vendor to improve and maintain the software. As such, submissions should budget for developer documentation, and implementation should use popular libraries where appropriate.
OCP strongly favors re-using frameworks, database systems, queuing systems, visualization platforms, etc. Open-source software is strongly preferred, but proprietary dependencies can be considered depending on the cost/benefit.
All components / classes should be implemented in a way that enables their reuse (it should be possible to create a new map using available components or classes).
There should be clear and detailed documentation for adding new pages, maps, charts, datasets to the platform.
OCP strongly favors delivering HTML over HTTP and disfavors single-page applications (SPA), unless an SPA offers an overwhelming advantage.
For reference, see OCP’s preferences for general software and Python packages.
The consultants will not have access to the production server, in order to ensure a complete knowledge transfer.
Quality Assurance Surveillance Plan (QASP)
OCP expects the project to meet the levels of quality in the Quality Assurance Surveillance Plan (QASP), to be monitored by OCP at the end of every sprint (or milestone).
Personnel
The consultants must designate a Project Manager and a Technical Lead as Key Personnel for this project. The Project Manager will be a direct liaison to OCP and will be responsible for the supervision and management of the consultants’ personnel. The Technical Lead must have a full understanding of the technical approach to be used by the consultants and will be responsible for ensuring that the consultants’ development team follows that approach.
Intellectual property
To the extent possible, all project outputs will either be owned by BMA, have rights assigned to BMA, or be licensed as open source.
The use of generative AI (LLMs, ChatGPT, Copilot, etc.) is prohibited (for example, to generate code, documentation or tests), except as agreed in writing with OCP.
Budget and timeline
Rather than pursue a fixed-scope and fixed-price contract, the consultants will be paid for time and materials.
Please make clear the total cost of the MVP, distinct from any should-have or could-have features.
The ceiling for the completion of the services is $35,000 USD. Work must be completed by February 28, 2025.
An advance payment can be invoiced upon signature of the contract. Work achieved will be invoiced either monthly or quarterly.
Qualifications
To qualify, consultants must have past projects involving designing and conducting user research, developing user requirements, developing online applications with interactive maps and data visualizations, building data pipelines / ETL processes. Likely past projects might include dashboards, data visualization platforms and / or tools, etc.
Consultants must speak English and Thai, since the team that will maintain the tool is based in Thailand.
- Experience in the development of open source ETLs, APIs, visualizations, and/or data quality improvement processes. Experience should be demonstrated through at least 3 completed projects.
- Knowledge of Python (Django, Flask), PostgreSQL, Leaflet / Mapbox, D3 / Vega, or similar data visualization frameworks. Experience with these technologies should be demonstrated through past projects.
- Knowledge of public procurement in Thailand and Bangkok specifically is a big plus.
Application process
A submission must consist of:
- a technical proposal and a timeline of at most ten (10) pages
- a staffing plan of at most three (3) pages, plus resumes and letters of intent (if any)
- references to one or more source code samples, preferably open source
- a price submission (1 page)
See the Software Bid Submission Instructions for details.
We encourage interested parties to submit questions to ahazin AT open-contracting.org. We will publish answers (without identifying submitters) at the top of this page.
Andrii Hazin, Senior Data Support Manager, and James McKinney, Head of Technology, are available to discuss the opportunity. If interested, email ahazin and jmckinney AT open-contracting.org. Answers to any questions on the calls will be anonymized and shared on this page.
Submission closing date is 27 September 2024.
Evaluation process
Complete submissions will be evaluated against the Software Bid Evaluation Criteria. The highest-rated submissions will be invited to participate in a one-hour interview.
The Key Personnel (Project Manager and Technical Lead) must participate in the interview. OCP will ask questions about the submission, to better understand and assess it.
Submitters will NOT be able to use or present any slides, graphs, charts, or other presentation materials – except as requested by OCP prior to the interview.
Appendix
List of datasets that can be added to a data visualization platform
List of BMA tools and platforms that can be utilized for data visualization platform development
Questions & Answers
Question: Can international providers apply, and are there any specific limitations we should be aware of?
Answer: We certainly welcome applications from international providers. There are no specific limitations in this regard. However, please ensure sufficient time zone overlap for effective communication and collaboration.
Question: Regarding the language requirement for Thai and English speakers, would it be acceptable for one team member to speak Thai while the others communicate in English?
Answer: While we ideally prefer multiple team members with proficiency in both Thai and English, we understand that this might present a challenge. In this case, having one dedicated team member who is fluent in Thai and can effectively bridge communication between the team and the BMA would be acceptable. This individual would likely need to combine user research and project management responsibilities to ensure smooth coordination. We would strongly prefer that the Thai-speaking team member be based in Bangkok to facilitate in-person collaboration with the BMA. The rest of the team can be remote.
Question: Is there a preferred infrastructure for deploying the ETL solution, or any licensing limitations we should be aware of? Should it be implemented in servers provided by OCR or should it incorporate the use of GCP or other Cloud solutions? If so, is there any preference?
Answer: The ETL solution should be deployable on an Ubuntu server. A staging server will be provided by OCP. The use of Docker containers for project-specific code is preferred, to simplify configuration. The production server will be operated by BMA. The project should plan for documentation and knowledge transfer, to allow BMA to re-deploy the ETL solution without outside intervention. The use of cloud services (like AWS Lambda, etc.) for the ETL solution is discouraged, unless the cost/benefit is unambiguously favorable.
Question: It’s mentioned that the platform will serve both internal BMA users and external users. Should the data visualization be unified for both groups, or do you envision separate versions for internal personnel and the general public? If a separated version is preferred, would we need to incorporate user credentials and permission controls for specific users?
Answer: Our current plan is to maintain a unified interface for both internal BMA users and external users. At this stage, we do not anticipate the need for user management, authentication, credentials, or permission controls.
Question: There’s mention of a Tableau prototype already in place. Is it expected to continue using Tableau, or are you considering switching to another BI tool? If switching, does BMA/OCP have existing licenses for any other BI software, or a preference for specific platforms?
Answer: While a Tableau prototype is currently in use, we do not plan to continue with Tableau for the final solution. BMA has generally expressed a preference for solutions that do not require ongoing licensing or subscription fees. However, we recognize that this may not always be feasible.
Therefore, while license-free solutions are preferred, we are open to evaluating proposals that involve low-cost subscriptions if they provide significant benefits. For instance, a BI platform with a modest monthly subscription fee would be acceptable, whereas a solution requiring annual licensing fees in the hundreds of thousands of dollars likely wouldn't be competitive with other submissions.