Blogs

GIS Q&A for Professionals

The best programming question and answer site on the web now has a dedicated GIS section.

http://gis.stackexchange.com

This is a great resource to try next time you have a question that you know others have faced.  You can limit your results to just gis.stackexchange.com performing a google search by adding the parameters "site:gis.stackexchange.com" to your query.

Here's a demonstration

 

Simple HTTP Redirect with Querystring in IIS7

 

HTTP Redirect seems simple enough. Always was in IIS6 and in IIS7 there's even a button labeled HTTP Redirect that promises relative redirects.  It looks like it'll be as easy Apache finally.  That is until you try to redirect a querystring.  Then everything bombs.

Turns out it still is relatively easy, except you have to know that Microsoft changed $S$Q to $V$Q. Why? $Ss and $Gs I suspect.

And How.
In our example we'll redirect all pages under http://olddomain.com/content to http://mydomain.com/content.



  1. Pick the virtual directory you want to redirect. e.g. http://olddomain.com/content
  2. Click HTTP Redirect under IIS in the IIS management console.
  3. In the HTTP Redirect Dialog:
    • Check Redirect requests to this destination
    • Enter your new path ending with $V$Q.  e.g. http://mydomain.com$V$Q
    • Counter-intuitively check Redirect all request to exact destination (instead of relative destination)
    • Choose the appropriate Status Code (Permanent or Temporary)
  4. Apply Changes and Test

Using Drupal Feeds Module to Import Content from MS Access

