Socrata datasets, including private datasets, can be accessed through a unique OData endpoint, allowing users to seamlessly connect to their data through a number of different tools.
As of 11/06/2017, Socrata's OData endpoints will also support OData v4, following the URL format of https://$domain/api/odata/v4/$dataset_identifier. If you are using the previous version (v2) of OData, endpoints follow this URL structure: https://$domain/OData.svc/$dataset_identifier, we recommend updating to the latest version.
Finding the OData Endpoint
You can easily locate the OData endpoint for a dataset on both the dataset's Primer page and through the data table.
To access the endpoint through Socrata Primer, select the "..." button on the top right of the screen. This will open up a drop-down where you can select "Access Data via OData". This will bring up a pop-up screen which will contain the OData Endpoint for the dataset. You can click on the OData box to choose either the v2 or the v4 endpoint.
This endpoint can also be found in the table view. When viewing the dataset select the “Export” pane and choose the OData section and copy either of the two listed OData endpoints.
Analyzing OData through Microsoft Excel
OData connections are often analyzed through Microsoft Excel. Supported versions of Excel and Power BI for use with Socrata OData are:
- Power Query Version 2.40.4554.161 or newer - Excel 2010 / Excel 2013
- Get & Transform Tool - Excel 2016 version 16.0.6965.2117 or newer
- Power BI Desktop - Version 2.42.4611.901 or newer
For more information on using Excel to connect to your OData endpoint, please see the following article.
Authenticating in OData
In some cases, it is required to authenticate with your Socrata username and password to access an Odata feed. This can be done using the basic authentication option in most software that offers Odata connections.
The main cases for requiring authentication is:
- When the dataset or view you are connecting to is Private.
- When the dataset or view has not been accepted in View Moderation or Routing and Approval. Even though these assets are not private, since they are hidden from the catalog, connecting to these assets will require authentication. (Hint: You can tell if an asset is not approved when it can be viewed on your browser without logging in but the asset is not available in the Public Catalog).
Querying using OData
You can also directly query Socrata data using OData as a REST API. Socrata datasets follow the OData URI Conventions documented in http://docs.oasis-open.org/odata/odata/v4.0/errata03/os/complete/part2-url-conventions/odata-v4.0-errata03-os-part2-url-conventions-complete.html#_Toc453752335. For more information on OData, see http://www.odata.org/documentation/
Here are some examples of how to apply these conventions to a Socrata catalog and dataset:
List all the datasets available for a domain.
Retrieve all the items in a particular dataset.
Retrieve one item, by identifier
Retrieving a particular field within the item
The OrderBy Query option
The Top Query option
The Skip Query Option
The Filter Query Option
The Select Query Option
The InlineCount Query Option
Socrata Datatypes exposed as OData
This table lists the OData types used to represent Socrata data.
|Date & Time||Edm.DateTimeOffset|
|Date & Time (with timezone)||Edm.DateTimeOffset|
|Document||Complext type with Edm.String listing the URL to the document and Edm.String listing the name of the document|
|Location||Complex type with Edm.Decimal for latitude/longitude and Edm.String for Address, City, State and Zip|
|Phone||Complext type with Edm.String listing the phone number and Edm.String listing the phone type|
|Photo||Edm.String listing the URL to the photo|
|Text (Formatted)||Edm.String with HTML tags|
|Website URL||Complex type with Edm.String listing the URL and Edm.String listing the description|
Four data types are not exposed via OData. These are: Star, Flag, Nested Table and Dataset Link.