Thomas Kaltvik Lidar SQL Plugin - Manual ver 1.0

About

The plugin uses SQL Server to store processed Lidar ground data from ASCII grid files / Esri grids and fetches the altitude for queried X,Y value points at venue ground and road creation. The plugin manipulates the area data so that it is normalized on center (0,0). This normalization can then be exploited to import roads with altitude also fetched from the database, resulting natural flow of sceenery and roads as a valid start point of your own venue project.

Download

Thomas Kaltvik on GitHub [github.com]

Author

Thomas Kaltvik Software Engineering Limited, Finland [www.facebook.com]

Intro

The plugin uses SQL Server to store processed Lidar ground data from ASCII grid files / Esri grids[en.wikipedia.org] (.asc) and fetches the altitude for query X,Y points at venue ground and road creation.

• This plugin is based on Race Track Builder plugin prototype project at: RaceTrackBuilder / RTBPlugins at GitHub[github.com]
  
  
• The programs used here can be replaced with others if you desire and are more familiar with others. I have selected tools that are free and available for every one. Also i am familiar with these programs as my tools, but same outcome can be achieved with other software as well.

Example video how it works (Click settings for 2K60fps):

• The plugin is created to version 1.0 and it supports only cellsize 1 on version 1.0. The fetching for alttitude looks for closest complete meter. With future development the plugin should also support cellsize 2 and cellsize 0.5 and then the fetching algorithms need to be implemented to fetch altitude based on those cellsizes.
  
  
• You need to install SQL Server to use this plugin. Select the free Developer edition, since it supports databases over 10Gb. A area covering 10x10km with index results almost 20Gb database.
  Once you have installed the SQL Server and made a database to it, figure out the connection string which is like: Data Source = YourServerName; Initial Catalog = YourDatabase; Integrated Security = True
  
  
• Future versions might support other database engines as well. For ver 1.0 the SQL Server was selected becouse it was familiar for developer and easy to access for every one.
  
  
• If you want to support the development or thank for this, you can show your appreciation by donating at: https://www.paypal.com/paypalme/kaltvik

Hardware used on development

It is good to show some light on the hardware that was used on development, since the data files are big and some of the steps will take a long time if the resources are slower than used here.

• HP ZBook 15 G5[support.hp.com]
  
• Intel Core i7-8750H
  
• 32Gg DDR4 <- at least 16Gb is recomened when working with big merger ASCII files on SAGA-GIS. Single tiles are quicker. This also depends on the size of the tiles, since there can be squares of 1x1km, 2x2km, 3x3km and so on.
  
• M.2 SATA SED Solid State Drive 500Gb <- This is a bottleneck if a normal HDD is used. At least SSD is recomended.

No need to mention other specs, since those are the ones that will determine this manuals data read/write speeds. With this setup, it takes 27 minutes to read a merged 12x12km.asc file to SQL database resulting 144 000 000 lines/points with X,Y values and a index for XY pairs. Yes, that is 144 million datapoints. The SQL read/write is using all the tricks in the book by using SQL datatype, BulkInsert etc. I started from 3 hours on prototype and managed to get it to 27minutes. Smaller areas and tiles are quicker of course.

Download and installation

You can download the plugin from: https://github.com/ThomasKaltvik/LidarSQLPlugins by clicking ThomasKaltvik_LidarSQLPlugins_RTB.7z

Unzip the package after download and copy the individual height and image plugins to folders RaceTrackBuilder\Plugins\ + Height or + ImageMap.

There can be many different plugins in the folders.

Install SQL Server Developer

Since the SQL server is a tool to be used for the plugin and is a third-party software with many search results on Google, i am not going to go into details about installing it but will provide example search results that have complete installation instructions for it:

1. https://www.sqlservertutorial.net/install-sql-server/

2. https://www.guru99.com/download-install-sql-server.html

Notice that at the end of installation, you will be provided a default connection string. Save that to Notepad or write it down.