Drupal Feeds Module works brilliantly, but there is a couple weird gotchas when working from MS Access to Drupal that are worth documenting:

  1. Seems self-explanatory, but since you are importing as .txt or .csv make sure you remove all carriage returns.  In MS Access this involves writing an update query and doing a replace on all Chr(10) and Chr(13).  e.g. Replace(Replace([myfield],Chr(10),"<br />"),Chr(13),"<br />")
     
  2. Although CCK Date fields import perfectly as MS Access dates, published dates do not.  This is because the published field in Drupal is a Unix Timestamp which is a quite different than Access dates. 

    To fix you need to first account for the difference between the start dates of the two time systems and then account for the fact that is decimal days and unix is seconds (or something like that.

    Formulas:

    Access -> Unix
    DateDiff("s", #1/1/1970#,[myAccessDate]) AS myUnixDate

    Unix -> Access
    DateAdd("s", [myUnixDate],
    #1/1/1970#) As myAccessDate
     

  3. Exporting from MS Access can also cause you problems with Memo fields.  Strangely, if you use Text Qualifiers your exported Memo fields will be truncated to 512 characters.  Don't do that.  There is a better way - export tables not queries and this won't happen.
     
  4. If Importing Taxonomy fields to Drupal, you'll need to edit your Taxonomies at least for now to Free Text.  You can set them back later, but at least for me, even if the Term existed it wouldn't insert unless I set it to Free Text.  Your mileage may vary.
     
  5. Apostrophes will cause you nightmares. They terminate strings early. Replace them with right apostrophes or quotes.
     
  6. Real HTML required. If you're like me and you've stored all your <HTML> as &lt;HTML&gt; in your ASP and .Net applications to get around Microsoft injection filters you'll have to undo all that silliness.

And I think that's it. If I remember any more from this evening I'll edit this post. Good times.

Finding and Replacing Special Characters in MS Access

Most special characters like Tabs and Line Break and Carriage Returns can be found and replaced in MS Access by using the character codes.  But you can't use the find and replace dialog.

Instead, author an update query and enter the following to remove tabs:
Replace([myfield],Chr(9),"")

If removing line breaks you need to replace both the line break and carriage return character:
Replace(Replace([myfield],Chr(10),""),Chr(13),"")

If editing for HTML you'd edit this to the following for HTML line breaks:
Replace(Replace([myfield],Chr(10),"<br />"),Chr(13),"<br />")

For other characters, look up the ascii character code. (use the numbers in the "Dec" column in the tables)

MS Access Truncating Exports of Memo Fields at 512 Characters

Recently I was surprised to find that exports of Memo fields to csv and txt truncate at 512 characters.

Thankfully, there is an easy, albeit poorly documented hack.  In the export wizard set "Text Qualifier" to "{none}"
 

Background on OpenID and oAuth

OpenID

OpenID explained - a federated passport for the web
http://openidexplained.com

Making openid pretty
http://stackoverflow.com/users/login

Built using jquery openid plugin
http://jvance.com/pages/JQueryOpenIDPlugin.xhtml

Another implementation w/ extend properties
http://www.zoho.com

OAuth

OAuth a Valet Key for the Web
http://hueniverse.com/oauth/guide/intro

Beginner's Guide to OAuth - Workflow
http://hueniverse.com/2007/10/beginners-guide-to-oauth-part-ii-protocol-workflow

Demo of OAuth
http://pikchur.com or http://yfrog.com

Twitter has a good explanation of OAuth 1.0a with workflow
http://dev.twitter.com/pages/auth

OpenID vs OAuth (federated vs delegated login)
http://hueniverse.com/2009/04/introducing-sign-in-with-twitter-oauth-style-connect

Library we will be using for implementation
http://www.dotnetopenauth.net/

Geographic Names Information System (GNIS) Query

Looking for a town, city or place outside of Idaho? You can use this website to query geographic features for all of the United States and its territories at http://geonames.usgs.gov/pls/gnispublic. Of course you can use the IDFG GeoTools LayerFetcher to zoom to locations in Idaho in ArcGIS.

ESRI Mapping Center has some Style

Take a minute to check out the ESRI Mapping Centers' ArcGIS Styles page and you will see that we have included graphics of the contents of the styles you can download. The center is easy to browse and you can download any style or symbolset to use in ArcGIS.  Ofcourse, some of the symbols will be a little hard to see in their full glory in these screen captures (for example, marker symbols that are bigger than the space allotted in the ArcMap Style Manager large thumbnail view.) However, you can still get a good idea of the contents.

Here's a couple examples to whet your appetite:

Gradients for Different BiomesElevation color ramps for different biomes. download ShadeMax

SubtleSet of light shades of a variety of hues. download Subtle Artist Light Colors

Color Deficient Set of colors for maps for the color deficient. download Color Deficient

View all the styles, plus models, scripts, expressions and more at the ESRI Mapping Center ArcGIS Resources Gateway

Also, just adverNPS Symbolstised, and to be added soon to the MapCenter Resource Page are National Park Service (NPS) symbol sets.

You can download these today from the NPS Harpers Ferry Center.

It is available there for download in a multitude of formats:

Map Symbols

Map<br /><br />
symbols
Recreation pictographs, north arrows, bar scales, road shields, etc.

Other Symbols

Other<br /><br />
symbols
Pictographs for accessibility, winter recreation, water recreation, etc.

NPS symbols are free and in the public domain. They derive from Ultimate Symbol Collection, a commercial product that offers hundreds of additional symbols.

TrueType Font Symbols

Map<br /><br />
symbolsOther<br /><br />
symbols
Fonts include both the Map Symbols and Other Symbols shown above.

ArcGIS Symbols

Map<br /><br />
symbolsOther<br /><br />
symbols
Includes both the Map Symbols and Other Symbols shown above.

Map Patterns

Map<br /><br />
patterns
Lava/reef, sand, swamp, and tree patterns (accessed as a swatch library in Adobe Illustrator CS or later).

There's lots of great options out there!  Try a new style today!

Database Documentation Made Easy

Data Dictionary Creator is a simple application which helps you document SQL Server databases. It stores all the information in Extended Properties, so it's easier to keep the documentation in sync with the database as it changes.  The tool is open source and available as an executable and source code on codeplex.  The first code release was November 2006 with over 40 releases and patches up until today.  Authored in C#.Net.

Database Dictionary Creator

I could go through a step-by-step, but there is already a great walkthrough online.

The html grouped option was great for me - it even creates the anchor tags to jump from the table list to the table details. 

The only step remaining in my mind was to link the database diagram to the table details by exporting a diagram from sql server and adding an image map.  Since those steps are custom, I'll document them here:

Exporting a SQL Server Relationship Diagram

  1. Right-click in the diagram pane
  2. Choose "Copy Diagram to Clipboard"
  3. Paste in an image management destination program and save as image

Creating an Imagemap

  1. Open an online imagemap creator such as http://www.maschek.hu/imagemap/imgmap
  2. Upload an image or provide a url to an image and click "accept"
  3. Draw a box around each table in the diagram.  Add hypertext references (href) and alternative text (alt) attributes for each hyperlink.  Href's should link to the anchors in the format "#dbo.{tablename}", alt tags are displayed on hover, "View {tablename}" is probably appropriate.
  4. When all tables have been outlined and linked, click code and copy the text

Putting it all together

  1. Open the html produced by the Database Documention Creator in export grouped mode
  2. Just inside the body tag (after <body>), add a reference to the image you created (hint: it must be accessible to the html you are viewing - it is easiest to place it in the same directory).  An html image is referenced like the following <img src="imagename.format" alt="Alternative Text to Display" />
  3. After your image paste the code you copied.  This is the imagemap it must be using the same copy of the image that your used to create the imagemap.  If the image has been resized or modified the coordinates will no longer match.
  4. Link the imagemap to the image by copying the map id from the map code to the image tag like the following: <img usemap="#map id value" src="imagename.format" alt="Alternative Text to Display" />.  Notice I have added a pound symbol before the id attribute.
  5. Save your html and view it in a browser.  The product should resemble http://fishandgame.idaho.gov/ifwis/core/home/model.

How this could be better

The source code for both applications I have used are available online.  The Database Dictionary Creator is available for free and the Imagemap Creation Tool for a modest donation.  The whole process might be slightly smoother if we write some code to wrap together the imagemap creation and publishing to the web.  I'm thinking we use the existing Database Dictionary Creator to export the grouped html, then have an upload page which accepts the created html and an image.  The user then adds an imagemap and the script cleans up the html, puts it all together into one page and publishes the product.

SQL Server User-Defined Data Types Not Visible to MS Access on Linked Table Refresh

Had one of those, "am I on crazy pills?!" problems the past few weeks. In an MS Access database I had a whole slew of SQL Server Linked Tables that included User-Defined Data Types(UDT). The UDTs were mostly auditing fields that referenced defaults to pull in the system.user and current datetime.

Everything worked great.

That was until I added a field and asked a user (with very elevated privileges) to refresh the linked tables. They did, but the audit fields referencing the user-defined types disappeared.

At first, I thought it was some silliness in their file-based DSN, but even using my connection (with Windows-based security... so it pointed to their account) the audit fields remained hidden.

To make the whole story more perverse, if I used the same connection logged in as me (I am a dbo) the tables refreshed with the audit fields and they could also see them.

After much googling I finally stumbled upon this post and this msdn article which indicate that user-defined types have permissions including: CREATE, GRANT, DENY, ALTER, CONTROL, TAKE OWNERSHIP, REFERENCES, EXECUTE, VIEW DEFINITION and REVOKE.

To set permissions to allow anyone to view a user-defined datatype, use the following
 

grant execute on TYPE::[dbo].[user_data_type] to public;

Or the appropriate user or role.

References: