Blogs

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.

Convert GPS Coordinates and Display Online

This website allows you to convert UTMs or other XY coordinates to different formats and then display the result on a Google Map or Topo map.

http://www.rcn.montana.edu/resources/tools/coordinates.aspx

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

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)

  1. Press the "ENTER" button (this will select the first option "Printing")
  2. Highlight "Queueing & Nesting..." and press "ENTER"
  3. Highlight "Queue Management" and press "ENTER"
  4. Highlight the job number (These are negative numbers in the order last printed. Select "-1" for the last job.)
  5. Highlight "Copies = 0..." and Press "ENTER"
  6. Highlight the number of copies you want in the Copies Menu and Press "ENTER"
  7. Press the "BACK" button to return to the Job Menu (Titled "-1:" in this example)
  8. Highlight "Move to Top" and Press "ENTER"

The plotter should start warming up and printing will begin almost immediately. Best yet, it will continue to print with no interruption for processing.

I just made three copies of a 36"x60" shaded relief poster in the time it took to type this out.

Saving Label Expressions

Using the database querying label expressions I wrote about on Thursday, I got to learn a new "feature" of ArcGIS today: Disappearing label expressions on modification of a definition query

That's right, spend five minutes building a database query into your label expression, get it working perfectly and then to celebrate try limiting it with a definition query. Bam! No more label expression. Ain't ArcGIS beautiful?

In dealing with this "feature", I discovered ArcGIS has the ability to Save and Load label expressions.

I'm quickly developing a catalog of label expressions.

ArcGIS One-To-Many Labeling

ArcGIS is just plain lousy at dealing with any relationship that isn't one-to-one.

We all have a slew of hacks just to deal with this limitation. I for one regularly am creating temporary cross-tab queries so I can represent multiple sample results at a collection point, fish surveyed at a cross-section and a host of other relationships.

The classic example for mapping comes from the cadastral community and condominium lots. It's an odd situation where more than one person has title to the same piece of ground. How do you represent this?

I've got a new trick thanks to Mohammed Hoque's article in ArcUser Magazine.

We're going to do a database query inside a label expression, loop through the results and output the entire list to label.

For our example we'll use Outfitting Areas in Idaho and we'll label them with the Outfitters and Guide License Numbers and Outfitter Names.

1.) Open ArcGIS and add your spatial layer with the unique identifier shared with your database.
2.) In the label expression, click Advanced


3.) Replace the labeling expression with the following:

Function FindLabel ([ID])
Dim strQry, strInfo, i
i = 1
strQry = "SELECT Outfitter FROM VU_GIS_Labeling WHERE ID = " & [ID]
Dim Conn
set Conn = createobject("ADODB.Connection")
Dim rs
set rs = createObject("ADODB.Recordset")
Conn.Open "PROVIDER=SQLOLEDB;Integrated Security=SSPI; Persist Security Info=False; Initial Catalog=aDatabaseName; Data Source=aServerName"
Conn.CursorLocation = 3
rs.Open strQry, Conn, 3, 1, 1
'if more than one records are found, append to the existing string.
Select Case rs.RecordCount
Case -1, 0
'If no record is found, return empty string
strInfo = ""
Case 1
'reading only the first record
strInfo = rs.Fields("Outfitter")
Case Else
Do While Not rs.eof
'if multiple records indicate how many using count
strInfo = strInfo & vbNewLine & rs.Fields("Outfitter") & " (" & i & ")"
i = i + 1
rs.movenext
Loop

End Select
'closing connections this is a must
rs.Close
Conn.Close
Set rs = Nothing
Set Conn = Nothing

'returning string for labeling
FindLabel = strInfo
End Function

You'll need to replace the bold values with those appropriate for your situation.

This example also uses SQL Server, different databases require different database connection strings:

Oracle
“PROVIDER=OraOLEDB.Oracle; Data Source=aDatabaseName; User ID=aUserName; Password=aPassword”

MySQL
“driver={MySQL ODBC 3.51 Driver}; Server=aServerName; Database=aDatabaseName; uid=aUserName; PWD=aPassword”

Microsoft Access
“PROVIDER=Microsoft.Jet.OLEDB.4.0; Data Source=c:\myDatabase.mdb;”

Microsoft SQL Server (using Windows NT Integrated security)
“Provider=SQLOLEDB; Integrated Security=SSPI; Persist Security Info=False; Initial Catalog=aDatabaseName; DataSource=aServerName”

4.) Finally, test your label expression for typos using the Verify button and if successful, OK your way out.

The final product:



Link