It also recomended to install SQL Server Management Studio[docs.microsoft.com] but it's not mandatory. The management studio is your browser to servers, databases and data tables under them. You can also write direct query commands as SQL scripts.

Thomas Kaltvik Lidar SQL Plugin creates tables:

• ProjectInfo
  
• Roads
  
• A new table for each new area. One table has one area data set.

The number of stored areas is only limited by the size on your harddrive. As an example the development used an area sized 144 square kilometers on one area table. The size of the table was about 20Gb.

Get Lidar data ready as ASCII Grid file(s)

• You need Lidar data in .LAS or .LAZ format, or directly as ASCII grid with cellsize = 1 (meter).
  There can be many tiles or just one. It is prefered to merge them to one big, but multiple works when the tiles are neighbour tiles forming a square. So 1, 4, 8 and 16 tiles is tested to work. In development the Lidar was taken from Finnish Land Survey agency that provides them freely. The tiles are 3kmx3km, so maximum amount tested consisted of 16 tiles giving the area coverage of 12kmx12km:
  (National Land Survey of Finland - File service of open data[tiedostopalvelu.maanmittauslaitos.fi])
  
  There is a lot of sources for national, Eu and other regional Lidar, use Google and track building forums to find them.
  
  
• If your data is allready in ASCII format, please jump to: TODO!
  
• If you have .LAZ formated lidar, use Laszip.exe to un-pack it to .LAS format.
  
• Once in .LAS form, copy the file(s) to one folder, that is easy to access, like c:\lidar
  Download and install FUSION. I prefer that this is in c:\FUSION so it's easy to access.
  
• Run from Command Prompt (CMD) for all the .LAS files:
  
  • c:\FUSION\GroundFilter c:\lidar\outputFileName.lda 1 c:\lidar\lasfilename.las
    
  • c:\FUSION\GridSurfaceCreate c:\lidar\dtmOutputFileName.dtm 1 f f 1 0 0 0 c:\lidar\outputFileName.lda
    
    (*Pay attention to those 1's and f's.. they have a meaning. You can run the Fusion commands separately like GroundFilter and it will tell you what the Switches and other options are.)
    
  • c:\FUSION\DTM2ASCII c:\lidar\dtmOutputFileName.dtm
    c:\FUSION\MergeRaster c:\lidar\merged.asc c:\lidar\*.asc

Install Saga-Gis and close gaps on data

• Install and open SAGA-GIS from SourceForge - SAGA-GIS[sourceforge.net]
  
• Open the merged.asc file in SAGA-GIS
  
• Open Geoprocessing menu -> Grid -> Gaps -> Close Gaps and process the file. The closing will take a long time so do something else that time.
  
• Once it's Gapped, select Import/Export -> Grids -> Export ESRI Arc/Info Grid and save the gapped file as c:\lidar\mergedAndGapless.asc
  
• Once the time consuming save is done, select once again Geoprocessing -> Terrain Analysis -> Morphometry -> Slope (Slope, aspect, curvature) and make it from the gappless grid.
  
• Save the slope as image -PNG
  
  Below is screenshot of 12kmx12km area which is 16 ASCII grids combained:
  
  And a zoom to exposed road section on that area:
  
  
  • We can use this as the background image in RTB and we can also use it to create roads in alternative way, wich i'll tell in Tips after basic instructions.
    
  • Create a project folder and copy the mergedAndGapless.asc file to there.
  
  
  

Using the data with plugin in Race Track Builder

• Open RTB and select the Lidar SQL height plugin.
  
  
• Paste the connection string
  
  
• Click Connect (If not Success! you have something wrong with the server or string -> go figure that out by following the manual on SQL Server installation)
  
  
• Create a new Area (or select existing empty one)
  
  
• Click ASCII to SQL checkbox -> click Read/Write button which comes visible.
  
  
• From new windows click Browse and select the folder where the .asc file is.
  
  
• If it tells you that file(s) were found everything ok and the information should update.
  
  The list of .asc files in selected folder is shown as dialog:
  
  The tiles and complete area are drawn as preview and data about the tile area is shown. You can access this data always from Area Info, since it is stored:
  
  
