Just a small intro. So GIS Cloud, we are a platform for collaborative mapping. We have a product, a freemium product. Software and platform as a service. Lots of users in lots of countries. We are hosted on Amazon and we built our platform, a map engine,

web and mobile products, link vector tiles for a long time now, for high-performing maps that run from the web but also now on mobile devices and we've been using lots of, of course, free and open source like Lifflet, GDAL, POGIS, SQLite, Specialite and so on. So with map rendering, the biggest problem with performance and the biggest issue with performance is actually a file format.

A form of the data set is stored in. So that can be a file. Some are optimized, some are not. It can be a database. Also some optimized, some not. Some with networking issues. So your map actually depends on the network connections towards a database or to a WMS or

WFS services and that's a problem if you want to deliver a map at very high-performing speed. Also, if we are thinking about mobile devices, then we have different, different issues, right? Because those devices even they are powerful today. Even as some PCs in a couple of years ago, but still there are no, there are not server farms that can really

deliver much CPU power and memory power, right? So map technologies that are being used today to deliver and render maps are not actually built for mobile devices. They're not mobile first. So usually how we deliver a map for a high-performing map is that we tile it. So we create a set of tiles

on these zoom levels, of course. We can easily distribute those tiles. There is no processing when you deliver them, but still you need to process a lot to generate them. And if you go deep down in zoom levels, as we know,

there can be really lots and lots of tiles. So the seeding of the map tiles, that's what I mentioned before. So it's a significant difference in user performance and user performance, but it takes too much time, storage and bandwidth, and when something changes in your data set, you need to still generate those tiles again.

So one idea is that we actually eliminate the problem with file formats, is that we dump the data from original formats, any format, vector or raster, into a format that can be easily and quickly read on any kind of client server,

setup or a mobile device. So we can think about databases, files, NoSQL. One common format or database that you usually use in Geo is, of course, PostG with PostgreSQL. It's a great database for storing all the spatial data. It's great. It has great performance for

reads and writes, lots of features and functionality, and it's key in building any kind of web or multi-tenant service. But they are not so good for rendering maps because they are usually a bottleneck in performance because you need to scale. It's sometimes difficult to scale it, and there are lots of complexity and features that you actually don't need, and you need to run a separate service.

So if you want to go for mobile, you will not install, of course, PostgreSQL with PostG on a mobile phone and then render your maps from there. So the answer, of course, is SQLite. SQLite is actually the most widely deployed database today in the world. So you use it even that you don't know it.

It exists in billions of copies. It's on every Android and iOS device on any OS you use, like Mac and Windows. All browsers use it. So it's a database that is actually embeddable, so there are no installs. The database itself is a library that you link together with your application code.

It's only 400 kilobytes in size, the database engine, of course, and that makes it portable. So you can port it on a mobile phone. You can port it on a desktop apps, which are usually using SQLite for storing data on the desktops, and it's really fast. So you eliminate any complex clients or architecture by actually

linking the database engine with your code, and then database is actually a file that you ship with your app. Some of you probably would ask, what about Geopackage? Of course, Geopackage is a great initiative and a standard, how you can actually a standard and container for SQLite.

When we started with SQLite and with this new approach, we were using Spatialite as a standard before Geopackage. So we built, and what I will show you today on SQLite and Spatialite, but of course we are thinking about Geopackage and to adapt to that standard, because it's an OGC standard. It's definitely something that we will do.

So how do you store maps in SQLite? You can store tile sets like vector and raster tiles, so that can be an MBTiles format, which is good for storing smaller areas, because you are still storing tiles. You need to store all those zoom levels.

It's easy to render those tiles on a mobile device, because there is no processing. You just display images and render them on the screen, so it's very light. But if you have lots of data, and you need to have a detail on your data, you will lose some information, because if you go very deep down with zoom levels, you will have too many tiles to ship over a phone and into a package.

So again, it requires lots of resources to generate, because tiles have to be seeded, packed, and then shipped to a device. The idea is that you store actually raw data, so records, attributes plus geometry into SQLite, because then you have the highest most possible precision.

