Subscribe to this thread
Home - General / All posts - Bringing LiDAR clouds into Future as a projected drawing
dchall8
341 post(s)
#12-Oct-17 00:09

I'm trying to simplify bringing 2,000 LiDAR files into Manifold Future. Each file has roughly 20 million points of various classifications. Ultimately all I need out of these files are the points where the value of the classification field = 6. This is a zero to 1% fraction of each file. Ultimately these points will all be in one drawing for the county. Is it possible to write a single query to link to all these files, one at a time or in batches, extract records where classification = 6, bring them to Manifold Future as a table, create a drawing in UTM 14N, and color the points red? If this cannot be done with a link, can I bring the .las file in and make the computer do the rest of the processing? I have the SELECT INTO FROM WHERE syntax to get just the class 6 points, but can that be extended to make (or append into) a projected drawing with red points?

danb


1,603 post(s)
#12-Oct-17 23:32

With some help from Adam and Tim, I have been experimenting with the following process (which admittedly will be a little cumbersome for 2000 files), though should work well if you could script it.

Firstly following Adam's advice (http://www.georeference.org/forum/t138408.11#138461) I link to all the las/las files I want to use.

I then write a query (example below) to return the filtered, scale corrected data.

Finally I use Edit > Export Results to write the filtered points back to a las or laz file*

-- $manifold$

SELECT

VectorValue(GeomCoordXYZ([Geom], 0), 0) AS [X],

VectorValue(GeomCoordXYZ([Geom], 0), 1) AS [Y],

VectorValue(GeomCoordXYZ([Geom], 0), 2) AS [Z],

[Geom]

FROM

(

SELECT [Geom] FROM [C_BG36_1K_1337]::[C_BG36_1K_1337] WHERE [classification] = 2

UNION

SELECT [Geom] FROM [C_BG36_1K_1338]::[C_BG36_1K_1338] WHERE [classification] = 2

UNION

SELECT [Geom] FROM [C_BG36_1K_1339]::[C_BG36_1K_1339] WHERE [classification] = 2

UNION

SELECT [Geom] FROM [C_BG36_1K_1435]::[C_BG36_1K_1435] WHERE [classification] = 2

);

* Note: I have found that when I use export results, the resulting las/laz seems to ignore the scaling value and write the file with a value of 1. I have reported this to tech.

At the current time, you have to build a temporary index on point clouds which can be painful. As I understand however there is a specialist point cloud index in the pipeline.

The process above would also be much easier if you could treat/define a folder or directory tree of las/laz files as a single virtual dataset.

Attachments:
Clipboard-1.png


Landsystems Ltd ... Know your land | www.landsystems.co.nz

dchall8
341 post(s)
#13-Oct-17 22:03

BIG HELP! Thanks Dan. Your query worked great for me. I'm doing them in batches of 8 files. That way I'll only have to run it 250 times... for each classification.