• Click Read/Write button. This might take also a lot of time and the estimate and progress bar have some bugs, so they might not always work (Before i fix them) so trust me that the process is okay and onces it's complete, the progress bar and finished time are updated. You can also check the progress in SQL Management Studio.
  
  The read/write progress looks like this when ui works normally:
  
  
• IF there is No-Data values, you will be prompted about it! NOTICE: If 0,0 has No-Data value the new venue creation will shurely fail! So please close the gaps in the data, like described above. The RTB starts the venue creation by checking the height on 0,0 and all other altitude values are compared to this. (I tried to manipulate and make a alternative method for this, but the 100% result comes only with closed gap - NO no-data values- so please follow the instructions.)
  
  
• Close the Read ASCII form.
  
  
• Your ASCII data is now set so that the center is 0,0. You can check the xll and yll (Lowest left corner) to verify the extents of the area. The original xll and yll are saved in the Area Info if you need them for something. Example bringing paths from Google earth to RTB. This can be done by manipulating path values XY. There is free converters for the lat/long values.
  
  
• Select the SQL Lidar Image plugin and set area widht. If your data is 6km*6km, set 5km*5km as an area and 6km*6km as Image Coverage. These are the values that are used and the RTB venue size is overriden by these.
  
  
• Select image background image size 512-16384 from RTB selection. (The biggest is good for this.) This is the size that RTB uses to call plugin for image creation. This value means pixels and is always a square: 512x512px, 1024x1024px... 16384x16384px and so on. All testing is done with goal to export to Assetto Corsa, so other values and settings are not tested on ver 1.0.
  
  
• Set Subdivide or not. Test this if problems creating a new venue. On development a maximum area of 10kmx10km with maximum subdivision was always succesfull. Bigger areas failed on any subdivsion.
  
  
• Click ok once you have set a project name.
  
  
• If nothing happens, gently click on the plain green area. I have noticed that some times the mouse click makes the venue to update and show gotten values from SQL as altitude changes.
  
  
• Start modelling your venue. Subdivide where needed to get more altitude points from SQL.

Install Irfanview and resize background image

• Install Irfanview[www.irfanview.com]
  
  
• Open the created Slope image.
  
  
• Resize it to the size that you selected the background to be. 512px-16384px square.
  
  
• Save the image somewhere as BackgroundMap.png
  
  
• Copy the image to the newly created projects Xpacks\MyPack\Textures\
  
  
• Say yes to overwrite. The plugin created image can be saved if you want to. This point is just a blank white.
  
  
• On RTB click F5 button to refresh textures.
  
  
• You can replace the image as many times you want. You can use different images while creating the venua and placing objects and another on the finnished venue.
  

Here is a example that has been manipulated by coloring and basic brightness/contrast settings to show forrests, lakes and fields on complete venue in game:

Install Inkscape, draw roads and export XY values

• Install Inkscape - Draw freely[inkscape.org]
  
  
• Download Inkscape_extension_ExportXY.7z from https://github.com/ThomasKaltvik/LidarSQLPlugins
  *that extension is a creation of others mentioned below:
  
  • Copyright (C) 2011 Jean Moreno
    
  • Copyright (C) 2011 John Cliff
    
  • Copyright (C) 2011 Neon22
    
  • Copyright (C) 2019 Jens N. Lallensack
    
  • Released under GNU GPL v3
  *to find the extension quickly, it is made available at the same place as the plugin, but other than that Thomas Kaltvik does not have any rights to it or can not, and does not claim any credits for it!
  
  
• Unzip and copy ExportXY.inx and ExportXY.py to folder: Inkscape\share\inkscape\extensions:
  
  
  
