Validation of Data Dictionary and Joins

[chevan]

Hello I’m exploring the data sets and wanted to verify the data dictionary and join keys:

Dataset 1: changesByDate

Column Name	Data Type	Description
Date	Date	The date of the entry, indicating when the changes were logged.
OPEN	Integer	The number of supercharge stations that opened on the specified date.
PERMIT	Integer	The number of supercharge stations for which permits were issued on the specified date.
CONSTRUCTION	Integer	The number of supercharge stations that were under construction on the specified date.

Dataset 2: allSites

Column Name	Data Type	Description
id	Integer	Unique identifier for each supercharging station entry.
locationId	String	A unique location identifier for the supercharging station, typically a descriptive string.
name	String	Name or description of the supercharging station, often including the city and state or region.
status	String	Current operational status of the station (e.g., OPEN, CLOSED).
address	Object	Nested structure containing detailed address information.
- street	String	Street address of the supercharging station.
- city	String	City where the supercharging station is located.
- state	String	State or province where the supercharging station is located.
- zip	String	Postal code for the supercharging station location.
- countryId	Integer	Numeric identifier for the country where the station is located.
- country	String	Country where the supercharging station is located.
- regionId	Integer	Numeric identifier for the region where the station is located.
- region	String	Region where the supercharging station is located (e.g., North America, Europe).
gps	Object	Nested structure containing GPS coordinates.
- latitude	Float	Latitude of the supercharging station.
- longitude	Float	Longitude of the supercharging station.
dateOpened	Date	Date when the supercharging station was opened.
stallCount	Integer	Number of charging stalls available at the station.
counted	Boolean	Indicates whether the station’s stall count is included in Tesla’s official network count.
elevationMeters	Integer	Elevation of the station in meters above sea level.
powerKilowatt	Integer	Maximum power output of the chargers at the station, in kilowatts.
solarCanopy	Boolean	Indicates whether the station is equipped with a solar canopy.
battery	Boolean	Indicates if there is battery storage available at the station.
otherEVs	Boolean	Indicates if chargers are compatible with electric vehicles other than Tesla.
statusDays	Integer	Number of days the station has maintained its current status.
urlDiscuss	Boolean	Indicates if a URL or forum link for discussion about the station is available.
stalls	Object	Detailed information about types of charging stalls available at the station.
- v2	Integer	Number of version 2 Tesla supercharger stalls.
- v3	Integer	Number of version 3 Tesla supercharger stalls.
- v4	Integer	Number of version 4 Tesla supercharger stalls.
- accessible	Integer	Number of accessible stalls available for disabled access.
- trailerFriendly	Integer	Number of stalls accessible for vehicles with trailers.
plugs	Object	Details about the types and numbers of charging plugs available at the station.
- tpc	Integer	Number of Tesla proprietary connector plugs.
- nacs	Integer	Number of North American Charging Standard plugs.
- ccs1	Integer	Number of Combined Charging System version 1 plugs.
- ccs2	Integer	Number of Combined Charging System version 2 plugs.
- gbt	Integer	Number of Chinese GB/T standard plugs.
- multi	Integer	Number of multi-standard plugs that support more than one type of connector.
- type2	Integer	Number of Type 2 connector plugs (common in Europe).
parkingId	Integer	An identifier for the parking facility where the charging station is located.
facilityName	String	Name of the facility where the charging station is housed (e.g., shopping center, parking lot).
facilityHours	String	Operating hours of the facility where the charging station is located.
plugshareId	Integer	Identifier for the station on PlugShare, a popular EV charging station locator and review platform.
osmId	Integer	Identifier for the station on OpenStreetMap, providing mapping details.
accessNotes	String	Additional notes about accessing the charging station, such as required adapters or restrictions.

Dataset 3: allChanges

