https://tech.marksblogg.com/open-charge-map-global-ev-charging-point-dataset.html Mark Mark Litwintschik I have 15 years of consulting & hands-on build experience with clients in the UK, USA, Sweden, Ireland & Germany. Past clients include Bank of America Merrill Lynch, Blackberry, Bloomberg, British Telecom, Ford, Google, ITV, LeoVegas, News UK, Pizza Hut, Royal Mail, T-Mobile, Williams Formula 1, Wise & UBS. I hold both a Canadian and a British passport. My CV, Twitter & LinkedIn. Home | Benchmarks | Categories | Atom Feed Posted on Mon 01 April 2024 under GIS Global EV Charging Points with Open Charge Map Open Charge Map (OCM) aims to document the world's Electric Vehicle (EV) Charging Points. To date, they have produced a dataset of over 200K+ charging point locations around the world. Data is sourced both from volunteers as well as official sources. The project is led by Christopher Cook, a software developer from Perth, Australia, who has been working on OCM since its inception in 2011. Below is a heat map of the OCM dataset. Operational Charge Points around the World. In the EU at least, Charge Point Location density resembles that of the population density map for this region. Operational Charge Points in Europe, the Middle East and North Africa When you open the OCM website or app, you'll see a map of charging points. Open Charge Map Overview Each point contains information on the number of station / bays available at the charging location as well as the sockets supported and power delivery. There will be an indication if the station is operational or not, if it's accessible to the public and pricing details. Open Charge Map Location Details Users can also submit photographs of the location, its equipment and the surrounding area. Open Charge Map Location Photo In this blog post, I'll walk through making the OCM dataset analytics-ready and walk through some analysis of the dataset. My Workstation I'm using a 6 GHz Intel Core i9-14900K CPU. It has 8 performance cores and 16 efficiency cores with a total of 32 threads and 32 MB of L2 cache. It has a liquid cooler attached and is housed in a spacious, full-sized, Cooler Master HAF 700 computer case. I've come across videos on YouTube where people have managed to overclock the i9-14900KF to 9.1 GHz. The system has 48 GB of DDR5 RAM clocked at 5,200 MHz and a 5th-generation, Crucial T700 4 TB NVMe M.2 SSD which can read at speeds up to 12,400 MB/s. There is a heatsink on the SSD to help keep its temperature down. This is my system's C drive. There is also an 8 TB mechanical disk that I use to hold GIS datasets. This is my system's F drive. The system is powered by a 1,200-watt, fully modular, Corsair Power Supply and is sat on an ASRock Z790 Pro RS Motherboard. I'm running Ubuntu 22 LTS via Microsoft's Ubuntu for Windows on Windows 11 Pro. In case you're wondering why I don't run a Linux-based desktop as my primary work environment, I'm still using an Nvidia GTX 1080 GPU which has better driver support on Windows and I use ArcGIS Pro from time to time which only supports Windows natively. Installing Prerequisites I'll first install some dependencies. $ sudo apt update $ sudo apt install \ git \ jq \ libimage-exiftool-perl \ python3-virtualenv Below I'll set up the Python Virtual Environment. $ virtualenv ~/.ev $ source ~/.ev/bin/activate $ python -m pip install \ rich Below I'll download and install v0.10.1 of the official binary for DuckDB. $ cd ~ $ wget -c https://github.com/duckdb/duckdb/releases/download/v0.10.1/duckdb_cli-linux-amd64.zip $ unzip -j duckdb_cli-linux-amd64.zip $ chmod +x duckdb I'll install a few extensions for DuckDB and make sure they load automatically each time I launch the client. $ ~/duckdb INSTALL json; INSTALL parquet; INSTALL spatial; $ vi ~/.duckdbrc .timer on .width 180 LOAD json; LOAD parquet; LOAD spatial; The maps in this post were rendered with QGIS version 3.36. QGIS is a desktop application that runs on Windows, macOS and Linux. The software is very popular in the geospatial industry and was launched almost 15 million times in May last year. I used QGIS' Tile+ plugin to add satellite basemaps. Tile+ can be installed from its ZIP distributable. The steps needed to reproduce the basemap used in this post can be found in my Aircraft Route Analysis post. Downloading Open Charge Map's Data The following will clone 204,187 files totalling 307 MB from GitHub. This repository is refreshed monthly. $ git clone https://github.com/openchargemap/ocm-export ~/open_charge_map I'll also download a dataset that will help me categorise countries into groups. For instance, Scandinavia, Finland and the Baltics will be classed as 'northernEurope'. $ git clone https://github.com/vijinho/ISO-Country-Data ~/ISO-Country-Data Open Charge Map's Data Structure OCM breaks up data by country and location. Below is an example from a location in Estonia. $ cd ~/open_charge_map $ jq -S 'del(.AddressInfo)' data/EE/OCM-11842.json { "Connections": [ { "Amps": 120, "ConnectionTypeID": 2, "CurrentTypeID": 30, "ID": 9252, "LevelID": 3, "PowerKW": 50, "Quantity": 1, "StatusTypeID": 50, "Voltage": 500 }, { "Amps": 32, "ConnectionTypeID": 25, "CurrentTypeID": 20, "ID": 25811, "LevelID": 2, "PowerKW": 22, "Quantity": 1, "StatusTypeID": 50, "Voltage": 400 } ], "DataProviderID": 1, "DateCreated": "2012-08-03T13:08:00Z", "DateLastStatusUpdate": "2014-05-29T04:52:00Z", "DateLastVerified": "2014-05-29T04:52:00Z", "GeneralComments": "Charger number 37001", "ID": 11842, "NumberOfPoints": 1, "OperatorID": 76, "StatusTypeID": 50, "SubmissionStatusTypeID": 200, "UUID": "810B3C57-F495-4028-B364-937576BD75AB", "UsageCost": "5EUR/up to 60min", "UsageTypeID": 1 } Each file is a location which will be run by a network operator. Each location will have one or more charging stations / bays. A station can have one or more sockets as well as various power delivery specifications. These sockets and power delivery specifications are listed individually in the 'Connections' dictionary and each connection will have a quantity associated with it. In the above example, only a single vehicle can be charged at any one time at this location and both a 50 kW, DC, 120A, 500V CHAdeMO socket as well as a 22 kW, AC (Three-Phase), 32A, 400V Type 2 socket are supported. The location files use primary keys for many attributes. Their corresponding records can be found in a referencedata.json file included in the git repository. Below are the 13 reference table names for this project. $ jq -S '.|keys' referencedata.json [ "ChargerTypes", "CheckinStatusTypes", "ConnectionTypes", "Countries", "CurrentTypes", "DataProviders", "DataTypes", "MetadataGroups", "Operators", "StatusTypes", "SubmissionStatusTypes", "UsageTypes", "UserCommentTypes" ] Below are the primary keys and attributes for the charger types. $ jq -S .ChargerTypes referencedata.json [ { "Comments": "Under 2 kW, usually domestic socket types", "ID": 1, "IsFastChargeCapable": false, "Title": "Level 1 : Low (Under 2kW)" }, { "Comments": "Over 2 kW, usually non-domestic socket type", "ID": 2, "IsFastChargeCapable": false, "Title": "Level 2 : Medium (Over 2kW)" }, { "Comments": "40KW and Higher", "ID": 3, "IsFastChargeCapable": true, "Title": "Level 3: High (Over 40kW)" } ] Charge Station Imagery User-submitted photographs are also listed in this dataset. Within any one location, one or more images may be available. A URL to a thumbnail as well as the original image are listed. $ jq -S '.MediaItems[0]' data/EE/OCM-103624.json { "ChargePointID": 103624, "Comment": "", "DateCreated": "2020-07-26T12:19:00Z", "ID": 23192, "IsEnabled": true, "IsExternalResource": false, "IsFeaturedItem": false, "IsVideo": false, "ItemThumbnailURL": "https://media.openchargemap.io/images/EE/OCM103624/OCM-103624.thmb.2020072612183898.jpg", "ItemURL": "https://media.openchargemap.io/images/EE/OCM103624/OCM-103624.orig.2020072612183898.jpg", "User": { "ID": 26470, "Username": "erkkiat" } } The original images make up ~30 GB altogether. There are a few 20 MB PNGs, especially for Italy and Spain, but for the most part, images are ~2 MB. The vast majority of these photos were taken in Europe with the US and Australia being the only other locations of any significance. Below is the proportion of disk space used by the images broken down by region and country. Image Sizes by Country in Open Charge Map The original images don't look to have been through any processing, either to reduce their size or to remove any metadata. Many images contain more than 100 pieces of metadata, such as the image below. $ wget https://media.openchargemap.io/images/ES/OCM64603/OCM-64603.orig.2016031412054624.jpg $ exiftool OCM-64603.orig.2016031412054624.jpg | tail -n20 Image Height : 3264 Encoding Process : Baseline DCT, Huffman coding Bits Per Sample : 8 Color Components : 3 Y Cb Cr Sub Sampling : YCbCr4:2:0 (2 2) Aperture : 2.4 Image Size : 2448x3264 Megapixels : 8.0 Shutter Speed : 1/1374 Create Date : 2016:03:05 11:27:08.053521 Date/Time Original : 2016:03:05 11:27:08.053521 Modify Date : 2016:03:05 11:27:08.053521 Thumbnail Image : (Binary data 5911 bytes, use -b option to extract) GPS Altitude : 70 m Above Sea Level GPS Date/Time : 2016:03:05 10:26:59Z GPS Latitude : 42 deg 10' 36.89" N GPS Longitude : 3 deg 6' 29.49" E Focal Length : 4.0 mm GPS Position : 42 deg 10' 36.89" N, 3 deg 6' 29.49" E Light Value : 12.9 Some photos are great research material with equipment and signage clearly presented. Others show the surrounding area and landscape which could be a replacement for Google Street View. This is handy as it can show if there is a canopy or if it's in underground / sheltered parking of some sort. Below is an image found in the Brazilian dataset showing two charging bays in a parking garage. Open Charge Map Image Surprisingly few people submit photographs of charging point failures. I found this one in the Brazilian dataset. Open Charge Map Image Instructions to not leave your vehicle while charging in Jordan. Open Charge Map Image Below is a photo of a canopy covering the charging bays in Malaysia. Open Charge Map Image Not much space to park in Cyprus. Open Charge Map Image Below is a photo of a charging point User Interface. Open Charge Map Image Some people are kind enough to photograph the specifications on the sides of the charging point machines. Open Charge Map Image Photos often give away what sort of area you'll be waiting around in. Open Charge Map Image Building an Analysis-Ready Dataset Below I'll denormalise the OCM dataset by connection. I'll also use the foreign keys to join to the reference datasets. The data will be first exported into JSONL before being imported into DuckDB using its internal file format. $ python3 from glob import glob import json from urllib.parse import urlparse from rich.progress import track # Lookups / Reference Data recs = json.loads(open('referencedata.json', 'r').read()) lookups = {} for x in recs.keys(): lookups[x] = {} for rec in recs[x]: id_ = rec['ID'] del rec['ID'] lookups[x][id_] = rec # Regions regions = json.loads(open('/home/mark/ISO-Country-Data/regions.json', 'r').read()) # Connections station_keys = ( 'DateLastVerified', 'ID', 'UUID', 'DataProviderID', 'UsageTypeID', 'UsageCost', 'NumberOfPoints', 'StatusTypeID', 'DateLastStatusUpdate', 'DateCreated', 'SubmissionStatusTypeID', 'ProviderBrand') with open('connections.json', 'w') as f: for filename in track(glob('*/OCM-*.json')): recs = json.loads(open(filename, 'r').read()) if 'RelatedURL' in recs['AddressInfo']: recs['ProviderBrand'] = \ urlparse(recs['AddressInfo']['RelatedURL'])\ .netloc\ .replace('www.', '')\ .split('.')[0] country_iso2 = filename.split('/')[0].upper().strip() region = None for region_, countries in regions.items(): if country_iso2 in countries: region = region_ break if region is None and country_iso2 == 'XK': region = 'southernEurope' dp_ = {} if 'DataProviderID' not in recs.keys() else lookups['DataProviders'].get(recs['DataProviderID'], {}) if 'DataProviderStatusType' in dp_.keys(): for k, v in dp_['DataProviderStatusType'].items(): dp_[k] = v del dp_['DataProviderStatusType'] op_ = {} if 'OperatorID' not in recs.keys() else lookups['Operators'].get(recs['OperatorID'], {}) st_ = {} if 'StatusTypeID' not in recs.keys() else lookups['StatusTypes'].get(recs['StatusTypeID'], {}) us_ = {} if 'UsageTypeID' not in recs.keys() else lookups['UsageTypes'].get(recs['UsageTypeID'], {}) ss_ = {} if 'SubmissionStatusTypeID' not in recs.keys() else lookups['SubmissionStatusTypes'].get(recs['SubmissionStatusTypeID'], {}) for rec in recs['Connections']: ct_ = {} if 'ConnectionTypeID' not in rec.keys() else lookups['ConnectionTypes'].get(rec['ConnectionTypeID'], {}) cu_ = {} if 'CurrentTypeID' not in rec.keys() else lookups['CurrentTypes'].get(rec['CurrentTypeID'], {}) cs_ = {} if 'StatusTypeID' not in rec.keys() else lookups['StatusTypes'].get(rec['StatusTypeID'], {}) f.write(json.dumps({**{'AddressCountryIso2': country_iso2, 'AddressWorldRegion': region}, **{'Connector' + k: v for k, v in rec.items()}, **{'Address' + k: v for k, v in recs['AddressInfo'].items()}, **{'DataProvider' + k: v for k, v in dp_.items()}, **{'Operator' + k: v for k, v in op_.items()}, **{'StatusType' + k: v for k, v in st_.items()}, **{'UsageType' + k: v for k, v in us_.items()}, **{'SubmissionStatusType' + k: v for k, v in ss_.items()}, **{'ConnectionType' + k: v for k, v in ct_.items()}, **{'CurrentType' + k: v for k, v in cu_.items()}, **{'ConnectionStatusType' + k: v for k, v in cs_.items()}, **{'Location' + k: recs.get(k, None) for k in station_keys}}) + '\n') The following will convert the JSONL into a DuckDB table using its internal file format. $ ~/duckdb -c "CREATE OR REPLACE TABLE connections aS SELECT * FROM 'connections.json';" \ connections.duckdb The resulting DuckDB file is 98 MB and consists of 373,428 records. Below is an example record. $ ~/duckdb -json \ -c "SELECT * FROM connections WHERE AddressCountryIso2 = 'EE' LIMIT 1;" \ connections.duckdb \ | tail -n1 \ | jq -S . [ { "AddressAccessComments": null, "AddressAddressLine1": "Mere Boulevard 7", "AddressAddressLine2": null, "AddressContactEmail": null, "AddressContactTelephone1": "+372 4473888", "AddressContactTelephone2": null, "AddressCountryID": 74, "AddressCountryIso2": "EE", "AddressDistanceUnit": 0, "AddressID": 103970, "AddressLatitude": 58.380532, "AddressLongitude": 24.491924, "AddressPostcode": "80010", "AddressRelatedURL": null, "AddressStateOrProvince": null, "AddressTitle": "Villa Ammende hotel and restaurant", "AddressTown": "Parnu", "AddressWorldRegion": "northernEurope", "ConnectionStatusTypeIsOperational": true, "ConnectionStatusTypeIsUserSelectable": true, "ConnectionStatusTypeTitle": "Operational", "ConnectionTypeFormalName": null, "ConnectionTypeIsDiscontinued": false, "ConnectionTypeIsObsolete": false, "ConnectionTypeTitle": "Tesla (Model S/X)", "ConnectorAmps": 32, "ConnectorComments": null, "ConnectorConnectionTypeID": 30, "ConnectorCurrentTypeID": 20, "ConnectorID": 146294, "ConnectorLevelID": 2, "ConnectorPowerKW": 22, "ConnectorQuantity": 1, "ConnectorReference": null, "ConnectorStatusTypeID": 50, "ConnectorVoltage": 400, "CurrentTypeDescription": "Alternating Current - Three Phase", "CurrentTypeTitle": "AC (Three-Phase)", "DataProviderComments": null, "DataProviderDateLastImported": null, "DataProviderID": 1, "DataProviderIsApprovedImport": true, "DataProviderIsOpenDataLicensed": true, "DataProviderIsProviderEnabled": true, "DataProviderIsRestrictedEdit": false, "DataProviderLicense": "Licensed under Creative Commons Attribution 4.0 International (CC BY 4.0)", "DataProviderTitle": "Manual Data Entry", "DataProviderWebsiteURL": "http://openchargemap.org", "LocationDataProviderID": 1, "LocationDateCreated": "2018-05-05 05:44:00", "LocationDateLastStatusUpdate": "2020-07-27 14:52:00", "LocationDateLastVerified": "2020-07-27 14:52:00", "LocationID": 103624, "LocationNumberOfPoints": 2, "LocationProviderBrand": null, "LocationStatusTypeID": 50, "LocationSubmissionStatusTypeID": 200, "LocationUUID": "56ae3b26-c6c0-4770-98bb-aa2beb850147", "LocationUsageCost": null, "LocationUsageTypeID": 4, "OperatorAddressInfo": null, "OperatorBookingURL": null, "OperatorComments": null, "OperatorContactEmail": null, "OperatorFaultReportEmail": null, "OperatorIsPrivateIndividual": false, "OperatorIsRestrictedEdit": false, "OperatorPhonePrimaryContact": null, "OperatorPhoneSecondaryContact": null, "OperatorTitle": "Tesla (Tesla-only charging)", "OperatorWebsiteURL": "http://www.teslamotors.com", "StatusTypeIsOperational": true, "StatusTypeIsUserSelectable": true, "StatusTypeTitle": "Operational", "SubmissionStatusTypeIsLive": true, "SubmissionStatusTypeTitle": "Submission Published", "UsageTypeIsAccessKeyRequired": true, "UsageTypeIsMembershipRequired": true, "UsageTypeIsPayAtLocation": false, "UsageTypeTitle": "Public - Membership Required" } ] Charging Points Around The World I'll break down the number of charging locations by region. These will be broken up into categories of how many stations / bays are available at each location. So far, there is only one location in the world, located in Sweden, with more than 500 bays. $ ~/duckdb connections.duckdb SELECT AddressWorldRegion, COUNT(DISTINCT LocationUUID) FILTER (WHERE LocationNumberOfPoints == 1) AS "1", COUNT(DISTINCT LocationUUID) FILTER (WHERE LocationNumberOfPoints >= 2 AND LocationNumberOfPoints < 5) AS "2_4", COUNT(DISTINCT LocationUUID) FILTER (WHERE LocationNumberOfPoints >= 5 AND LocationNumberOfPoints < 20) AS "5_19", COUNT(DISTINCT LocationUUID) FILTER (WHERE LocationNumberOfPoints >= 20 AND LocationNumberOfPoints < 50) AS "20_49", COUNT(DISTINCT LocationUUID) FILTER (WHERE LocationNumberOfPoints >= 50 AND LocationNumberOfPoints < 500) AS "50_499", COUNT(DISTINCT LocationUUID) FILTER (WHERE LocationNumberOfPoints >= 500) AS "500_plus", FROM connections GROUP BY AddressWorldRegion ORDER BY 2 DESC; +--------------------+-------+-------+-------+-------+--------+----------+ | AddressWorldRegion | 1 | 2_4 | 5_19 | 20_49 | 50_499 | 500_plus | | varchar | int64 | int64 | int64 | int64 | int64 | int64 | +--------------------+-------+-------+-------+-------+--------+----------+ | westernEurope | 11561 | 13919 | 1561 | 122 | 10 | 0 | | northernEurope | 7861 | 16216 | 5126 | 418 | 98 | 1 | | southernEurope | 6676 | 17910 | 1607 | 80 | 10 | 0 | | northernAmerica | 3634 | 5589 | 2458 | 193 | 13 | 0 | | easternEurope | 2630 | 1590 | 68 | 1 | 0 | 0 | | australia | 906 | 989 | 136 | 1 | 0 | 0 | | westernAsia | 774 | 360 | 37 | 1 | 0 | 0 | | eastAsia | 446 | 148 | 25 | 18 | 0 | 0 | | caribbean | 341 | 39 | 0 | 0 | 0 | 0 | | southAmerica | 267 | 95 | 7 | 2 | 0 | 0 | | southeastAsia | 247 | 162 | 12 | 1 | 0 | 0 | | polynesia | 156 | 7 | 1 | 0 | 0 | 0 | | centralAmerica | 115 | 45 | 1 | 0 | 0 | 0 | | northAfrica | 98 | 49 | 3 | 0 | 0 | 0 | | southernAsia | 91 | 30 | 1 | 0 | 0 | 0 | | southernAfrica | 35 | 107 | 0 | 0 | 0 | 0 | | eastAfrica | 15 | 7 | 2 | 0 | 0 | 0 | | westAfrica | 2 | 0 | 1 | 0 | 0 | 0 | | centralAsia | 2 | 1 | 1 | 0 | 0 | 0 | +--------------------+-------+-------+-------+-------+--------+----------+ | 19 rows 7 columns | +------------------------------------------------------------------------+ This is a breakdown for countries outside of Europe, North America and Australia. Some of these countries are fairly small and have very good charging point coverage but others, I suspect, are vastly underrepresented in this dataset, like China with 126 locations listed. SELECT AddressCountryIso2, COUNT(DISTINCT LocationUUID) FILTER (WHERE LocationNumberOfPoints == 1) AS "1", COUNT(DISTINCT LocationUUID) FILTER (WHERE LocationNumberOfPoints >= 2 AND LocationNumberOfPoints < 5) AS "2_4", COUNT(DISTINCT LocationUUID) FILTER (WHERE LocationNumberOfPoints >= 5 AND LocationNumberOfPoints < 20) AS "5_19", COUNT(DISTINCT LocationUUID) FILTER (WHERE LocationNumberOfPoints >= 20 AND LocationNumberOfPoints < 50) AS "20_49", COUNT(DISTINCT LocationUUID) FILTER (WHERE LocationNumberOfPoints >= 50 AND LocationNumberOfPoints < 500) AS "50_499", COUNT(DISTINCT LocationUUID) FILTER (WHERE LocationNumberOfPoints >= 500) AS "500_plus", FROM connections WHERE AddressWorldRegion NOT LIKE '%Europe' AND AddressWorldRegion != 'northernAmerica' AND AddressCountryIso2 != 'AU' GROUP BY AddressCountryIso2 ORDER BY 2 DESC LIMIT 20; +--------------------+-------+-------+-------+-------+--------+----------+ | AddressCountryIso2 | 1 | 2_4 | 5_19 | 20_49 | 50_499 | 500_plus | | varchar | int64 | int64 | int64 | int64 | int64 | int64 | +--------------------+-------+-------+-------+-------+--------+----------+ | TR | 522 | 177 | 13 | 1 | 0 | 0 | | NZ | 389 | 510 | 72 | 1 | 0 | 0 | | DO | 331 | 6 | 0 | 0 | 0 | 0 | | JP | 226 | 13 | 1 | 0 | 0 | 0 | | BR | 206 | 51 | 3 | 1 | 0 | 0 | | IL | 166 | 81 | 20 | 0 | 0 | 0 | | CL | 156 | 7 | 1 | 0 | 0 | 0 | | CN | 121 | 4 | 1 | 0 | 0 | 0 | | CR | 111 | 45 | 1 | 0 | 0 | 0 | | ID | 110 | 78 | 1 | 0 | 0 | 0 | | MY | 95 | 78 | 10 | 1 | 0 | 0 | | MA | 71 | 29 | 2 | 0 | 0 | 0 | | HK | 67 | 117 | 20 | 18 | 0 | 0 | | IN | 48 | 13 | 0 | 0 | 0 | 0 | | AE | 48 | 65 | 4 | 0 | 0 | 0 | | LK | 40 | 16 | 1 | 0 | 0 | 0 | | ZA | 34 | 106 | 0 | 0 | 0 | 0 | | JO | 34 | 34 | 0 | 0 | 0 | 0 | | TW | 32 | 12 | 3 | 0 | 0 | 0 | | UY | 25 | 9 | 1 | 0 | 0 | 0 | +--------------------+-------+-------+-------+-------+--------+----------+ | 20 rows 7 columns | +------------------------------------------------------------------------+ Much of the data has been submitted by volunteers but there are also official sources as well. Below are the most significant official sources broken down by region. SELECT AddressWorldRegion, REPLACE(SPLIT_PART(REPLACE(REPLACE(DataProviderWebsiteURL, 'http://', ''), 'https://', ''), '/', 1), 'www.', '') AS domain, COUNT(DISTINCT LocationUUID) n_locations FROM connections WHERE DataProviderWebsiteURL != 'http://openchargemap.org' GROUP BY 1, 2 HAVING n_locations > 50 ORDER BY 1, 3 DESC; +--------------------+-------------------------+-------------+ | AddressWorldRegion | domain | n_locations | | varchar | varchar | int64 | +--------------------+-------------------------+-------------+ | eastAsia | chademo.com | 1268 | | eastAsia | oplaadpalen.nl | 182 | | eastAsia | carstations.com | 117 | | easternEurope | sitronics-electro.com | 93 | | northernAmerica | afdc.energy.gov | 57074 | | northernAmerica | carstations.com | 2464 | | northernEurope | chargepoints.dft.gov.uk | 11864 | | northernEurope | nobil.no | 9081 | | northernEurope | ev.lakd.lt | 234 | | southernEurope | mobie.pt | 751 | | southernEurope | oplaadpalen.nl | 59 | | westernEurope | bundesnetzagentur.de | 10533 | | westernEurope | data.gouv.fr | 10230 | | westernEurope | oplaadpalen.nl | 7698 | +--------------------+-------------------------+-------------+ | 14 rows 3 columns | +------------------------------------------------------------+ There has been a move to either partly or fully automate data imports. Below is a breakdown by each location's last update as to which process was used to refresh its record. SELECT SUBSTR(LocationDateLastStatusUpdate::TEXT, 1, 4) last_update, COUNT(DISTINCT LocationUUID) FILTER (WHERE DataProviderTitle = 'Manual Data Entry') as manual, COUNT(DISTINCT LocationUUID) FILTER (WHERE DataProviderTitle = 'Partially Automated (Ad-Hoc Import)') as partly, COUNT(DISTINCT LocationUUID) FILTER (WHERE DataProviderTitle = 'Automated Import') as automated FROM connections GROUP BY last_update ORDER BY last_update DESC; +-------------+--------+--------+-----------+ | last_update | manual | partly | automated | | varchar | int64 | int64 | int64 | +-------------+--------+--------+-----------+ | 2024 | 5362 | 6288 | 42766 | | 2023 | 25136 | 2764 | 17231 | | 2022 | 11433 | 0 | 5729 | | 2021 | 5590 | 253 | 10592 | | 2020 | 9507 | 7 | 9275 | | 2019 | 7478 | 127 | 4861 | | 2018 | 8151 | 1 | 25 | | 2017 | 8546 | 1 | 2484 | | 2016 | 4568 | 18 | 7857 | | 2015 | 3902 | 0 | 105 | | 2014 | 650 | 0 | 120 | | 2013 | 523 | 0 | 3 | | 2012 | 182 | 1 | 0 | | 2011 | 89 | 0 | 0 | | 2010 | 25 | 0 | 0 | +-------------+--------+--------+-----------+ | 15 rows 4 columns | +-------------------------------------------+ There has been a considerable amount of effort put into refreshing data. Below is a breakdown of the number of locations refreshed before and after 2021. SELECT AddressWorldRegion, COUNT(DISTINCT LocationUUID) FILTER (WHERE SUBSTR(LocationDateLastVerified::VARCHAR, 1, 4)::INT < 2021) AS "before_2021", COUNT(DISTINCT LocationUUID) FILTER (WHERE SUBSTR(LocationDateLastVerified::VARCHAR, 1, 4)::INT >= 2021) AS "2021_onward" FROM connections GROUP BY AddressWorldRegion ORDER BY 2 DESC; +--------------------+-------------+-------------+ | AddressWorldRegion | before_2021 | 2021_onward | | varchar | int64 | int64 | +--------------------+-------------+-------------+ | westernEurope | 28264 | 19871 | | northernAmerica | 19253 | 53468 | | northernEurope | 10155 | 32841 | | southernEurope | 7443 | 19981 | | eastAsia | 1860 | 46 | | easternEurope | 1758 | 2650 | | australia | 480 | 1560 | | westernAsia | 388 | 943 | | centralAmerica | 52 | 109 | | southeastAsia | 37 | 385 | | southernAsia | 21 | 101 | | southernAfrica | 16 | 126 | | southAmerica | 15 | 357 | | northAfrica | 13 | 137 | | caribbean | 9 | 371 | | polynesia | 5 | 172 | | eastAfrica | 3 | 21 | | centralAsia | 2 | 2 | | westAfrica | 0 | 3 | +--------------------+-------------+-------------+ | 19 rows 3 columns | +------------------------------------------------+ Charging Point Operators ChargePoint, Tesla, BlinkCharging, flo and Shell Recharge Solutions are the most significant operators with more than 3K locations and a significant presence outside of their home market. CREATE OR REPLACE TEMPORARY TABLE op_locations AS SELECT SPLIT_PART(OperatorTitle, ' (', 1) AS OperatorTitle, AddressCountryIso2, COUNT(DISTINCT LocationUUID) n_locations FROM connections WHERE LENGTH(OperatorTitle) > 1 AND OperatorTitle NOT LIKE '(Unknown Operator)' AND OperatorTitle NOT LIKE '(Business Owner at Location)' GROUP BY 1, 2 HAVING n_locations > 1000; CREATE OR REPLACE TEMPORARY TABLE op_locations_fillin AS SELECT SPLIT_PART(OperatorTitle, ' (', 1) AS OperatorTitle, AddressCountryIso2, COUNT(DISTINCT LocationUUID) n_locations FROM connections WHERE SPLIT_PART(OperatorTitle, ' (', 1) IN ( SELECT DISTINCT OperatorTitle FROM op_locations) AND AddressCountryIso2 IN ( SELECT DISTINCT AddressCountryIso2 FROM op_locations) GROUP BY 1, 2; WITH pivot_alias AS ( PIVOT op_locations_fillin ON AddressCountryIso2 USING SUM(n_locations) GROUP BY OperatorTitle ) SELECT OperatorTitle, US, CA, * EXCLUDE(OperatorTitle, US, CA) FROM pivot_alias ORDER BY US DESC; +--------------------------+--------+--------+--------+--------+--------+--------+--------+--------+--------+ | OperatorTitle | US | CA | DE | ES | FR | GB | IT | NL | PT | | varchar | int128 | int128 | int128 | int128 | int128 | int128 | int128 | int128 | int128 | +--------------------------+--------+--------+--------+--------+--------+--------+--------+--------+--------+ | ChargePoint | 23374 | 2483 | 19 | | 53 | 317 | 14 | 20 | | | Tesla | 7242 | 825 | 790 | 83 | 532 | 631 | 711 | 128 | 178 | | Blink Charging | 6432 | 27 | | | | 552 | | | | | EV Connect | 1687 | 64 | | | | | | | | | Shell Recharge Solutions | 1289 | 95 | 823 | | 4 | 273 | 1 | 1228 | | | eVgo Network | 1220 | | | | | | | | | | PEA Volta | 1191 | 1 | | | | | | | | | flo | 783 | 2958 | | | | | | | | | Enel X | 16 | | | 1 | | 2 | 4124 | | | | EV-Box | 3 | | 9 | | 3 | 17 | 24 | 2055 | | | Endesa | | | | 1291 | | | | | | | Izivia | | | | | 1406 | | | | | | Mobie.pt | | | | | | | | | 1769 | | EVnetNL | | | 6 | | | | | 1788 | | | ubitricity | | | 57 | | 3 | 5253 | | | | | POD Point | | | | | | 3342 | | 1 | | | Chargeplace Scotland | | | | | | 1359 | | | | | Innogy SE | | | 2205 | | | 1 | | | | | Circuit Electrique | | 2089 | | | | | | | | | EnBW | | | 1268 | | | | | | | | Char.gy | | | | | | 2862 | | | | | Source London | | | | | | 1172 | | | | | Mercadona | | | | 1071 | | | | | | | BP Pulse | | | | 73 | | 2157 | | | | | SureCharge | | | | | | 1780 | | | | | Iberdrola | | | | 1734 | | | | | 36 | | ladenetz.de | | | 1488 | | | | | 2 | | +--------------------------+--------+--------+--------+--------+--------+--------+--------+--------+--------+ | 27 rows 10 columns | +-----------------------------------------------------------------------------------------------------------+ Charging Infrastructure Below are the most popular connection types in Europe. SELECT ConnectionTypeTitle, COUNT(*) FROM connections WHERE AddressWorldRegion LIKE '%Europe' GROUP BY 1 ORDER BY 2 DESC LIMIT 10; +-----------------------------------+--------------+ | ConnectionTypeTitle | count_star() | | varchar | int64 | +-----------------------------------+--------------+ | Type 2 (Socket Only) | 147088 | | CCS (Type 2) | 32961 | | CEE 7/4 - Schuko - Type F | 23758 | | Type 2 (Tethered Connector) | 22946 | | CHAdeMO | 21898 | | Unknown | 12391 | | Tesla (Model S/X) | 4749 | | SCAME Type 3A (Low Power) | 2257 | | SCAME Type 3C (Schneider-Legrand) | 2159 | | Type 1 (J1772) | 1445 | +-----------------------------------+--------------+ | 10 rows 2 columns | +--------------------------------------------------+ Below are the most popular connection types in North America. SELECT ConnectionTypeTitle, COUNT(*) FROM connections WHERE AddressWorldRegion = 'northernAmerica' GROUP BY 1 ORDER BY 2 DESC LIMIT 10; +---------------------------+--------------+ | ConnectionTypeTitle | count_star() | | varchar | int64 | +---------------------------+--------------+ | Type 1 (J1772) | 56622 | | CCS (Type 1) | 9869 | | CHAdeMO | 7554 | | Tesla (Model S/X) | 6356 | | NACS / Tesla Supercharger | 2663 | | NEMA 5-20R | 1375 | | Unknown | 443 | | NEMA 5-15R | 247 | | NEMA 14-50 | 231 | | Avcon Connector | 44 | +---------------------------+--------------+ | 10 rows 2 columns | +------------------------------------------+ There is a wide variety of power delivery specifications for each connection type. These are the most popular in Europe. SELECT ConnectionTypeTitle AS Connector, ConnectorVoltage AS Voltage, ConnectorAmps AS Amps, ConnectorPowerKW AS PowerKW, CurrentTypeTitle AS Current, COUNT(DISTINCT LocationUUID) AS n_locations FROM connections WHERE AddressWorldRegion LIKE '%Europe' AND ConnectorVoltage > 0 GROUP BY 1, 2, 3, 4, 5 ORDER BY 6 DESC LIMIT 20; +------------------------------+---------+-------+---------+-------------------+-------------+ | Connector | Voltage | Amps | PowerKW | Current | n_locations | | varchar | int64 | int64 | double | varchar | int64 | +------------------------------+---------+-------+---------+-------------------+-------------+ | Type 2 (Socket Only) | 400 | 32 | 22.0 | AC (Three-Phase) | 22279 | | Type 2 (Socket Only) | 230 | 32 | 7.0 | AC (Single-Phase) | 7525 | | Type 2 (Socket Only) | 230 | 16 | 3.7 | AC (Single-Phase) | 5311 | | Type 2 (Socket Only) | 400 | 16 | 11.0 | AC (Three-Phase) | 4032 | | CEE 7/4 - Schuko - Type F | 230 | 16 | 3.7 | AC (Single-Phase) | 3202 | | Type 2 (Tethered Connector) | 400 | 63 | 43.0 | AC (Three-Phase) | 2089 | | CHAdeMO | 400 | 125 | 50.0 | DC | 1819 | | Tesla (Model S/X) | 400 | 32 | 22.0 | AC (Three-Phase) | 1811 | | Type 2 (Socket Only) | 220 | 7 | 4.8 | AC (Single-Phase) | 1797 | | Type 2 (Socket Only) | 230 | 22 | 5.1 | AC (Single-Phase) | 1771 | | Type 2 (Tethered Connector) | 400 | 32 | 22.0 | AC (Three-Phase) | 1712 | | CHAdeMO | 500 | 100 | 50.0 | DC | 1613 | | Type 2 (Socket Only) | 230 | 7 | 5.0 | AC (Single-Phase) | 1582 | | SCAME Type 3A (Low Power) | 240 | 16 | 3.7 | AC (Single-Phase) | 1495 | | Type 2 (Socket Only) | 400 | 32 | 22.0 | DC | 1450 | | Unknown | 230 | 16 | 3.7 | | 1414 | | CCS (Type 2) | 500 | 100 | 50.0 | DC | 1051 | | Type 2 (Socket Only) | 230 | 32 | 7.4 | AC (Single-Phase) | 1036 | | CHAdeMO | 500 | 120 | 50.0 | DC | 1016 | | CCS (Type 2) | 400 | 125 | 50.0 | DC | 1003 | +------------------------------+---------+-------+---------+-------------------+-------------+ | 20 rows 6 columns | +--------------------------------------------------------------------------------------------+ These are the most popular in North America. SELECT ConnectionTypeTitle, ConnectorVoltage, ConnectorAmps, ConnectorPowerKW, CurrentTypeTitle, COUNT(DISTINCT LocationUUID) n_locations FROM connections WHERE AddressWorldRegion = 'northernAmerica' AND ConnectorVoltage > 0 GROUP BY 1, 2, 3, 4, 5 ORDER BY 6 DESC LIMIT 20; +---------------------+------------------+---------------+------------------+-------------------+-------------+ | ConnectionTypeTitle | ConnectorVoltage | ConnectorAmps | ConnectorPowerKW | CurrentTypeTitle | n_locations | | varchar | int64 | int64 | double | varchar | int64 | +---------------------+------------------+---------------+------------------+-------------------+-------------+ | Type 1 (J1772) | 230 | 16 | 3.7 | AC (Single-Phase) | 45891 | | Type 1 (J1772) | 230 | 16 | 3.0 | AC (Single-Phase) | 4842 | | Tesla (Model S/X) | 230 | 16 | 3.7 | AC (Single-Phase) | 1419 | | CCS (Type 1) | 400 | 100 | 40.0 | DC | 1338 | | CHAdeMO | 400 | 100 | 40.0 | DC | 1180 | | Type 1 (J1772) | 230 | 32 | 7.0 | AC (Single-Phase) | 698 | | Tesla (Model S/X) | 230 | 16 | 16.0 | AC (Single-Phase) | 568 | | Tesla (Model S/X) | 230 | 16 | 8.0 | AC (Single-Phase) | 433 | | NEMA 5-20R | 120 | 16 | 1.9 | AC (Single-Phase) | 414 | | NEMA 5-20R | 120 | 16 | 1.0 | AC (Single-Phase) | 233 | | NEMA 5-15R | 120 | 16 | 1.9 | AC (Single-Phase) | 222 | | Tesla (Model S/X) | 230 | 16 | 13.0 | AC (Single-Phase) | 165 | | CCS (Type 1) | 400 | 100 | 40.0 | AC (Single-Phase) | 123 | | NEMA 14-50 | 120 | 16 | 1.9 | AC (Single-Phase) | 123 | | Unknown | 230 | 16 | 3.0 | AC (Single-Phase) | 113 | | CHAdeMO | 400 | 100 | 40.0 | AC (Single-Phase) | 111 | | Type 1 (J1772) | 240 | 72 | 17.3 | | 110 | | CCS (Type 1) | 400 | 125 | 50.0 | DC | 92 | | CHAdeMO | 400 | 125 | 50.0 | DC | 92 | | Unknown | 120 | 16 | 1.9 | AC (Single-Phase) | 50 | +---------------------+------------------+---------------+------------------+-------------------+-------------+ | 20 rows 6 columns | +-------------------------------------------------------------------------------------------------------------+ Looking at the most popular connection and power delivery combination for North America, it's not one but many operators that offer this combination. SELECT OperatorTitle, ConnectionTypeTitle, COUNT(DISTINCT LocationUUID) n_locations FROM connections WHERE ConnectorVoltage = 230 AND ConnectorAmps = 16 AND ConnectorPowerKW = 3.7 AND CurrentTypeTitle = 'AC (Single-Phase)' AND LENGTH(OperatorTitle) > 1 AND AddressWorldRegion = 'northernAmerica' GROUP BY 1, 2 ORDER BY 3 DESC LIMIT 20; +-----------------------------------+---------------------+-------------+ | OperatorTitle | ConnectionTypeTitle | n_locations | | varchar | varchar | int64 | +-----------------------------------+---------------------+-------------+ | ChargePoint | Type 1 (J1772) | 18837 | | Blink Charging | Type 1 (J1772) | 5770 | | flo | Type 1 (J1772) | 2654 | | Circuit Electrique | Type 1 (J1772) | 1718 | | EV Connect | Type 1 (J1772) | 1193 | | Tesla (Tesla-only charging) | Tesla (Model S/X) | 1184 | | PEA Volta | Type 1 (J1772) | 1141 | | Shell Recharge Solutions (US) | Type 1 (J1772) | 882 | | AmpUp | Type 1 (J1772) | 764 | | SWTCH - Charge Everywhere | Type 1 (J1772) | 482 | | SemaConnect | Type 1 (J1772) | 394 | | Livingston Charge Port | Type 1 (J1772) | 325 | | OpConnect | Type 1 (J1772) | 296 | | eVgo Network | Type 1 (J1772) | 230 | | GE WattStation (No longer active) | Type 1 (J1772) | 162 | | Rivian Waypoints L2 Network | Type 1 (J1772) | 160 | | ChargeUp (NovaCharge) | Type 1 (J1772) | 146 | | Universal EV Charging | Type 1 (J1772) | 143 | | Blink Charging (Europe) | Type 1 (J1772) | 136 | | Powerflex | Type 1 (J1772) | 134 | +-----------------------------------+---------------------+-------------+ | 20 rows 3 columns | +-----------------------------------------------------------------------+ Charging Point Access In terms of location access, the vast majority are publically available. Of those that are private, they still might be accessible as both "For Staff, Visitors or Customers" and "Privately Owned - Notice Required" are included in the 16,229 locations below. SELECT COUNT(DISTINCT LocationUUID) FILTER (WHERE UsageTypeTitle LIKE 'Public%') AS public, COUNT(DISTINCT LocationUUID) FILTER (WHERE UsageTypeTitle LIKE 'Private%') AS private, COUNT(DISTINCT LocationUUID) FILTER (WHERE UsageTypeTitle NOT LIKE 'Public%' AND UsageTypeTitle NOT LIKE 'Private%') AS unknown, FROM connections ORDER BY 1 DESC; +--------+---------+---------+ | public | private | unknown | | int64 | int64 | int64 | +--------+---------+---------+ | 150481 | 16229 | 657 | +--------+---------+---------+ Thank you for taking the time to read this post. I offer both consulting and hands-on development services to clients in North America and Europe. If you'd like to discuss how my offerings can help your business please contact me via LinkedIn. - Back to Index Copyright (c) 2014 - 2024 Mark Litwintschik. This site's template is based off a template by Giulio Fidente.