Global Mapper v25.0

Determine Variables (parameters) based on excel,csv, dbf file?

RobertR
RobertR GlobalMapper Fan!Trusted User
edited June 2013 in GM Script Language
Hello Mike,

I am currently working with a script that I think could be enhanced and I would like to get an opinion on this subject.

If you deal with US Tiger data, the state name, abbreviation, code are known and are always the same.

Is it possible to determine within the script some parameters if you have the correspondence between them in an excel (dbf, csv..) table ? To be more specific:

One has 3 prompt-parameters: st_code, st_abb and st_name

If you know that it's always '06'- 'ca'- 'california', it would be great to add just one parameter in the command line (eg 06 ) and to auto complete the st_abb and the st_name from the excel table within the script.

I hope I sated the question clearly. :)

Comments

  • global_mapper
    global_mapper Administrator
    edited June 2013
    Robert,

    So you want to be able to define the st_code value and then automatically have st_bb and st_name values assigned based on some lookup table from the st_code value? There isn't really any way I can think to automatically do that now. I could perhaps add a conditional DEFINE_VAR so that you would add a COMPARE_STR to the DEFINE_VAR command to be something like:

    DEFINE_VAR NAME="st_abb" VALUE="ca" COMPARE_STR="st_code=06"

    Then this would only define the st_abb value to ca if the st_code value was 06. You would then have a bunch of these, one for each value. And the same for the st_name variable. Would that work?

    Thanks,

    Mike
    Global Mapper Guru
    geohelp@bluemarblegeo.com
    http://www.bluemarblegeo.com/
  • RobertR
    RobertR GlobalMapper Fan! Trusted User
    edited June 2013
    Mike,

    I have to give it a one night thought, just to make sure your work will not be in vain. There aren't too many states so I guess you would only have to write them down only once.

    Would it be possible to have something like? (although it looks odd to be honest):

    DEFINE_VARS NAME_1="st_abb" NAME_2="st_name" VALUE_1="ca" VALUE_2="california" COMPARE_STR="st_code=06"

    You understood perfectly what I want to do. If you deal with a lot of tiger data, the state code already gives you the state abbreviation and the state fullname.

    so instead of writing a command line with 3 parameters you would just prompt for the state code and then deduct the rest from the lookup table or a line in the script.

    Of course, 3 params are not much at all, but considering that you also have counties (county name, county code).. you end up with too many variables in the end.

    Maybe in the future the SQL support will bring good alternatives in GM scripting as well (IF..THEN statements);
  • global_mapper
    global_mapper Administrator
    edited June 2013
    Robert,

    How about maybe something more generic where you just define the entire table, like:

    DEFINE_VAR_TABLE TABLE_NAME="state_codes"
    st_code,st_abb,st_name
    "06","ca","california"
    "08","co","colorado"
    END_VAR_TABLE

    The first line is the variable names to define the names of the variables, then the rest are the values. Then later to define a variable you would use:

    DEFINE_VAR NAME="st_abb" VALUE_TABLE="state_codes" COMPARE_STR="st_code=06"
    DEFINE_VAR NAME="st_name" VALUE_TABLE="state_codes" COMPARE_STR="st_code=06"

    Those lines would then examine the specified table for a st_code that is 06 and assign the specified variable value.

    Thanks,

    Mike
    Global Mapper Guru
    geohelp@bluemarblegeo.com
    Blue Marble Geographics for Coordinate Conversion, Image Reprojection and Vector Translation
  • RobertR
    RobertR GlobalMapper Fan! Trusted User
    edited June 2013
    Mike,

    I think this would be a great addition. It would practically allow a user to use a 'lookup' list within the script and then pass those values throughout the entire .gms file.
  • tjhb
    tjhb Global Mapper User Trusted User
    edited June 2013
    In my opinion, to have table definitions within the script would be unwieldy and hard to manage. (Imagine editing many scripts dealing with similar data.)

    I think it would be cleaner (and more future proof) to allow scripts to read a table from a CSV file.

    I also think the pseudo-SQL command should ideally include a source column name, separately from the variable name. E.g.
    DEFINE_VAR NAME="Abbreviation" FILENAME="State codes.csv" COLUMN="st_abb" COMPARE_STR="st_code=06"

    Similar to SQL:
    SELECT FIRST([st_abb]) AS [Abbreviation] FROM [State codes] WHERE [st_code] = "06";
  • global_mapper
    global_mapper Administrator
    edited June 2013
    I think if we update the DEFINE_VAR_TABLE to either have a table defined right there or have a FILENAME parameter that points to a CSV file with the table contents that should work the same. Sometimes you don't want a separate file all the time. Or you could always have one script file with the tables and just pull that in with EMBED_SCRIPT. We are working on implementing this and will let you know when something is ready.

    Thanks,

    Mike
    Global Mapper Guru
    geohelp@bluemarblegeo.com
    Blue Marble Geographics for Coordinate Conversion, Image Reprojection and Vector Translation
  • global_mapper
    global_mapper Administrator
    edited June 2013
    There is now a new DEFINE_VAR_TABLE command. I have placed a new build at http://www.bluemarblegeo.com/downloads/global-mapper/global_mapper14.zip with the latest changes for you to try. Simply download that file and extract the contents into your existing v14.xx installation folder to give it a try. If you are using the 64-bit v14 version there is a new build at http://www.bluemarblegeo.com/downloads/global-mapper/global_mapper14_64bit.zip .

    Here is the documentation:


    DEFINE_VAR_TABLE -- The command can be used to set up a look-up table in the script. Once the table is set up, you can use a DEFINE_VAR command to set up a script variable by looking up a value in the table. The data that makes up the table can be specified inline, or read from a CSV file. GM assumes that the first line of the data contains the column names for the table. This is true regardless of whether the data is inline or in a file.

    END_VAR_TABLE -- Indicates the end of the DEFINE_VAR_TABLE command. This is always required, whether the data is specified inline or in a file.

    Parameters:

    NAME=<the name> -- Defines the name of the table.
    FILENAME=<full path> -- (optional) Provides the full path to the CSV file that contains the table data. If both a file name and inline data are provided, only the data from the file is used; the inline data will be ignored.

    Changes to existing scripting command: DEFINE_VAR -- there are some new parameters:

    VALUE_TABLE=<tablename> -- Provides the name of the table to be queried.
    VALUE_COLUMN=<column name> -- Provides the name of the column that contains the data used to set the value of the parameter.
    COMPARE_STR=<column=value> -- Indicates which row to use for setting the value.

    Example (data specified inline):

    DEFINE_VAR_TABLE NAME="state_codes"
    st_code,st_abb,st_name
    06,ca,"california,xyz"
    08,co,colorado
    END_VAR_TABLE

    DEFINE_VAR NAME="st_abb1" VALUE_TABLE="state_codes" VALUE_COLUMN="st_abb" \
    COMPARE_STR="st_code=06"
    DEFINE_VAR NAME="st_name1" VALUE_TABLE="state_codes" VALUE_COLUMN="st_name" \
    COMPARE_STR="st_code=06"

    Example (data in a file):

    DEFINE_VAR_TABLE NAME="state_codes" \
    FILENAME="C:\Temp\GlobalMapperWorkspaces\test_table.csv"
    END_VAR_TABLE

    DEFINE_VAR NAME="st_abb5M" VALUE_TABLE="state_codes" VALUE_COLUMN="st_abb" \
    COMPARE_STR="st_code=09"
    DEFINE_VAR NAME="st_name5M" VALUE_TABLE="state_codes" VALUE_COLUMN="st_name" \
    COMPARE_STR="st_code=09"

    The example CSV file contains the following data:

    st_code,st_abb,st_name
    06,ca,"california,xyz"
    08,co,colorado
    09,me,maine

    Thanks,

    Mike
    Global Mapper Guru
    geohelp@bluemarblegeo.com
    Blue Marble Geographics for Coordinate Conversion, Image Reprojection and Vector Translation
  • RobertR
    RobertR GlobalMapper Fan! Trusted User
    edited June 2013
    Great.
    Sure just to be sure..if you can expect any state code as an input, you have to write DEFINE_VAR NAME=".." multiplied with the number of state codes? (aka a DEFINE_VAR_NAME for each state code?)

    Can I do:

    DEFINE_VAR NAME="statecode" PROMPT="DIR" PROMPT_TEXT=...
    DEFINE_VAR NAME="st_abb1" VALUE_TABLE="state_codes" VALUE_COLUMN="st_abb" \
    COMPARE_STR="st_code=%statecode%"
  • bmg_bob
    bmg_bob Global Mapper Programmer
    edited June 2013
    Robert,
    RobertR wrote: »

    Can I do:

    DEFINE_VAR NAME="statecode" PROMPT="DIR" PROMPT_TEXT=...
    DEFINE_VAR NAME="st_abb1" VALUE_TABLE="state_codes" VALUE_COLUMN="st_abb" \
    COMPARE_STR="st_code=%statecode%"

    Yes, you can do that.

    Cheers,

    Bob