Column Name	Data Type	Description
id	Integer	Unique identifier for the change record.
siteId	Integer	Identifier linking to a specific supercharging site.
date	Date	The date when the change was recorded.
changeType	String	Type of change made (e.g., ‘ADD’, ‘UPDATE’).
siteStatus	String	Current status of the site after the change (e.g., ‘OPEN’, ‘CONSTRUCTION’).
prevStatus	String	Previous status of the site before the change, if any.
stallCount	Integer	Number of charging stalls after the change.
prevCount	Integer	Number of charging stalls before the change, if known.
powerKilowatt	Integer	Power capacity of the site in kilowatts after the change.
notify	Boolean	Indicates whether notifications for the change were sent out.
siteName	String	Descriptive name of the supercharging site.
regionId	Integer	Numeric identifier for the region where the site is located.
region	String	Name of the region where the site is located (e.g., ‘Asia Pacific’, ‘Europe’).
countryId	Integer	Numeric identifier for the country where the site is located.
country	String	Country where the site is located.
state	String	State or province where the site is located.
site	Object	Detailed information about the site including its complete address, GPS coordinates, and more.
- locationId	String	A unique location identifier specific to each site.
- address	Object	Nested structure containing detailed address information.
– street	String	Street address of the supercharging site.
– city	String	City where the supercharging site is located.
– state	String	State or province where the supercharging site is located.
– zip	String	Postal code for the supercharging site location.
– countryId	Integer	Numeric identifier for the country where the site is located.
– country	String	Country where the site is located.
– regionId	Integer	Numeric identifier for the region where the site is located.
– region	String	Region where the site is located.
- gps	Object	Nested structure containing GPS coordinates.
– latitude	Float	Latitude of the supercharging site.
– longitude	Float	Longitude of the supercharging site.
- dateOpened	Date	Date when the supercharging site was opened.
- stallCount	Integer	Number of charging stalls at the site.
- counted	Boolean	Indicates whether the station’s stall count is included in Tesla’s official network count.
- elevationMeters	Integer	Elevation of the site in meters above sea level.
- powerKilowatt	Integer	Power capacity of the chargers at the site, in kilowatts.
- solarCanopy	Boolean	Indicates whether the site is equipped with a solar canopy.
- battery	Boolean	Indicates if there is battery storage available at the site.
- otherEVs	Boolean	Indicates if chargers are compatible with electric vehicles other than Tesla.
- statusDays	Integer	Number of days the site has maintained its current status.
- urlDiscuss	Boolean	Indicates if a URL or forum link for discussion about the site is available.
- stalls	Object	Detailed information about types of charging stalls available at the site.
– v2	Integer	Number of version 2 Tesla supercharger stalls.
– v3	Integer	Number of version 3 Tesla supercharger stalls.
– v4	Integer	Number of version 4 Tesla supercharger stalls.
– accessible	Integer	Number of accessible stalls available for disabled access.
– trailerFriendly	Integer	Number of stalls accessible for vehicles with trailers.
- plugs	Object	Details about the types and numbers of charging plugs available at the site.
– tpc	Integer	Number of Tesla proprietary connector plugs.
– nacs	Integer	Number of North American Charging Standard plugs.
– ccs1	Integer	Number of Combined Charging System version 1 plugs.
– ccs2	Integer	Number of Combined Charging System version 2 plugs.
– gbt	Integer	Number of Chinese GB/T standard plugs.
– multi	Integer	Number of multi-standard plugs that support more than one type of connector.
– type2	Integer	Number of Type 2 connector plugs (common in Europe).
parkingId	Integer	An identifier for the parking facility where the charging station is located.
facilityName	String	Name of the facility where the charging station is housed (e.g., shopping center, parking lot).
facilityHours	String	Operating hours of the facility where the charging station is located.
plugshareId	Integer	Identifier for the site on PlugShare, a popular EV charging station locator and review platform.
osmId	Integer	Identifier for the site on OpenStreetMap, providing mapping details.
accessNotes	String	Additional notes about accessing the charging station, such as required adapters or restrictions.
dateFormatted	String	Formatted date string representing the date of the change.

Joins Summary

Join Type	Primary Dataset	Secondary Dataset	Join Key(s)	Description
Join by Site ID	allChanges_df	allsites_df	siteId in allChanges_df to id in allsites_df	Links change records to the specific sites, providing complete site details for each change.
Join by Location ID	allChanges_df	allsites_df	site.locationId in allChanges_df to locationId in allsites_df	Provides a direct link if the change records include specific location identifiers matching the allsites dataset.
Join by Region and Country	allsites_df	allChanges_df	regionId and countryId in both datasets	Allows aggregation or analysis of changes based on regional or country-specific data, enhancing geographical insights.
Date-based Join	changesByDate_df	allChanges_df	Date in changesByDate_df to date in allChanges_df	Correlates specific date-based changes from allChanges_df with aggregated daily changes in changesByDate_df.

[paulkva]

Hey, just wanted to let you know that I just saw this post and will try to get back to you soon with a more thorough answer. At a glance I don’t see any glaring issues with anything you’ve written. But please note that the allChanges endpoint hasn’t been released yet, it’s currently only available on a development server. I’m hoping to get it onto our test instance tonight (or some time this week).

Welcome, and thanks for exploring and asking! (And for documenting!)

[Rovastar]

Maybe missing stalls urban?

[paulkva]

@chevan

Hello I’m exploring the data sets and wanted to verify the data dictionary and join keys:

I’ve copied most of your content below and put suggested changes/additions in bold text. Hope this helps and happy to discuss any of it further. If you don’t mind, we might even borrow some of it to build out more “official” documentation.

Dataset 1: changesByDate

This can be generalized to:

