Msql Database Search Help Page

This html and associated cgi program are trying to demonstate using a relational database to search on key FGDC metadata fields. This is part of the NSDI program.

I am using miniSQL, a rdbms that I found on the net. It is designed to connect to databases over the net. For info on miniSQL try

miniSQL has a limited subset of operators, including < > <= = >= like.

'Like' only works on character searchs. It has been modified to always do a case-INsensitive substring search. DO NOT enclose the string in single quotes or in percent signs (% %).

The regular expression syntax supported by LIKE clauses is that of standard SQL:

So, to search for the state of WA (Washington) in the Place_Keywords field, you would enter WA, the query could look like this:

Also note that the '(' is special in the regular expression routines which are invoked via the use of 'LIKE' instead of '='

The following is valid msql (am unsure whether the same is true for ANSI SQL): I'm not sure this still works. 

      SELECT * FROM metadata WHERE Theme_Keyword 
               LIKE 'LULC (Hydro%|Cens%|Poli%)'

This will return all rows having the Theme_Keywords:

 LULC Hydrologic Units                    
 LULC Census County Subdivisions          
 LULC Political Units                     

This avoids needing to have a great number of OR clauses.

The search IS case INsensitive. DEM does equal Dem. 

To select values that contain reserved characters such as the ' you
must 'escape' the character with a \     Try

   SELECT * FROM metadata WHERE Title LIKE \'

This should give you COEUR D'ALENE 

The = operator requires that the data value be in single quotes. It also 
requires trail blank spaces be included if the data was entered with
training blank spaces. eg 'Tacoma           '
The fields can be linked with either AND or OR. This is done by clicking on the Connector button. The default is AND.

There are at present (May 7,1995) about 800 records in the database. You can see them all by summiting a query with all blank values. Most states are represented by their 2 CAPITAL letter standard abbreviation in the Place_Keyword field. There are 2 data type in the Theme_Keyword field. They are LULC and DEM. I expect to add some DLG records soon. The coordinate fields are in lat/long decimal degree format. All longitudes are negative. The only scale is 100000. 


The following are all the unique values in the Theme_Keyword field.

 LULC Composite Theme Grid                
 LULC Hydrologic Units                    
 LULC Census County Subdivisions          
 LULC Political Units                     
 LULC Land Use & Land Cover               
 DEM                                      

The Place_Keyword field can have up to 4 adjacent states, eg.

 DE NJ PA MD
 
Note that they are NOT in alphabetical order.
The HTML uses a different approach from most forms based query. It allows the user quite a bit of latitude to create his or her own query. Although I set up some default fields and operator, all of them can be changed to 'customize' the query.

Other versions of database search htmls I've worked on are at

I wrote the CGI program msql_cgi program in C. It is designed to allow users of the net to quickly create HTML interfaces to the miniSQL (and other) rdbms. The C program takes all the needed information from the form, so it does not need to be customized for each application. You might think of it as supporting object oriented HTMLs.

Some of the data sets are on-line for immediate transfer. The USGS Clearinghouse address is:

The data was taken from some USGS digital map indices that I found on the net at

Other related efforts are described in

For more information, contact Sol Katz, skatz@blm.gov, 303-236-0101