As you are well aware, the first tedious part of this is making the individual links from Future to the LAS files in a folder on your computer. LAS file names are written in a code with spaces and subtle differences which make them hard to target individually I created an Excel file with the LAS file names and check them off as I go, so I don't duplicate them. I copy the file name from Excel, click File>Link in Future, and paste the file name into the search box at the upper right of the Link dialog box. Windows finds the file for me. Then doubleclick on the file to complete the link. That way I don't have to hunt for it in the list of look-alike names. It would be nice if the import could be done in a multiple select or batch mode. Or it would be nice if a script could iterate through the remote folder to make these links. The second tedious part is reforming the query in the [las_data_source]::[las_table] format. I have been dragging the table into the Query Builder lower right frame, double-click selecting the old text in the query, and double clicking the table name in the Query Builder lower right frame. That replaces the old text with the new text. The reason I limit these queries to 8 files is that it becomes hard to distinguish one line of code from another when all the file names are so subtly similar. So I tried dragging one of the linked tables into a folder on Future. What happened is Future imported the file instead of retaining it as a link. That defeats the purpose of linking. So I dragged the entire link into a folder. I'm not sure that helps. It would be nice if a script could use that folder in Future to iterate through the tables buried therein. If I were to ask for the world, it would be very nice if a script could

  1. go to a named LAS files folder source,

  • link to the first LAS file,

  • run the query to extract data where classification = 6 (in my case),

  • delete the link,

  • iterate to the next file in the LAS files folder,

  • link to the selected LAS file,
  • run the query to append new results to the old results,

  • delete the link,
  • repeat steps 5-7 until the folder of LAS files is exhausted
  • use the results to create a table
  • use the table to create a drawing in a selected projection.

    I can take it from there. If I knew how to do any of that I would be all over it.

    Does it seem odd that you have to export the results of the query and bring them back in to use them in your project?

  • otm_shank
    48 post(s)
    #14-Oct-17 06:46

    I've been trying to do similar, however I haven't been able to find the answer on how to import or link (or delete a link) via scripting:

    http://www.georeference.org/forum/t138528.17

    If that were possible, you could create a list of your LAS files using a DOS command into a text file, the script could open/link these one at a time and query your required data into a table within the MAP project.

    rk
    216 post(s)
    #16-Oct-17 10:47

    Dan, question below.

    I used very similar query for importing lines from linked dwg-s and experienced nonlinear slowdown when adding more and more UNIONs. I ended up using INSERT for every linked file, otherwise it got too slow. It happened even if I imported dwgs first into project - adding UNION made query more slower than adding extra INSERT.

    Didn't you have that problem?

    I see that my query uses extra values in every UNION, that is different.

    --SQL9

    insert into [result] ( ... )

    select

      ...

    from

      (

    -- 

      SELECT [ObjID][Geom]'B1' as [BLOCK]'1' as [LEVEL] from [B1]::[1 Table] WHERE GeomIsLine([Geom])

    UNION ALL SELECT [ObjID][Geom]'B1' as [BLOCK]'2' as [LEVEL] from [B1]::[2 Table] WHERE GeomIsLine([Geom])

    UNION ALL SELECT [ObjID][Geom]'B1' as [BLOCK]'3' as [LEVEL] from [B1]::[3 Table] WHERE GeomIsLine([Geom])

    UNION ALL SELECT [ObjID][Geom]'B1' as [BLOCK]'4' as [LEVEL] from [B1]::[4 Table] WHERE GeomIsLine([Geom])

    -- 

    UNION ALL SELECT [ObjID][Geom]'B2' as [BLOCK]'1' as [LEVEL] from [B2]::[1 Table] WHERE GeomIsLine([Geom])

    UNION ALL SELECT [ObjID][Geom]'B2' as [BLOCK]'2' as [LEVEL] from [B2]::[2 Table] WHERE GeomIsLine([Geom])

    UNION ALL SELECT [ObjID][Geom]'B2' as [BLOCK]'3' as [LEVEL] from [B2]::[3 Table] WHERE GeomIsLine([Geom])

    UNION ALL SELECT [ObjID][Geom]'B2' as [BLOCK]'4' as [LEVEL] from [B2]::[4 Table] WHERE GeomIsLine([Geom])

    otm_shank
    48 post(s)
    #05-Nov-17 23:47

    Not sure if this is still helpful since you may have processed all the files manually... however I've been playing with automating file import/processing (thanks to Adam's assistance in another thread) and think it could be done in your scenario.

    1. Create a DOS command file (e.g. CREATE_LAS_FILE_LIST.cmd) which when run will scan through your folders and list any .las filepaths it finds into a new text file. Point it at the top folder, e.g.

    C:

    CD C:\LAS Files\

    dir /s /b *.las >las_file_list.txt

    When run it will create a .txt file which will contain all the filepaths for your 2,000 LiDAR files, check this has worked properly.

    2. Create a new script for language VBScript, and use the code below. There's a few sections you'll need to alter to suit your setup, hopefully you can work it out or else just advise of the right paths etc. Also it assumes all the tables have the same structure - if this isn't the case you'll need to specify fields required within the INSERT INTO query. At the end you will/should have one table [big_las] containing all the records from your 2,000 files where classification = 6, then it's easy to just create the drawing etc. I've tested it on 40 sample LAS files and it works, let me know how you go! Tim

    ' VBScript

    Sub Main

    Set app = Manifold.Application

    Set db = app.GetDatabaseRoot()

    Set fso = CreateObject("Scripting.FileSystemObject")

    Set textFile = fso.OpenTextFile("C:\LAS Files\las_file_list.txt")

    i = 1

    Do Until textFile.AtEndOfStream

    app.Log "Processing file " & i

    app.OpenLog

    filePath = textFile.ReadLine

    fileName = fso.GetFileName(filePath)

    tableName = Left(fileName, InStr(1, fileName, ".")-1)

    lRet = ImportLasFile(filePath, tableName)

    If i = 1 Then

    text = "SELECT * INTO [big_las] FROM ["+tableName+"] WHERE [classification] = 6;"

    Else

    text = "INSERT INTO [big_las] SELECT * FROM ["+tableName+"] WHERE [classification] = 6;"

    End If

    db.Run(text)

    text = "DROP TABLE ["+tableName+"];"

    db.Run(text)

    i = i + 1

    Loop

    textFile.Close

    app.OpenLog

    End Sub

    Function ImportLasFile(filePath, tableName)

    Set app = Manifold.Application

    Set db = app.GetDatabaseRoot()

    Set db2 = app.CreateDatabaseForFile(filePath, true)

    Set file_table = db2.Search(tableName)

    Set source_schema = file_table.GetSchema()

    local_table = db.Insert(tableName, "table", source_schema)

    Set new_table = db.Search(tableName)

    Set fields = source_schema.Fields

    ReDim names(fields.Count - 1)

    For j = 0 To fields.Count -1

    names(j) = fields(j).Name

    Next

    new_table.InsertBatch file_table.SearchAll(names)

    ImportFile = true

    End Function

    dchall8
    341 post(s)
    #06-Nov-17 22:37

    O...M...G

    This looks like exactly what I need. If this works I will buy you the Internet or a reasonable facsimile (think Etch-A-Sketch).

    Ran the .cmd and got a list of 2,200 files with no prob. It ran so fast I thought it didn't run, but it did. Shortened the list to way more than 40. I didn't think I had scrolled down that far but there 95 files on the test list. Plugged the modified script into MF and it's running. "Processing file 3" after about 30 minutes. Big_las has been created. I'll let this run until it stops. Estimating 16 hours at 10 minutes per file. These files range from 18 million to 25 million points, so we're dealing in averages. The main point is it is running on the files in an automatic mode. THAT'S what I'm talkin' about!

    otm_shank
    48 post(s)
    #07-Nov-17 08:50

    Glad to hear it might be helpful. There are a few ways we could try to speed up the processing, starting with adding an index to the classification field so the query runs faster. However the bulk of the time is probably spent importing the files, which isn't ideal since they're deleted as soon as the query is completed. Instead it would be better if the files were linked instead of imported. I'm not sure how to do this directly with code, but it can be done thru SQL (which can be run via code). I'll have a look tomorrow.

    otm_shank
    48 post(s)
    #07-Nov-17 10:30

    OK try this code instead:

    Sub Main

    Set app = Manifold.Application

    Set db = app.GetDatabaseRoot()

    Set fso = CreateObject("Scripting.FileSystemObject")

    Set textFile = fso.OpenTextFile("E:\LAS\las_file_list.txt")

    i = 1

    Do Until textFile.AtEndOfStream

    app.Log "Processing file " & i

    app.OpenLog

    filePath = textFile.ReadLine

    fileName = fso.GetFileName(filePath)

    tableName = Left(fileName, InStr(1, fileName, ".")-1)

    newPath = CreateBackslashPath(filePath)

    text = "CREATE DATASOURCE ["+tableName+"] ("

    text = text + "PROPERTY 'Source' '{ \""Source\"": \"""+newPath+"\"", \""SourceCacheExternal\"": \""false\"" }',"

    text = text + " PROPERTY 'Type' 'las' );"

    db.Run(text)

    If i = 1 Then

    text = "SELECT * INTO [big_las] FROM ["+tableName+"]::["+tableName+"] WHERE [classification] = 6;"

    Else

    text = "INSERT INTO [big_las] SELECT * FROM ["+tableName+"]::["+tableName+"] WHERE [classification] = 6;"

    End If

    db.Run(text)

    db.Delete tableName

    i = i + 1

    Loop

    textFile.Close

    app.OpenLog

    End Sub

    Function CreateBackslashPath(filePath)

    newPath = ""

    for i = 1 To Len(filePath)

    char1 = Mid(filePath, i, 1)

    newPath = newPath + char1

    If char1 = "\" Then

    newPath = newPath + "\\\"

    End If

    Next

    CreateBackslashPath = newPath

    End Function

    dchall8
    341 post(s)
    #07-Nov-17 14:17

    SUCCESS! Qualified success, but success in so many ways. It stopped running at file 32. "Cannot load file," I believe is what the error said. By my rough calculations that would have been at 1 hour after I abandoned my computer to go home. It could be the computer goes to sleep after an hour. We have a contractor managing our security, so this could be something I need to fix.

    I'll run the new code starting at file 32 and see how it works. But otherwise, I got a big file of class=6 data. I created a new drawing in the right projection and found just what I was looking for.

    Thank you so much for lending your skills to this.

    dchall8
    341 post(s)
    #07-Nov-17 16:45

    Second script did not run the first try. Error msg says

    [23:1] Can't add record.

    So I tried running the first script again and got basically the same error although the distinction may mean something to someone.

    [49:1] Can't add record.

    Thinking the file might be corrupt, I removed it from the list of files to process and reran. The second script ran on the first file and stopped with file 2. I changed the file name to big_las2 to keep it separate. The error says

    [33:1] Can't add record

    The first time this ran table big_las contains 91,200 records. Considering there were roughly 620 million records NOT imported from the 31 files processed, this is a huge savings for me. big_las2 has 7,637 records.

    I just noticed that after the error came up, a new file has been added to the project. It is named the same as file 2 in my list of files. I opened the drawing accompanying the table and it seems to load normally. I selected points where classification=6 and viewed them with Google Earth. Everything seems normal. I saved and closed MF and started over with this. I got the same error [46:1] Can't add record. in the middle of processing file 2. The same file is now part of the project.

    Before I delete this second file name from my file name list and rerun, I'm going to see if there is a reply to this message with another idea. If some of the files are corrupt, it seems like I got very lucky to have 31 in a row load successfully and then two files almost adjacent to each other that failed.

    dchall8
    341 post(s)
    #07-Nov-17 17:55

    I think I have more personal insight into this. The number in brackets seems to be the script line number that fails. I ran the first script in the new instance of MF and after 20 minutes or so it stopped. I ran the second script and after 2 minutes or so, it stopped. So YES, you were correct. Linking to the file is much faster than importing. I think we knew this, but I wasn't paying attention to the differences between the two scripts. Now that I see what they are doing, I believe the problem is in the data.

    In both cases the reported line number has this text

    db.Run(text)

    I doubt that is the issue since, again, 31 files in a row worked seemingly flawless with that syntax. When the script runs, as you know, the Project pane shows the current imported or linked file. When the script stops, that file remains in the project. That is, hopefully, helpful in diagnosing the reason for this. big_las stops again with 7,637 records. Would it be appropriate to think that record number 7,638 is the problem child causing the problem? That record could be hard to find.

    I extracted all the class=6 points into a table. I sorted the table on each field and could not find anything out of order with any of the records at either end of those sorts. There is one record with 5 returns. Most have 1 return. Many have 2, 3, or 4, but only one has 5. I'm not sure what that means. In the previous project, after 31 files processed, there were 9 records with 5 returns and two with 6 returns. If there is a problem with one of these records it is not obvious with this cursory glance.

    otm_shank
    48 post(s)
    #07-Nov-17 22:49

    I think it's the mfd_id field and index which is causing the issue, it's carrying across values which then aren't unique in the big_las table, which breaks the 'unique' constraint on the index. Try the code below, it's a bit clunky but it drops the mfd_id field and index as it goes along. Make sure to run it in a new project, i.e. so that table big_las doesn't exist yet.

    ' VBScript

    Sub Main

    Set app = Manifold.Application

    Set db = app.GetDatabaseRoot()

    Set fso = CreateObject("Scripting.FileSystemObject")

    Set textFile = fso.OpenTextFile("C:\LAS Files\las_file_list.txt")

    i = 1

    Do Until textFile.AtEndOfStream

    app.Log "Processing file " & i

    app.OpenLog

    filePath = textFile.ReadLine

    app.Log filePath

    fileName = fso.GetFileName(filePath)

    tableName = Left(fileName, InStr(1, fileName, ".")-1)

    newPath = CreateBackslashPath(filePath)

    text = "CREATE DATASOURCE ["+tableName+"] ("

    text = text + "PROPERTY 'Source' '{ \""Source\"": \"""+newPath+"\"", \""SourceCacheExternal\"": \""false\"" }',"

    text = text + " PROPERTY 'Type' 'las' );"

    db.Run(text)

    If i = 1 Then

    text = "SELECT * INTO [big_las] FROM ["+tableName+"]::["+tableName+"] WHERE [classification] = 6;"

    db.Run(text)

    text = "ALTER TABLE [big_las] ( DROP INDEX mfd_id_x, DROP mfd_id );"

    db.Run(text)

    Else

    text = "SELECT * INTO [test_select] FROM ["+tableName+"]::["+tableName+"] WHERE [classification] = 6;"

    db.Run(text)

    text = "ALTER TABLE [test_select] ( DROP INDEX mfd_id_x, DROP mfd_id );"

    db.Run(text)

    text = "INSERT INTO [big_las] SELECT * FROM [test_select];"

    db.Run(text)

    db.Delete "test_select"

    End If

    db.Delete tableName

    i = i + 1

    Loop

    textFile.Close

    app.OpenLog

    End Sub

    Function CreateBackslashPath(filePath)

    newPath = ""

    for i = 1 To Len(filePath)

    char1 = Mid(filePath, i, 1)

    newPath = newPath + char1

    If char1 = "\" Then

    newPath = newPath + "\\\"

    End If

    Next

    CreateBackslashPath = newPath

    End Function

    tjhb

    7,545 post(s)
    #07-Nov-17 23:08

    It's nice code, but can you use the code button to format it for the forum?

    otm_shank
    48 post(s)
    #07-Nov-17 23:39

    Ooh didn't realise that was there, will do - much nicer

    ' VBScript

    Sub Main

    Set app = Manifold.Application

    Set db = app.GetDatabaseRoot()

    Set fso = CreateObject("Scripting.FileSystemObject")

    Set textFile = fso.OpenTextFile("C:\LAS Files\las_file_list.txt")

    i = 1

    Do Until textFile.AtEndOfStream

     app.Log "Processing file " & i

     app.OpenLog

     filePath = textFile.ReadLine

     app.Log filePath

     fileName = fso.GetFileName(filePath)

     tableName = Left(fileName, InStr(1, fileName, ".")-1)

     newPath = CreateBackslashPath(filePath)

     text = "CREATE DATASOURCE ["+tableName+"] ("

     text = text + "PROPERTY 'Source' '{ \""Source\"": \"""+newPath+"\"", \""SourceCacheExternal\"": \""false\"" }',"

     text = text + " PROPERTY 'Type' 'las' );"

     db.Run(text)

     If i = 1 Then

     text = "SELECT * INTO [big_las] FROM ["+tableName+"]::["+tableName+"] WHERE [classification] = 6;"

     db.Run(text)

     text = "ALTER TABLE [big_las] ( DROP INDEX mfd_id_x, DROP mfd_id );"

     db.Run(text)

     Else

     text = "SELECT * INTO [test_select] FROM ["+tableName+"]::["+tableName+"] WHERE [classification] = 6;"

     db.Run(text)

     text = "ALTER TABLE [test_select] ( DROP INDEX mfd_id_x, DROP mfd_id );"

     db.Run(text)

     text = "INSERT INTO [big_las] SELECT * FROM [test_select];"

     db.Run(text)

     db.Delete "test_select"

     End If

     db.Delete tableName

     i = i + 1

    Loop

    textFile.Close

    app.OpenLog

    End Sub

    Function CreateBackslashPath(filePath)

     newPath = ""

     for i = 1 To Len(filePath)

     char1 = Mid(filePath, i, 1)

     newPath = newPath + char1

     If char1 = "\" Then

     newPath = newPath + "\\\"

     End If

     Next

     CreateBackslashPath = newPath

    End Function

    tjhb

    7,545 post(s)
    #08-Nov-17 02:31

    Yay! Code button!

    dchall8
    341 post(s)
    #08-Nov-17 18:54

    Genius, Dude! It's running and up to file 5 already, so you broke through the barrier. Clunky? Maybe, but it is still very speedy with the file link instead of import. Without putting a stopwatch to it I'd say it's 10x faster.

    So lets see if I understand. The first pass through it creates big_las and loads all the class 6 records from the first file.

    Then it deletes the mfd_id_x and mfd_id index fields from big_las.

    With the next pass it creates the test_select table and loads that with all the class 6 records from the first file.

    Then it deletes the mfd_id_x and mfd_id index fields from test_select.

    Then it copies all the records from test_select into big_las.

    Then it deletes the test_select table.

    With subsequent passes it recreates the test_select table and loads the class 6 records from each file into the big_las table via the test_select table.

    Is that about right? At this point it's up to file 23 in the time it took me to understand the script and write the summary. Aren't the index fields sort of important to have in the end result?

    otm_shank
    48 post(s)
    #08-Nov-17 19:05

    Yep that's pretty much it, except after using the first file to create the big_las table, it then jumps straight to file 2.

    Once it's finished you can just add the mfd_id field index to the big_las table, this could also be added to the script as the final step after the loop finishes.

    Glad to hear it's working so far, let me know how it goes.

    Tim

    dchall8
    341 post(s)
    #08-Nov-17 22:33

    3,793 seconds to process 62 files. 1 minute per. That is incredible. 503,348 records imported.

    This is a rural county. These 62 files cover an area which is sparsely populated. When I get to the part with more buildings the import may take a little longer. This is pretty great.

    Manifold User Community Use Agreement Copyright (C) 2007-2017 Manifold Software Limited. All rights reserved.