idfg-bthomas's blog

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/

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:

Repairing Broken Data Sources

What do you do when you open your map and you have a layer that looks like this? What does it mean?

The red exclamation point is a visual indicator that something is incorrect in the layer's data source.  To fix it you'll need to know where the data lives and tell the layer in the map this information.  You're giving direction to poor lost layer so it can find itself, you're a metaphysical geographer!

 Repairing a data source:

  1. Right-click on the layer, from the contextual menu choose Properties
  2. In the Properties dialog box choose the Source tab
  3. Click Set Data Source
  4. Browse to the location of the data layer, select and click Add
  5. OK out of the Properties dialog

If all went well your data is now displayed.

But what if it isn't one layer?  What if you have an entire map of mis-pathed layers?

Here's where ArcCatalog comes to the rescue:

  1. Locate the map in ArcCatalog
  2. Right-click and select Set Data Source(s)
  3. Highlight one of the Data Layers that is in error and click Replace...
  4. The find box should load with the erroneous path of the layer you select, if not enter it
  5. In the replace box enter the correct path and click Ok
  6. Repeat as necessary

But what if they are SDE Layers?  This whole batch thing is no help at all.
Yes, this is where it gets nasty. Basically, you're left out in the cold.  You can either fix them one at time, or you're going to need a little script my coworker developed and that isn't so simple.

The Case IsNull Now

In SQL Server views I've been using CASE statements to look for nulls for years. I've just nullified this practice.

For the following Employees Table:
EmployeeIDLastNameFirstNameNickname
39CarterJamesJimmy
40ReaganRonald 
41BushGeorge 
42ClintonWilliamBill

Would be used to create the View:
EmployeeIDLastNameHandle
39CarterJimmy
40ReaganRonald
41BushGeorge
42ClintonBill

This is the CASE:
SELECT EmployeeID
, LastName
, CASE Nickname
WHEN Null THEN FirstName
ELSE Nickname
END As Handle
FROM Employees

ISNULLified by:
SELECT EmployeeID
, LastName
, ISNULL(Nickname, FirstName) AS Handle
FROM Employees

 

An Inquiry Into IN Query

I had to select a ton of records today for a project in ArcGIS. You know the normal method, hellish SQL repetition: ID = 1 OR ID = 5 OR ID = 6 OR ID = 12 OR ID = 14 OR ID = 27 OR ID = 41 OR ID = 43


The past few months I've been working a lot in SQL Server and this experience made this repetition seem like too much work. So I got lazy and I made an inquiry into IN query: ID IN (1,5,6,12,14,27,41,43)


And it just worked. There's no documentation in the main help file that even mentions it. If you do open the ArcGIS Help and search on "SQL Reference" nearly half way down you find a section on Subqueries. Here they explain that IN queries are supported in geodatabases and EXISTS is supported as well! Who knew? How was I supposed to know this if I hadn't gotten lazy?

Here's what you'll find in the ArcGIS Help File:

Subqueries
NOTE: Coverages, shapefiles, and other non-geodatabase file-based data sources do not support subqueries. Subqueries done on a versioned ArcSDE feature class which has been registered without the option to move edits to base will not return features stored in the delta tables. File geodatabases provide the limited support for subqueries explained in this section, while personal and ArcSDE geodatabases provide full support. For information on the full set of subquery capabilities of personal and ArcSDE geodatabases, refer to your DBMS documentation.

A subquery is a query nested within another query. It can be used to apply predicate or aggregate functions or to compare data with values stored in another table. This can be done with the IN or ANY keywords. For example, this query would select only the countries that are not also listed in the table indep_countries:

"COUNTRY_NAME" NOT IN (SELECT "COUNTRY_NAME" FROM indep_countries)

This query would return the features with a GDP2006 greater than the GDP2005 of any of the features contained in countries:

"GDP2006" > (SELECT MAX("GDP2005") FROM countries)

For each record in the table, a subquery may need to parse all the data in its target table. It may be extremely slow to execute on a large dataset.

Subquery support in file geodatabases is limited to the following:

IN predicate. For example:

"COUNTRY_NAME" NOT IN (SELECT "COUNTRY_NAME" FROM indep_countries)

Scalar subqueries with comparison operators. A scalar subquery returns a single value. For example:

"GDP2006" > (SELECT MAX("GDP2005") FROM countries)

For file geodatabases, the set functions AVG, COUNT, MIN, MAX, and SUM can only be used within scalar subqueries.

EXISTS predicate. For example:

EXISTS (SELECT * FROM indep_countries WHERE "COUNTRY_NAME" = 'Mexico')

OperatorDescription
[NOT] EXISTSReturns TRUE if the subquery returns at least one record; otherwise, it returns FALSE. For example, this expression returns TRUE if the OBJECTID field contains a value of 50:

