Skip to main content

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

Comments

Thank you for this. It all seems to work (verifies and the expression dialog box closes) just fine, except... no labels! The Access database is on a network drive, and everything else is on my local machine. Might that be the problem?
Brent said…
Without your label expression I'm forced to guess, but it is likely either one of two things is going on.
1.) You don't have labels turned on (check box on labels tab in layer properties)

or

2.) You are not using the right connection. Try the MS Jet connection listed above.

If this fails paste your layer expression below.
Thank you for your time.

Function FindLabel ( [PIN] )
Dim strQry, strInfo, i
i = 1
strQry = "SELECT Permit_Number FROM 2008_Zoning_Permits WHERE PIN = " & [PIN]
Dim Conn
set Conn = createobject("ADODB.Connection")
Dim rs
set rs = createObject("ADODB.Recordset")
Conn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0; Data Source=f:\Documents\Planning\Townships\Evangeline\Zoning\Evangeline Zoning Permits.mdb;"
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("Permit_Number")
Case Else
Do While Not rs.eof
'if multiple records indicate how many using count
strInfo = strInfo & vbNewLine & rs.Fields("Permit_Number") & " (" & 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
Brent said…
Shannon,

I just took your label expression and crafted an MS Access database and shapefile to match. Changing only part of the path of your label expression everything worked perfectly.

You said the expression verified correctly, however when you hit verify do you see a sample label?

I have a couple ideas of what might still be causing you issues. One possibility is that the data types do not match up. In the SQL Expression strQry I am comparing an integer to an integer (strQry = "SELECT Permit_Number FROM 2008_Zoning_Permits WHERE PIN = " & [ID]). Integers are by far the easiest to compare. If PIN is a string in your database you will have to change the syntax to correctly quotate the [ID] field.

Another possibility is that there is either a lock on the database from another user/application (is there an .ldb file with the same name in the directory) or a security issue. The best way to test this is to make a copy of the database in a directory where you know you have full read/write privileges. And although I was able to manipulate the database in MS Access while labeling, having both open at the same time may cause issues as well.