• Open Inkscape
  
  
• Set the path string format to be Absolute from Preferences -> SVG output
  
  
• Set the editor to have Y-axis to point positive up by uncheking the box:
  
  
• Open the original Slope image (The original! Not the resized!)
  
  
• Set the image size and offset on editor. If you data was one(1) tile with coverage of 3000x3000m then the size is 3000x3000px and offset is -1500x and -1500y.
  
  Then the center of image is (0,0). For example i'm showing a image that is 12000*12000px giving the offset values -6000x -6000y:
  
  
  
• Lock the values and remember not to move the picture while drawing road paths!
  
  
• Save this as a Inkscape .SVG. You can always come back to it and re-draw roads or add new paths for roads.
  
  
• Select Draw Bezier lines and curves tool and start clicking the path on road:
  
  
• Ones your finnished, select all path nodes and click Made selected nodes smooth:
  
  
• Select from menus: Extensions -> Export -> ExportXY (It is visible if you have copied the files to correct place) (Other Inkscape extensions to do this are available also.)
  
  
• Wait for it to process - it takes a long time - and then copy the values from dialog:
  
  
• You can paste the values directly in plugin Roads, but it's recomended that you save them to else where too as .csv. This can be done in Notepad or Notepad++.

Process the exported road in plugin and import in RTB

• Open RTB and open the project. Select Edit -> Venue -> Plugins -> Height and SQL lidar plugin.
  
  
• Click on the Roads and Information button. (Can be accessed on New Venue creation by clicking Area Info. Select correct area first, since the road is tied to selected area!)
  
  
• Add Road -> Browse the file or paste to editor. Give a name and save.
  
  
• Select the newly created road and click "Get Road altitude values from area data" and voila.. the road points have Z value. The road is automatically saved to database, so you can access it any time and add new roads. The number of roads in project/area is not restricted.
  
  
• Export the road to .csv file on your selected location:
  
  
• Close the Venue edit window and select RTB File -> Import track and select the file just saved.
  
  
• Click ok and voila! nro.2, the road should be in correct place:
  

Examples of background image changes

Original untouched slope as BackgroundMap.png

Slope image turned to greenish and black

Aerial ortho image as BackgroundMap.png

Known issues and bugs on Ver1.0

• Spelling mistakes on english language throuhg out the UI and this manual.
  
• The plugin is only tested to work with 10x10km area. More testing with bigger venue and underlying dataset is needed to promise bigger areas to create from the new venue creation functionality.
  
• UI and progress bar freezes when reading .asc file(s). Need to implement background worker. The progess is okay while UI freezes. Just wait it to end and end time and progress will be updated.

Future plans that are dependent on future Race Track Builder options

• From the raw Lidar data .LAS file is also possible to pin point trees by canopy, so it is possible to add complete forests if Race Track Builder will support object placing by plugins in the future.
  
• From real estate maps it is possible to pin point building locations, their size (in some accurancy) and orientation, so adding all area buildings is possible if Race Track Builder will support object placing by plugins in the future.

Wishlist for RTB functionality

• Checkbox option for altitude fetching
  
  • On -> Fetch altitude from selected plugin source for selected points and for area points on subdivision mouse click, even when subdivision is not applied to area -> existing points update.
    
  • Off -> don't fetch, like it is now.
    
  • Use case: User has made a mistake on manual venue editing and wants to go back to the original altitude on area where mistakes have happened, with out undoing subdivisioning and re-doing it.
    

[barnaudprod]

Dear Thomas ,
Can you explain what the string key should contain in RTB because I couldn't find the information indicating it when installing QLS Server?
Here's what I did:
data Source = MSSQLSERVER; initial Catalog = DbForRTBPlugin; integrated Security = True

Thank you in advance

[les_neilson]

As soon as I can I will try this out. Would be great to have support for 50cm data, as a lot of this is available for the UK. Many thanks for this!

[Pyrx]

great guide and impressive work with the RTB plugin!