Review Info

 

Common File Types:

 
Text files
  • *.txt - may be tab delimited
     
  •  *.csv – comma delimited
     
  • *.tab – tab delimited
     
  • *.asc – ASCII file typically used to store raster data in text format
     

Excel Files

  • *.xls – Office 2003 or older Excel file
     
  •  *.xlsx – Default Office 2007 or newer Excel file
     
  • *.xlsm – Macro Enabled (has some custom code within it) Office 2007 or new Excel File
     

dBase Files

  •  *.dbf – ‘old-school’ database program file, currently only used to store the attributes within a shapefile. Can be opened using Excel or ArcGIS.

Access Database

  • *.mdb – Office 2003 or older.
    • Must zip it to email to someone or it will get blocked. Use this freeware to zip files http://www.7-zip.org/
    • Even if you have Office 2007 or newer, if you create a Personal GeoDatabase using ArcGIS, the file extension will still be *.mdb
       
  • *.accdb – Office 2007 or newer database file
    • Probably should zip this file type also
       

ArcGIS

  • *.mxd (a map document that stores the path names to your spatial data)
     
  • *.gdb – a file geodatabase. Works similar to a personal geodatabase, but this format is proprietary to ESRI products and cannot be read by other software (yet)
     
  • Raster datasets – rows and columns in which each ‘cell’ contains a value. The rows and columns will have a cell size (i.e. 30m x 30m) describing the raster resolution:
    • *.img (ERDAS IMAGINE)
    • *.tif (usually called a geo-TIFF)
    • *.asc (ASCII raster. First 6 lines describe the size and location of raster data. All of the rest is a matrix of cell values)
    • There are also a lot of other formats that require a little research in how to parse/open them.
       
  • Vector datasets:
    • Shapefiles – they require a minimum of 4 different files for them to work within ArcGIS (*.shp, *.shx, *.dbf, *.prj)….the optional files can be (*. sbn, *.sbx, *.shp.xml and some others)
    • Personal geodatabase is just a Microsoft Access (*.mdb) database that contains a data-model able to store and display spatial data within ArcGIS.
      • This is VERY POWERFUL, for if you know how to perform advanced queries within Access/Program R, coupled with the advanced Spatial analysis ArcGIS software…the sky is the limit for providing efficient and accurate analyses.
    • SDE/SQL Database can also store vector-based spatial information within the geometry/geography data types.

Program R

  • *.RData or *.rda (stored images of the R workspace)
    • In the R interface use the ls() command to view all of the R objects stored within the R workspace/data file.
       
  • *.r (a script file containing R functions/commands)
    • I like using R-Studio to build R scripts/functions because I can debug the code line-by-line where as it is much more difficult with other editors. This is by far the best IDE (Integrated Development Environment) available
 

Common Data Types:

 

***** For a complete listing of SQL Server, MySQL and Access data types, this seems to be a good source:

www.w3schools.com/sql/sql_datatypes.asp

 Text or String

  • Typically limited to 255 total characters (includes spaces).
  • Recall that a cell within Excel can have as long of a string as you want, but if you import into Access, the field will be cut-off if you specify a type of Text.
  • nvarchar in SQL stores up to 4000 characters...
     

Numeric

  • Integer – can hold a whole number between -32,768 and 32,767
     
  • Long Integer – can hold a whole number between -2,147,483,648  and 2,147,483,647
     
  • Double – also known as float, use this if you need to store decimal values

Date/Time

  • Can store dates and times separately or concurrently
    • Recall the ‘Date System Issue’ To check on your worksheet and see which date system is being used (you want Excel to use the 1900 data system)
      • Click on the File tab -> Options -> Advanced -> then scroll down until you reach the ‘When calculating this workbook’. Make sure the ‘Use 1904 date system’ is not checked. If it is checked, 4 years will be added to any date value in your current workbook. Vice versa if you uncheck the box (4 years will be subtracted).
         

Memo (not a common field)

  • Use this to store long sentences. Can hold up to 65,000 characters  (within Access)