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": "5€/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": "Pärnu",
    "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.

Copyright © 2014 - 2024 Mark Litwintschik. This site's template is based off a template by Giulio Fidente.