DLG, USGS .DLG, .DO, .OPT

A U.S. Geological Survey (USGS) format, Digital Line Graph (DLG) format has been replaced by newer formats.  However, many drawings are still available in DLG format. Manifold's DLG dataport imports so-called "DLG Optional" format, the DLG format version which replaced the original DLG format used many years ago. Virtually all "DLG" files available use the "optional" format; however, on rare occasion one may encounter very old "dlg" files in the older format.  DLG files should have extensions such as .dlg, .do, or .opt.    Most DLGs will import with coordinate system automatically assigned.

 

DLG files, using the .opt  or .do extension, have been published by USGS in 1:24,000 and 1:100,000 scales, with a prefix indicating the content.  For example, the rd_ prefix indicates roads, hy_ indicates hydrology, rr_ indicates railroads, hp_ indicates hypsography (terrain elevation contours) and so on.   The files often extract into a drawing and a table, plus another table that contains attributes which, if desired, must be JOINed with the drawing's table to provide attributes for each drawing record.   

 

Drawings will often include areas and points which have little use, for example, points in hydrography layers that mark where a stream lines exit lakes.     Quite often the art of using DLG data boils down to cleaning drawings of unnecessary objects.  Since the useful data is often line objects, taking a moment to delete areas and points in a DLG drawing is often a quick and dirty way of getting a more useful drawing.   That is a "quick and dirty" approach since sometimes points in layers may indicate landmarks.  See the Example: Import DDF SDTS DLG Vector File topic for an example.

 

dlg_import_dlg.png

 

To import from DLG Optional format:

 

  1. Choose File-Import from the main menu.

  2. In the Import dialog browse to the folder containing data of interest.

  3. Double-click the file desired ending in .dlg, .do, or .opt.

  4. Tables and drawings and comments will be created.

 

eg_import_dlg01_01.png

 

We can double-click on drawings that are created to view them.   For a more interesting display, we first create a new data source using a Bing street maps image server as shown in the Example: An Imageserver Tutorial topic.   We then create a map and drag and drop the Bing layer into the map, and then we drag and drop the drawing into the map.

 

eg_import_dlg01_02.png

 

The drawing appears using the correct projection.  As is typical with DLGs, it includes areas and points that clutter the display.

 

eg_import_dlg01_03.png

 

We launch the Select pane.  First we select all of the areas and delete them, and then we select all of the points and delete those as well.   The result is seen above, a drawing that consists of lines.

 

eg_import_dlg01_04.png

 

We open the drawing's table to see it has an ID field for each object and a Geom geometry field that contains the geometry for each line.

 

eg_import_dlg01_05.png

 

If we open the other Attributes table, we see that USGS has separated the attributes for each object into a separate table.   That's a goofy way of organizing the data, but lucky for us we have a JOIN statement in our SQL that we can use.

 

We want to create a table like that seen below, where we join the two tables together using a field, ElementID, that occurs in both.  

eg_import_dlg01_06.png

 

JOIN queries are really simple to write.  The general form of a join is:

 

SELECT <fields from both tables> FROM <left table> JOIN <right table> ON <condition>;

 

The <condition> in this case is simple:  "where ElementID is the same".   What can make the query tedious is that our tables have long names.   When a table has a long name,  like rd_keokuk_ia_24k Attributes, it makes the query look complicated.    A way to simplify that complication is to use the AS statement to alias the long name into a short name.

 

Our query:

 

SELECT rd.ElementID, rd.Geom, att.MajorCode, att.MinorCode, att.Description

  INTO [rd_keokuk_ia_24k Joined]

  FROM [rd_keokuk_ia_24k] AS rd

  JOIN [rd_keokuk_ia_24k Attributes] AS att

  ON rd.ElementID = att.ElementID;

 

Run that query and it creates the Joined table seen above.  After creating the table, we also used the procedure in the Add an Index to a Table topic to add an mfd_id field and an index.

 

The query uses AS aliasing to replace [rd_keokuk_ia_24k] with rd, and to replace [rd_keokuk_ia_24k Attributes] with att wherever possible.  We could use whatever aliases we like that help us understand what we are doing:  the query uses rd as a mnemonic for "roads" and att as a mnemonic for "attributes."  

 

We have also avoided using square [ ] brackets wherever they are not necessary, for example, where field names or table names do not include a space.    That makes the query a lot shorter and easier to understand than the version without using aliasing:

 

SELECT [rd_keokuk_ia_24k].[ElementID], [rd_keokuk_ia_24k].[Geom],  

    [rd_keokuk_ia_24k Attributes].[MajorCode], [rd_keokuk_ia_24k Attributes].[MinorCode],

    [rd_keokuk_ia_24k Attributes].[Description]

  INTO [rd_keokuk_ia_24k Joined]

  FROM [rd_keokuk_ia_24k]

  JOIN [rd_keokuk_ia_24k Attributes]

  ON [rd_keokuk_ia_24k].[ElementID] = [rd_keokuk_ia_24k Attributes].[ElementID];

 

Notes

DLG in STDS Format - Many DLG files have been republished by USGS in SDTS format.  See, for example, the Example: Import DDF SDTS DLG Vector File  topic.

 

Square Brackets -  Square brackets are used to enclose names that contain spaces, such as table names and field names.   They are required for a table name such as My Table but they are not required for a name without spaces such as MyTable.   Some people prefer a style that always uses square brackets, whether they are required or not.  Other people prefer to use square brackets only when necessary.  Some people always choose names without spaces so they never have to use square brackets.    We can use whichever style we prefer.

 

See Also

Drawings

 

Tables

 

Queries

 

Selection

 

Add an Index to a Table

 

Select Pane

 

Style

 

JOIN Statements

 

Example: Spectacular Images and Data from Web Servers

 

Example: An Imageserver Tutorial

 

Example: Import DDF SDTS DEM Raster File

 

Example: Import DDF SDTS DLG Vector File