Column Name	Data Type	Description
Date	Date	The date of the entry, indicating when the changes were logged.
{STATUS}	Integer	The number of supercharge stations that were updated with the given status on the given date. {STATUS} can be any of the following: CLOSED_PERM, CLOSED_TEMP, VOTING, PLAN, PERMIT, CONSTRUCTION, EXPANDING, OPEN.

Dataset 2: allSites

Each site:

Column Name	Data Type	Description
id	Integer	Unique identifier for each supercharging station entry.
locationId	String	A unique location identifier for the supercharging station, typically a descriptive string.
name	String	Name or description of the supercharging station, often including the city and state or region.
status	String	Current operational status of the station (e.g., OPEN, CLOSED).
address	Object	Nested structure containing detailed address information.
- street	String	Street address of the supercharging station.
- city	String	City where the supercharging station is located.
- state	String	State or province where the supercharging station is located.
- zip	String	Postal code for the supercharging station location.
- countryId	Integer	Numeric identifier for the country where the station is located.
- country	String	Country where the supercharging station is located.
- regionId	Integer	Numeric identifier for the region where the station is located.
- region	String	Region where the supercharging station is located (e.g., North America, Europe).
gps	Object	Nested structure containing GPS coordinates.
- latitude	Float	Latitude of the supercharging station.
- longitude	Float	Longitude of the supercharging station.
dateOpened	Date	Date (if any) when the supercharging station was opened.
hours	String	For sites that are not open 24/7, text indicating hours of availability for charging. (Note this is separate from the “facilityHours” field.) If this field is missing/null/blank, the site is available for charging 24/7.
stallCount	Integer	Number of charging stalls available at the station.
counted	Boolean	Indicates whether the station’s stall count is included in Tesla’s official network count.
elevationMeters	Integer	Elevation of the station in meters above sea level.
powerKilowatt	Integer	Maximum power output of the chargers at the station, in kilowatts.
solarCanopy	Boolean	Indicates whether the station is equipped with a solar canopy.
battery	Boolean	Indicates if there is battery storage available at the station.
otherEVs	Boolean	Indicates if chargers are compatible with electric vehicles other than Tesla. (Note: use this in conjunction with the “plugs” field to determine whether an adapter is required.)
statusDays	Integer	For sites that are not OPEN, number of days since the site changed to its current status. (For OPEN sites, use the “dateOpened” field instead.)
urlDiscuss	Boolean	Indicates if a URL or forum link for discussion about the station is available.
stalls	Object	Detailed information about types of charging stalls available at the station.
- urban	Integer	Number of urban Tesla supercharger stalls.
- v2	Integer	Number of version 2 Tesla supercharger stalls.
- v3	Integer	Number of version 3 Tesla supercharger stalls.
- v4	Integer	Number of version 4 Tesla supercharger stalls.
- other	Integer	Number of stalls of unknown or other type/version.
- accessible	Integer	Number of accessible stalls designated for disabled access.
- trailerFriendly	Integer	Number of stalls accessible for vehicles with trailers.
plugs	Object	Details about the types and numbers of charging plugs available at the station.
- tpc	Integer	Number of Tesla proprietary connector plugs.
- nacs	Integer	Number of North American Charging Standard plugs.
- ccs1	Integer	Number of Combined Charging System version 1 plugs.
- ccs2	Integer	Number of Combined Charging System version 2 plugs.
- gbt	Integer	Number of Chinese GB/T standard plugs.
- multi	Integer	Number of stalls that support more than one type of connector (e.g. via multiple cables or MagicDock).
- other	Integer	Number of plugs of unknown or other type/version.
- type2	Integer	Number of Type 2 connector plugs (AKA Mennekes, common in Europe).
parkingId	Integer	An identifier for the parking rules and fees where the charging station is located. (See Dataset 4 below.)
facilityName	String	Name of the host or primary facility where the charging station is housed (e.g., shopping center, parking lot).
facilityHours	String	Operating hours of the host or primary facility where the charging station is located. (Note: This is separate from the “hours” field. If this field is blank or missing, the facility’s hours are simply unknown / not recorded.)
plugshareId	Integer	Identifier for the station on PlugShare, a popular EV charging station locator and review platform.
osmId	Integer	Node Identifier for the station on OpenStreetMap, providing mapping details.
accessNotes	String	Additional notes about accessing the charging station, such as site-specific parking rules/fees/hours, required adapters or restrictions.
addressNotes	String	Additional notes about locating the charging station, such as which entrance to use, what level of the garage, etc.

Dataset 3: allChanges

