Skip to main content


Showing posts from 2008

Translating Coordinates

Of all the GIS questions I field, coordinate system related questions are the most frequent. The following is typical:
I got a shape file of land parcels from Bonneville Co. and am trying to overlay that onto the County NAIP imagery. For some reason, when I have the same coordinate system and datum assigned to all layers, the NAIP and shape files are projecting at different scales and not even coming close to lining up. I’ve tried everything in my limited arsenal. If you have any idea of what’s going on, please let me know.

The problem here is not a lack of understanding of the software (or grammar), but misunderstanding concepts behind assigning and translating a coordinate systems.

Translate This
I've found language translation to be the most accurate analogy for making sense of coordinate systems in ArcGIS.

Suppose you're traveling on a ferry in the Baltic Sea (why not?). A frazzled tourist is running from person to person animatedly asking a question. Fortunately, you have…

Replace ntext with n/varchar(MAX)

Starting with SQL Server 2005 replace ntext with more efficient varchar(max). varchar(max) and nvarchar(max) store data fields smaller than 8,000 characters in the table rather than as a large object (LOB/BLOB).

Beware when upgrading data currently stored as NText as it will remain a LOB until you run an update query setting the value equal to the current value, e.g.
UPDATE testTable SET testText = testText
Full details and thanks go to Making Stuff Faster


How do you search for * and ? (asterisks and question marks) in MS Access?

Bracket the criteria:

SELECT * FROM Table WHERE Column NOT LIKE "*[*]*"

Serving up KML in IIS 6

To serve up KML in IIS 6, you have to add a few MIME Types. The easiest way to do this is to apply new MIME Type settings globally by changing the properties on your server's "Web Sites" folder in IIS.

Google Earth reads KML and KMZ files. The MIME type for KML files is

* application/

The MIME type for KMZ files is

* application/

Source: Google KML Tutorial

To add a MIME type to a Web site or directory

1. In IIS Manager, right-click the Web site or Web site directory for which you want to add a MIME type, and click Properties.

2. Click the HTTP Headers tab.

3. Click MIME Types.

4. Click New.

5. In the Extension box, type the file name extension.

6. In the MIME type box, type a valid MIME type. If you define a MIME type that has already been defined at a higher level, you are prompted to select the level where the MIME type should reside.

To create a MIME type for an undefined MIME type, type an asterisk (*) in the Extension box, an…

Resultant table not allowed to have more than one AutoNumber field

Ever get this error on a create table or insert query? The work around is quite simple. Cast the autonumber as an integer. In query design view change your field definition for one of your autonumbers to read:
XID: CInt([ID])
Or in SQL View:
(Ran into this error message again this morning and it reminded me I should share the workaround.)

Simple Record-Level Auditing in SQL Server & ArcSDE

I've had simple auditing set up in my databases for years thanks to our old DBA. This is not robust transaction logging, it just answers the questions "Who created this?", "When was this created?", "Who last edited this?" and "When was this last edited?".

After witnessing a brand new table audit itself without triggers, I set about to understand the setup. This seemed curious to me, so I thought it might also be curious to you.

