GeoTongue

The firehose of information known as the FOSS4G conference is over. I had a great time. The best part was just getting to meet and trade ideas with so many people that I only knew via twitter or email lists. 

Highlights of the conference:

• Social events (official and un-official)
  Shaking someone's hand, sharing a drink or meal with them, and actually talking to them in person is an invaluable experience. Especially, when you have only known of the person via thier 60x60 profile pic and what they write in 140 characters or less.
• PostGIS 2.0
  Steven Citron-Pousty (@theSteve0) and I were excited by nearly every slide in the PostGIS 2.0 The New Stuff presentation. With 2.0, you can now do nearly every task from the combined Network Analyst, Spatial Analyst, Raster Analyst, and ArcInfo level ArcGIS directly in the PostGIS database. Mark my words. The user-facing tools that allow people to directly harness the power of PostGIS 2.0 without having to know and fully understand spatial sql / pgscripting will forever change the Desktop/Professional GIS world.
• GeoScript
  Even if you have never had any interest or knowledge of GeoTools and aren't a huge fan of GeoServer, you are going to like this. If you are already a proponent or user of those programs, then you will LOVE GeoScript. Do you want a new WFS output format, a new data source type or a new WPS process but don't have a Java developer with GeoTools/GeoServer experience? No problem, write a few lines of your favorite scripting language (Python and its various flavors, Javascript via Rhino, Groovy, or Scala), name the file according to some very reasonable naming conventions, drop it in the correct place and you just built a library/server extension. How about building a complex rule based, scale based gradient style with labels? Have you tried doing that in SLD? It's not much fun. However, they did something like that live, on-stage using the Javascript version of GeoScript an a Javascript object literal with less than 10 properties to represent the style. If you want to get the raw SLD back that the scripting process generated, that is available too. The ability of GeoScript to load shapefiles or configure data sources with an AMAZINGLY simple API was also very impressive. You could use GeoScript directly from the command line for simple cases; create one off scripts for loading and configuring multiple data sets or project specific resources; or create standard processes or user-facing applications. To say I was impressed would be an understatement.
• Mapserver 6
  Mapserver has always prided itself on being strong and fast. However, with limited output support, lack of WFS-T, and having to use the very error prone map file format; I've steered clear of it in favor of Geoserver for several years. With the 6.0 release which includes support for OGR output and vast labeling improvements which don't require complex SLD, Mapserver started leveling the playing field. There is also (and appearently has been for a little while) support for authoring your mapfile in XML which Mapserver will convert to a regular mapfile. It actually writes the mapfile in place and uses that until the XML file changes, so you only get a one-time conversion overhead with the benefit of writing your config file in a file format nearly all programmer focused text editors and numerous IDEs can validate. Additionally, Mapserver Suite will be released soon which bundles TinyOWS WFS-T and MapCache for a map serving package which rivals GeoServer. The WMS Shootout results also showed that until the known JAVA image generation bug is fixed or worked around, Mapserver can scale better without requiring multiple instances or cores as the JAVA based map servers (GeoServer and Constellation) do.
• Landspeed.js
  This is the node.js based WMS server for Mapnik that they used for the WMS Shootout. It currently only supports simple GetMap requests without any filtering support. However, it is FAST and scales well. If you don't need a fully OGC compliant WMS server, but are required to use WMS and need both scale and speed, then Lanspeed.js is a very serious option.
• OpenLayers Code Sprint
  Before the conference, there was very little talk of any OpenLayers participation in the Code Sprint and Roald deWitt and I seemed to be the only ones interested in moving GeoExt to ExtJS4. That all changed once people actually started meeting up and talking to each other at the conference and even at the start of the Code Sprint the exact direction of the OpenLayers contigent that was there was somewhat up in the air. Eventually, Tim Schaub started captaining things and moving it forward. It was a fruitful sprint with the biggest accomplishment, thanks to Christopher Schmidt's numerous attempts and even reading the GitHub itself's source code, was moving OpenLayers from OSGeo hosted SVN to GitHub.
  •  Accomplishments:
    1.  OpenLayers using GitHub instead of SVN - SVN is a read only mirror. Many thanks again to Christopher Schmidt.
    2. Marc Jansen's test suite enchanements
    3. Roald deWitt's halo labels and other style improvements
    4. Frédéric Junod & Eric Lemoine's tile loading and animated panning enhancements. 
  • Other work: 
    1. Eric Lemoine's investigations into a different non-browser dependant test suite for the vast majority of the tests as they are not browser dependant.
    2. Tim, Marc, and my investigations into new API Documentation generators and NaturalDocs to JSDoc conversion. That change is definitely forth-coming but will require an update to the NaturalDocs to JSDoc conversion script and some manual post-conversion updates for it to work correctly. Also, some documentation generator improvements may need to happen before the dual goal of having modern, sorted API docs with inherited memebers and JSDoc comments to enable proper Closure compiler optimization can take place.

