We Are Here To Help

Follow

Access Socrata Data using Odata

Socrata 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.

Pasted_Image_11_2_17__10_22_AM.png

 

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:

  1. Power Query Version 2.40.4554.161 or newer - Excel 2010 / Excel 2013
  2. Get & Transform Tool - Excel 2016 version 16.0.6965.2117 or newer
  3. 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

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.

https://sandbox.demo.socrata.com/api/odata/v4/

Retrieve all the items in a particular dataset.

https://sandbox.demo.socrata.com/api/odata/v4/nimj-3ivp 

Retrieve one item, by identifier

https://sandbox.demo.socrata.com/api/odata/v4/nimj-3ivp(16) 

Retrieving a particular field within the item

https://sandbox.demo.socrata.com/api/odata/v4/nimj-3ivp(16)/depth/$value 

The OrderBy Query option

https://sandbox.demo.socrata.com/api/odata/v4/nimj-3ivp?$orderby=magnitude 

The Top Query option

https://sandbox.demo.socrata.com/api/odata/v4/nimj-3ivp?$top=2 

The Skip Query Option

https://sandbox.demo.socrata.com/api/odata/v4/nimj-3ivp?$skip=1005 

The Filter Query Option

https://sandbox.demo.socrata.com/api/odata/v4/nimj-3ivp?$filter=magnitude%20gt%205 

The Select Query Option

https://sandbox.demo.socrata.com/api/odata/v4/nimj-3ivp?$select=magnitude,depth 

The InlineCount Query Option

https://sandbox.demo.socrata.com/api/odata/v4/nimj-3ivp?$filter=magnitude%20gt%205&$inlinecount=allpages 

Socrata Datatypes exposed as OData

This table lists the OData types used to represent Socrata data.

Socrata Datatype ODataDatatype
Checkbox Edm.Boolean
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
Email Edm.String
Location Complex type with Edm.Decimal for latitude/longitude and Edm.String for Address, City, State and Zip
Money Edm.Decimal
Multiple Choice Edm.String
Number Edm.Decimal
Percent Edm.Decimal
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
Text (Plain) Edm.String
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.

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments

Powered by Zendesk