The bandwidth and storage requirements are minimal, because you are storing only records. You get full precision, as I said. Maps can be then rendered on the fly from that database. It's possible to query the data, so not only that you will render maps on your device or a phone, but you can also get access to the features, attributes, do a search,

everything that is available on the phone. But it can be hard to process. There are some things that I'll show you what we did, because with limited hardware, you still need to deal with lots of records. Also, what we were using in our setup is Spatialite. So it's actually like what is PostG is to Postgres.

This is what Spatialite is to SQLite. It extends SQLite with ST functions, also their own binary format for storing geometries and, of course, projections and all that. So it's a really nice way actually to have ST functions in a SQLite database. Here is one example, an OpenStreetMap data that we used for a test.

So one country of around, roads of around 93,000 vectors. So in a shapefile, we told those extensions it's around 97 megabytes. When you convert it to SQLite, it's almost similar in size. And when you create some indexes, so you can create an R3 index,

which will make your SQLite database perform much better for doing any kind of spatial queries. And you can also do some full text searching indexes. It can go up, but still when you zip it, it's only around 50 megabytes that you need to ship to your mobile device. So let's say roads from the entire country.

How do you load vectors into SQLite? Of course, you can use GDAL and OGR to OGR, a very well-known command. So basically you say I want to format my data into SQLite, source, destination. You can use a small optimization here I mentioned, so you can turn off spatial indexes and then create them later on because the data will be loaded

into the SQLite faster than creating those indexes as you load it. So spatialite also gives you some of the additional tables in your SQLite database. Similar like Poggis is doing, so you get Geometry Columns, Spatial Reference System,

but also the second one is actually quite crucial. It's a virtual table. And an R3 index of your geometries. So if you want to query the data and let's say tile them, then of course you need R3 index because then you can quickly get features that intersect with any tile boundary.

How do you query the data? You select from your data set. It's very easy. And if you want to like intersect your query with a tile rectangle or a boundary, you can do that by using the R3 index, also a simple query, to get all the features that intersect with a certain area or tile in this case.

For search, SQLite itself has really great capabilities. So it has a couple of versions of their full text search engine, which works really, really fast and it uses low resources, low memory and has a really good performance.

We were using the version 5, full text 5, because it's the most optimal and it supports order by rank so that you can actually return results in relevance to your query that you have entered in the search input. How to render maps from SQLite? So one idea would be that you render features when you intersect

the database with your screen and when you pan around you can get features that are intersecting with your screen and then you can just draw them on the screen. Of course, if the database is big and we are talking here about hundreds of thousands of vector features, even millions, then that can be difficult, of course, to render on a mobile device and you are constantly hitting the database with

requests because every time you pan and zoom you need to grab the features. So the idea is that we actually do vector tiles, that we do them on a phone in a way that the phone is actually a tile server, so the phone generates the tiles on the fly and uses SQLite database as a source. By using the R3 index

we can then actually quickly generate and query those tiles. We can cache the tiles on the device so when you open the map again, it will not process again and hit the database. And it can be very fast with some optimizations when you down sample the data on zoom levels and you remove features that you don't actually need on a certain zoom levels, you can reduce the amount of

geometries that will be drawn on the screen in a tile. For this we were using the HTML5 canvas, so we are mostly web-based, so it works on mobile devices and web at the same time.

So the topic from today's talk is actually the sync, how we can sync the data. So once the data has been copied into SQLite, we can do sync in three ways. We can do a full update, partial update or incremental update. So full update is actually very basic. There is a new map on the back end, we pack the entire map, ship it to the device and then overwrite the existing

map on the device. It works, but it's not optimal because we are sending the data that we have on the device all over again and we are losing lots of bandwidth and unnecessary processing. Partial update is better because if you have a map and some layers have changed, then the client or a device can actually, in their request to the server,

they can say, I have this data set with this time stamp. The server can determine does this data set or time stamp is different than what we currently have on the back end. If it is, then it ships those layers into package and sends it over to the mobile device.

