The “DATA”

“The wonderful thing about standards is that there are so many of them to choose from.” – Grace Hopper

A Shape file is the most common format for GIS vector data and just about every GIS program can use them.  Unfortunately not all GIS data come in a shape file format. An E00 (E-zero-zero) file is the file format used by Environment Systems Research Institute’s (ESRI). ESRI is the Photoshop of the GIS workplace. E00 files are used by ArcInfo and ArcGIS Explorer.  These are the two most common file formats. Spatial Data Transfer Standard (SDTS) is used by the US government to transfer data between dissimilar computer systems and GPS (.gpx) data used to transfer data to and from a GPS device.

Here is a list of the vector and raster file formats I have come across.

• .shp/.shx/.dbf Shapefile – vector data (All three files make up a set.)
• .E00 Proprietary ESRI files.
• .klm Keyhole markup language used by Google Earth (GE)
• .gpx/ .gps a common GPS data format for software applications
• .gml Geographic markup language
• .pdb PocketAPRS vector map
• .map APRSdos/WinAPRS/MacAPRS vector map
• .gnis GNIS labels file (actually points instead of vectors)
• .tif/.fgd geo TIFF raster image maps

Geospatial Data Abstraction Library (GDAL) is the work horse utility convert between these formats..  It is really a set of utilities and is the guts of  the MapServer. The program ogr2ogr is included in GDAL. This program is key to converting and getting data into MySQL.  The OGR toolkit is a sub kit of the FW Tools Toolkit. It has several command line tools. The two most useful are:

• ogrinfo – inspects a GIS data source and spits out summary data or detailed information about the layers, kinds of geometries found in the file.
• ogr2ogr – this is a command line tool that converts data. Ogr supports many formats: Shapefile, MapInfo Tab file, TIGER, s57, DGN, CSV, DBF, GML, KML, Interlis, SQLite, ODBC, ESRI GeoDatabase (MDB format), PostGIS/PostgreSQL, MySQL

Like the unix ‘file’ command, these utilities can recognize the type of data you feed them. One odd thing about ogr2ogr is, its command options are in output file then the input file order.

Here are a couple of useful examples from a cheat sheet on these utilities.

# Again this is based on Redhat 5.5
 yum install gdal

 ogrinfo ../data/Oklahoma/oklahoma_poi.shp
 INFO: Open of `../data/Oklahoma/oklahoma_poi.shp'
 using driver `ESRI Shapefile' successful.
 1: oklahoma_poi (Point)

 wget http://mappinghacks.com/data/TM_WORLD_BORDERS-0.2.zip
 mkdir boundaries_shp
 unzip TM_WORLD_BORDERS-0.2.zip -d boundaries_shp

 cd boundaries_shp
 ogr2ogr -f "MySQL" MySQL:"geo,user=root,host=localhost,password=" -nln world_borders -lco engine=MYISAM TM_WORLD_BORDERS-0.2.shp

 mysql geo -e 'select iso3,astext(SHAPE) from world_borders where iso2="VA";'
 +------+--------------------------------------------------------------------+
 | iso3 | astext(SHAPE)                                                      |
 +------+--------------------------------------------------------------------+
 | VAT  | POLYGON((12.445 41.903,12.451 41.907,12.456 41.901,12.445 41.903)) |
 +------+--------------------------------------------------------------------+

In part one I said “GIS / Mapping Systems work with both text data and graphical data.” There are even more raster graphical file formats. GDAL has a library for raster GEO data types with a very large list of file formats it supports.  Besides converting format types, GDAL can also warp, stitch and re-project images to fit a give projection.  These blog posts are about MySQL so I will leave the conversion of graphical data to someone else.

In a coming posts I’ll go over:

• What data is available and where can you find it?
• More examples on what you can do with GIS data and MySQL.
• Viewing our GIS data.
• How to collect your own GIS data.
• Good and bad examples of searching GIS data.
• Optimizing MySQL GIS.  Is it really worth using?

Tweet