Working on Tiger Line Geographic ESRI Shape Files with PHP and MySQL
- United States Board on Geographic names
- The TIGER LINE Census HTML Page
- Geospatial Data Abstraction Library
- 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.
—The TIGER LINE Census HTML Page
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.
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