Column Name	Data Type	Description
id	Integer	Unique identifier for the change record.
siteId	Integer	Identifier linking to a specific supercharging site.
date	Date	The date when the change was recorded.
changeType	String	Type of change made (e.g., ‘ADD’, ‘UPDATE’).
siteStatus	String	Current status of the site after the change (e.g., ‘OPEN’, ‘CONSTRUCTION’).
prevStatus	String	Previous status of the site before the change, if any.
stallCount	Integer	Number of charging stalls after the change.
prevCount	Integer	Number of charging stalls before the change, if known.
powerKilowatt	Integer	Power capacity of the site in kilowatts.
notify	Boolean	Indicates whether notifications for the change were sent out.
siteName	String	Descriptive name of the supercharging site.
regionId	Integer	Numeric identifier for the region where the site is located.
region	String	Name of the region where the site is located (e.g., ‘Asia Pacific’, ‘Europe’).
countryId	Integer	Numeric identifier for the country where the site is located.
country	String	Country where the site is located.
state	String	State or province where the site is located.
site	Object	Detailed information about the site including its complete address, GPS coordinates, and more. Same fields as Dataset 2. Note that some of the top-level fields in the “allChanges” dataset (e.g. powerKilowatt, region, country, state) are redundant copies of what’s in the “site” object and might be removed or changed in a future release.
dateFormatted	String	Formatted date string representing the date of the change.

Dataset 4: parking

There are currently only 7 rows in this lookup table; it is meant to indicate a general policy (e.g. free always vs sometimes, self vs valet, etc). It is NOT meant to be a detailed, site-specific value.

Column Name	Data Type	Description
parkingId	Integer	Unique identifier.
name	String	Short name of parking rules and fees (e.g. “Free at all times” or “Paid - self parking”).
description	String	Detailed description of parking rules and fees.

Joins Summary

Not sure specifically what you were looking for here. For joining among supercharge.info datasets, I generally recommend using siteId, regionId, or countryId. Use locationId only for joining with Tesla’s own data.

[chevan]

I’m happy to collaborate with you on building out official documentation, let me know how I can help. Also, I just wanted to check-in and see if the production endpoints have been updated / are available yet. No rush. Thanks, Chevan

[paulkva]

Ah yes – 4.1.0 was released on May 15, I think a couple hours after I posted my previous reply here. Sorry, I meant to follow up here but forgot …and actually I still have a few other posts elsewhere that need follow-up.

I’ll tag in @corywright & @Rovastar on the question of how best to help with documentation. Short answer is I’m not sure what would be best but I expect we’re open to ideas. We do have an overview-level API reference which I recently updated, and @Rovastar has been documenting a few things via other forum posts, but beyond that I’d say we don’t have a ton of reference materials currently. Thank you for the offer!

[chevan]

Maybe create a documentation repo on github and link changes from the API as issues to keep them sync’d? also @corywright shared 3 API links with me. Are those public links? Are they published anywhere?

[corywright]

I think it’d be better to keep the documentation in the primary API repository, that way any changes to the API could be committed with corresponding documentation updates.

There is a “documents” directory that contains some existing API documentation in .ods format. Might be nice to have it in a more easily readable/editable format such as markdown.

If you would like to contribute documentation (which would be greatly appreciated!), perhaps clone the supercharge.info-api repository and begin adding to the documents directory. Then submit a pull request on github to have those changes reviewed and added to the primary repository.

[chevan]

Should I create a branch for my local changes? Do you have a naming convention for branches?

[corywright]

We don’t have any conventions for branch name. Maybe prefix your branches with docs- or something that would give an indication of what the branch is addressing.

[chevan]

I converted the api-reference spreadsheet into a markdown document. Not sure what I’m looking at there to provide more detail. Any help?

[paulkva]

You’re looking at the complete set of possible API links, with a rough summary of what each link does. So, for example, your “dataset 1” above (changesByDate) is represented on row 6 of the spreadsheet. It’s within the “ChangeLogController” section because that’s the name of the Java file that contains the logic that returns “dataset 1.”

Does that help?

[paulkva]

@paulkva

You’re looking at the complete set of possible API links

Correction: it’s not quite complete. I just found several other endpoints that weren’t documented at all. Looks like you haven’t created a fork yet, so I’ll try to get the existing doc updated ASAP, at least in one of my branches.

[paulkva]

API reference updates are part of the latest PR on GitHub, which also includes another endpoint (restricted to editors).

[chevan]

Hello- blast from the past. I created a fork GitHub - chevan-nanayakkara/supercharge.info-api: supercharge.info API and ran the excel spreadsheet and the work that we did before through ChatGPT to create two new API docs, one for users interested in the data and then there were some POST commands available, so I created a separate doc for programmatic access. I’m not sure how correct these are but though this would be a good starting point.

API_DATA_ACCESS.md
API_PROGRAMMATIC_ACCESS.md

Let me know if you have any questions or if there’s anything more I can do to help. I assume there are probably updates to the API since May so I’m happy to incorporate those in as well. Also if you’d like to merge these documents into your release process, let me know how you’d like to synchronize that going forward.

Thanks,
Chevan