Explore and Use Free Government Data with Low-Code Tools
City, state and federal government agencies across the United States began sharing the data they collect with the public in the early 2000s, and they have continued to improve the resources for accessing it. That open data — covering everything from taxes to health trends to police calls to environmental science — is available to improve your research or put to work in applications. And because the data is freely available, all you need are curiosity and the skills to do something with it.
Low-code platforms are perfect for tapping those data resources because you can simply point to an API endpoint to get started building an app. You can then use declarative wizards to build your own solutions and mashups with all those available datasets and services.
Find and Access Open Data
Before we get to building apps using our low-code platform, in this case, Oracle APEX, let’s look at some basics of open data.
Hundreds of cities, states, federal agencies, nonprofits and non-governmental organizations (NGOs) provide datasets. A key to the value of these datasets is in their availability as standards-based REST APIs. For background, REST is an interface between systems using HTTP to obtain datasets in formats such as XML and JSON. Most open-data APIs are based on REST.
The Socrata Open Data API (SODA), which hosts hundreds of different data catalogs for governments, nonprofits and NGOs worldwide, is the most widely adopted government open-data solution. Another is Open Data Network, which provides a global catalog of open datasets that 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 it 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.
Use Case: New York City Emergency Calls
The data we will be using for our purposes is from New York City, which enacted its first open-data law in 2012. The city’s opendata.cityofnewyork.us site lets you discover and use hundreds of datasets by agency or category.
Here’s an example endpoint for New York City’s 311 service requests.
Full documentation of this endpoint (the NYC 311 API) can be found here.
Note that all the resources are accessed through a common base path of /resource/ along with a unique dataset identifier — eight alphanumeric characters split into two four-character phrases by a dash. This URL can be opened using any web browse or with a variety of tools, such as the Paw or Postman REST client browser plugins.
To request specific datasets, or to query and manipulate the results, simple filters and SoQL (Socrata Query Language) parameters can be added to the endpoint URL.
SODA APIs are self-describing, which means the schema and contents of the datasets themselves determine how they 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 include only 311 service requests placed in Brooklyn, the NYC borough can be added as a parameter followed by the borough name:
https://data.cityofnewyork.us/resource/fhrw-4uyv.json?borough=BROOKLYN
SoQL is a simple, SQL-like query language designed to make it easy to work with data on the web. As with standard SQL, developers can select specific columns, filter the results using a where clause, order and group the results, and apply certain aggregation functions.
Map shows the 1,000 most recent 311 calls by location
Given that many datasets are extremely large, with thousands and even hundreds of thousands of rows, it’s critically important to aggregate the data before 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 viable because in many cases, it would not be practical to download all the data each time you intend to run the application or generate a report.
The following provides an overview of available URL parameters and their functions:
| Parameter | Description | Default | In $query |
| $select | The set of columns to be returned, similar to a SELECT in SQL | All columns | SELECT |
| $where | Filters the rows to be returned, similar to WHERE | No filter | WHERE |
| $order | Column to order results on, similar to ORDER BY in SQL | Unspecified order | ORDER BY |
| $group | Column to group results on, similar to GROUP BY in SQL | No grouping | GROUP BY |
| $having | Filters the rows that result from an aggregation | No filter | HAVING |
| $limit | Maximum number of results to return | 1,000 | LIMIT |
| $offset | Offset count into the results to start at, used for paging | 0 | OFFSET |
| $q | Performs a full text search for a value | No search | N/A |
| $query | 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
Why Low Code
Low-code application development is all about boosting productivity: Tools like APEX offer you far more functionality with far less effort. Low-code tools allow an application developer to focus on solving the business problem, spending less time on repetitive lower-level coding.
Meanwhile, users without software development experience can use low-code tools to automate line-of-business processes and quickly develop point solutions. When used properly, low-code tools deliver superior productivity for a wide range of use cases.
Report shows a list of the most recent 311 calls placed.
Using Open Data with APEX, Step by Step
As mentioned above, the open data portals of New York City and many other governments use the SODA API, which makes the data available via http in JSON and CSV formats. Low-code platforms let developers simply plug in the corresponding SODA URL, then use declarative wizards to build powerful components using the data — such as reports, charts and calendars — without having to write a lot of code or any at all. Many low-code platforms also offer components for reporting, data visualization and form controls.
For this example, we’re using APEX, which can be applied to a wide variety of use cases, from a simple spreadsheet replacement to a mission-critical enterprise system.
To start building the app with APEX, you have different options. You can sign up for an Oracle Cloud Free Tier account and then either create an Always Free APEX Service or provision an Always Free version of Oracle Autonomous Database. You can request a Free Workspace on apex.oracle.com. Of course, you can also stand up your own APEX instance locally.
Once a service is available, you will need to create an APEX workspace and can then start building applications using the declarative Create Application Wizard.
- For this example, it’s sufficient to build an application with a single blank page. After you build the application, you’ll need to create a reference to the REST API. This is done in “Shared Components” using a component called “REST Data Source.” Creating a new REST Data Source requires the URL endpoint of the API, along with the REST Data Source’s name, as well as information on the REST Data Source type and HTTP data transfer method. You can access the SODA APIs via http and https. APEX will split up the endpoint URL into server-specific and service-specific parts. With the server-specific part, APEX will create a new remote server object, which allows you to group REST endpoints pointing to the same server. If the server changes location, simply adjust the remote server object and that entire collection of REST endpoints will continue to work.
- Next, you 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. You can define SODA filters and SoQL queries using optional module parameters. To include parameters in the URL, select the parameter type “Query String.” You can change parameters dynamically at runtime.
- The last step of the REST Data Source creation is “Discovery,” which makes an HTTP request to the API endpoint. The result is returned in JSON format. APEX analyzes the JSON structure and creates a data profile that includes all available columns along with the discovered data types. The declarative component will later use the REST Data Source and data profile to create wizards in APEX, similar to the way APEX typically uses the database data dictionary to read table and column information on local tables.
After you create a REST Data Source, many standard components in APEX — such as Interactive Reports, Classic Reports, Charts and Calendars — let you connect directly to the REST API. To make use of a REST Data Source, you simply step through the corresponding create page wizard and select “REST Data Source” as the data source. You can then choose which columns to include in the report, or for a chart or calendar page, choose which column(s) to use as the display and value columns.
The following shows an application created by using the NYC 311 Service Calls API. In this example, the developer used an APEX Interactive Grid, selecting for the report the most relevant column from the dataset. Using the built-in chart functionality, the developer added an Oracle JET chart that shows in which of NYC’s five boroughs (Brooklyn, Manhattan, Queens, Staten Island and the Bronx) the most 311 calls are being placed.
Chart shows the 1,000 most recent 311 calls by New York City borough.
Once the data is in your APEX app, it can be easily extended to include maps, smart filters and search. It’s one example of how a low-code platform can help you quickly build an application around all that freely available municipal data. The open data information in this article should help you find and access that data with the low-code application of your choice.