I recently answered an email about the state of geospatial web apps for mobile devices and what his options were. Below are the questions and answers I gave. This is a space that is rapidly changing and further developments are constantly occuring. While this info might be current now (June 2011) it might be grossly out of date within a few months.

Is there any mobile platform that can display maps served from a web service, such as Geoserver ?

Short Answer: Yes, lots.

Long Answer:

OpenLayers 2.11

http://m.openlayers.org

OpenLayers hosts a mobile optimized minimal build and you can also make your own. A recent code sprint added significant support for touch events, the geolocation API and several mobile enabled controls. OpenLayers can be used within a mobile app framework or on its own. You can see the mobile examples at http://m.openlayers.org

 

Google Maps (Bing Maps also, I guess)

Google Maps has a mobile optimized javascript api for displaying Google Maps background and interactive data layers from your own servers or theirs.

This is actually a really good option that works for a wide variety of platforms. However, if it is used in a private or commercial site (ie requires payment, logon or used only behind corporate firewall) then you have to pay between $5,000 - 10,000 USD/year

 

Update: Added Cloudmade's Leaflet (June 20, 2011)

Leaflet

http://leaflet.cloudmade.com

A javascript mapping library that focuses on "A" grade browsers (desktop & mobile). An alternative to OpenLayers. This is newer than the next 2 javascript mapping libraries, but is being developed by Cloudmade and should have good support and forward momentum. Supports regular tiled base maps, WMS base maps, GeoJSON vector data and static image overlays. If you must have WMS base map support then this is probably the most viable OpenLayers alternative.

Tile5

http://www.tile5.org/

A javascript mapping library that uses HTML5 technologies and doesn't attempt to support older browsers. It is designed with a mobile-first focus and integrates well with JQuery Mobile and Sencha Touch, but can also be used well on its own. It is an alternative to OpenLayers and uses a completely different code base. It supports rendering tiled map backgrounds along with vector data in GeoJSON format. The library also includes a few map controls as well.

 

Polymaps

http://polymaps.org

Polymaps is similar to Tile5 but has more intentional support for desktop and uses SVG as the primary rendering method rather than Canvas as Tile5 does. In other words, this is another alternative to OpenLayers and can also be displayed by itself or integrated into a mobile app framework like JQueryMobile or Sencha Touch. OpenLayers, Tile5 and Polymaps all use very different javascript code bases and can't broadly share any code.

GeoExt

http://geoext.org/

There is an active project supported by the Swiss government to create a Sencha Touch version of GeoExt that includes the most important part of that framework written for Sencha's mobile framework which is not directly compatible with ExtJS 3 but shares common elements with the newly released ExtJS 4. The general consensus is that GeoExt 2 will be based on Sencha Touch & Sencha ExtJS 4 and thus share a common code base for mobile and desktop apps. The initial Sencha Touch based version of Mobile GeoExt should be released, tested and ready for use in the late summer.

ExtJS 3 & GeoExt 1 don't work particularly well on mobile devices.

 

Does JQuery Mobile also support a host of different phones (perhaps through phonegap) ?

Yes, JQuery Mobile is fully compatible with PhoneGap and you can generate iPhone, Blackberry, Android, Palm, and Symbian native apps (support for Windows Mobile, MeeGo & Bada apps is planned in the near future)

  

Can we capture photo’s, video and link it to the geo-locater and compass.

You can use the geolocation and orientation features in a mobile browser app. You can have people upload or link pictures/video already on the phone (you can, with some extra permission dialogs, get the camera app to open from a web app, if you really want to). However to truly interact live with a phone's camera you must create a native app

