Preface
In this topic I would like to write some staff that I worked on lately as a long term feature and challenge I thought to introduce into the Mission-X plugin.
Unfortunately, from my perspective it was not high on the "to do" list since the feature will be usable only in specific cases and for specific feature... namely SAR. Yes, this type of missions can be a "pain in the XXXX" for programmers ;-)
In my previous rant, I spoke about extracting the OSM data into a local database. To summarize the topic, we read the OSM file and tried to reduce its content to a manageable size and store only relevant information (pretty sure there is room for improvements).
Now that our data is stored in a database, I want to read it so I'll have more precise locations to place our targets.
So let's take a look at some information I currently have in my "Washington" state OSM database.
So first You can see that the metadata can hold useful information regarding the characteristics of the OSM node segments.
So what are we going to do with this data
I admit that the amount of information is overwhelming and I think that currently the best way to approach this is to focus on: how to fetch the data from C/C++ code, and construct the best queries to fetch this information.
I know that this might sound "shallow" and trying to push the inevitable, you are correct, there are so many options, and I'm just one person, so I think at this stage I prefer to start simple and do some POC, test it in the plugin with minimal options, and in time add more options that will correspond to the new features. The OSM data will probably have some transformations as we move forward.
It is going to be "nasty" in next few lines, so bail out if you don't like codding :-)
My first attempt in this was to write a simple c++ project that will connect to our "sqlite" database and fetch the information at hand.
My approach to this capability is the same, more or less, as I do in the plugin.
When the Random Engine builds a mission, it picks a location/navaid in a certain radius, depends on the template designer. It can do basic filtering based on distances and type of plane.
When using OSM, I'm searching in a square area, and I can only fetch locations that were mapped into a "node" or "coordinate". So the data do not hold "forest" data, well I haven't really figured how to fetch this kind of information, but as any database, the OSM purpose is to let people add information freely, and I guess forests and such are harder to plot or they have little interest.
On the other hand, you can get information on "path" information that might point to "nature" environment.
But I digress.
Since SQLite is, in my opinion, an API based database, It is quite easy to just create a file and work on it.
So first thing I defined the header of the code. It includes quite a lot of files, but that is mainly because I done all sort of tests.
The most important is the "sqlite3.h" file which handles the engine.
The tables I created ahead of time, so our program just needs to fetch the data.
Now there are many examples on the web that explains how to do staff and why they are there, I'll just focus on what interests the plugin. I want to fetch coordinate data relative to a location I'll provide to the query (probably the plane location).
We already saw above an example to how I fetch the information in a client tool. Now we need to do the same from our program.
Next, we will execute a "select" statement (fetch data), but we will use the prepare directive. This means we prepare the statement ahead of time, the SQLite engine checks for its validity, and only then we will execute the query (in our example we will also bind variables to the statement).
My suggestion is to first test the query in a dedicated SQLite client tool (like SQLite Studio).
About the code
The "sqlite3_prepare_v2" function receives the "db" engine pointer and the query. It evaluates the query and return a "statement" pointer ("&stmt") that we will use later to execute and retrieve the results.
About the query
The query that I shared will fetch all "coordinates" that are in certain distance relative to a tested coordination of the plane location (at that time. I need to make these values dynamic too)..
Disclaimer: In this query I also use none default functions, like "radians, cos etc..." that do not come build in in SQLite. I'll speak about this later.
The question marks: "?" in the query, means that we would like to replace those signs with dynamic values (the number after the ? is for convenience, not mandatory but helps indexing large number of parameters we will later bind).
The first 4 values that I'll bind, represent the boundaries of the location we would like to pick for the mission. The last 2 values represent the distance constraints. Should be any positive number, between zero and Nth nautical miles.
How do we bind these values to the "?" sign.
Now all we have to do is to execute and fetch the information.
Interestingly, the function to execute the prepared statement and its bind variables is called: "sqlite3_step(stmt)"
If we had more fields in the query, we could store it in other containers/arrays ( what ever you see fit).
This is all that is needed to read from a SQLite database, quite simple. But nothing is free, and not all queries runs fast, there are other consideration that you need to take into consideration:
- Know the amount of data you need to fetch and make sure you do not exceed an expected amount of rows (unless you really need). Try not to fetch the whole table.
In Mission-X case, we should only fetch information up to ~100nm to pick an OSM node of information. - Limit the use of the "file based database" to specific actions. Working with external file can hurt overall performance of X-Plane.
- Use threads as much as possible to decrease the overhead on the main flight loop back and therefore might contribute to the stutters.
Since the Mission-X plugin use the database only for picking an OSM location, we only use it during the Random Engine work, which is already threaded. - Performance, make sure you leveraged the database capabilities to fetch the information as fast as possible, this includes indexes, table de-normalization or table partitioning to table by subject (this is not exactly pure partitioning but I want to simplify).
Since our database is mainly for reading information, we can safely create indexes and evaluate their performance and their benefit to the query at hand.
Here are some statistics related to the importance of indexes.
The first example, shows the time it took to fetch "node" information in certain area. We can see that it took ~18sec to fetch this information. This is quite a long time and in many cases unacceptable, even though it is running in a threaded loop.
Tip
If you are satisfied with your query results but not with the performance, use the "explain query plan" syntax to investigate how the database is fetching the information. Using the result, you can make your queries faster (do it in a dedicated tool first, only then through code).
Here is an example for an "explain plan" command on none indexed tables.
Now that the performance is reasonable, we should try and use the same in the plugin. Unfortunately the plugin needs more layers of manageability and customization before actually reaching that phase, but I hope to have it soon available for tests.
External Functions
SQLite comes with limited amount of build in functions. It lacks most of the mathematical functions that we are used to have in other databases or programming languages. Fortunately there are few simple ways to extend these functions, and one of the simplest ways is to use contributed one.
In my case, I added the "extension-functions.c" program to register many of the useful mathematical functions it adds.
The code was execute right after the database was opened.
Until next time
Blue Skies
Saar