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

Creating ArcGIS Image Catalog From USB Stick

I like using ArcGIS Table Based Image Catalogs when working with large numbers of rasters in ArcMap. The old style table based Image Catalogs work for clients that still have ArcGIS 8.x or even ArcView 3.x. While a TileCache/Mapserver WMS image server is faster, it is sometimes not a real option for certain clients & situations.

However, lately I’ve been running into a number of situations in which the Image Catalog creator scripts that I’ve always used : USGS Image Catalog Builder or This One aren’t working. On Vista, the user extensions won’t even register and thus are of no use. I’ve also had troubles with large numbers of the newest MrSID type imagery giving "Automation Error" errors & the script stops working.

So I needed another way to create image catalogs that was easy and wouldn’t require me installing additional software on clients’ computers, etc.. While, I could have used Python or some other script, I wanted something that people without any scripting or coding experience could also do.

Software Used:

• FWTools (with a few environment variables modified in the startup batch file so that it would run properly from a stick)
• PSPad

Steps:

1. Open FWTools Command Line
2. Run gdaltindex on the folder containing the imagery.

   gdaltindex naip_05_index.shp 2005_NAIP_NC\*.sid

3. Use ogr2ogr to create a BNA format file from the tile index shapefile created in the previous step.

   ogr2ogr -f BNA naip05index.txt naip_05_index.shp

   This gives you a file with this sample output:

   "2005_NAIP_NC\e2997_01_1.sid","",5
   
   596140.0000000000,3319570.0000000000

   602854.0000000000,3319570.0000000000

   602854.0000000000,3311970.0000000000

   596140.0000000000,3311970.0000000000

   596140.0000000000,3319570.0000000000

4. Open the file with PSPad
5. Record a macro in PSPad that  flattens this to a single line.
6. Go to Tools->Macros->Marco Manager and select "Run X Times" and have it run for as many files as you created the tile index from.
7. Final step is to label the columns correctly
   • You need to add IMAGE, XMIN, YMIN, XMAX, & YMAX column headers
   • The 2nd coordinate group is the XMAX/YMAX group & the 2nd to last (4th) coordinate group is the XMIN/YMIN group.
   • You can call the other columns whatever you want, they will be ignored. However you MUST label them or delete them. I use:

     IMAGE,A,B,C,D,XMAX,YMAX,E,F,XMIN,YMIN,G,H

THAT’S IT!

Now you have a CSV based text file table that you can use as an Image Catalog.

The only additional step you may need to take is to convert it to a DBF file to work properly on older versions of ArcGIS or ArcView. This can easily be accomplished in the ArcCatalog, ArcToolbox or by loading the table into a map and exporting it to a DBF. Alternatively you can just use Excel, Open Office, or DBF Explorer to convert it as well.

It actually took me much longer to write this post then it did for me to create several image catalogs this way.

Part Timer Goes Full

Three months ago, in October, I quit my regular salaried job and became a full-time freelance developer. My son was born in July and I took significant time off of work. During that time I started aggressively pursuing work for Kestrel Computer. I made a rookie mistake of worrying about not having a regular paycheck and said yes to every project I could. I ended up working much longer hours and harder than I had ever worked for my previous employer with just barely more pay. The reduced pay was due in large part to me GROSSLY under estimating my time to complete projects. When you are used to stealing time in evenings & weekends to do freelance work and then see entire weeks of possible work time in front of you, I think it is natural to assume you could do exponentially more work that ever before.

However, even with the large amount of family & interaction with my newborn son that I missed out on, I knew I would rather be doing full time custom development work than working for my previous employer.

I had always bristled at any sales targets, quotas, or billable work targets in previous jobs. I am not primarily motivated by money and such things generally struck me as something “dirty” & un-pure. I am mostly motivated by the challenge of solving problems and by food . However, now there is a direct correlation between my billable hours and food on our table. Suddenly tracking, targeting, and making goals for billable hours/work no longer seems cheap & tawdry but rather vital & important. To ensure that I am accountable, my wife is helping set my billable work goals & tracking them. To motivate us, if I hit my billable work goals every week in the month, we get a date night. With a 6 month old & a 4 year old, that’s pretty good motivation.

I’ve promised myself that in the new year I would also be contributing more regularly (at least once a week) with my blog. The future post will be more technical than personal. I’ve gotten the opportunity to work on some interesting things and would like to share them with the community.