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.

Comparing Mashup Platforms Using JSON & MySQL - Part 2 - Process JSON

Technologies Used: PHP 5, MySQL 4.1.2, JavaScript, JSON

Software Used: PHP Designer, Aptana, Firebug, Firefox

There are a wide variety of ways to handle JSON that is returned from a server. One of these methods is to provide a callback function pass it to the JSON emiting webservice via REST. When used with static or dynamic script tags, you are no longer restricted to Cross Site Scripting (XSS) limits requireing that the source be on the same server as your webpage. The XSS limit seems particularly ill-suited for mash-ups. The callback function method is what I have choosen for my examples. You will also see a static script tag for these examples. That is to simplify them and make them easier to read. The general preference is to use dynamic script tags rather than static ones.

Actual JSON string returned:

handleJSON({”Observations”:[{"Station":{"number":"1","lat":"29.5762","lon":"-98.7041","total_sp":"9","Species":[{"common":"Mourning Dove","number":"1","code":"MODO","scientific":"Zenaida macroura"},{"common":"Eastern Phoebe","number":"1","code":"EAPH","scientific":"Sayornis phoebe"},{"common":"Carolina Chickadee","number":"1","code":"CACH","scientific":"Poecile carolinensis"},{"common":"Black-crested Titmouse","number":"3","code":"BCTI","scientific":"Baeolophus atricristatus"},{"common":"Carolina Wren","number":"1","code":"CARW","scientific":"Thryothorus ludovicianus"},{"common":"Bewick's Wren","number":"2","code":"BEWR","scientific":"Thryomanes bewickii"},{"common":"Rufous-crowned Sparrow","number":"1","code":"RCSP","scientific":"Aimophila ruficeps"},{"common":"Northern Cardinal","number":"3","code":"NOCA","scientific":"Cardinalis cardinalis"},{"common":"Brown-headed Cowbird","number":"1","code":"BHCO","scientific":"Molothrus ater"}]}},{”Station”:{”number”:”2″,”lat”:”29.574″,”lon”:”-98.7036″,”total_sp”:”5″,”Species”:[{"common":"Black-crested Titmouse","number":"1","code":"BCTI","scientific":"Baeolophus atricristatus"},{"common":"Carolina Wren","number":"1","code":"CARW","scientific":"Thryothorus ludovicianus"},{"common":"Ruby-crowned Kinglet","number":"1","code":"RCKI","scientific":"Regulus calendula"},{"common":"Northern Cardinal","number":"1","code":"NOCA","scientific":"Cardinalis cardinalis"},{"common":"Brown-headed Cowbird","number":"1","code":"BHCO","scientific":"Molothrus ater"}]}}]});

It is not very human readable, but it is highly machine readable.

Below is an extremely simple example of handling JSON and doing something with it.

<html>
<head>
<meta http-equiv=“Content-Type” content=“text/html; charset=iso-8859-1″ />
<title>Simple JSON Handling</title>
<script type=”text/javascript” charset=”utf-8″>
function showCoords(jsd){
var lat = jsd.Observations[0].Station.lat;
var lon = jsd.Observations[0].Station.lon;
var msg = ‘Lat:’ + lat + ‘, Lon:’ + lon;
alert(msg);
}
</script>
</head>
<body>
<script src=http://www.plateauwildlife.com/bbc-mgmt/getstations.php?action=getdata&cid=2&year=2006&func=showCoords type=”text/javascript” charset=”utf-8″></script>
</body>
</html>

See It In Action

The above example is not particulalry usefull for anything other than demostration purposes. We want to actually DO something with our JSON to move us closer to creating the actual mashup. The number thing which I intially strugled with when using JSON & callback functions was that you MUST define the callback function BEFORE your dynamic or static script tag.

Google Maps, Google Earth, Yahoo Maps, and Virtual Earth all take HTML for the contents of the info window when you rollover or click on a point.

ArcWeb Explorer (AWX), however doesn’t take HTML as info window content. AWX does take styled text, videos, picture, audio, & swf for info window content. To this end, if you want to embed rich non-HTML content, AWX allows for some extremelly interesting content to be blended together and presented with great ease. The documentation for text styling is lacking, so creating simple content is actually more dificult in this platform than the others.

I’ve created a javascript file which we can reference in any of the HTML docs that actually embed the mashup.

This file contains

1. The main callback function
2. A function which builds an array of HTML tables containing the formated results from each station
3. A function which builds an array of jscript strings containg the weakly formated results from each station

See The HTML Builder In Action

    //A global variable to assign the parsed JSON to

var jsobj;

 

//Main Callback handler.

//simple assignment to a global variable allows me to reuse and pass

//around the object without any server trips

function handleJSON(reply){

    jsobj=reply;

}

 

    function buildHTML(Observations){

    var info_win = new Array();

    // Build a table element with Station number & total species observed

    for(var i=0;i<Observations.length;i++){

        str = ‘<table border=”1″><tbody><tr class=”station”>’;

        str += ‘<td colspan=”2″>Station ‘+ Observations[i].Station.number + ‘</td></tr>’;

        str += ‘<tr class=”sta_total”><td colspan=”2″>Total Species - ‘ + Observations[i].Station.total_sp + ‘</td></tr>’;

       str += ‘<tr class=”obs_header”><td>Species</td><td>Number</td></tr>’;

        var details = “”;

        var arr = new Array();

        // assign each Species array to a local variable to reduce typing & increase readibility

        //build an string of <tr> elements containing the details of species observed

        arr = Observations[i].Station.Species;

            for (var y=0;y<arr.length;y++){

            details+=‘<tr class=”obs_detail”><td><a href=”http://www.google.com/search?q=%22′;

            details += arr[y].scientific.replace(/\s/,“+”);

            details += ‘%22″>’ + arr[y].common + ‘ (’ + arr[y].code + ‘)</a></td>’;

            details += ‘<td>’ + arr[y].number + ‘</td></tr>’;

            }

        str +=    details;

        str += ‘</table></tr></tbody></table>’;

        //add table element to array of table element html strings

        info_win[i]=str;

    }

return info_win;

}

 

function buildAWXtxt(Observations){

        var info_win = new Array();

    // Build a formated text list for each Station number & total species observed

    for(var i=0;i<Observations.length;i++){

        str = ‘Station ‘ + Observations[i].Station.number + ‘\n’;

        str += ‘Total Species - ‘ + Observations[i].Station.total_sp + ‘\n’;

       str += ‘Species          Number’;

        var details = new Array();

        // assign each Species array to a local variable to reduce typing & increase readibility

        //build an array formatted text data elements containing the details of species observed

        //using this convention, we can assign a url property to each species line

        //through .data{elements[]} in the properties for each marker

        arr = Observations[i].Station.Species;

            for (var y=0;y<arr.length;y++){

            details[y] = arr[y].common + ‘ (’ + arr[y].code + ‘) - ‘ + arr[y].number + ‘\n’;

            }

        var obs_info = new Array([str,details]);

        //

        //add table element to array of table element html strings

        info_win[i]=obs_info;

    }

return info_win;

}

 

Previous Parts

1. Emit JSON

 

Next Parts

3. Arcweb Explorer Mashup

4. Yahoo Maps Mashup

5. Virtual Earth Mashup

6. Google Maps Mashup