Key Takeaways
- Municipal governments produce and commission large amounts of data and information every day. Making these datasets available to the public enables these institutions to become more transparent and accountable to their citizens. In New York City, Open Data is the law.
- The Socrata Open Data API (SODA) is the most widely adopted government open data solution. Data is exposed via REST-like APIs.
- Low code, cloud-based development platforms offer easy and declarative access to external REST APIs and enable a much broader -- potentially less tech-savvy -- audience than ever before to take advantage of the information available.
- The article concludes with a demonstration of using Oracle Application Express (APEX) -- a low code, cloud-based development tool -- to build an app that includes a report and a chart based on the NYC 311 service request open data
Municipal governments produce and commission large amounts of data and information every day. Making these datasets available to the public enables these institutions to become more transparent and accountable to their citizens. By encouraging the use and free distribution of datasets, governments are inviting citizens to more actively engage with public agencies and are promoting the creation of innovative, citizen-centric services and solutions.
In New York City, Open Data is the law. The "Open Data Law", enacted in 2012, mandates that all public data be made available on a single web portal by the end of 2018. New York City already makes hundreds of datasets available at opendata.cityofnewyork.us This site enables citizens to locate datasets by agency or category and to search and filter data through the provided utilities. Hundreds of cities around the country provide similar datasets, often following the same standards for publication.
The true value of these datasets though is in their availability as standards-based REST APIs, which allow developers, using third-party tools and frameworks, to tap into this wealth of information and to build new and innovative apps and solutions. Low code, cloud-based development platforms offer easy and declarative access to external REST APIs and enable a much broader -- potentially less tech-savvy -- audience than ever before to take advantage of the information available.
In this article we'll be providing a high-level overview the concept of Municipal Open Data, REST APIs and the Socrata Open Data API, which is utilized by the City of New York and many other municipalities. You'll learn about using simple filters, SoQL queries and SoQL functions to view and analyze the available data. And we'll look at a variety of tools that allow for inspecting and understanding the provided APIs. With that knowledge, you'll then learn how to build apps on some of the most popular datasets available, like 311 service calls and NYC job posting.
Municipal Open Data
The idea of making government data publicly available has been gaining momentum since the early 2000s. The Internet is commonly seen as the public space of the modern world, enabling governments to better understand the needs of their citizens and citizens to more fully participate with their government. Thus, opening up government data to the public, facilitates civil discourse, improved public welfare and allows for more efficient use of public resources.
A convention of Open Government advocated in Sebastopol, CA in 2007, defined eight principles of Open Government Data:
- Complete: All public data should be made available with exceptions based on privacy and security limitations.
- Primary: Data should be made available as is, with the highest level of granularity and not be aggregated or modified.
- Timely: Data should be made available as quickly as possible and necessary to preserve the value of the data.
- Accessible: Data should be made available to the widest range of users and purposes.
- Machine-processable: Data should be structured to allow for automated processing.
- Non-discriminatory: Data should be available to anyone and access should not require registration.
- Non-proprietary: Data should be made available in an open format, with no entity having exclusive control.
- License-free: Data shouldn’t be subject to any copyright, patent trademark or trade secret regulation.
New York City makes all public data generated by the various New York City agencies and other City organizations available for public use. As part of an initiative to improve the accessibility, transparency and accountability of City government, the Open Data Catalog catalogue, available at opendata.cityofnewyork.us, offers the public access to a repository of government-produced, machine-readable data sets.
Anyone can use these data sets to participate in and improve government by conducting research and analysis or creating applications, thereby gaining a better understanding of the services provided by City agencies and improving the lives of citizens and the way in which government serves them.
Figure 1: NYC Open Data home page
Socrata Open Data API (SODA)
REST stands for Representational State Transfer and is an architectural style for distributed hypermedia systems. It was introduced and defined by Roy Fielding in his doctoral dissertation. REST is an interface between systems using HTTP to obtain data and generate operations on this data in formats like XML and JSON. Most Open Data APIs are based on REST. This includes the Socrata Open Data API (SODA).
Socrata is a Software company that provides services to public sector organizations to help make existing government data discoverable, usable, and actionable for government workers and for the public. Socrata’s solutions allows public sector institutions to automate the flow of data from their primary, line of business systems, into publicly accessible service. The Socrata Open Data API (SODA) is the most widely adopted government open data solution. Socrata hosts hundreds of different data catalogs for governments, nonprofits and NGOs around the world. Many cities in the United States have Open Data portals today, as do many other organizations. The Open Data Network provides a global catalog of open datasets: https://www.opendatanetwork.com, which can also be programmatically integrated and queried from other systems using the Global Catalog API.
To interact with an open data resource and to integrate these with other systems, an endpoint is required. The “endpoint” of a SODA API is a unique URL that represents an object or collection of objects. Every Socrata dataset, and every individual data record, has its own endpoint.
The following is an example endpoint for New York City’s 311 service requests:
https://data.cityofnewyork.us/resource/fhrw-4uyv.json
All resources are accessed through a common base path of /resource/ along with the dataset identifier. Datasets have a unique identifier, an eight alphanumeric characters split into two four-character phrases by a dash. This URL can be opened using any web browser, or with a variety of tools, such as REST Client browser plug-ins, Paw or Postman.
To request specific datasets, or to query and manipulate the results, simple filters and SoQL parameters can be added to the endpoint URL.
SODA APIs are self-describing – the schema and contents of the datasets itself determines how it can be queried. Any field within the data can be used as a filter, simply by appending it to the API endpoint as a GET parameter. In order to filter the previous example to only include 311 service requests that were placed in Brooklyn, the “borough” can be added as a parameter, followed by the borough name:
https://data.cityofnewyork.us/resource/fhrw-4uyv.json?borough=BROOKLYN
The “Socrata Query Language” (SoQL) is a simple, SQL-like query language designed for making it easy to work with data on the web. Much like standard SQL, developers can select specific columns, filter the results using a where clause, order and group the results and apply certain aggregation functions.
Given that many data sets can be extremely large, i.e. have thousands and hundreds of thousands of rows, it’s critically important to be able to have the data aggregated prior to downloading it to the client application. Loading only the data and aggregations needed, rather than downloading complete datasets, improves performance and, in many cases, makes the client application actually viable, because in many cases it would not be practical to download all the data all the time.
The following provided an overview of available URL parameters and their functions:
Parameter |
Description |
Default |
In $query |
The set of columns to be returned, similar to a SELECT in SQL |
All columns |
SELECT |
|
Filters the rows to be returned, similar to WHERE |
No filter |
WHERE |
|
Column to order results on, similar to ORDER BY in SQL |
Unspecified order |
ORDER BY |
|
Column to group results on, similar to GROUP BY in SQL |
No grouping |
GROUP BY |
|
Filters the rows that result from an aggregation |
No filter |
HAVING |
|
Maximum number of results to return |
1000 |
LIMIT |
|
Offset count into the results to start at, used for paging |
0 |
OFFSET |
|
Performs a full text search for a value. |
No search |
N/A |
|
A full SoQL query string, all as one parameter |
N/A |
N/A |
To further filter and aggregate the 311 data from the previous example, the following URL selects only the complaint type, along with the total number of complaints in Brooklyn, sorted in descending order:
- https://data.cityofnewyork.us/resource/fhrw-4uyv.json?
- $select=complaint_type,%20count(complaint_type)&
- $group=complaint_type
- $order=count(complaint_type)%20desc
- &borough=BROOKLYN
Low Code Application Development
The Open Data portal of the City of New York and many of the other Open Data portals that utilize Socrata’s Open Data solutions, provide easy access to myriad public data sets. Using the provided browser-based viewer, citizen can search, filter, visualize and export any of the available data. Using the provided tools is a great way to find information and do research. Many of the datasets though also have great potential to be used as the basis for building new and innovative solutions, beyond simple query interfaces. Developers who wish to integrate those datasets into their own applications can make use of the Socrata Open Data API (SODA), which is available for the vast majority of data sets and makes the data available via http in JSON and CSV format.
All SODA APIs include extensive documentation, including code samples showing how to consume the data using some of the most popular programming languages. This opens up the data to anyone who possesses the required coding skills. To broaden the potential target audience for these APIs beyond professional developers, “Low Code” development tools play an increasingly important role, allowing developers to simply plug in the corresponding SODA URL and then use declarative wizards to build powerful components utilizing the data, like reports, charts, calendars, etc., all while having to write only very little or no code at all.
Low code application development is all about high productivity: more functionality, greater consistency, and higher fidelity, all with less effort. Low code frameworks leverage powerful components that provide reporting, data visualization, and form controls. This allows an application developer to focus on solving the business problem and delivering solutions, with less time and effort spent on mundane and repetitive lower-level coding.
Low code tools dramatically improve application development productivity and maintainability. Citizen developers can automate line of business processes and develop point solutions. However, low code tools are equally attractive to professional developers, as they can dramatically reduce the time and complexity of developing and maintaining applications. When done properly, low code tools deliver superior productivity for the widest range of use cases using declarative programming.
Building Apps on Municipal Open Data Using Low-Code Solutions
To illustrate how this can be done, we’re going to continue using the New York City 311 Service Calls API:
https://data.cityofnewyork.us/resource/fhrw-4uyv.json
We will use the low code Oracle Application Express (APEX) suite to build an app that includes a report and a chart based on this data set, as shown in Figure 2. Oracle Application Express enables developers to create database-centric Web applications that are reliable, scalable, and secure. Using only a web browser, and with limited programming experience, developers can build and deploy professional Web-based applications for desktops and mobile devices.
Figure 2 - APEX chart showing NYC 311 Service Requests
To start building an app with a low-code solution, developers will have to either stand up a local APEX instance, or sign-up for a Cloud-based APEX service. A Cloud-based service has the advantage that it’s already “online”, i.e. once the app is developed, it’s instantly available to anyone the developer chooses to share it with.
Once a service is available, developers need to create an APEX workspace and can then start building applications using the declarative Create Application Wizard. For the purpose of this example, it’s sufficient to simply build an application with a single blank page. After the application is created, a reference to the REST API needs to be created. This is done in “Shared Components”, using a component called a “Web Source”. To create a new web source, the URL endpoint of the API is required, along with a web source name and information on the web source type and HTTP method. The SODA APIs can be accessed via http and https.
In the next step, APEX will split up the endpoint URL into a server-specific and a service-specific part. With the server-specific part, APEX will create a new Remote Server object. That additional entity allows to group REST endpoints pointing to the same server. When, for such a collection of REST services, the server changes, it will be sufficient just to adjust the Remote Server object.
Next the developer will be prompted for authentication information. For the SODA APIs no authentication is required, however developers can include an app token using the advanced attributes. Additionally, developers can define SODA filters and SoQL queries using optional module parameters. In order to include parameters in the URL, the parameter type should be “Query String”. Parameters can be static or dynamic, i.e. they can be changed dynamically at runtime based on user actions.
The last step of the web source creation is the “Discovery”. This actually makes a HTTP request to the API endpoint. The result is returned in JSON format, APEX analyses the JSON structure and creates a data profile that includes all available columns along with the discovered data types. The web source along with the data profile will later be used by the declarative component create wizards in APEX, much like APEX typically uses the database data dictionary to read table and column information on local tables.
After having created a web source, many standard components in APEX allow for directly connecting to the REST API. This includes Interactive Reports, Classic Reports, Charts and Calendars. To make use of a web source, developers simply step through the corresponding create page wizard and select “Web Source” as the data source. Once selected, developers can choose which columns to include in the report, or in case of a chart or calendar page, choose which column(s) to use as the display and value columns.
The following shows an application created by utilizing the NYC 311 Service Calls API. In this example an APEX Interactive Grid was used, the most relevant column from the data set were selected to be shown in the report, and using built-in the “Chart” functionality, an Oracle JET chart was added that shows in which boroughs the most 311 calls are being placed.
Figure 3 -- NYC 311 service calls
Conclusion
Open Data provides great opportunities for municipalities, local, state and federal government agencies to engage with their constituents and become more transparent and accountable. Beyond inviting the public to lookup information on the provided Open Data portals, Open Data APIs can be used the basis for building new and innovative services. Low Code development platforms, which allow for simply pointing to an API endpoint to get started with building an app, play an important role in making Open Data APIs more accessible to a broader audience of developers. Developers can use simple and declarative wizards to build out their own solutions and mesh-ups with other data sets and services. Today’s Cloud service providers make it easy to get started with building apps on public REST APIs, as all the mundane tasks of acquiring a server, licensing and configuring software and network, etc. are generally taken care of by the service provider.
About the Author
Marc Sewtz is a Software Development Manager for Oracle Application Express in the Oracle Database Tools Group. Marc has 14 years of industry experience, including roles in Consulting, Sales and Development and joined Oracle in 1998. Marc has been working with Oracle Application Express since 2001, initially as a Consultant, using Oracle Application Express as a development framework for writing extensions to the Oracle E-Business Suite and later as a Software Architect and Development Manager in the Oracle Application Express development organization. In this role, Marc manages a globally dispersed team of developers and product managers and is responsible for and contributed to product features such as the reporting engine, tabular forms, PDF printing and BI Publisher integration, wizards and the user interface and artwork.