It turned out to be a two step system which rendered my insert triggers superfluous (at least I think so, tell me if I'm missing something and I need insert triggers after all).

Our solution uses a combination of two methods. For inserts we are going to tie our audit fields to User Defined Data Types which inherit Defaults we have specified. For updates we will build a trigger to log the changes. We will use the following fields in our tables to track inserts and updates:AUD_NUser - Creator
AUD_NDate - Creation …

Making Copies with a HP DesignJet 5000ps (Queue Management)

At my old job making copies on the plotter was a piece of cake. You went into the Queue, selected the job and number of copies and presto! For some reason, possibly because I hardly make paper maps anymore, learning queue management on the DesignJet 5000ps did not come intuitively.

My latest map kept running out of memory on the plotter, so when it finally did print after spooling for three hours, I had the incentive I needed to explore the DesignJet 5000ps menus.

I must admit that I have waited five years and wasted the total of several days spooling print jobs. It isn't even complicated.

Starting at the Main Menu (use "Top" key to make sure you are at the Main Menu)
Press the "ENTER" button (this will select the first option "Printing")Highlight "Queueing & Nesting..." and press "ENTER"Highlight "Queue Management" and press "ENTER"Highlight the job number (These are negative numbers in the order last printe…

Recording a Booby Observation for Posterity

CC Licensed photo by Rileypie Building on the database that spurred my Color Considerations, I spent the better part of today debating the best way to log observers in a database. Having documented it to explore the pros and cons with my coworkers, I figured I'd share my musings with y'all as well. What way do you think is best? Is there a better pattern I left out?

On a blue-footed booby capture Nov. 11, 2007 we had four biologists recorded on the capture event: Mike, Roy, Phil and Jay.

How do I record this? Let’s review a few options:

Two Integer Fields to an Observer lookup tableObserver1ID: “272” (MIKE)
Observer2ID: “312” (ROY)

NEGATIVE: The rest of the observers disappear in the data abyss.

One Integer Field to an Observer lookup, One Free Text FieldObserver1ID: “272” (MIKE)
OtherObservers: “ROY, PHIL, JAY”

NEGATIVE: Nothing lost, but only able to reliably query on Observer1… Other Observers are listed, but there is no way to find PHIL.…

Color Considerations

I love the odd places my job takes me. Today I am building a database to house all of the "marks" we place on animals we capture. Tags, Collars, Leg Bands, RFID Tags (they're in fish and your passport), Tattoos, Radio Collars, GPS Collars, Backpacks, Wing Tags.... the list is seemingly endless.

The fun thing about designing databases is figuring out ahead of time all the exceptions. For the moment at least, I now know more details about tagging animals than many field biologists.

So far we have the following attributes:

Tag TypeCollar, Band, Round, Square
Tag LocationRight Leg, Left Leg, Right Wing, Left Wing, Neck, Nose, Right Ear, Left EarTag ConstructionPlastic, Metal, Leather, Ink (tattoo)ColorRed, Green, Yellow, Black, WhiteMarkR-2345, 562626849057, TIGGER, Y-7892-23(We'll leave radio frequencies out of our discussion and focus purely on the visual aspects)

The challenging part of this is deciding what to leave in, what to leave out.

I can probably discard Tag Cons…

Auto Format (and Color) Outlook Appointments

A few years ago I got turned on to the idea of indexing your life by color. In a quick glance your mind can comprehend really complex patterns. By coloring entries in my calendar I am able to tell immediately if I am available or if a future appointment conflicts with a work meeting.

There are a number of ways to set this up. Outlook allows you to add a label to every appointment. However this is an Outlook specific feature and I sync my calendar across Outlook, Yahoo! and iCalendar. The later two don't even have labels.

Besides, calendars should be simple. Complexity only hinders usability, so I prefer an automated solution.

How to color appointments in Outlook automatically:
In Calendar, right-click the calendar grid, and then click Automatic Formatting on the shortcut menu. Click Add, and then type a name for the rule. In the Label list, click a color. Click Condition to specify the conditions under which the color will be applied.Note: If you manually assign a color to an it…

Learning the Hard Way: Installing Drupal on XP

Open Source on Closed Source. Probably a bad idea from the start.

Installing Drupal hearkened back to the old days of computing. Way too much jargon and far too little push-a-button-and-it-works. But I'm sold on the Drupal. If I wasn't, I wouldn't have made it through the past two hours trying to install it (I'm tempted to say her here, which suggests a certain love-hate relationship is already evolving).

Drupal installations should be straight forward. Lullbot manages the whole install in one five minute video that I would highly recommend. My installation took significantly longer.

MySQL - At least in my instance the directions for setting permissions given in INSTALL.mySQL.txt were not sufficient to allow the database creation scripts to run.

Command provided in INSTALL.mysql.txt:
ON databasename.*
TO 'username'@'localhost' I…

Printing Folder Contents

Sometimes the old ways were a lot simpler. In DOS and UNIX printing a file directory was as simple as "DIR". And if you wanted to be really fancy you could add a couple characters to switch the listing format.

DIR command line switches:/B Uses bare format (no heading information or summary)/L Forces lowercase display of file names/O List by files in sorted order:N by name (alphabetical)S by size (smallest first)E by extension (alphabetical)D by date/time (oldest first)G group directories first- using this prefix reverses the sort order/P Pauses after each screenful of information/S Lists files in the specified directory and all subdirectories/W Uses wide list format/X Displays 8.3 versions of long file namesAnd if you wanted to put this list in a text file to save, copy, paste or print it was as simple as: dir /b /s > DirectoryListing.txt
Somewhere in the ease of use of folders and drag and drop what was simple became hard. In DOS and UNIX this was all easy. It can be ag…

Maintaining Cross-Database Referential Integrity

It is often convenient to house your master tables in a separate database from application specific databases so that primary keys are copasetic and multiple databases have access to the same lookups.

Common examples would include employees, counties, regions and other lookup tables.

Once these tables are in a separate database however it is no longer possible to simply drag-and-drop a relationship between them to maintain referential integrity.

Here's a Solution
For this example we'll use an Observation table, tied to a master list of Sex, that's right, Sex (we could call it Gender to be all PC, but these are animals, not Pat in the Personnel Department.)

Step 1:
We create a new view called PIC_Sex in our Application Database by querying the table PIC_Sex in our Master Database:

CREATE VIEW dbo.PIC_Sex AS SELECT SexID, Sex, Rank FROM Master.dbo.PIC_Sex ORDER BY Rank
Yes, that's right, Male and Female! Slugs are awesome.

Step 2:
We create a new user-defined function xdf_Val…