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.

The FREE Rosetta Stone of GIS Data Formats

Could DNR Garmin be the Rosetta Stone of GIS Data?

It will now read from and/or save to the following file formats:

• Shapefiles
• ESRI File-based Geodatabase (9.2 only & only if you have it installed on your system. It apparently uses ArcObjects)
• KML
• GPX
• Text files (comma or semi-colon delimited)
• DBF

 

The Save As file options (I don’t have ArcGIS 9.2 installed on this computer so the Geodatabase option doesn’t show up).

In addition to saving as & reading from files, it will also read from & save directly into ArcMap or Google Earth.

A new version (5.2 beta) was released on March 12, 2007, with several new & important features. This program was already on my “Must Have” list for anyone working with GPS units. It is invaluble for the field work we do at Plateau Land & Wildlife.

Another great feature that it has but only some of us can use is the Image Hotlink/Geocode feature. This works essentially the same as GPicSync (a Google Code project). But DNR Garmin has been doing it for several years now.

This feature works based on the time stamps of your waypoints or tracks and the time stamp of your photos. It can automatically calculate an offset if your clocks aren’t syncronized.

Notice the “Project and Unproject Coordinates” menu items above. The software uses the Proj.4 projection libary to allow reprojection into a vast array of coordinate systems & datums very rapidly.

I’m fairly certian that you could use the libaries used by this program to make a utility that worked with any and all attributes included in your data rather than just the GPS centric fields that this works with by default. To be fair, though if you have some standard attributes, you can cutomize the fields for waypoints, tracks, routes, and real-time data to include whatever fields you want.
Update - Price for all of this : Free

I’m sure you can think of plenty of innovative ways to use this software but let me share an example with you.

As I’ve posted about before we do a lot of bird surveys in the spring. Many of these are done by sub-contractors who don’t have access to a GIS system. Let’s say we have a new client for whom we have never done a survey before. We send the sub-contractor a shapefile of the client property. She then uses DNR Garmin to convert that shapefile directly into KML & into her Google Earth My Places file. She opens up GE & there the property is with aerial photo, roads, & elevation. She adds placemarks for each of the proposed survey stations which she picks based on the info visible in GE. If she wants to colaborate with the biologist familar with the property they can easily share the info to each other. Then she opens DNR Garmin, loads those placemarks directly from GE to her GPS unit. Gets driving directions from her house to the client property through GE and goes and does the survey. If we wanted her to, she could send us back a shapefile of the census stations by saving the actual station locations from her GPS unit into a shapefile via DNR Garmin.