Working on Tiger Line Geographic ESRI Shape Files with PHP and MySQL

I have an interest in D3 JavaScript package, and while reviewing it I saw maps created in JavaScript. I am new to GIS and did not want to steel anything. By stealing I mean latitiude and longitude, state id's and other map or chart related data. Not knowing what else to do I started to use google, and searched for mapping utilities. I got a bunch of C++ and Java map utilities, and while reviewing their pages I discovered ESRI shape files and a few other important GIS sites. These important sites are:

  1. United States Board on Geographic names
  2. The TIGER LINE Census HTML Page
  3. Geospatial Data Abstraction Library
  4. ESRI
  5. MySQL Spatial Data Types

I found some shape files on the US Census Bureaus website under the name TIGER/Line Shapefiles.

The TIGER/Line Shapefiles are the fully supported, core geographic product from the U.S. Census Bureau. They are extracts of selected geographic and cartographic information from the U.S. Census Bureau's Master Address File/Topologically Integrated Geographic Encoding and Referencing (MAF/TIGER) database. The shapefiles include information for the fifty states, the District of Columbia,
Puerto Rico, and the Island areas (American Samoa, the Commonwealth of the Northern Mariana Islands, Guam, and the United States Virgin Islands). The shapefiles include polygon boundaries of geographic areas and features, linear features including roads and hydrography, and point features. They do not contain any sensitive data.

I went ahead and downloaded the 2015 version. There are a lot of different ones, and I have not yet reviewed all of them or the TIGER/Line documentation. There are quite a lot of files and it may take you some time to download them.

A few months later I wanted to start working with state boundary latitude and longitude. A google search on how to get the boundaries into a database led me to a posting utilizing the Geospatial Data Abstraction Library (GDAL). So now I have discovered an application for translator library for raster and vector geospatial data formats which supports ESRI shape files. That is the file format of the TIGER/Line files.

The commands allowed me to write a PHP script which would extract the data into text files and insert it into a MySQL database of my own design. I have now been able to write a program to insert the data into a MySQL database version 5.6 using Gentoo Linux. The table storage engine is InnoDB, but I may change it to MyISAM due to lack of index support on InnoDB tables [Creating Spatial Indexes].

As a result, I have not added indexes into my database tables.

The code is located here: Geography Mapping. It should work fine when inserting the data into the database structure provided in file kml_gis.sql.gz.

To run the script you need to checkout my command line library and the common library. Both of these are written in PHP.

Also required are the TIGER/Line STATE files. They are located here and provide the boundaries for each US state.

An example command processing the TIGER/Line state file is:
php insertMysql.php --user=gis_user --pass=pass12345 --file=./TIGER2015/STATE/lower48/tl_2015_us_state.shp

Next I hope to work on the MySQL spatial indexes and try the gdal package feature of export to SVG. I hope that the future work goes smoothly. It would be nice to integrate D3 JavaScript package into my project, but I am not sure what will occur in the future.