We're sorry but this page doesn't work properly without JavaScript enabled. Please enable it to continue.
Feedback

Offline Maps Sync using SQLite

00:00

Formal Metadata

Title
Offline Maps Sync using SQLite
Title of Series
Number of Parts
208
Author
License
CC Attribution 4.0 International:
You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal purpose as long as the work is attributed to the author in the manner specified by the author or licensor.
Identifiers
Publisher
Release Date
Language

Content Metadata

Subject Area
Genre
24
197
SynchronizationPoint cloudPay televisionMobile WebComputing platformProduct (business)Vector spaceArtistic renderingService (economics)DatabaseComputer hardwareBefehlsprozessorProcess (computing)Read-only memoryMathematical optimizationDistribution (mathematics)NP-hardVolumenvisualisierungLevel (video gaming)HoaxInformationSource codeComputer fileFunction (mathematics)SoftwareClient (computing)Server (computing)Overhead (computing)Query languageSocket-SchnittstelleVirtual machineGraphical user interfaceWeb browserInstance (computer science)Complex (psychology)ArchitectureCodeRaster graphicsData storage deviceFormal grammarBand matrixProcess (computing)TesselationComputer fileDifferent (Kate Ryan album)Data storage deviceTexture mappingFile formatDatabaseSynchronizationClient (computing)Complex (psychology)MappingLibrary (computing)Front and back endsComputer architectureSoftware developerCollaborationismStandard deviationZoom lensLevel (video gaming)Computing platformSoftwarePoint cloudMobile WebVector spaceBefehlsprozessorSemiconductor memoryService (economics)Set (mathematics)Web 2.0MathematicsKey (cryptography)Android (robot)Web browserInstallation artoutputWindowMobile appCartesian coordinate systemRaster graphicsOpen sourceAttribute grammarMultiplication signGeometryBand matrixLink (knot theory)Form (programming)FreewareConnected spacePower (physics)Polar coordinate system
Core dumpLibrary (computing)GeometryWKB-MethodeSource codeVector spaceSubject indexingVector graphicsInformationTable (information)Price indexQuery languageAttribute grammarRectangleLattice (order)DatabaseRead-only memoryRankingOrder (biology)VolumenvisualisierungArtistic renderingNumbering schemeCache (computing)Mobile WebServer (computing)SynchronizationFile formatHeat transferOverhead (computing)Band matrixTimestampParameter (computer programming)Mathematical optimizationSanitary sewerGroup actionComputer configurationRankingSemiconductor memoryOrder (biology)GeometrySubject indexingDatabaseRectangleRevision controlQuery languageTexture mappingSynchronizationVolumenvisualisierungVector spaceTesselationSet (mathematics)Entire functionExtension (kinesiology)Structural loadProjective planeFunctional (mathematics)Multiplication signPartial derivativeFile formatState of matterRow (database)Differenz <Mathematik>Mathematical optimizationSampling (statistics)TouchscreenoutputWeb 2.0Zoom lensLevel (video gaming)Mobile WebBinary fileAdditionFront and back endsTimestampBand matrixProcess (computing)Client (computing)Server (computing)AreaBoundary value problemCASE <Informatik>Different (Kate Ryan album)Table (information)Computer animation
Server (computing)Client (computing)Maxima and minimaBand matrixData storage deviceSynchronizationRevision controlTimestampDatabaseRow (database)Revision controlDifferent (Kate Ryan album)State of matterServer (computing)Mobile WebMultiplication signMultiplicationInsertion lossComputer fileStatement (computer science)Point (geometry)Group actionDifferenz <Mathematik>Front and back endsClient (computing)Set (mathematics)Computer animation
Demo (music)Vector spaceMobile appDemo (music)Set (mathematics)Texture mappingText editorFile viewerCartesian coordinate systemComputer animation
File viewerFront and back endsSet (mathematics)Texture mappingClient (computing)Asynchronous Transfer ModeZoom lens2 (number)MathematicsDatabaseQuery languageXML
Attribute grammarServer (computing)NumberoutputStrategy gameUtility softwareCuboidTesselationRevision controlStreaming mediaSinc functionLatent heatCodeMathematicsSynchronizationVector spaceGeometryDifferenz <Mathematik>Subject indexingAsynchronous Transfer ModeClient (computing)
Transcript: English(auto-generated)
Hi guys, I'm Dino. I'm co-founder and CEO of GIS Cloud. In my core, I'm a developer and so I'll talk, I'll have a technical talk today about SQLite, how you can use it to store maps on the mobile devices, but also focus on the sync, how you can actually sync data from the back end to the client or a device in the most optimal way.
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.