This describes the sample script. script. To use this script an ODBC connection connection to a similar data set set must be created. Some sample sample data is included at the end of this document. document. Any line with a leading # is only a comment comment for coding clarity and is skipped by Lava. Lava. Lava is an implementation of the Perl programming programming language. language. Perl is a very open language and has contributions that implement implement a cornucopia of functionality. It is mostly free to download and use.
These Perl modules are included included with your installation of Vulcan. Vulcan. Lines that Lava/Perl will interpret as programming programming statements end in a semicolon. The word “use” tells Perl to look to this module when trying to interpret the commands commands in this Lava script. These three modules, modules, Lava, Vulcan, and Macro extend the Perl language to include functions and objects specific to Envisage. # modules used by Envisage use Lava; use vulcan; use Macro;
These Perl modules are most easily installed using cpan cpan or cpanm. The DBI module provides Lava with objects needed to access databases. databases. The DBD::ODBC module module allows this script to use data through an ODBC connection connection that is established on this computer. The Switch module allows this script to use a programming programming structure that allows the script to select one of a set of choices using the Switch syntax. # modules to handle the ODBC connection use DBI; use DBD::ODBC; # module to create the switch (case) programming programming construct use Switch;
ODBC means Open DataBase Connectivity and DSN Data Source Name. These are terms used for parts of the data connection that the operating system provides. These connections connections must be set up by you. This line specifies the name that is set up as the DSN that this t his script will use for the ODBC connection to the correct data. Many ODBC connections can exist exist on the operating system, but this script is looking for the one named named “ODBC Text”. Everytime the script sees $odbc_dsn, $odbc_dsn, it interprets it as the character string ‘ODBC Text’ not including the quotes. So the name of the ODBC connection that this script it looking for for is ODBC Text. Text. The my has to to do with variable scoping in the script. script. It is necessary and specifies that this variable, variable, $odbc_dsn, only has this visibility within this program program block. I won’t go into this concept further here, but using my instead of our is safer and normally what is needed. my $odbc_dsn = 'ODBC_Text'; 'ODBC_Text';
SQL stands for structured query language. It strives to be a generic method of accessing data regardless of software provider. This line is the SQL statement that specifies what order this Lava script expects the data table columns to be in when it asks the ODBC connection for data. This line specifies the content of the variable $sql. The leading qq/ and trailing / are quote marks in Lava. In this case qq/ stands for double quote and the trailing / ends the double quote. The important part of this line is between the double quotes. SELECT is a SQL, not Lava, command. With this Lava script, we are telling Lava to use SQL to get some data from a specific ODBC data source. The SELECT statement tells Lava what data to get. This SELECT statement specifies that it wants the POINT-ID column from the data to be the first that the ODBC connection returns to the Lava script. Likewise, the second, third, and fourth columns will be the X, Y, and Z columns from the data. It also specifies that the data source is StopeDate.txt. The key is that in the original data, the columns are labeled POINT-ID, X, Y, and Z. These columns can be in any order in the original data, along with any other columns that may be contained in the original data. This SQL command will only return the specified POINT-ID, X, Y, and Z columns. Additionally, it will return them in that order. # the query to execute my $sql = qq/SELECT [POINT-ID], [X], [Y], [Z] FROM StopeData.txt/;
The following lines specify some important information that the Lava script needs to interact with Envisage. This ODBC/SQL/Lava script is set up to read a sample comma separated variables file. This is a text file consisting of a line for each point. The first item in the line is the POINT-ID. If the first two characters of the POINT-ID are SL, it is a sill point. Likewise RB indicates a rib point. Sill points will be assigned to the layer IMPORTED_SILL, will be assigned color 2, and will be displayed using the symbol SILL from the symbol dgd. Note that this is not a standard Envisage symbol. If the first two characters of the POINT-ID are RB, the point will be assigned to the layer IMPORTED_RIB, will be assigned color 1, and will be displayed using the symbol RIB. # assign layer names and attributes for different types of points my $sill_id = "SL"; my $sill_layer = "IMPORTED_SILL"; my $sill_symbol = "SILL"; my $sill_color = 2; my $rib_id = "RB"; my $rib_layer = "IMPORTED_RIB"; my $rib_symbol = "RIB"; my $rib_color = 1;
The following code makes certain that a dgd is open in Envisage. If one is not open, it opens or creates and opens one called temp.dgd.isis. If that is not possible, the script ends. The code goes on to print the name of the operative dgd in the Envisage console. It also prints the name of the current
dgd to the Envisage console. This section of code is very reusable. Notice that Lava/Perl uses curly braces {} to encompass blocks of code. These can lead to some coding headaches. A text editor that tracks programming blocks, such as Notepad++ is highly recommended. Use it as if a Lava script were a text file that you are opening in Notepad. Notepad++ also has a Language menu option. If Perl is specified for an Envisage Lava script, Notepad++ will use color and margin blocking to delineate code blocks, key words, and variables. It is an excellent free tool. # make sure a dgd is open, if not create a temp dgd my $dgd = Lava::Current_DGDName( 1); if ($dgd eq '') { RunMenu("FILES_DESIGN_OPEN:temp.dgd.isis","abort","FINISH") || die "Macro mismatch.\n"; } $dgd = Lava::Current_DGDName( 1); print "\nDGD: $dgd\n";
This very technical section of code uses the DBI and DBD::ODBC modules to connect to the data via the $odbc_dsn specified earlier in the code and in the operating system. The key is that the $odbc_dsn variable contains the correct name for the name of the ODBC connection that this Lava script and contained SQL statement expects. # open the ODBC connection using the DSN my $dbh = DBI-> connect('dbi:ODBC:'.$odbc_dsn, undef, undef, {PrintError => 0, RaiseError =>0});
This is more very technical DBI code that handles the actual process of connecting to the data via ODBC. Important to note is that this code will print informatively to the Envisage console. Also, the last line, $sth->execute(); actually retrieves the data. This code was used nearly verbatim from the DBI and DBD::ODBC modules documentation. if (!$dbh) { print "$DBI::err\n$DBI::errstr\n$DBI::state"; } else { if (defined($dbh->err)) { if ($dbh->err eq 0) { print "Warning message : ", $dbh->errstr, "\n"; } elsif ($dbh->err eq '') { print "Informational message : ", $dbh->errstr, "\n"; } } # set up and execute the sql statement
my $sth = $dbh->prepare($sql); # prepare the query $sth->execute(); # execute the query
These lines specify the variables that will hold the values for each line of the data as it is read from the data connection. The my statement only creates the variable. The order of the variables does not matter in this line. It only creates the variables. The bind statements handle assigning specific columns to specific variables. The data will be assigned to these variable one row at a time, so each variable will hold one POINT-ID, X, Y, and Z. The names of the variables are colPointID, colX, colY, and colZ respectively. The $sth->bind_col part of these lines assigns specific column number to these variables. This is a little different from SQL programming convention. SQL normally does not require any specificity for column order, and operates only on column names like POINT-ID, X, Y, and Z. This implementation of DBI and DBD:ODBC require the column number. Again, breaking from most programming convention, the column numbers begin at 1 rather than 0. ############################################## # specify the variable for each column in the query # Note columns start at 1 (not 0). ############################################## my ($colPointID, $colType, $colX, $colY, $colZ); $sth->bind_col(1, \$colPointID); $sth->bind_col(2, \$colX); $sth->bind_col(3, \$colY); $sth->bind_col(4, \$colZ);
This programming structure, known as a while loop, runs through the data row by row. The $sth>fetch part gets consecutive lines of data from the ODBC connection. Notice that there is an opening curly brace. This is matched by a closing curly brace later in the code. These braces must always match or the script will fail. # retrieve one row at a time, parse rows and coords into vars while ($sth->fetch) {
These following lines do minimal error checking. The more rigorous the error checking, the more robust the code. # some minimal checking if (! $colX) {print "**** no $colLabel X value\n"} if (! $colY) {print "**** no $colLabel Y value\n"} if (! $colZ) {print "**** no $colLabel Z value\n"}
These two lines peel off the first two characters from the POINT-ID to store the type of point in the variable $colType. Then the data is printed in the Envisage Console. $colType = substr $colPointID, 0, 2; print "ID: $colPointID, Type: $colType, X: $colX, Y: $colY, Z: $colZ\n";
Next a Lava object is created and stored in the variable $new_point. Lava requires another object, called $new_point_coords here, to access other attributes such as coordinates. The last line in this group is commented out, but indicates how to set the attributes for a basic point using Lava. It is not used in this script in favor of symbolizing the points as seen in below. # create Envisage objects my $new_point = new Lava::Obj; my $new_point_coords = $new_point->coordinates; # $new_point_coords->i( 0, $colX, $colY, $colZ, 0, 0, $colPointID);
This line lets Envisage symbolize the point. . $new_point->feature( "SYMBOL");
This line assigns the POINT-ID to the name of the point. This can be displayed in Envisage by showing Object Name as the point label. $new_point->name( $colPointID);
The switch line initiates a programming structure that executes only certain lines of code based on a decision. In this case, the contents of the variable $colType is the basis for the decision. The variable $colType was set to the first two characters of the POINT-ID in a line above. Notice again, the opening curly brace that is matched by a closing one below that encloses the entire switch structure. This larger switch structure contains other structures with sets of curly braces nested within those of the switch structure. Lava and Perl scripts contain many such nested structures. In fact this switch structure is nested within an if – else structure initiated above. switch ($colType) {
One choice of the switch structure is the case that the contents of $colType is the same as the contents of $sill_id. In a line above, $sill_id was set to the character string “SL”. So, if the first two characters of the POINT-ID are SL, then this code between this set of curly braces is run.
The symbol is set to the $sill_symbol, symbol size to 100, symbol rotation to 0, point type to 1, coordinates to the data coordinates. The point color is set to $sill_color. The $sill_layer is created or activated. And finally, the new symbolized point is added to the layer. case "$sill_id" { $new_point_coords->symbol( 0, $sill_symbol, 100, 0, 1, $colX, $colY, $colZ); $new_point->colour( $sill_color); my $layer = new Lava::Layer( $sill_layer); $layer->add($new_point); }
Similar to the sill points, the rib points are assigned attributes if the first two characters of the POINTID are RB. This case structure only has choices for sill and rib points. To handle other points, similar sections of code are added to the switch structure. case "$rib_id" { $new_point_coords->symbol( 0, $rib_symbol, 100, 0, 1, $colX, $colY, $colZ); $new_point->colour( $rib_color); my $layer = new Lava::Layer( $rib_layer); $layer->add($new_point); }
The first closing curly brace below closes the switch block of code. The next one closes the while block of code. Because the switch block of code is inside the while block, the switch decisions are made for each line of data read by the while block. Notice in the sample data below that there are other types of points, TP, CL... These are examples of types of points for which this switch structure has no case section. This script will not process those points except that it will print them to the Envisage console. Finally, some programming cleanup is done for the data connection. The last closing curly brace ends the block of code started in the if – else structure that checked to see if the data connection opened properly. } } # clean up the ODBC connection $sth->finish(); # execute the query $dbh->disconnect if ($dbh); }
The nice feature of ODBC is that it separates the type of data from the program that uses it. This script was written with the data in a text file. The same text files that are typical of Leica total station job file export. The data might be in an excel spreadsheet, an Access database, or a SQL backend database. The script does not care as long as the names of the table and columns are correct in the SELECT statement above, and the ODBC connection specifies the data location. The select statement is simple to modify for different field names.
Full Code: # modules used by Envisage use Lava; use vulcan; use Macro; # modules to handle the ODBC connection use DBI; use DBD::ODBC; # module to create the switch (case) programming construct use Switch; # ODBC (Open DataBase Connectivity) DSN (Data Source Name) for the data connection my $odbc_dsn = 'ODBC_Text'; # the query to execute my $sql = qq/SELECT [POINT-ID], X, Y, Z FROM StopeData.txt/; # assign layer names and attributes for different types of points my $sill_id = "SL"; my $sill_layer = "IMPORTED_SILL"; my $sill_symbol = "SILL"; my $sill_color = 2; my $rib_id = "RB"; my $rib_layer = "IMPORTED_RIB"; my $rib_symbol = "RIB"; my $rib_color = 1; # make sure a dgd is open, if not create a temp dgd my $dgd = Lava::Current_DGDName( 1); if ($dgd eq '') { RunMenu("FILES_DESIGN_OPEN:temp.dgd.isis","abort","FINISH") || die "Macro mismatch.\n"; } $dgd = Lava::Current_DGDName( 1); print "\nDGD: $dgd\n"; # open the ODBC connection using the DSN
my $dbh = DBI-> connect('dbi:ODBC:'.$odbc_dsn, undef, undef, {PrintError => 0, RaiseError =>0}); if (!$dbh) { print "$DBI::err\n$DBI::errstr\n$DBI::state"; } else { if (defined($dbh->err)) { if ($dbh->err eq 0) { print "Warning message : ", $dbh->errstr, "\n"; } elsif ($dbh->err eq '') { print "Informational message : ", $dbh->errstr, "\n"; } } # set up and execute the sql statement my $sth = $dbh->prepare($sql); # prepare the query $sth->execute(); # execute the query ############################################## # specify the variable for each column in the query # Note columns start at 1 (not 0). ############################################## my ($colPointID, $colType, $colX, $colY, $colZ); $sth->bind_col(1, \$colPointID); # $sth->bind_col(?, \$colType); $sth->bind_col(2, \$colX); $sth->bind_col(3, \$colY); $sth->bind_col(4, \$colZ); # retrieve one row at a time, parse rows and coords into vars while ($sth->fetch) { # some minimal checking if (! $colX) {print "**** no $colLabel X value\n"} if (! $colY) {print "**** no $colLabel Y value\n"} if (! $colZ) {print "**** no $colLabel Z value\n"} $colType = substr $colPointID, 0, 2; print "ID: $colPointID, Type: $colType, X: $colX, Y: $colY, Z: $colZ\n"; # create Envisage objects my $new_point = new Lava::Obj; my $new_point_coords = $new_point->coordinates; # $new_point_coords->i( 0, $colX, $colY, $colZ, 0, 0, $colPointID); $new_point->feature( "SYMBOL"); $new_point->name( $colPointID);
switch ($colType) { case "$sill_id" { $new_point_coords->symbol( 0, $sill_symbol, 100, 0, 1, $colX, $colY, $colZ); $new_point->colour( $sill_color); my $layer = new Lava::Layer( $sill_layer); $layer->add($new_point); } case "$rib_id" { $new_point_coords->symbol( 0, $rib_symbol, 100, 0, 1, $colX, $colY, $colZ); $new_point->colour( $rib_color); my $layer = new Lava::Layer( $rib_layer); $layer->add($new_point); } } } # clean up the ODBC connection $sth->finish(); # execute the query $dbh->disconnect if ($dbh); }
Sample Data: RB_3-29_77,81.365,107.179,58.422 RB_3-29_78,105.707,118.01,54.997 RB_3-29_79,114.952,104.248,53.548 RB_3-29_83,83.201,116.986,55.434 RB_3-29_85,97.101,95.738,54.056 RB_3-29_86,83.311,116.995,55.862 RB_4-1_77,106.626,125.849,54.183 RB_4-1_78,118.602,121.66,57.21 SL_4-1_79,112.975,121.392,49.646 CL_4-1_82,113.681,125.292,55.041 RB_4-4_7,85.513,99.384,54.535 RB_4-4_8,77.326,111.244,55.796 CL_4-4_11,81.001,105.084,56.895 TP_4-4_1,101.293,89.698,53.314 TP_4-4_2,114.656,102.86,55.443 TP_4-4_3,120.343,122.762,58.789 TP_4-4_4,105.018,124.226,58.662 TP_4-4_5,99.972,120.825,57.413
RB_4-4_79,108.574,137.15,60.36 RB_4-4_80,122.523,132.593,59.898 CL_4-4_83,116.59,138.431,60.01 RB_4-7_77,115.563,152.146,63.673 RB_4-7_78,128.14,146.329,63.245 SL_4-7_79,124.187,148.121,57.788 CL_4-7_83,122.718,151.116,63.071 RB_4-7_17,74.232,92.398,57.772 RB_4-7_18,66.291,105.354,58.301 SL_4-7_19,72.044,98.414,52.25 CL_4-7_22,69.213,98.188,57.122 RB_4-10_7,120.841,165.036,67.089 RB_4-10_8,134.166,158.614,65.415 4-10_9,127.395,159.694,79.503 FT_4-10_10,120.961,164.158,73.313 CL_4-10_11,128.652,163.561,65.09 RB_4-10_15,63.986,85.847,60.635 RB_4-10_16,56.195,97.241,61.796 CL_4-10_19,57.919,90.069,61.062 RB_4-12_50,45.128,89.067,60.98 RB_4-12_51,51.446,77.132,61.834 CL_4-12_54,47.547,82.411,61.133 RB_4-15_50,141.65,170.114,65.985 RB_4-15_51,125.247,174.915,66.843 CL_4-15_52,134.645,175.327,66.267 RB_4-17_77,33.424,83.775,63.919 RB_4-17_78,39.042,69.366,64.074 SL_4-17_79,36.804,75.052,59.59 CL_4-17_82,34.883,76.556,64.581 RB_4-18_77,148.29,181.654,68.616 RB_4-18_78,129.597,188.794,68.591 SL_4-18_79,140.185,183.278,64.322 CL_4-18_80,139.253,190.354,70.711 RB_4-19_50,20.286,76.777,67.137 RB_4-19_51,26.465,63.783,66.702 CL_4-19_55,22.512,69.767,66.091 RB_4-23_77,151.839,196.498,71.759 RB_4-23_78,133.737,203.074,71.095 SL_4-23_79,143.587,197.403,66.37 CL_4-23_82,143.002,201.357,71.536 RB_4-23_17,13.001,60.092,69.865 RB_4-23_18,7.976,73.618,69.952 SL_4-23_19,8.911,67.564,65.221
