SQL Example: List Transformation Grids

In this example we use a few snippets of easy SQL to list NADCON, NADCON 5, and NTv2 transformation grids that are available within the grids.dat compressed collection of transformation grid files.   Grid transformation files can be used when creating custom base coordinate systems, for NADCON (including HARN and HPGN), NADCON 5, and NTv2 high accuracy transformations that use grid files.    Listing the grid transformation files also shows what resources we have in the automated availability of enhanced-accuracy Conversion methods in the Reproject Component dialog when converting between EPSG coordinate systems.


If we have downloaded and installed the grids.dat file as specified in the Product Downloads web page, when Manifold launches it will automatically find and use the grids.dat file.  The grid-based coordinate transformations will become part of our working environment, easily discovered and listed using the CoordTransforms SQL function.   


The grids.dat file includes compressed versions of over 170 grid files for NADCON and NTv2 transformations, plus NADCON 5 grid files.   Each grid file has a name.  NTv2 transformations use a single grid file that ends in a .gsb extension.  NADCON transformations use two grid files, one for a latitude shift and one for a longitude shift, that end in .las and .los extensions.  NADCON 5 transformations use at least two files, for GridLat and GridLon, and also may optionally use a third file, for GridHeight.

NTv2 Grid Files

We can find grid files within the grids.dat collection by searching either for the name of the transformation type, such as NTv2, NADCON, or NADCON5, or by searching for the files desired, for example, searching for all files with a .gsb extension if we want NTv2 grid files.



We launch the Command Window for SQL, and then enter and run the following query:


SELECT * FROM CALL CoordTransforms() WHERE StringContains(Value, '.gsb');


That gives us a report in JSON format for each grid file found, with the names of NTv2 grids that were loaded from the grids.dat file in the Value field.  Because the WHERE clause requires .gsb in the Value field, we get only the NTv2 grids.  


If all we want is a list of the names, we can adjust the query to use the StringJSONValue function to extract the grid name from the JSON value:



The query text is:


SELECT StringJsonValue(Value, 'grid', FALSE)

  FROM CALL CoordTransforms()

  WHERE StringContains(Value, '.gsb');


We have also added some newlines and indents, to make the query more legible.


If we like, we can Ctrl-click the result column header to sort the column, giving us a list of NTv2 grid transformations in alphabetical order:



If we would like to store the results in a new table, instead of just looking at them on the fly in the Command Window, that is easy to do by adding an INTO clause:



The query text is:


SELECT StringJsonValue(Value, 'grid', FALSE)

  INTO [NTv2 Grids]

  FROM CALL CoordTransforms()

  WHERE StringContains(Value, '.gsb');


That creates a new table called NTv2 Grids and saves the results into that table.



We can double-click open the new table to see the contents.

NADCON Grid Files

If we are interested in grid files for NADCON transformations, we can easily adapt our queries to do that:



The query text is:


SELECT Epsg, Value

  FROM CALL CoordTransforms()

  WHERE StringContains(Value, 'NADCON');


In this case, we have selected only two fields, the Epsg and the Value field, for all records with NADCON in the Value field.    We can see from the result that the JSON for NADCON transform grids lists both the .las and the .los files.


To see just the .las file names, we can again use the StringJsonValue function to extract only the GridLat name:



The query text is:


SELECT StringJsonValue(Value, 'GridLat', FALSE)

  FROM CALL CoordTransforms()

  WHERE StringContains(Value, 'NADCON');


That produce a results table with a neat list of only the .las file names.    If we do not want to take it on faith that NADCON automatically includes similarly-named .los file, we can ask for that as well:



The query text is:


SELECT Epsg, StringJsonValue(Value, 'GridLat', FALSE),

   StringJsonValue(Value, 'GridLon', FALSE)


  FROM CALL CoordTransforms()

  WHERE StringContains(Value, 'NADCON');


We SELECT three fields for the results table, the Epsg number, the name of the GridLat file, and the name of the GridLon file, and we put the results INTO a new table called NADCON Grids.



Double-clicking open the new table, we can see that each NADCON entry contains like-named .las and .los files.

NADCON 5 Grid Files

We can take a similar approach to listing all NADCON 5 grid files in grids.dat, searching for the NADCON5 string:


SELECT Epsg, StringJsonValue(Value, 'GridHeight', FALSE) AS [GridHeight],

   StringJsonValue(Value, 'GridLat', FALSE) AS [GridLat],

   StringJsonValue(Value, 'GridLon', FALSE) AS [GridLon]

  INTO [NADCON5 Grids]

  FROM CALL CoordTransforms()

  WHERE StringContains(Value, 'NADCON5');


In the query we select the EPSG number and strings for GridHeight, GridLat, and GridLon.   We use AS aliases to name the results fields.



The resulting table shows that not all NADCON 5 grids have a GridHeight file.   In the table above we have widened the GridLon field to show examples of the very long names NADCON 5 uses for grid files.


See Also



Assign Initial Coordinate System


Repair Initial Coordinate System


Reproject Component


Map Projection


Coordinate System


Coordinate System Metrics


Base Coordinate System


Favorite Coordinate Systems


Favorite Base Coordinate Systems


Example: Assign Initial Coordinate System - Use the Info pane to manually assign an initial coordinate system when importing from a format that does not specify the coordinate system.


Example: Change Projection of an Image - Use the Reproject Component command to change the projection of an image, raster data showing terrain elevations in a region of Florida, from Latitude / Longitude to Orthographic centered on Florida.


Example: Adding a Favorite Coordinate System - Step by step example showing how to add a frequently used coordinate system to the Favorites system.


Example: Detecting and Correcting a Wrong Projection - A lengthy example exploring projection dialogs and a classic projection problem.  We save a drawing into projected shapefiles and then show on import how a projection can be quickly and easily checked and corrected if it is wrong.