But still there is some overhead because if you have a layer of million features and there is two new features in your data set, you will still send the entire layer across the entire data set. So the most optimal way is actually incremental update, where we can actually create a diff in simple format on the back end

and then send that diff to the mobile device so that mobile device can modify their own database and into a state of the database on the back end. So we actually can send SQL commands to modify the database on the device. And you can do that easily with SQL diff command, which actually does diff difference between one database and another.

So if you have a database with one time stamp on the back end and there is a different time stamp, if you use this function, this command, it will create SQL queries, insert, update and delete. It will actually modify one database into another to make them equal.

Here's one example of how it works. So we have like one database in two versions. One version has two records, the other one has three records and there are of course different time stamps. So if we do incremental updating that way, the client or the device can actually ask the server about certain data set

and it ships also time stamp, which it has on their own device. If server determines that the most recent data set has a new time stamp, it will make a diff from that time stamp to the time stamp of the device.

So you execute the commands like that and it creates a file. So it creates a new insert statement that can be executed on the mobile device and modify states to actually catch up with the server.

So when you execute that on the device, you can issue a command or if you are using SQLite in your code, you can actually use SQLite API to do that. So you need to open the database, read the file that the server has sent to you,

execute one command by another and then you get your database synced with the back end. So benefits of the incremental update are that it's very fast because you're only sending the changes, only actions that will modify your local device and your local device and its databases to be updated with the back end

and also it can work with multiple versions of copies. So if you have multiple devices and one device has time stamp one, the other time stamp two, the third time stamp three and on the back end there is time stamp five, which is the most recent. Each device will get a different package because the server will make the diff between one to five, two to five and three to five.

So in that way, all devices can be synced at any point in time with the recent state of the back end. I don't have a live demo but I'll show you some screenshots how it looks on the apps we have. So this is one data set of a couple of hundred of thousand features, vector features in our map editor app that you can then style and create.

Then you can go in the map viewer app and you can access all your maps and open them in the map viewer application and then if you want to download that map offline, you can do that and it's being then downloaded and stored locally

in the most compact way as a SQLite database. And if you want to make an update, you again click on the download, it will send over a request, a similar request I have shown you before and make the update with the back end. Some of our clients are using really large data sets like one map has five gigabytes of SQLite databases

which we store locally on the device and for these kind of updates before this method, they were doing updates for 30 minutes or one hour but now they can do them in a couple of minutes or less, in a couple of seconds depending on the changes.

And this is how it works then in the offline mode so you can zoom in, zoom out, search, query the attributes, search for the data and yeah, that's it. Thank you. Any questions?

Yeah, sure. So how do you think that's going to work if you guys actually do adopt Geopackage since Geopackage doesn't really have a notion of how to work with SpatialLite yet? I think SpatialLite supports Geopackage from recently and also in the specification of Geopackage, there are some ideas about how they could stream the data.

So some strategies that we are using here for the changes I think could be applied in Geopackage so that you have a standard how you will actually do the updates of Geopackages.

So the question was does the diff go in both ways or just one way? For now we are doing it one way but the same strategy could be used to actually when you make the changes on the dataset in offline mode on the device, change geometries or attributes that you can use the diff

to send that diff back to the server to sync up.

So I didn't look much into the code of SQL diff but it probably uses indexes and probably there is record-by-record scanning but we are doing that, we try that with large datasets and we don't notice any kind of issues with performance for now.

But then I don't have any numbers how it works but it's an utility you get out of the box with SQLite and it seems very useful for this.

So yeah, I mentioned we have a client with 5 gigabytes of vectors stored locally on my phone and it works really well. I don't know how many vector features there is but lots of layers and the search, rendering, it all works really well.

Yes, so the question was we were using HTML5 canvas but did we do anything with native? We played a bit with iOS and MapKit and then we can use the same strategy we're using for rendering on canvas

we can then render native because the server itself generates the JSON tile, vector tile but also there is a binary version so you can easily parse that and then render on anything. So you can go native as well but it's just an experiment for now.

Thanks very much. Thank you.