FWTools & MySQL leaves ArcGIS geoprocessor in the dust

I wanted to know what block groups where included in each of the Zillow neighborhoods. So I needed to:

• Merge all the Zillow neighborhoods into a single datasource
• Intersect the block group data source with the neighborhoods

I had just done a similar process for the block groups and all US Census “populated places”, so I knew that the spatial intersecting in the ArcGIS geoprocessor was going to take a bit of time. I already had a MySQL data source with the block groups, so I thought I’d do a comparison.

FWTools & the Geoprocessor merged all the shapefiles into a single shapefile in about the same amount of time. In case you don’t know, here is how you do that using the ogr2ogr utility & DOS/Windows BATCH syntax.

FOR %f IN (*.shp) DO ogr2ogr -update -append -f "ESRI Shapefile" zillow_hoods.shp %f -nln zillow_hoods

Now that I had a combined shapefile, I loaded into the MySQL database,  using the ogr2ogr command for MySQL. This was executed in less than a few seconds.

Finally, I wrote a SQL query to intersect the 2 datasources and create a new table with the results.

CREATE TABLE remap.bghoods SELECT b.bg_num,z.* FROM remap.bg_data b, remap.zhoods z WHERE INTERSECTS(b.SHAPE,z.SHAPE)

The final result

ArcGIS Geoprocessor : 1hr 2min

MySQL : 5min 29sec