A support question arrived asking about adding data into a spatial database, as it happens a article was written a while back on LinkedIn. In short, the GeoMapElements (Point, Circle, Line, Polygon, LineString, etc) are easily added to a spatial database using the method AsGeography(). The article describes how data contained in a Excel spreadsheet is read by an App that uses the FPAssistant SDK, then maps the data to a graphical element to be added to a spatial database.
It is interesting to see the patterns showing the placements of the wind turbines offshore UK, the red dots in the image below.
The article is…
QGIS displaying UK obstacle data
For a while I’ve been looking at QGIS as a tool to display geographical data, it was other colleagues in the aviation world that pointed me in this direction. After having a play with QGIS I thought I need something to achieve so I came up with a plan to display the UK obstacle data. This does sound straight forward but I did have a few hurdles to jump before I got it working to a point that I was happy with.
I got the data from the NATS website which is in an XLS format which I needed to get into a form that I could read from an app I’ve been developing, so I decided to go for the old CSV format. Saving the XLS out to CSV created issues as some columns in the spreadsheet have new line characters to separate the data like in the case of the Latitude/Longitude coordinate. This was not a big hurdle but a pain as I had to do manipulations in the XLS before I saved to a CSV file.
Next I visited Microsoft Azure and created a SQL Azure database and added a table to hold the Geography data and a few further fields for other data. i.e. Obstacle Identification and Description. This was quick and I made good progress.
I knocked up a small piece of C# code to add a single row to my table include spatial LINESTRING geography field and this I can see the data using a SQL query in SQL Server Management Studio. Then using QGIS, I attached to the SQL Azure database and a few minutes later I can see the geographical line in QGIS…Bingo!!
I needed an app to add the type of data I wanted into the table in SQL Azure, so I created a Windows 10 WinForm app and started to program. A few days later and further small hurdles jumped I now have an app that allows me to add aviation based data into the database which can be viewed in QGIS. I have tried to allow for most possibilities like adding point obstacles as geography POINT datatype as well as LINESTRING drawing a circle. I can also add 3D surfaces but as LINESTRING’s or POLYGON’s which the difference that the forma is 3D and the latter being 2D.
The main hurdle was getting the correct authentication of a SQL Azure login and user details for the database as QGIS and SQL Azure did not see eye to eye to begin with.