EXISTS (SELECT * FROM parcels WHERE "OBJECTID" = 50)

EXISTS is supported in file, personal, and ArcSDE geodatabases only.
 
[NOT] INSelects a record if it has one of several strings or values in a field. When preceded by NOT, it selects a record if it doesn't have one of several strings or values in a field. For example, this expression searches for four different state names:

"STATE_NAME" IN ('Alabama', 'Alaska', 'California', 'Florida')

For file, personal, and ArcSDE geodatabases, this operator can also be applied to a subquery:

"STATE_NAME" IN (SELECT "STATE_NAME" FROM states WHERE "POP" > 5000000)



Remix IN with some external SQL that EXISTS here in ArcGIS One-to-Many Labeling and you might confuse ArcSDE for an RDMS. Just don't try look for anything meaningful in a RELATE.

Restoring Orphaned SQL Server 2008 Users

When moving databases across servers and reinstalling SQL Server almost everything transitions perfectly except SQL Server Users. Unless the user already exists in the Master Security table, the database user is left orphaned inside the database without the ability to perform any useful function. Like you right now, but we're gonna change that.

Here is an example, note how there is a User Name but no Login Name.

User Name but no Login Name

Step 1: Recreate the User in Master
First, if they don't already exist, is to recreate the user in the master database by browsing to the root level Security folder in SQL Server Management Studio, choose Logins and right-click create user.

You tried to check the database under the "User Mapping" page didn't you?

Create failed for User 'UserName'.

That was a good idea hotshot, but it ain't gonna work for you. Just create the user and Ok your way out of there. Time for Step 2.

Step 2: Remap User to Database
We need to run a system stored procedure I found on MSDN, it is pretty straightforward, unfortunately it must be performed for each database.
NOTE: There's got to be a better way, and supposedly this method is deprecated, but this is the only thing I could get to work in my instance, so if you got something better please share.

USE DATABASENAME;
GO
EXEC sp_change_users_login 'Auto_Fix', 'UserName', NULL, 'UserPassword';
GO


Where:
DATABASENAME = The name of your database
UserName = SQL Server Login to repair
UserPassword = SQL Server Login's password to repair

Full documentation on MSDN for sp_change_users_login
http://msdn.microsoft.com/en-us/library/ms174378.aspx

Translating Coordinates

Of all the GIS questions I field, coordinate system related questions are the most frequent. The following is typical:

I have 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, 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 a web browser on your phone and you call up Google Translate to assist. You are two dropdown listboxes away from understanding:

  • First, you must correctly identify what language they are speaking.
  • Second, you must indicate what language to which you want the information translated (most likely English)

You hand your phone over and they pound quickly into the keys:

missa ovat wc

You've selected English as the output for your benefit, but the original language requires a guess, so you randomly start trying local tongues.

Swedish suggests Ovat has bad aim...

Ms. Latvia haz Water Closet...

Ah, the Finn has to pee!

Ah, but how do we ask the Dutch crew?

Projections in ArcGIS present exactly the same problem. ArcGIS needs to “know” what coordinate system you want everything to be in, and it needs to know what coordinate system the different layers are in to begin with, so it can successfully translate. In some situations (generally where the datum differs) it is a best practice to permanently re-project the data to match the rest of your data. The most insidious errors occur when you incorrectly guess one or more coordinate systems and receive gibberish in response.
 

Idahos to the Rescue

So what to do when layers don't play well with others? Out here we bring on the Idahos. Or "Projection Finder" as I call it professionally. This is a layer I made that is projected in IDTM83 and displays where fifteen common projections fall to use in identifying projections.

How to use this layer

  1. Open a new map and add the Project Finder layer first. This is important as starting a new map and adding a layer sets the default projection of the data frame to match the first layer added.
  2. For the layer you want to determine a projection for, first make sure it has no projection defined. There are two ways you can do this: in Windows Explorer delete the corresponding .prj file or in ArcCatalog right-click on the layer, select Properties, select the XY Coordinate System Tab and spank the Clear button.
  3. Once the coordinate system is nuked, add the layer to the map.
  4. Zoom to the layer to to see in which projected Idaho it falls.
  5. Now you can go to Toolbox > Data Management Tools > Projections and Transformations > Define Projection and do just that.
  6. Once it is defined, remove it and re-add it to your map and it should move to fall inside IDTM83 since it now has a projection defined it is reprojecting on the fly to match the data frame. This is a good thing.

You're Finnished.

Oliko se hyvä sinulle? Osta minulle olutta.

 

 

Downloads

Copy the projection files to the State Coordinate Systems folder for your ArcGIS installation if they are not already present.  This location is typically (where DesktopX.X is your ArcGIS version number):

C:\Program Files\ArcGIS\DesktopX.X\Coordinate Systems\Projected Coordinate Systems\State Systems