Finally, you really want to be working with an MS Access table and not a query. Queries will theoretically work but are horridly slow in ArcGIS and may timeout.
Wow. Again, thank you very much for your time. I'll have to get with our network/database person (for instance, there is an .ldb file for this, and the ID field is a string) to resolve the matter. The sample label box has never actually shown a sample label (I hoped that was just a fluke). But one last question: I thought I was working with an Access table; where am I working with a query?
Brent said…
No need. I changed my database PIN field to a string and instantly got the same behavior you described (not labeling... though I did get an error on validation, but that could be because I'm using 9.3)

Because your PIN field is a string you need to change your SQL expression. For MS Access JET this strings are delimited with single quotes. So you change:

strQry = "SELECT Permit_Number FROM 2008_Zoning_Permits WHERE PIN = " & [ID]

to

strQry = "SELECT Permit_Number FROM 2008_Zoning_Permits WHERE PIN = '" & [ID] & "'"

And you should be good. I don't think the lock file (.ldb) will get in your way. You should be golden.
Brent said…
Oh, and I didn't know if you were dealing with a table or a query. They are both possible using Jet in ArcGIS, it's just that queries are significantly slower so there is an advantage to using a table.
Hey! Whatta know, it worked just fine.

Thank you again.

Take care,
Shannon
ULIKA BBQ said…
Brent,
I used this vbscript and it worked great. However I would like to replace the count (1) with an additional field from the database. I tried several things that I thought might work, but with no success. I also could not figure out how to take the space off the top line. You can see this when you add a text box behind the label. I thought that removing &vbnewline& would take it out, but it put everything on one line.

Thanks for posting this.
ULIKA BBQ said…
I was able to add an additional field and now I am trying to add an If statement to the information.

If rs.Fields("Code") = "W" Then strInfo = rs.Fields("State_Rank")

but I am not sure where to place this in the code.
Brent said…
It would probably be simplest to add this right after the recordset is opened (rs.Open) and before the Select Case statement and set it to a variable. Then use the variable inside the case statement. If the value is null this could cause you issues on concatenating so it might be best to set strInfo = "".

If rs.Fields("Code") = "W" Then
strInfo = rs.Fields("State_Rank")
Else
strInfo = ""
End If

Of course if you are using a database other than SQL Server the quote syntax may be different, for example, MS Access uses single quotes (see comment above).
FTWorthMapper said…
Brent,

I am especially new to writing VB for labeling. My problem is that I am trying to relate a .dbf file to a layer in ArcView, and I am trying to make labels from the related fields.

I am trying to label the multiple owners associated with a single property, and I can not get your code to work. Like I said, I am very new to the coding world, so please be gentle!

Thanks in advance for your help!
Brent said…
FTWorthMapper,

Each datasource has it's own connection string. My example uses SQL Server and I provided a number of different connections for other databases.

dbf files have their own connection string as well. You need to use the following connection string for dbase:
Conn.Open "Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=C:\path\to\folder\in\which\dbf\resides"

In the SQL Query replace the table name (VU_GIS_Labeling in my example) with the name of the dbf file with no extension e.g. myfile.dbf type:
strQry = "SELECT Outfitter FROM myfile WHERE ID = " & [ID]

Where ID is the column name and [ID] is the column in GIS. Note that if it is a shapefile or odd GIS datasource the brackets may be quotes. You can see the correct "bracketing" by using the standard dropdowns to make your label and then switching to advanced.
FTWorthMapper said…
Brent,

Thanks for the quick response. I changed your code as shown below. Please check and see if you can see where I am messing up. "TID" is the field in my Shapefile the corresponds to "TRACTID" in the .dbf file.

I think there may be an issue. I have my .dbf related to my shapefile, but I can not see the fields in my .bdf as choices by which to label. Is that a problem?

I am trying to get it to label the field from my .dbf called LSE_NAME. Please let me know if you need any other information.

Feel free to e-mail me directly at potter.josh@hotmail.com



Function FindLabel ( [TID] )
FindLabel = Function FindLabel ([TID])
Dim strQry, strInfo, i
i = 1
strQry = "SELECT LSE_NAME FROM SandLakeSS WHERE TRACTID = " & [TID]
Dim Conn
set Conn = createobject("ADODB.Connection")
Dim rs
set rs = createObject("ADODB.Recordset")
Conn.Open "Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=M:\Projects\Celero_Energy\Sand_Lake\Tables\SandLakeSS.dbf"
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("LSE_NAME")
Case Else
Do While Not rs.eof
'if multiple records indicate how many using count
strInfo = strInfo & vbNewLine & rs.Fields("LSE_NAME") & " (" & 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
End Function
Brent said…
FTWorthMapper,

You have a couple issues going on here:
1.) dbf files in every software on earth except ESRI software (grrr) are limited to 8 character filenames. Since we're using MS Jet to connect we have the same limitation, so change the name to "SandLake.dbf". http://allfaq.org/forums/t/143048.aspx

2.) You want to remove the filename from the path so that it just reads:
Conn.Open "Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=M:\Projects\Celero_Energy\Sand_Lake\Tables"

3.) You're closing your function twice so remove the second END FUNCTION.

Good luck! and I'll email you the full function. Note, be sure you are in Advanced mode when using this Label Expression (checkbox above large textarea).

Popular posts from this blog

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.
Pick the virtual directory you want to redirect. e.g. http://olddomain.com/content Click HTTP Redirect under IIS in the IIS management console.In the HTTP Redirect Dialog:
Check Redirect requests to this destinationEnter your new path ending with $V$Q.  e.g. http://mydomain.com$V$QCounter-intuitively check Redirect all request to exact destination (instead of relative destination)Choose the appropriate Status Code (Permanent or Temporary)Apply Changes and Test

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! You don't even want to go down that road.

Step 2:
We create a new user-defi…

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/vnd.google-earth.kml+xml

The MIME type for KMZ files is

* application/vnd.google-earth.kmz

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…