OData metadata and URIs

user_13102306 0
Viewed 264

First off, I'll say that this is no where near my field of expertise. I am pulling this data into an Excel Power Query from a public OData feed, below is the metadata url:

https://safetydata.fra.dot.gov/MasterWebService/PublicApi/frads/v1/odata/gcis/$metadata

When I request a dataset, it returns a "Crossing" with each of those Properties defined in that metadata above (can't post a link to a dataset since it requires a token), but below is essentially what's returned.

<ArrayOfCrossing>
<Crossing>
<Property></Property>
</Crossing>
</ArrayOfCrossing>

Where I am lost, is how I drill down into that metadata and return the metadata about each property. For instance, the property 'CntyCD' (County) returns a code that refers to a County. Instead of the code, I want the County name.

What I have tried:

  • Reviewing OData 2, 3, and 4 URI references
  • Spent hours reviewing different sites, reading and trying all sorts of different URI combinations

There's not much documentation on the API website other than forming basic queries.

If anyone could take a look at this metadata url and help me understand the "shape" of the metadata or point me to any resources I would be extremely grateful.

odata metadata uri
2 Answers

For anyone finding this later, the answer is you cannot derive any additional metadata from this. You have to build your own lookup tables from various sources on their website.

My source to this solution is the folks who administer the data source (Federal Railroad Administration) who pointed me to this URL: https://safetydata.fra.dot.gov/OfficeofSafety/publicsite/DownloadCrossingInventoryData.aspx

The above url contains various lookup tables in CSV format under the heading “Reference Data and Documents”, as well as a pdf explaining the coding for each field. From what I can see, most of the fields have their values listed in one or more of those downloadable files.

Not the solution I was hoping for, but it works. I was hoping to get the values from a query and not by maintaining my own table data, which will need to be updated as they make any changes to their lookups.

user_131023060

OData is great, $metadata is a rich descriptive language that allows the API developers to express the structural bounds of the schema. A well defined service $metadata you should be able to describe most valid input and output expessions. The metadata for the property in question is this:

<Property Name="CntyCD" Type="Edm.String" frads:DisplayName="County"/>

Unfortunately, as this property is described as a String with no validation or other constraints, and there are no further annotations regarding this property in the $metadata, then there is no further information that can be found using the standards simply because their schema is not well designed to allow you to obtain any further information.

So this isn't a deficiency in the OData Protocol itself, just a deficiency in the specific API that you are querying.

A common way to specify that one property is dependant on a specific list of values is to include a navigation property, or at least to specify that a property returns a collection or instance of a complex type. if we look at the TripPinService you can see this type of information:

<EntityType Name="Person">
  <Key>
    <PropertyRef Name="UserName"/>
  </Key>
  <Property Name="UserName" Type="Edm.String" Nullable="false"/>
  <Property Name="FirstName" Type="Edm.String" Nullable="false"/>
  <Property Name="LastName" Type="Edm.String" MaxLength="26"/>
  <Property Name="MiddleName" Type="Edm.String"/>
  <Property Name="Gender" Type="Trippin.PersonGender" Nullable="false"/>
  <Property Name="Age" Type="Edm.Int64"/>
  <Property Name="Emails" Type="Collection(Edm.String)"/>
  <Property Name="AddressInfo" Type="Collection(Trippin.Location)"/>
  <Property Name="HomeAddress" Type="Trippin.Location"/>
  <Property Name="FavoriteFeature" Type="Trippin.Feature" Nullable="false"/>
  <Property Name="Features" Type="Collection(Trippin.Feature)" Nullable="false"/>
  <NavigationProperty Name="Friends" Type="Collection(Trippin.Person)"/>
  <NavigationProperty Name="BestFriend" Type="Trippin.Person"/>
  <NavigationProperty Name="Trips" Type="Collection(Trippin.Trip)"/>
</EntityType>

However even then, the specific validation rules become more data related, even if the structure is correct. We can only inspect data values by querying them from somewhere. In the case of TripPin, it does provide endpoints to lookup the necessary information.

If there was a finite list of possible static values, then this should be defined in the $metadata as an Enum, an example again from TripTip where there is a PersonGender enum defined:

<EnumType Name="PersonGender">
  <Member Name="Male" Value="0"/>
  <Member Name="Female" Value="1"/>
  <Member Name="Unknown" Value="2"/>
</EnumType>

As this is a question about data and not the schema. And this API does not provide an endpoint to return the possible CntyCD values then one option is to query the unique values for CntyCD from this table:

https://safetydata.fra.dot.gov/MasterWebService/PublicApi/frads/v1/odata/gcis/Crossings?$apply=groupby((CntyCD))

NOTE: This resultset can not be guaranteed to be a finite list of ALL possible values, but it might be adequate for your needs.

When dealing with public or government APIs, when the data structure assumes that you must already know about some aspects of the data, then they are likely to have published that list of data in a separate location.

A good place to start is to goto the host or root of the API's URL to see if there is any documentation served as html or some other browsable form. In this case https://safetydata.fra.dot.gov does provide a listing and in the menu you can see they have Downloads and in that list is FRA Auxiliary Tables

If that information was not available, then the local postal service for any region is usually a great place to start looking for location demographic information.

user_16902170
Related