take the edge off Survey, GIS

Credit for this one goes to Alec Tadman from Connell Wagner.
He came up with the innovative idea and emailed me - I just put the SQL code together (with a little help from my friends..)

The basic idea is to keep the points near the specified contour intervals (blue) and get rid of the rest (red), before building the contours.

[More:]

The Problem

• It’s all about Large Data Sets these days.
• With Civil 3D, we have funky tools for (nearly) every occasion but we are not geared up for the large point clouds from the laser surveys.
• There are workarounds.

The Workaround

There is a lot to all this - it’s been on the webcasts, newsgroups and eventually put together into a presentation at AU 2007 (see end of article for links) - these cover the specifics of the procedure in detail and are highly recommended - the general gist is:

• Instead of bringing in all the points, just bring in a representative sample to form contours for the rough lay of the land.
  (The points are added directly to the surface instead of creating COGO entities).
• Identify the area of interest from the rough contour map.
• Use some cunning map queries to bring in the rest of the points for that region only so fully accurate contours can be formed for this area of interest.

The first step of filtering the points to get the rough lay of the land is usually achieved by changing the number of points sampled. Take a 2 million point data set, sample every 2 points and the surface object only has 1 million points to worry about. This works very well but the filtering process is random.

The procedure in this post is a variation on this first filtering step.

The Variation

Image on left: 5m contours formed from a laser survey;
Image on right: Same data set - points filtered specifically for 5m contour intervals.

Less Points - similar Contours: Points are filtered before the DTM is built.

---

• Pick a contour interval (5m) and a tolerance (0.2m)
• Check the Z value of each Point - if it is within 0.2m of the even 5m interval (40.0, 45.0, 50.0 etc) then keep it - otherwise discard it.

---

(x,y,51.53) - 50.00 = 1.53 | >0.2 - Discard
(x,y,50.16) - 50.00 = 0.16 | <0.2 - Keep
(x,y,45.61) - 45.00 = 0.61 | >0.2 - Discard
(x,y,40.21) - 40.00 = 0.21 | >0.2 - Discard
(x,y,45.15) - 45.00 = 0.15 | <0.2 - Keep

Start off with a huge CSV Points File.

• Step 1: Build the Access Database (get the CSV to the MDB.)
• Step 2: Build the Filter Query
• Step 3: Export the Filtered Points to a new CSV file

Import the New Reduced CSV file to Civil 3D and business as usual.

Step 1: Get the CSV to the MDB

• In Access, Select File > New, (Blank Database), Pick File Name and Folder, Select Create
• File > Get External Data > Import, browse to the Points CSV file
• Select ‘Delimited’ - press NEXT
• Select ‘Comma’ (in this case) - press NEXT
• Select ‘In a New Table’ for where to store the data - press NEXT
• Highlight Field 1, call it ‘x’ repeat for Fields 2 and 3, calling them ‘y’ and ‘z’ (leave the data type as ‘double’ - press NEXT
• Select ‘Let Access add the Primary Key’ (if we had a separate column with point number, we could use that instead) - press NEXT
• Call the Table Table1 - press FINISH

This copies the entire CSV file into the Access Database - we can now run queries to filter the points. We are doing a data filter - we can use the same database for a spatial filter (again, see the links at the end for details on the spatial filters)

Step 2: Build the Filter Query

Access has wizards for queries which allow you to start with a simple statement and build up to a hardcore complex query. We are going to jump straight to the SQL code here as it is the simplest way to get this job done (especially when the code can be copied and pasted).

• Click Queries, Click New.
• Select ‘Design View’.
• Close the ‘Show Table’ dialogue box when it comes up.
• Select ‘SQL View’ from the View Pull Down Menu.
• Paste in the following SQL Code:

SELECT Table1.x, Table1.y, Table1.z
FROM Table1
WHERE (((Abs([Table1].[z]-Round([Table1].[z])))<0.2) AND ((Round([Table1].[z]) Mod 5)=0));

• Save the Query (I’ve called it ‘200mm on 5m Conts’).
• Click ‘DataSheet View’ on the View Pull Down Menu - see the result.

…the result

• In this case the original file had 4.2+ million points
• The filtered file now has less than 1.9 million points

We could have achieved a similar percentage reduction by making a new Point File Format in the Settings tab of the Prospector with the option ticked to sample every 2 points.
By using this SQL filter we gain some control over which points are removed. There is some judgment required on what values to use for the tolerance - see the last section for how to adjust the SQL query to vary the number of points. The idea is to work towards a point number that the hardware configuration can handle without damaging the accuracy too much.

Step 3: Export the Filtered Points to a New CSV File

We now export the filtered list of points to a new CSV file to be imported to Civil 3D.

• File > Export
• For ‘Save as Type’ - Select (Text Files (*.txt;*.csv;*.tab;*.asc))
• For Name, type ‘FilteredPoints.CSV’
• Click ‘Export all’
• On the Export Text Wizard, select ‘Delimited’ for the format, Next, ‘comma’ for the delimiter and Finish.

From here, its mere competence - import the points directly to the surface and identify the region of interest as mentioned above - there is a link at the end of this post to a webcast which goes through the procedure.

More on SQL

The SQL code, like many other codes, is quite easy to understand - not too difficult to change around - not quite so easy to compose from scratch. In this case, if we remember the mission statement to filter 200mm above and below a 5m interval, it is quite easy to follow.

SELECT Table1.x, Table1.y, Table1.z
FROM Table1
WHERE (((Abs([Table1].[z]-Round([Table1].[z])))<0.2) AND ((Round([Table1].[z]) Mod 5)=0));

could be translated as

SELECT X,Y,Z values
FROM The table imported from the CSV file
WHERE The Z value is within 200mm of an even number AND The even number is an even 5m interval

Change Contour Interval/Tolerance

Substitute the relevant values for TOL and CONT in the WHERE line of the SQL statement

(((Abs([Table1].[z]-Round([Table1].[z]))) (TOL) ) AND ((Round([Table1].[z]) Mod (CONT))=0));

E.G. to filter a 0.5m tolerance on 10m contours

(((Abs([Table1].[z]-Round([Table1].[z]))) (<0.5) ) AND ((Round([Table1].[z]) Mod (10) )=0));
(remove bold brackets)

What Else?

• The Naming Convention (Table 1, X,Y,Z(column names)) is important as the query statement refers to the specific names. If you change one, don’t forget to change the other.
• This docco is based on Access 2003 - the same query will work on 2007
• We have written a little freebie application to allow easier input of parameters - if you would like a copy of this or a sample database file with the query set up, please let me know.
• VOLUMES… The contours work well enough, but what about volumes? Alec Tadman, the person who came up with the idea in the first place, did some research into this - I’ll ask him if he can share a summary of this.

Its an interesting topic - there is plenty more to say about it, particularly the judgment side of assessing the tolerance for the contour interval.
cheers,
SeanT

Links

• Large Surfaces Paper. A presentation at AU 2007 by Dana Probert of Engineered Efficiency going into the specifics for hardware configurations, best practices etc.
• Lidar Survey Paper. A paper by Chris Gountanis of ImaginIT on how to weed points by location.
• Lidar Webcast. A webcast on the procedure for dealing with Lidar Data.