Support OpenStreetMap as data source

[dbrgn]

OpenStreetMap has a tagging schema for charging stations: https://wiki.openstreetmap.org/wiki/Tag:amenity%3Dcharging_station It is the ideal database for this kind of data: It's a geo-database for objects with certain associated attributes.

What would it take to integrate OSM as a data source into EVMap? I assume excluding duplicates would be a challenge.

(PS: Very nice app, thanks for developing it!)

[johan12345]

In #96 I am currently adding OpenChargeMap as a data source (see also #81) and also restructuring the app's code to allow for interchangeable data sources as much as possible. So that might also make it possible to add OpenStreetMap. But I have a few concerns (mainly as I'm not familiar with that data source):

• Do you know how good the current charging station data in OSM is (both in terms of quantity and quality)? Does OSM provide as many details about the charging stations as GoingElectric or OpenChargeMap do? OpenChargeMap also regularly imports charging station data from official government sources in some countries, I don't know if this is the case for OSM as well.
• Is there an API for easily accessing charging stations in OpenStreetMap within a certain geographical bounding box as e.g. JSON data? Or would this first have to be extracted from the raw data somehow?
• Would we need to host the data ourselves? As far as I know this would be the case for OSM map tiles (see also the discussion in https://github.com/johan12345/EVMap/issues/36), which is why I opted for Mapbox in the end

[dbrgn]

Do you know how good the current charging station data in OSM is (both in terms of quantity and quality)?

I don't yet have good data, but here's a preview of the data in Switzerland: https://umap.osm.ch/de/map/electromobility-charging-stations-in-switzerland-b_941#8/46.770/8.086 I'd estimate that there are still a lot of charging stations missing, but that may be because there's no good app for searching/displaying this data so far.

Does OSM provide as many details about the charging stations as GoingElectric or OpenChargeMap do?

Yes, check out https://wiki.openstreetmap.org/wiki/DE:Tag:amenity%3Dcharging_station for the tagging schema. It's quite powerful.

OpenChargeMap also regularly imports charging station data from official government sources in some countries, I don't know if this is the case for OSM as well.

If the license is compatible, imports into OSM would be possible as well. I myself will definitely start to invest some time into improving the data, and I know a few people that are quite active in the OSM community that might help.

Is there an API for easily accessing charging stations in OpenStreetMap within a certain geographical bounding box as e.g. JSON data? Or would this first have to be extracted from the raw data somehow?

I think the data could be queried through Overpass: https://wiki.openstreetmap.org/wiki/Overpass_API However, I'm not sure how fast this will be in practice.

The ideal solution would be to fetch the OSM database, extract the data and then use a webservice endpoint that serves this data from a PostGIS database. I could certainly help with setting up such a setup, but it's definitely an investment (time and server resources), because the full dataset is quite large. (I wrote the POI server for Threema, so I have some experience with these things.) The advantage is that you have full control over the type of information that is extracted, and can optimize the querying using appropriate database indexes.

Maybe @sfkeller or @packi have some ideas as well?

Would we need to host the data ourselves?

Regarding the Overpass API I'm not sure, I think you can use it as long as you don't download huge datasets. Overpass is also not a central service, so there are multiple servers / service providers that may have different terms of use. Again, maybe @sfkeller or @packi have some experience with that.

[sfkeller]

I can confirm what @dbrgn wrote above.

The Overpass API (over http with json/geojson response) is a well known "entry point" to get data out of OSM (see User's Manual https://dev.overpass-api.de/overpass-doc/en/ ). It's restricted to some MB of data though.

There's a nice diagram about choosing a tool to extract OSM data - which is a bit biased towards their 2ohsome framework", but still useful: http://k1z.blog.uni-heidelberg.de/2020/09/10/the-future-of-working-with-osm-data/ .

And since OSM is an established crowdsourcing platform with "critial" mass of users, one can assume that it's capable to complete charging stations within few months. See e.g. this action which already got a proposal to map charging stations: https://wiki.openstreetmap.org/wiki/DE:Project_of_the_month_Switzerland .

[johan12345]

Okay, so it looks like the current data in Switzerland does have a large number of stations, but many with a low amount of metadata (e.g. no information about which plugs are available and with what power, which is quite essential information). But I agree, if the community is willing to improve on that, it could definitely be a good data source for EVMap (though it will lead to fragmentation with some people contributing to GoingElectric, some to OpenChargeMap and some to OpenStreetMap...).

https://wiki.openstreetmap.org/wiki/Overpass_API seems to have some information on the usage policy of the different Overpass API servers. For the ones where it is specified, the suggested limits are on the order of 1,000 to 10,000 requests per day. The app already has more than 10k users (I don't have statistics for F-Droid, but 10k from Google Play) and all users combined have generated ~60k requests per day to the GoingElectric API on average in the last month. So of course the number of people using OSM as a data source in EVMap would be lower initially, but in the long term we might put a significant load on these Overpass API servers. So we would probably need to either

• talk to the operators of these servers if it these rather small-scale requests (maybe combined with marker clustering, if Overpass supports that) are fine or if they could just block the app when we are over the limit
• think about hosting something ourselves pretty soon

And yes, finding out how fast these requests would be is definitely important.

The ideal solution would be to fetch the OSM database, extract the data and then use a webservice endpoint that serves this data from a PostGIS database. I could certainly help with setting up such a setup, but it's definitely an investment (time and server resources), because the full dataset is quite large.

Yep, I guess that would be the most stable way to go in the long term. The donations I receive from the app would however be just barely enough to to afford a small VPS, so probably far from something that could work for the OSM database...

[sfkeller]

(though it will lead to fragmentation with some people contributing to GoingElectric, some to OpenChargeMap and some to OpenStreetMap...)

I'm not sure about how much the fragmentation will be since I think there are different motivations in order to contribute among at least of the mappers (as OpenStreetMap volunteers are called). As said, the asset of OpenStreetMap (OSM) is it's potentially large user base (see e.g. https://osmstats.neis-one.org/?item=countries&country=Switzerland ).

but in the long term we might put a significant load on these Overpass API servers

You could cache Overpass API results.

[dbrgn]

You could cache Overpass API results.

Hm, that's actually not a bad idea, since it would avoid needing to download the full OSM planet file. However, this would require downloading the full dataset of charging stations, which might be larger than the overpass limits.

I'll do a quick test to see how fast the planet data (only charging stations) can be extracted, and how large the dataset is currently.

[dbrgn]

Actually, if the dataset is not too large, there's another alternative to the API which might even be way cooler: The server could preprocess the charging station data and host it as a regular static file in a compressed archive. The apps could then download that data (similar to the OSMand map data downloads) and would then work offline. This would require the implementation of an updater, but offline support is really nice and not API needs to be implemented.

[johan12345]

You could cache Overpass API results.

In the app dynamically during use - maybe. We do already do caching for GoingElectric (at least according to their caching rules, i.e. 24 hours), but that mainly affects the charger details and photos requests, not the requests for the list of chargers on the map, which are more difficult to cache due to server-side clustering and varying bounding boxes.

On a server - yes, if the full dataset can be easily obtained through Overpass and is not too large.

In the app with a single request download - yes, might also work depending on the dataset size. That might also be a good idea for other APIs (at least OpenChargeMap, who offer a single gzipped file for download - GoingElectric probably prohibits this in their API terms).

[dbrgn]

I downloaded the OSM planet file (60 GiB) and ran Osmosis on it to extract all nodes with amenity=charging_station. This results in 52'210 nodes exported.

In an uncompressed "PostgreSQL dump" file, this is 16 MiB. Gzipped it's 3.4 MiB.

Other output formats are possible as well, for example XML. Maybe we could even write a small conversion script that would output OpenChargingMap compatible JSON files (although non-preprocessed OSM-style raw key-value data might be better because it allows tweaking the filtering / data processing in the app without changing the data converter script).

[johan12345]

That sounds pretty manageable, so we could host that as a static gzipped file somewhere (maybe even on GitHub for now) and update it regularly. Of course it would be better if there were a script to update it through the Overpass API instead of by downloading 60 GiB of OSM data every time.

I agree it probably makes sense to use OSM's own format (e.g. as JSON or XML) and build a separate API implementation for it in EVMap. You could start based on what is currently on the OpenChargeMap branch, but of course the implementation would need to look a bit different as the data would need to be downloaded and stored in a new table of the local database.

[dbrgn]

Hi @johan12345, quick status update!

The Swiss OSM Community's "Project of the Month" in September was focussed on adding and improving charging stations in OSM: https://wiki.openstreetmap.org/wiki/DE:Project_of_the_month_Switzerland/Charging_Stations

Right now, there are 2251 charging stations in Switzerland, 370 of them were added last month (and hundreds of them were updated with more detailed information). Worldwide, there seem to be 57436 charging stations. I also spent some time improving the documentation in the Wiki, and I also created a JOSM Preset that simplifies the mapping of charging stations (right now with a Swiss focus which would need to be generalized). The work on this project of the month also led to an improved tagging schema for authentication and payment.

The current status (limited to stations in Switzerland) can be viewed on this interactive map: https://umap.osm.ch/de/map/electromobility-charging-stations-in-switzerland-b_941#12/47.2731/8.7911

Of course it would be better if there were a script to update it through the Overpass API instead of by downloading 60 GiB of OSM data every time.

You're right, I'll try to create a test query for the Overpass API. This should give us JSON of all charging stations together with their metadata.

[johan12345]

Thanks for the update! 2251 charging stations in Switzerland brings OSM almost on the same level as GoingElectric (2774 stations), so this sounds pretty good.

You're right, I'll try to create a test query for the Overpass API. This should give us JSON of all charging stations together with their metadata.

Great 👍 Then I could create e.g. a separate GitHub repo with that JSON file and set up a cronjob to regularly update that. raw.githubusercontent.com already serves files gzipped, so that could be used as the URL to access the file.

And then, if you would like to start the implementation in the app: I would suggest changing the ChargepointApi interface to allow the API implementation to state whether it supports online (i.e. request to the API every time the map is moved) or offline mode (data is downloaded to the local database once) or both. Then the OSM implementation would need a function that downloads the JSON file and stores its content in the local DB. There is already the concept of a ReferenceData class which is already used for storing certain more or less static information (such as mappings between IDs of charging station operators and their names) in the DB for the OpenChargeMap implementation, but that data is always fully loaded into memory before executing any of the other API methods, so it would not be a good idea to use this to store the whole list of charging stations.

[dbrgn]

Here's my PoC: https://github.com/dbrgn/evmap-osm

The script currently takes roughly 6 minutes to fetch all 57437 charging stations via Overpass query (27 MiB of raw JSON) and writes them to a 2.8 MiB gzipped JSON. (When substituting gzip for xz, the size decreases to 2.0 MiB, however xz compression is a bit less widely supported than gzip.)

Included data per charging station:

• Unique numeric ID
• Latitude, longitude (WGS84 coordinates, I assume)
• Timestamp of last update
• Numeric version number, monotonically increasing for every update
• User that last modified this POI
• OSM tags (raw key-value)

I hope this is helpful. If someone thinks that the format should be changed, feel free to let me know, I'm happy to adjust the script.

Right now I don't know if I can promise an integration into EVMap, so if someone else has the time to do so, please go ahead!

[johan12345]

Thanks a lot! 👍

For development purposes I've set up your script on a temporary server that makes it available (served gzip-compressed) at https://evmap-dev.vonforst.net/charging-stations-osm.json (please only use this URL for development - if this goes into production use I'll move it to another server first)

The data format seems fine to me so far, just a few notes:

• Initially I thought it might be useful to ensure a fixed order (i.e. sort by ID) if the JSON file is ever put in version control. But the uncompressed JSON is already pretty large for that (GitHub limit is 50-100 MB), so I think I'll not try to host this through GitHub anyhow.
• There are some tags that seem to always contain boolean values (as "yes" or "no"), or integers, but are formatted as strings. Can we assume that they always contain this type data and convert them into the correct JSON types, or is there no such validation in place on the OSM side?
• It seems that the "socket:...:output" tags can contain arbitrary text (typically it is number + " kW", but sometimes also a semicolon-separated list or even other units). This needs to be converted to a floating point number in kW for EVMap, ignoring unparseable data. But that can be done on the client side.
• There are of course a lot of stations in the list that have almost no details except the location (and sometimes a name of the location or the operator). We'll have to see how much sense it makes to include those into EVMap (e.g. it will not be able to show the correct icon color depending on the power output).

[dbrgn]

Can we assume that they always contain this type data and convert them into the correct JSON types, or is there no such validation in place on the OSM side?

No, there is no validation or schema. Both keys and values are always strings.

It seems that the "socket:...:output" tags can contain arbitrary text (typically it is number + " kW", but sometimes also a semicolon-separated list or even other units).

Yeah, essentially the "schema" is a wiki page that recommends how to tag, so we always need to parse as loose/lenient as possible. To my knowledge, the proper format for output is socket:type2:output=22 kW (number plus kW), but people may use other formats. Maybe a RegEx (/[0-9\.,]+\s*kW/i).

Regarding the semicolon, I think this is for multiple outputs:

{
  "type": "node",
  "id": 8219906320,
  "lat": 69.5416905,
  "lon": 20.5507106,
  "timestamp": "2020-12-11T14:32:22Z",
  "version": 1,
  "changeset": 95686942,
  "user": "NorNorth",
  "uid": 420980,
  "tags": {
    "amenity": "charging_station",
    "capacity": "5",
    "fee": "yes",
    "motorcar": "yes",
    "name": "Ishavsveien Manndalen",
    "operator": "Ishavsveien",
    "socket:chademo": "2",
    "socket:chademo:output": "100 kW;50 kW",
    "socket:type2": "2",
    "socket:type2:current": "32",
    "socket:type2:output": "22 kW;22kW",
    "socket:type2:voltage": "400",
    "socket:type2_combo": "2",
    "socket:type2_combo:output": "175 kW;50kW"
  }
}

This looks a bit weird to me. I suspect that these are actually two charging stations with different power outputs merged into one. Regarding the integration into EVMap, if the format cannot be parsed in a meaningful way, I'd simply ignore the key. In this case, we still know that there are two Type2, two CCS and two CHAdeMO outputs, that's already valuable information.

Sometimes people also mapped plain numbers (e.g. 7000, I assume that's 7 kW), ranges (5,5 - 11 kW, huh?) or even power (32 A), which is wrong. Again, if we cannot parse, just ignore the field.

There are also some fields tagged with kVA. These can be treated equivalent to kW.

There are of course a lot of stations in the list that have almost no details except the location (and sometimes a name of the location or the operator). We'll have to see how much sense it makes to include those into EVMap (e.g. it will not be able to show the correct icon color depending on the power output).

That is true. I think it's still valuable to see that there is a charging station there, but indicate that we have no information about the type of connectors or the output power. It might also be nice to show a note, saying that the data could be improved on OpenStreetMap.

Since I have some experience with OSM tagging schemes, as a first step, would it help if I'd create a PR that can parse the dataset entries and convert them into some kind of domain object (together with unit tests)? I assume there are already classes to handle things like "charging station" and "output socket" and "output power", right?

[johan12345]

No, there is no validation or schema. Both keys and values are always strings.

Okay, then we should keep it that way and handle the conversion within the app (and ignore the field if it doesn't conform to the expected format).

This looks a bit weird to me. I suspect that these are actually two charging stations with different power outputs merged into one.

Yeah, I suspect that too - that is also typically done in the other data sources and is supported by EVMap. It simply makes sense to put chargers at the same site by the same operator into one entry, instead of mapping e.g. a Tesla Supercharger site with 20 stalls as 20 separate entries. So I think we should also try to parse this format (as long as the length of the list matches the number of plugs listed it should work).

Since I have some experience with OSM tagging schemes, as a first step, would it help if I'd create a PR that can parse the dataset entries and convert them into some kind of domain object (together with unit tests)? I assume there are already classes to handle things like "charging station" and "output socket" and "output power", right?

Yeah, this would be great! The classes for this in EVMap are in this file (relevant ones should be ChargeLocation (= a whole charging site), Chargepoint (= one socket with a certain power, which may be available multiple times), Coordinate, OpeningHours, Cost and possibly Address and ChargerPhoto (loosely based on the data structure from GoingElectric with some additional keys).

So probably it makes sense to start from a class named e.g. OSMChargeLocation that matches the format of items in the JSON data (with a Map<String, String> for the tags), which then has a method like convert to convert it to a ChargeLocation. That OSMChargeLocation class could then later be used to store and retrieve the data from the local SQLite database.

[fhvyhjriur]

To let the developer know that there are more people that would like to see OpenStreetMap as data source, i leave a :+1:

[dbrgn]

I started working on the parsing and conversion code: https://github.com/dbrgn/EVMap/tree/osm

Right now I only handle a single JSON entry. However, the current data source JSON file contains a top-level object with an "elements" array. While this is very flexible (we can easily add more fields later on), we cannot easily stream-process the entries.

Thus, my suggestion would be to change the format from this

{
  "timestamp": 1642673932.142791,
  "elements": [
    {
      "id": 8143993909,
      "lat": -21.1283775,
      ...
    },
    ...
  ]
}

...to a JSON-object-per-line based format:

{"id": 8143993909, "lat": -21.1283775, ...}
{"id": 8149615093, "lat": -53.1657964, ...}

If we want to retain the possibility to attach metadata, we could also put a meta-object on the first line:

{"type": "metainfo", "timestamp": 1642673932, "total_count": 7342, ...}

The parser would parse or skip the first line, and could then stream-process the rest.

@johan12345 what are your thoughts on this? If you agree, I could update the evmap-osm script.

The other topic is data storage and retrieval. How are current APIs handled? Is the data always loaded live from the API, filtered by the current viewbox, and then kept in memory? Or is the data stored/cached in the database?

Is it a regular SQLite database, or can you use the SpatiaLite GIS extension? It would of course be preferrable if we can filter the loaded charging stations by viewbox. But for now, we could probably even fit all charging stations in memory, as long as we only load a subset of the data, i.e. id, lat/lng, and the speed (for the color).

[dbrgn]

The evmap-osm script is updated: https://github.com/dbrgn/evmap-osm

The output now looks like this:

[johan12345]

Thanks for the progress update, and sorry for the late reply!

I think it is possible to process a large JSON array in a streaming-based fashion without changing to a line-based format with Moshi, the JSON parsing library that EVMap uses. For example, this post shows an example of that in the third part ("Coroutines magic for big data sources"). I'm not yet sure how to exactly integrate it with the HTTP request, but it should be doable.

The other topic is data storage and retrieval. How are current APIs handled? Is the data always loaded live from the API, filtered by the current viewbox, and then kept in memory? Or is the data stored/cached in the database?

Currently, the data is always loaded live from the API when panning the map. The ChargeLocation table in the database is only used to store the favorites. But I agree this should be changed in the future, with a separate table for favorites that only references the ID of an entry in the ChargeLocation table (which also makes it easy to support #127) and a possibility to cache arbitrary numbers of stations locally in the ChargeLocation table (to support #88). I'll think about how we can adapt the DB schema for this.

Is it a regular SQLite database, or can you use the SpatiaLite GIS extension? It would of course be preferrable if we can filter the loaded charging stations by viewbox. But for now, we could probably even fit all charging stations in memory, as long as we only load a subset of the data, i.e. id, lat/lng, and the speed (for the color).

I think SpatiaLite is possible to use with Android's SQLite databases (e.g. I found https://github.com/anboralabs/spatia-room with a quick search), but for filtering by viewbox it might not even be necessary, as the viewbox can simply be given as a range of minimum and maximum latitude and longitude values, which we can use to query in the database. Of course, spatialite might later be needed e.g. for marker clustering or querying for the closest chargers to a specific location.

[johan12345]

I've added a proposal for restructuring the DB in #157 - with this change we'll be able to use the ChargeLocation table for local data storage.

[dbrgn]

Very nice!

I'll try to finish the parsing code this week.

[dbrgn]

An initial version of the parser is almost done, only power parsing is left which should be fairly straightforward.

During the implementation I decided to make Chargepoint.power nullable instead of using the "0.0 kW power" workaround: https://github.com/dbrgn/EVMap/commit/2598e69aafc051f851ad3b1d9cfe68984bba9558 I hope that's in your interest as well.

[johan12345]

Nice!

Yes, it makes sense to make the power information nullable as it's not always available on OSM. Only regarding the conversion for the Chargeprice API I would rather have avoided to filter out chargepoints with power == null - it might work better when we pass 0 kW instead of not passing any chargepoints at all if the power information is not available.

But I'm not sure if Chargeprice will work with OSM data at all as they claim to only support GoingElectric and OpenChargeMap so far.

[dbrgn]

I opened a PR for the Chargepoint.power field: #159.

And another one for the parser itself: #160. Let's continue the implementation discussions there.

[dbrgn]

Cool, now that #160 is merged, what are the next steps?

I wanted to give it a try, but without knowing the codebase / architecture in detail, and with OSM being different from the other two APIs (local data that needs to be refreshed from time to time) I hit a point where I can't currently put enough time into an implementation to get it to work... @johan12345 I suspect with your knowledge of the codebase it wouldn't be all too hard for you to add the OSM integration, right?

Here's a suggestion for the new data source description:

diff --git a/app/src/main/res/values/strings.xml b/app/src/main/res/values/strings.xml
index 90a2c34..75f0676 100644
--- a/app/src/main/res/values/strings.xml
+++ b/app/src/main/res/values/strings.xml
@@ -211,8 +211,10 @@
     <string name="data_sources_description">EVMap supports multiple data sources for charging stations. Please select the one you would like to use. You can always change it later in the app\'s settings.</string>
     <string name="data_source_goingelectric">GoingElectric.de</string>
     <string name="data_source_openchargemap">Open Charge Map</string>
+    <string name="data_source_openstreetmap">OpenStreetMap</string>
     <string name="data_source_goingelectric_desc">Very good coverage in Germany, Austria and Switzerland and many neighboring countries. Descriptions in German. Community-maintained.</string>
     <string name="data_source_openchargemap_desc"><![CDATA[Worldwide coverage with varying quality. Descriptions in English or local language. Community-maintained & government open data in some countries (e.g. North America, UK, France, Norway).]]></string>
+    <string name="data_source_openstreetmap_desc"><![CDATA[The largest freely editable general-purpose geographic database of the world. Worldwide coverage with varying quality. Data is maintained by a community of thousands of mappers.]]></string>
     <string name="next">next</string>
     <string name="get_started">Get started</string>
     <string name="got_it">Got it</string>
diff --git a/app/src/main/res/layout/data_source_select.xml b/app/src/main/res/layout/data_source_select.xml
index 67e4b9c..3bc3985 100644
--- a/app/src/main/res/layout/data_source_select.xml
+++ b/app/src/main/res/layout/data_source_select.xml
@@ -41,4 +41,22 @@
         android:layout_marginStart="32dp"
         android:text="@string/data_source_openchargemap_desc" />

+    <RadioButton
+        android:id="@+id/rbOpenStreetMap"
+        android:layout_width="wrap_content"
+        android:layout_height="wrap_content"
+        android:text="@string/data_source_openstreetmap"
+        android:textColor="#e1630e"
+        app:buttonTint="#e1630e"
+        android:textSize="16sp"
+        android:textStyle="bold" />
+
+    <TextView
+        android:id="@+id/textView29"
+        android:layout_width="match_parent"
+        android:layout_height="wrap_content"
+        android:layout_marginTop="-8dp"
+        android:layout_marginStart="32dp"
+        android:text="@string/data_source_openstreetmap_desc" />
+
 </RadioGroup>
\ No newline at end of file

Regarding the general approach, this is what I had in mind when OSM is selected as data source:

• On app start, when data is missing or outdated (e.g. >30d old), ask user whether data should be downloaded (indicate how large the download size is)
• (As a future step, add a way to manually refresh the data)
• After downloading the data, store it in the SQLite database
• Implement the ChargepointApi in a way that only selects from the local database, not from the network

Maybe the ChargepointApi interface needs some adjustments as well.

[johan12345]

Thanks! I hope I can continue to work on this soon, the last few days have been pretty stressful at work. I started a (not yet functional) implementation for offline caching on the caching branch, so that we can either use the local DB or the online APIs to fetch chargers depending on what's available. I tried to design this as something that stands inbetween ChargepointApi and the UI so that the implementation can be used for all data sources. In the case of OSM, we would then set it to always use the local DB if available and not outdated, and otherwise ask the user to download the data.

I should probably also think about adding some sensible indices to the SQLite DB now that it will potentially contain much more data than before 😄

[treestryder]

Sorry to interrupt, but I just had to say how excited I am to watch this effort take place. As an OpenStreetMap contributor it has always made sense to me that the empirical information about a charger would best be stored in one geospatial-aware database, OpenStreetMap. And as an EV driver, the way I currently access this information has been OsmAnd, which works, but is hard to recommend to other EV drivers. It will be great to have a dedicated app for this purpose. Maybe one day, this app could also add the ability to validate and improve the OpenStreetMap data.

[johan12345]

Hmm, it seems that while DB queries like

SELECT * FROM chargelocation WHERE dataSource == :dataSource AND lat >= :lat1 AND lat <= :lat2 AND lng >= :lng1 AND lng <= :lng2

do work in the Android SQLite database, we cannot make them run fast with an index. For that we would need either Spatialite or at least RTree support, which are both not available by default on Android.

I need to check out if https://github.com/anboralabs/spatia-room helps.

[dbrgn]

Unfortunately I can't be of much help, since I'm not really familiar with neither SpatiaLite nor Room. I usually use PostGIS for geodatabase purposes 🙂 (Not on Android though, obviously.)

With only around 60k charging stations, queries without an index might be fast enough. Spatia-Room looks interesting though.

[dbrgn]

July 2021:

I downloaded the OSM planet file (60 GiB) and ran Osmosis on it to extract all nodes with amenity=charging_station. This results in 52'210 nodes exported.

October 2021:

The script currently takes roughly 6 minutes to fetch all 57437 charging stations via Overpass query (...)

And I just ran the script again, by now there are 64100 charging stations, the number is increasing rapidly 🎉 I think with that OSM already contains roughly 4000 more charging stations than the GoingElectric database 🙂

[johan12345]

Today I spent a couple of hours again to try and get spatia-room to work. Unfortunately it seems to only be a part of the solution: It does make it possible to create a database that uses SpatiaLite instead of Android's integrated SQLite binary, but it doesn't offer any support for actually creating entities that have geometry fields and mapping those to Spatialite tables. So even with spatia-room all fields are still required to be one of the default SQLite data types. And it looks like Room is not flexible enough to easily change this (as this would need changes to the compiler that generates the SQL queries at build time, not just the runtime component)...

Other ideas:

• Fork Room to add support for geometry fields in the compiler?
• Get rid of Room completely and rebuild all the ORM stuff ourselves (i.e. write a lot of boilerplate code 😞)
• Add a separate Spatialite (or RTree) database which doesn't use Room and which we use only to do geospatial queries. This would still add some overhead as we need to fetch the remaining data from the Room database based on the IDs obtained from the geospatial query.
• Come up with another way of making these queries fast (e.g. divide the globe into a set of N rectangles, and store the ID of the rectangle for each charger. Then, if we want to query for chargers in a certain bounding box, first find possible matches by searching for chargers in the corresponding rectangle IDs (which is fast because we can create an index on this column), and only then filter the results by the actual coordinates)
• Revert back from the idea of storing all chargers locally and instead build a simple server with an API that does the geospatial queries

[dbrgn]

Hmm, if this really cannot be integrated well with spatia-room, then this might be the best option:

Add a separate Spatialite (or RTree) database which doesn't use Room and which we use only to do geospatial queries.

However, I'll shamelessly ping @dalgarins, maybe he has an idea how to handle this with spatia-room?

[dalgarins]

Hi, it's exciting to see that you are trying to use sparia-room, about @johan12345 said it's right now it's not possible to create a table from the entity, but I would like to ask you some questions to understand better the problem:

• Why don't you create a db(schema) with all tables that you need and use spatia-room to map the data and query the data. also you can insert the data without any problem. (instead of create the db from the entities)
• Have you tried use migrations? maybe create the tables without geom fields at the begining create some migrations to add the columns with geom types.

and if you don't mind, share with me a little poc, I will review that a help to find a solution.

Note: the next step for spatia-room it's create its own room compiler because the current compiler is limiting the library, if someone wants to join, let me know.

[johan12345]

@dalgarins Hey, thanks for your help!

Creating an initial DB and loading that is not a good option in this case, as we need to migrate the data from the current database for existing app users. But I think I now have an idea how to solve it, I was just missing two key pieces of information:

• The geometry columns in Spatialite databases are basically binary columns (SQLite's BLOB type), so Room can read the binary data from these fields and map it to Kotlin's ByteArray type. We can use a TypeConverter to implement the conversion into a useful format (i.e. latitude and longitude values for points) based on the documentation of the data format.
• If we implement such a type converter, Room will treat the geometry column as a BLOB column and create corresponding CREATE TABLE statements to initialize the database. I thought this would interfere with Spatialite, as the doumentation states that valid geometry columns can only be created with SELECT AddGeometryColumn statements. But it seems that SELECT AddGeometryColumn still works fine if a column with that name already exists (correct?) - so I can simply add the SELECT AddGeometryColumn statements to a DB migration and to Room's onCreate callback so that it is executed when the DB is first created or migrated to the new schema.

I created a simple POC here: https://github.com/johan12345/SpatiaRoomTest - a first commit initializes the Room database without Spatialite (with simple double fields for lat and lng), and a second commit migrates it to Spatialite using spatia-room.

@dalgarins does that look sensible to you or did I miss a more straightforward solution? I guess if this is how it is supposed to work, it would make sense to add such Geometry types (e.g. Point, Polygon, etc.) into spatia-room as Kotlin data classes and implement the corresponding TypeConverters into Spatialite's binary format. Then there would not even really be a need to modify the Room compiler as long as one makes sure to always include the correct SELECT AddGeometryColumn statements into onCreate callbacks or migrations. I could help by contributing the TypeConverter for points that I built to spatia-room, but we probably won't need other types for now.

[dalgarins]

@johan12345 congrats for your work, I was reviewing the POC, everything looks working fine, I did the following test.

• checkout to this commit e8ab85f
• install the app and execute the inserts with the old schema. Note: I had a problem but I fixed with this:

  @Entity
  data class Poi(
  @PrimaryKey val id: Int,
  val name: String,
  @Embedded
  val location: LatLng
  )

  that was in the first commit, only to run the app.

• after that I did checkout to cf38c26, deleted my preview hotfix and execute the app with the migrations
• and works.

Impressive job and you are right, it will be a great idea to have all this converters in spatia-room library, PR are welcome.

[fhvyhjriur]

To let the developer know that there are more people that would like to see OpenStreetMap as data source, i leave a +1

I now take back my interest in OpenStreetMap support in EVMap. There is a simple reason why: OsmAnd have a great support for charging station display and download the map for offline use. There are even live-maps available where you can add a charging station POI in the OpenStreetMap database and after an hour its already available for offline download in osmand thanks to livemaps. And yes, there is filtering functionality for different connector types and so on. Open the POI search, choose charging station, and press the option-button (the two lines). Then you can choose what types of charging stations you would like to display.

[dbrgn]

Hi @johan12345, are there any news regarding OSM support? Is there anything I can do to help?

[johan12345]

Hi @dbrgn! Support for offline caching of chargers (using spatia-room) is pretty much finished in PR #164 - after a bit more testing I want to release a beta version of that soon. Currently it just caches the chargers that are loaded from the API in the local DB to avoid making repeated API calls for chargers/map regions which have already been loaded recently.

So I think the next steps to add OSM support would be:

• Introduce the concept of an "offline-only" API where all chargers must be downloaded into the DB first before it can be used. Maybe something like could be added to the ChargepointApi interface to make this possible:

  
  /**
  * Whether this API supports querying for chargers at the backend
  *
  * This determines whether the getChargepoints, getChargepointsRadius and getChargepointDetail functions are supported.
  */
  val supportsOnlineQueries: Boolean

/**

• Whether this API supports downloading the whole dataset into local storage
• This determines whether the getAllChargepoints function is supported. */ val supportsFullDownload: Boolean

/**

• Fetches all available chargers from this API.
• This may take a long time and should only be used when the user explicitly wants to download all chargers.
• TODO: add an optional callback parameter to this function to be able to receive updates on the download progress?
• TODO: Should this also include getting the ReferenceData, instead of taking it as an argument?
• ReferenceData typically includes information that is needed to create the filter options, e.g.
• mappings between IDs and readable names (for operators etc.). So probably for OSM it makes sense
• to generate that within this function (e.g. build the list of available operators using all the
• operators found in the dataset). */ suspend fun getAllChargepoints(referenceData: ReferenceData): List

  
  - Build a `ChargepointApi` implementation for OSM - in this case the main functions that would need to be implemented are `getAllChargepoints`, `getFilters` (with a sensible set of filters based on the properties that are available in OSM) and `convertFiltersToSQL`
  - Adapt the frontend so that OSM can be chosen as the data source and that the user is prompted to download the data once when the data source is selected
  - Implement a mechanism to manually or automatically (e.g. once per week) update the data
  - Set up a production server to host the JSON data. As discussed above, GitHub pages might not be a good idea (as the uncompressed JSON file is a bit too big for keeping it in a git repo with daily updates). My current plan would be to use [Uberspace](https://uberspace.de/), where we could also run the daily cronjob for updating the data using your shell script.

Does this sound good to you? Maybe if you have time, you could already start taking a look at the first two points.

[johan12345]

Hey @dbrgn, I started a basic implementation of the ChargepointApi for OSM and it already kind of works - see #290 for the code and open TODOs.

[dbrgn]

Hey @johan12345, sorry for not replying to your message from May... I thought I would have some time soon to help out, but we moved recently and I'm still swamped with other tasks that have higher priority 😅 I'll add some comments at #290.

[danieldegroot2]

If the license is compatible, imports into OSM would be possible as well. I myself will definitely start to invest some time into improving the data, and I know a few people that are quite active in the OSM community that might help.

Please see Import wiki page for -required- guidelines and other information.

[danieldegroot2]

Also, you should attribute "OpenStreetMap contributors" ("OpenStreetMap" is recommended but not required, the old version is still okay.) for data and (seperately) map data as needed. You should link "OpenStreetMap" to https://openstreetmap.org/copyright (the copyright page, please read it) if possible. Similarly, if using Mapbox, they require you to attribute them as well (see Documentation).

[danieldegroot2]

Furthermore, I would like to suggest the following;

Show the OpenStreetMap community what data you are using. Add EVMap to the list of Taginfo Projects.

[johan12345]

Also, you should attribute "OpenStreetMap contributors" ("OpenStreetMap" is recommended but not required, the old version is still okay.) for data and (seperately) map data as needed. You should link "OpenStreetMap" to https://openstreetmap.org/copyright (the copyright page, please read it) if possible. Similarly, if using Mapbox, they require you to attribute them as well (see Documentation).

Yes, of course we are already doing that: https://github.com/ev-map/EVMap/blob/107ec10ccb6359eec8e4840fab50c680f804df1f/app/src/main/java/net/vonforst/evmap/api/openstreetmap/OpenStreetMapModel.kt#L114

Map data from Mapbox also displays the attribution logo provided by their library.

Show the OpenStreetMap community what data you are using. Add EVMap to the list of Taginfo Projects.

Thanks, I will look into that when OSM support (#290) is finished.