There are numerous use cases in which you might need to place markers on a map and then update their position & direction at regular intervals. OpenLayers provides good methods for doing this. I needed to track vehicle movements received at regular intervals via GeoJSON, but for some reason things were not quite behaving as I expected. So I searched the OpenLayers mailing lists to find an example of someone else using an external graphic image and automatic style based rotation.

I found this post (http://n2.nabble.com/externalGraphic-rotation-in-a-vector-layer-td1828899.html) linked to this example (http://www.otg-nc.com/csc/index1.html).

However, that example uses techniques & conventions from somewhere between OpenLayers 2.6 & 2.7. It also merely removes the marker & then adds it back in the new position. That technique is TERRIBLY leaky and quickly eats up system memory. It also doesn’t work in IE 7.

I updated the samples to OpenLayers 2.8 techniques & conventions & changed it so that it works in at least IE7, FF3, & Chrome 2.

Examples:

1. Move Vector Markers with OpenLayers.Feature.Vector.move method

   No memory leaks in IE7, slight memory leaks (4 - 60kb per update interval) in FF3 & Chrome 2

2. Move Vector Markers by destroying then adding back in new position

   Big memory leaks in IE7, slightly smaller memory leaks in FF3 & Chrome 2. Leaks between 250kb - 1.25Mb per update interval depending on browser

3. Move Vector Makers with move method, Force HTML5 Canvas vector rendering

   This example doesn't really work right & looks much worse than the first example. Not sure if it is my code or OpenLayers's HTML5 Canvas Renderer

Conclusions:

• The upper limit on my computer for IE7 was 100 features updated every 1 second, or 200 features updated every 5 seconds. Other browsers handled more features. If you actually needed to show that many objects updating that quickly, then clustering or using a WMS would probably be required.
• It would be interesting to see what happens when you use the ‘old school’ OpenLayers.Layer.Markers layer which uses simple <img> tags. You lose the cool automatic StyleMap & Select Control stuff, but could replace that functionality in the code fairly easily. You also lose the new Protocol & Strategies functionality of vector feature layers. This is more difficult to replace in code and much more convenient for real world applications
• I know the markers’ angle & actual movement direction don’t always match up. I could fix it but I’m being lazy, you still get the idea of what is happening and how to do it.

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.

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.

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

I needed a way to run SQL queries on structured text files & Excel files.

Download links available at end of post

For the last few months, I have been toiling away in the highly arcane world of fixed width text files. Very large ones at that (250-500mb). These come from text file data dumps of appraisal district databases. I need only a subset of these records. From there they go into Excel where they are cleaned up on a nearly line by line basis that can't be handled consistently by RegEx since data is recorded so differently by each county. From there I want to summarize that data on a single household basis. I could go in Excel & do:

1. Advanced filter, Unique Values (based on a unique household identifier)
2. Use vlookup & sumif functions to copy and/or sum the data for each column

However, this is rather time consuming since different data is provided by in different order and level of detail for each county. Also, for rolls with more than 25k records and over 100 fields these Excel formulas can take ~30min to complete. You then need to copy & paste values or you'll have to wait a long time anytime a recalc is performed. Needed a programmatic way to deal with this data and to minimize the human time to just records which required actual human eyes to modify.

Admittedly, creating an OleDb connection, command, reader & even datatable using ADO.Net methods is a pretty simple thing.

Dim connect As New OleDb.OleDbConnection
Dim strConnect As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
connect.ConnectionString = strConnect
Dim cmd As New OleDb.OleDbCommand("SELECT * FROM [Sheet1$] WHERE City='Austin' AND LandValue > 30000", connect)
Dim reader As OleDb.OleDbDataReader = cmd.ExecuteReader()
Dim dt As New DataTable
dt.Load(reader)

But I wanted something even simpler & significantly more flexible than that. I wanted an object that could handle any potential Jet datasource, field list, target table, and SQL Select statement. I wanted something that could be reused in a variety of different applications and not require any hard coding of these parameters or make me implement different ways to do this in each application. I needed a good object oriented design with high reusability. To this end I created the JetDbConnect.dll

Structure

Properties

• Closed - OleDbConnection closed or open
• ConnectionString (ReadOnly)
• DataSource - the file path to your Jet compatible datasource. The ONLY property you must set either by assignment or through instantiation.
• DbConnection (ReadOnly)
• ExtendedProperties - if you need to use a Jet datasource that is not directly supported, here is where you would give the connection string parameters that would allow you to use it
• StorageMethod - one of the 4 enums of the file types this handles directly (Text, Excel, Access, Dbase). Should never need to be set unless you have a really messed up filename.
• TablesList - List of Strings of available tables in the datasource
• TargetTable - The table from which to query data, only needed in Excel & Access. If not given for those then it picks 1st table on table list
• TextFormat - enum: FixedWidth or Delimited. If not set assumes delimited with default delimiter set in your registry settings

Methods

• New
  • () - Default
  • (DataSource as string, TextFormat Enum)
    • This is used ONLY with text-based datasources
    • DataSource = path to datasource
    • TextFormat Enum is one of the 2 members of this Enum
  • (DataSource as string, [TargetTable] as string)
    • This can be used with any datasource type.
    • If you use it with a text-based datasource, and it is not delimited with your default delimiter, then you will also have to set the TextFormat enum elsewhere BEFORE using one of the Command,Reader, or Records methods
• Close() - manually close the connection. Not required but can be useful
• CommandGetAll, ReaderGetAll, RecordsGetAll ([DataSource] as string, [TargetTable] as string)
  • All 3 methods take the same parameters, an optional datasource path and an optional target table name
  • These methods allow you to easily retrieve an OleDbCommand, OleDbReader, or DataTable containing all the records in your target table of your datasource
  • If you don't set DataSource here and it has not been set elsewhere, this method will fail
  • If you don't set TargetTable here and it is an Access or Excel datasource and TargetTable is not set elsewhere, then "Sheet1" or the first member of TablesList will be used for Excel or Access datasources respectively
• CommandGetSql, ReaderGetSql, RecordsGetSql (sqlWhere as string, [fields] as string, [DataSource] as string, [TargetTable] as string)
  • All 3 methods take the same parameters:
    • Required: sqlWhere string, ex. "zipcode='78258' AND land_value>10000"
    • Optional: fields. A comma delimited list of fields to return with the query. ex. "owner_name, zipcode, land_value"
    • Optional: DataSource & TargetTable - see above for how the default values are handled for these 2 parameters
  • These methods allow you to easily retrieve an OleDbCommand, OleDbReader, or DataTable containing just the records in your target table of your datasource which meet your SQL criteria and optionally to return only the specified fields
  • If you want ALL the fields, pass Nothing or Void for fields. An empty string will cause an error.
• SchemaTable
  • Returns a Schema DataTable for the DataSource not a TargetTable.
  • If you want a Schema DataTable for a table then get it from an OleDbReader on that table
• FromXml, ToXml
  • Provides methods for serializing & de-serializing the object.
  • Since only DataSource is required for text & Dbase files and DataSource & TargetTable for Access and Excel, I don't see these methods as being particularly useful. However, I always like to implement them just in case I need to persist an object or compare an object to a previous state.

Usage

Examples:

Get an OleDbReader for all records in an Excel file from a named sheet

dim jetdb as new JetDbConnect("C:\myfiles\ExcelFile.xls", "Hays County")

dim reader as OleDbReader = jetdb.ReaderGetAll()

-or-

dim jetdb as new JetDbConnect

dim reader as OleDbReader = jetdb.ReaderGetAll("C:\myfiles\ExcelFile.xls", "Hays County")

note: the class automatically adds the required decorations so that Hays County becomes [Hays County$] as required for SQL statements on an Excel datasource

Get a DataTable with select records & fields from delimited text file

dim jetdb as new JetDbConnect("C:\myfiles\some text file.txt")

dim dt as DataTable = jetdb.RecordsGetSql("zipcode='78258' AND land_value>10000","owner_name, zipcode, land_value")

note: I did not need to ever specify the TargetTable attribute since it is automatically set to "some text file.txt" and the Data Source is actually set to "C:\myfiles"

Get a list of available Sheets and Ranges from an Excel file without the need for any Office libaries or applications

dim jetdb as new JetDbConnect("C:\myfiles\ExcelFile.xls")

dim tables as List(of string) = jetdb.TablesList

Downloads

Just the DLL

The Visual Studio 2005 project

Day 3 Thurs Mar 22

I'm finally getting a chance to blog about this now. Steve, I, and nearly 2 dozen other attendees got totally screwed by the storms in Phoenix. However, at least we didn't have to sit on the tarmack for nearly 3 hours like the other group waiting at the same gate. Though, by the time I got home 25 hours after leaveing the conference, sleeping in the Phoenix airport and eating only pretzals and snack mix; that wait seemed pretty trivial. I hope everyone else also eventually got home safely.

General Thoughts

This was a short day. Only 2 sessions and then the lunch & closing session. It seemed that about 25-30% of the folks had already left and a number of the ESRI devs had left as well. The sessions seemed a bit less technical that those on Wensday.

Sessions Attended

Building & Using ArcGIS Server Map Caches (Best Practices)

The MapCache is a tile cache created by rasterization & tile cutting of the data you have presented in your MXD. The main take home points of this talk were:

• Understand what a tiling schema is 1st
• Design your tiling schema BEFORE trying to build on for production use
• Design a SINGLE schema that will serve the entire organization and all your data layers. In other words: Level 10 = the same scale for regardless of which layer you are building from; Tile sizes should be equal across various data sources; Use same origin point for all caches.
• Client can rapidly reproject data to any projection system so use one that makes the most sense for both your data and web services methods (ideally, Geographic).
• DO NOT use png24. PNG8 best if you need transparency, JPG best if you don't need transparencey.
• Smaller tile sizes (128x128) do not neccessarily result in faster performance over the web, while they take 5 times as long to produce & take up significantly more storage space than large ones (512x512). Test to make sure performance improvement is worth the extra effort. Use the largeest tiel size you can.
• Fuse as many layers together as possible to create your base map caches. Therefore instead of Intermitten Streams, Permanent Streams, Rivers, & Lakes all being separate caches, they would be a single Hydrography cache that would appear as a single layer in the map service.
• Each zoom level should be cartographically distinct
• Forcing an ExportMapImage call from a web service can be a big performance hit. Understand what would make a client do that and avoid it as much as possible.
• Building large many leveled caches can be VERY time consuming (days-weeks for worldwide layers). Pick a small area, TEST-TEST-TEST and then TEST before doing the full export, instead of having to do it over & over.
• Cache will build over the extent of your MXD if the layer(s) you are building a cache for don't have data in the full extent, background color tiles will be created. A waste of CPU time & storage space.
• Dynamic cache building (a la TileCache) is planned for future releases.

Creating Windows & Web Apps with WPF

This was not at all about GIS. It was strictly on WPF.

WPF is very powerful & can provide you with a lot of UI design patterns.

Security & Permissions can be tightly controlled. Each app runs in a security sandbox. App origin, app settings, and client settings all interact to chose the most restrictive security level for the app.

You can do stuff with this technology TODAY. But, it's real power won't be realized until Visual Studio ORCAS is released and Vista or .Net 3 runtimes are more widely accepted. The 1 code for web & desktop paradigm is only for IE6+ with .Net 3 runtime right now. Must use WPF/E for other browser support.

Closing Session

Open Q&A after lunch. No really hard questions were asked, but Steve & other continued to point out that the extra cost of putting the ADF on a separate server is just not reasonable, and that the overall cost of ArcGIS Server is too high.

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

Software Used: PHP Designer, Aptana, Firebug, Firefox

While I was not particularly impressed with the ArcWeb Explorer & ArcWeb Services session on Tuesday, it did inspire me to want to do a mashup using my own data sources. I also wanted to compare the mashup creation process & end results across multiple possible mashup platforms. I decided to use JSON since all the mashup API's are in Javascript and it is an output format that both Google & Yahoo web services provide. I am already using JSON with the data entry interface for our Breeding Bird Census data that Plateau Land & Wildlife collects each spring.

JSON (or JavaScript Object Notation) is a well described data exchange format that is rapidly gaining favor over XML for web service data transport as it is less verbose and automatically parsed by Javascript.

While I had already created a JSON object model for use with our survey management system, it was structured in a totally different way than what I really wanted for my mashup. I also wanted to demonstrate doing this from scratch.

Step 1 - Design your JSON Object (i.e. Class Structure)

You want to put some thought into designing your class structure that you will emit. JSON can represent arrays, classes, and arrays of classes. The Good news is that JSON is VERY flexible, The Bad news is that JSON is VERY flexible. A general rule to follow in determining where you should use an array or class is that indexed lists = arrays, anything else is a class, or a class property (key:value pair). I just used the high tech method of pen & paper to design my JSON object, but you could also use any XML schema generation tool, VS2005 Class Designer, VIsio, etc.

My desired end product is a map showing the location & number of each point count station, with different symbols if it has been surveyed or not. A mouse over or click event will bring up an html table of with information about species observed and provide a Google search link for the bird based on it's scientific name. The common name usually gets you all kinds of results whereas the scientific name gets you good reference results.

Thus I need Station number, lat/lon for the station, total observed, number of each species observed and scientific name for each species.

JSON Object

{"Observations":

[{"Station":

{"number":"9", "lat":"30.9999",

 "lon":"-98.9999","total_species":"9",

{"Species":

[{"common":"American Robin",

"number":"9", "code":"AMRO",

"scientific":"Turdus migratorius "},

{species cont....}

] } }

]

}

Or in other words:

Array Observations contains Station classes with Properties

1. number
2. lat
3. lon
4. total_species
5. Array Species contains unnamed object with Properties
   1. common
   2. number
   3. code
   4. scientific

Step 2 - Create your JSON Object (i.e. Class Structure)

There are a whole bunch of ways to do this and really depends on what language your are programing in on your server side code. It basically boils down to either populating a class and then parse it to JSON through a method or class in your language, or directly writing the JSON string from some data source.

I chose the later for this project because my PHP install didn't have a built in method for this. But rolling your own is pretty easy.

1. Get results set from SQL query

function getData($cid,$myYear,$func) {

//cid = client id, myYear=year of survey, func=callback function to handle response

//Get a count of unique species occurring on the property for each point survey station, even those which have no recorded observations

$q_species="SELECT DISTINCT st.sta_num as station, COUNT(species) as species, st.lat, st.lon

FROM Stations as st LEFT JOIN (Surveys as su) USING (station_id)

WHERE (st.client_id=$cid AND (su.client_id=$cid OR su.client_id IS NULL) AND su.year=$myYear)

GROUP BY station ORDER BY station;";

 

//Get the details of species observed at each station

$q_obs="SELECT su.species, su.number, sp.code, sp.scientific, st.sta_num as station

FROM Surveys as su, Species as sp, Stations as st

WHERE su.client_id=$cid AND su.year=$myYear AND su.species=sp.common

AND su.station_id=st.station_id

ORDER BY station, sp.species_id";

 

//Create the results sets                     

$resSpecies = mysql_query($q_species);

$resObs = mysql_query($q_obs);

 

2. Create the JSON String 

//Loop through each Station & create the JSON string for that Station & it’s data

$myJSON= $func.'({"Observations":[';

While ($c=mysql_fetch_array($resSpecies)){

$myJSON.= '{"Station":{"number":"'.$c["station"].'", "lat":"'.$c["lat"].'", "lon":"'.$c["lon"].'", "total_sp":"'.$c["species"].'", "Species":[';

                 While ($s=mysql_fetch_assoc($resObs)){

                                   if ($s["station"]==$c["station"]){

$observations.='{"common":"'.$s["species"].'", "number":"'.$s["number"].'", "code":"'.$s["code"].'", "scientific":"'.$s["scientific"].'"},';

                                }

                }

                $myJSON.=rtrim($observations, ',').']}},';

                $observations="";

                mysql_data_seek($resObs,0);

 }

 

$myJSON=rtrim($myJSON, ', ').']});';

return $myJSON;

}

Step 3 - Emit JSON

This is the easiest part of all. Just return the JSON string using whatever the response function is for the language you are using. PHP can use the HTTP Send Request / Get Response functions to give you more control & learn more about what is happening on either side of the exchanges. But, I kept it simple and just echo-ed the string.

echo getData($cid,$myYear,$func);

NEXT STEPS:

>Make & Handle request in your webpage/webapp

>Create mashup

Day 2 , Wed Mar 21

I was really hanging this morning, for someone who has a beer or glass of wine only occasionally, going beer for beer with professional buisness travelers is never a good idea. But it was free and pretty decent beer so I can't complain too much.

General Thoughts

I got a whole lot more out of today's sessions and meetings than the 1st day. The talks I attended went into more depth, the .Net SIG was good, and I took Brian & James's advice and spent more time talking with devs in the Community center.

A few interesting things I learned in these informal conversations

• ArcGIS Explorer (AGX) will NOT have a custom skining option. You can mess around with some config files and the AGX document to change color themes, but that is about it. So a custom branded AGX that looked completely like your product but was actually AGX under the art, won't be easily done.
• AGX has no inherent editing capabilities, no area measurement tool and no way to discover any attributes about data sources other than local vector sources. You have to capture mouse events and do that work yourself.
• AGX has no way of directly interperting ArcGIS geometeries, you must provide a map/globe service that is spitting out rasterized data or convert that geometery to E2 geometery yourself.
• ArcWeb Services can add an ArcIMS service & soon an ArcGIS Server service to maps, but GeoRSS feeds, PostGIS, WMS, OGC, local/server stored vectors (shapefiles,PGDB, GDB, SDE, etc) all have to be progamatically adding in by transforming and adding the geometery to the map yourself. The only other way to get those alternate sources in is to intergrate them with ArcIMS or ArcGIS Server. If you do have a WMS you could but it through the OGC/ArcIMS translator servlet and leverage it that way.
• A large number of people have compalined about the highly fragmented documentation, poor indexing, incomplete docs, and lack of community comments on EDN & other ESRI help sites. It may just be conference talk but it looks like at least a few people at ESRI are begining to listen. Time will tell.

Sessions Attended

• Programing Custom Tasks for ArcGIS Explorer
• The task framework is not direct & intuitive. RTFM on that or you will really be lost. Not directly supported formats such as GeoRSS are not terribly difficult to transform into E2 geometeries and create content from a vast array of data sources. Just don't expect AGX to do that for you. Deploying custom tasks is SUPER easy. Deploying a malicous task is also possible so watch what you download. They are looking for ways to patch that which won't create Admin required installs. AGX2 is in the works and the more feedback you give them on the forums and directly, the better it will be.
• Special Interest Group: .Net
• A brief marketing talk about MS Visual Studio Team Foundation Server was followed up by Dave Bouwman's great talk about wrapping feature classes, SDE, & GDB in .Net abstractions using well known code gen techinques from non-spatial DB. It pulled together some of his posts on the subject and made it all look like such a better way to code. I've actually used the same techinques on shapefiles using RapTier and either the Jet OleDb or the ESRI OleDb drivers. Shapefiles are not as ammenable to being edited in this manner as PGDB or other real database storage formats are. I can read the data very well and databind on it but updating the gives rather unpredictable results.
• I would strongly recommend visiting Dave's blog and checking out his talk and an resources it is an exicting concept. It also makes you wonder, why they don't just offer Dave a position or short-term contract at ESRI to incorporate all they modern coding techniques in at least ArcGIS 10 if not 9.3. When you see simple object property assignments rather than the AO way of doing it and the self policing code it creates, you wonder why in the world would you want to do it the old way.
• James Fee hit on the craziness that is the 9.2 server licensing & the unreasonable expectation to pay 1/2 of the 1st license cost (of ~20k at retail) to put the Web ADF on another machine. He was told, "We know people don't like it, but that is the business model for today. We may be changing it in future releases but don't hold your breath" (paraphrasing, not a direct quote).
• Building & Deploying Enterprise Solutions with ArcGIS Server
• Not a lot of good tech meat here, just an eye opening talk about all the hoops & roadblocks that deploying ESRI server products in lerge organizations that don't conform to ESRI's way of doing things can cause.
• Developing Custom Web Tasks using the .NET Web ADF
• A lot of technical meat in this session. It went a long way to demonstrate that doing interesting things using this framework is not going to be so easy. You really have to jump through a lot of hoops to make cool things happen, and you have to jump through too many hoops to make super simple things happen. They covered a lot of techniques, tips, & tricks for working with this and I highly recomend veiwing this session on the post-conference web site if available. Especially the code that way demonstrated at the last 3rd of the talk.
• ArcGIS Mobile SDK
• A super energetic talk was presented by Jeff & Mike. They have really worked hard to abstract all the underlying native C++ code into a 1st class .NET SDK. They have built a really nice toolkit for VisStudio & using it and the Mobile 5 libaries, you can get some nice applictions working with a very minimal amount of code. It relies heavily on MapCaches produced by ArcGIS Server and they didn't go into creating those or pushing them out but I'm hoping to get that info in a later session. This was my favorite talk by ESRI thus far.