topic Re: how to make an address locator for township-range-section polygons in Data Management Questions

how to make an address locator for township-range-section polygons

MatthewPainter1 — Wed, 22 Jan 2014 15:49:39 GMT

Hello all,

As stated im having troubles making an address locator to show land ownership

1 company owns 4000+++ peices of land which i have on an excell spreadsheet broken town to Township Range Section

i have the state sections breakdown date

the table looks like this

but im stuck at this.

Im guessing i need to add an alia refrence data of my collected data from my spreadsheets but im unsure how to arrange that and if i can just add a excel file

Re: how to make an address locator for township-range-section polygons

RichardFairhurst — Wed, 22 Jan 2014 16:08:38 GMT

You only showed the Section table. Show the relevant Section Township and Range fields of the Owner data that could match that.

I would not bother with an address locator at all. I would do a standard concatenation of the section fields to form a single string uniquely describing each section and create a Join/Make Query Table merge of the two datasets.

Re: how to make an address locator for township-range-section polygons

MatthewPainter1 — Wed, 22 Jan 2014 16:27:02 GMT

You only showed the Section table. Show the relevant Section Township and Range fields of the Owner data that could match that.

I would not bother with an address locator at all. I would do a standard concatenation of the section fields to form a single string uniquely describing each section and create a Join/Make Query Table merge of the two datasets.

this is a shot of some of the data i have

alright how do i join to two tables together?

explain it idiot proof for me 🙂

Re: how to make an address locator for township-range-section polygons

RichardFairhurst — Wed, 22 Jan 2014 17:14:40 GMT

alright how do i join to two tables together?

explain it idiot proof for me 🙂

I assume all Townships are North in your jurisdiction and all Ranges are West for your ownership data and in your jurisdiction. So you would only need to deal with the numbers. This is shown in the TwpRngSec field of your Sections table with a value like 16405525. Check if that column is a text field or a number field.

Add a column header in the next empty column of your ownership table named TwpRngSec (in cell D1). To make a text value in your owner table that would match the value in that same column in the Section table you would add a formula to cell D2 that reads:

=CONCATENATE(IF(B2<100,"0",""),IF(B2<10,"0",""),B2,IF(C2<100,"0",""),IF(C2<10,"0",""),C2,IF(A2<10,"0",""),A2)

If the value that appears is 14509323 the formula worked. Copy that formula down to the bottom of the table (double click the cell corner).

This formula should work for all Townships from 1 to 999, all Ranges from 1 to 999 and all Sections from 1 to 36 (and technically also up to 99).

The Excel table can be joined on the column named TwpRngSec to the field named TwpRngSec in the Section table provided it is imported into ArcMap with field type that is compatible with the one used in the Section table. Or else join it to the TRSTXT field if it imports as text.

Do not use the Excel table directly for the join. Import it to a File Geodatabase table or a dbase table. (I always use File Geodatabase Tables and Feature Classes and hate shapefiles). If the relationship is one to many (one section has many owners) you need to import both the owner table and the Section shapefile into the same File Geodatabase to use the Make Query Table too correctly.

Re: how to make an address locator for township-range-section polygons

MatthewPainter1 — Wed, 22 Jan 2014 17:39:14 GMT

I assume all Townships are North in your jurisdiction and all Ranges are West for your ownership data and in your jurisdiction. So you would only need to deal with the numbers. This is shown in the TwpRngSec field of your Sections table with a value like 16405525. Check if that column is a text field or a number field.

Add a column header in the next empty column of your ownership table named TwpRngSec (in cell D1). To make a text value in your owner table that would match the value in that same column in the Section table you would add a formula to cell D2 that reads:

=CONCATENATE(IF(B2<100,"0",""),IF(B2<10,"0",""),B2,IF(C2<100,"0",""),IF(C2<10,"0",""),C2,IF(A2<10,"0",""),A2)

If the value that appears is 14509323 the formula worked. Copy that formula down to the bottom of the table (double click the cell corner).

This formula should work for all Townships from 1 to 999, all Ranges from 1 to 999 and all Sections from 1 to 36 (and technically also up to 99).

The Excel table can be joined on the column named TwpRngSec to the field named TwpRngSec in the Section table provided it is imported into ArcMap with field type that is compatible with the one used in the Section table. Or else join it to the TRSTXT field if it imports as text.

Do not use the Excel table directly for the join. Import it to a File Geodatabase table or a dbase table. (I always use File Geodatabase Tables and Feature Classes and hate shapefiles). If the relationship is one to many (one section has many owners) you need to import both the owner table and the Section shapefile into the same File Geodatabase to use the Make Query Table too correctly.

alright i believe i did everything you said

but when i joined it my table shows up on the end as shown in the photo but doesnt so anything?

Re: how to make an address locator for township-range-section polygons

RichardFairhurst — Wed, 22 Jan 2014 18:43:39 GMT

alright i believe i did everything you said

but when i joined it my table shows up on the end as shown in the photo but doesnt so anything?

You did not import the Excel table, otherwise it would have an FID or ObjectID field. I don't use Excel for joins ever, because Excel spreadsheets are not really tables with defined field types and it rarely works. I am also not sure you can join to an Excel formula field ever, since it does not really store the value it shows.

Create a File Geodatabase and import (or export) the Excel spreadsheet to it before joining it. Also convert your Section Shapefile to the File Geodatabase as a Feature Class (right click it in catalog and choose data export into the file geodatabase by making sure the type of target is a geodatabase at the bottom of the dialog). Performance will improve and memory faults like you are seeing will be avoided. Before Joining make sure the owner field is numeric if you are joining to the TwpRngSec field of the Sections. If it imports as text join it to the TRSTXT field. Get used to using File Geodatabases if you want to do professional GIS work.

Do you know if more than one owner is associated to any given section ever? If that type of relationship exists a join will not work to show you all of the owners, only one of them. To see all of the owners you would need to use the Make Query Table Tool. If only one owner is associated to a Section the regular Join is fine.

I am not really sure what your owner table does for you, since it only has township information and no owner names or IDs to group or symbolize on.

Re: how to make an address locator for township-range-section polygons

RichardFairhurst — Wed, 22 Jan 2014 19:09:29 GMT

Keep in mind the Join or Make Query Table will end up creating polygons, while the Address Locator option would create points. The effects you can do when overlapping of multiple ownerships per section occurs will differ. With Polygons you would have to use open hatched symbology with a variety of hatch patterns and different outline widths and colors to make them all visible on the map. With points you could do dispersion techniques. So choose the approach that ends up showing the effect you want. Either way the TwpRngSec field will let you do the Join or Locator.

Re: how to make an address locator for township-range-section polygons

MatthewPainter1 — Thu, 23 Jan 2014 15:55:50 GMT

Alright so i did what you said by making a file gdb and importing my data to it.

I added to the map and i did the join although my data shows up as <null> whats this mean? did i do something wrong?

Re: how to make an address locator for township-range-section polygons

MatthewPainter1 — Thu, 23 Jan 2014 16:03:52 GMT

also wont let me alter the data time in the tables properties

Re: how to make an address locator for township-range-section polygons

RichardFairhurst — Thu, 23 Jan 2014 16:17:48 GMT

Alright

so i uploaded the table how you meantioned into a new file gdb to work from i included a column for ownership and a column thats the same as in my sections table and the formula looks like it matches up... cool beans

when i imported the sections to my gdb as a feature class it seems like it screwed up some settings as you can see it fully shades my map? but skimming through properties its identical to the original??? whats the cause of this?

alright now to join it finally... am i missing the simplicity of this project and making it harder than it is...
here i join it although it doesnt list the corresponding column on my table??? why is this?

For the Sections shading I am not sure what occurred. I would check the Source tab of the layer properties and verify that it is the same Coordinate system as the original. Also test altering the Symbology tab setting. It does not look like it matches the symbology in the legend, so we want to make it match that symbology. For the export did you right click the Sections shapefile and use the Data Export to Single Feature Class option?

For the second, probably the formula did not export. You will have to convert the formula to actual values. To keep your original data with the formulas select everything on the page and then use the Paste Special - Values Only on a new tab of the Spreadsheet to convert the formula to value. Then retry the export/import of the Excel table using the second sheet.

Lets forget about the time format issue until the normal layer behavior for drawing and joining is solved.

Re: how to make an address locator for township-range-section polygons

MatthewPainter1 — Thu, 23 Jan 2014 16:50:03 GMT

For the second, probably the formula did not export. You will have to convert the formula to actual values. To keep your original data with the formulas select everything on the page and then use the Paste Special - Values Only on a new tab of the Spreadsheet to convert the formula to value. Then retry the export/import of the Excel table using the second sheet.

Lets forget about the time format issue until the normal layer behavior for drawing and joining is solved.

alright i did what you said and imported a new table that has no formula only the values. Doing so TWPRNGSEC Owner columns are listed as text data fields and cannot be alter nor show up in the drop menu when trying to join? It gives me an error when trying to change them to double.

Re: how to make an address locator for township-range-section polygons

RichardFairhurst — Thu, 23 Jan 2014 18:31:07 GMT

alright i did what you said and imported a new table that has no formula only the values. Doing so TWPRNGSEC Owner columns are listed as text data fields and cannot be alter nor show up in the drop menu when trying to join? It gives me an error when trying to change them to double.

You can't change the type, but you can change the name in a File Geodatabase in ArcCatalog. So change the name and call the exported field TRSTXT instead to match the name of the Text Field in the Section table. Then create a new field and give it the TWPRNGSEC name and make it a long or double field. Then use the Field Calculator to calculate the TRSTXT field text values into it. As long as every text value is a valid number the calculation will do the conversion and not give you any error. An error would occur if a non-numeric value is in that column, but you can tell the calculation to proceed and it will convert what it can. Then everything will join fine (except any records that threw an error, which would not have worked anyway).

These types of hurdles are not uncommon with GIS, especially when conversions are involved, but after a while you learn what works and what doesn't and it gets faster overcoming these kinds of glitches. These kinds of glitches are why I generally avoid using Excel, because it is not a real database and makes faulty assumptions during conversion to a real database.

I assume the Section layer display problem is resolved.

Re: how to make an address locator for township-range-section polygons

MatthewPainter1 — Thu, 23 Jan 2014 19:45:38 GMT

I assume the Section layer display problem is resolved.

Yes the section layer doesnt block out the map anymore and the join worked perfectly thank you for helping me figure that out.

The new ownership shows up but doesnt display on the map?

is there a setting im unaware of?

Re: how to make an address locator for township-range-section polygons

RichardFairhurst — Thu, 23 Jan 2014 19:51:26 GMT

The new ownership shows up but doesnt display on the map?

is there a setting im unaware of?

Now that you have a successful join you have to use the joined fields to change the layer properties to use them. You could change the Label tab settings to display owner names from the joined fields or you could change the Symbology tab settings to show different fill or outline colors or hatch symbols based on different ownerships using a Categorized symbol legend list. Your owner table is only township/range/section data, which the Section table already had. So the owner table may needs more fields about the owners to be useful for making the layer show ownership information.

Since the ownership was all for one company then you could make the Symbology Categories only show NULL values in the list for the TWPRNGSEC field with a background symbol for that value, and then assign a fill symbol intended for your company to the All Other Values option and rename that option with the Company name. You would have to have a field that had the company name in every record of the Company table to make the symbology work where the category list has the company name and the All Other Values option is showing a background symbol for lands not owned by the company.

Or else you could make a simple fill symbol for the whole layer and set a Definition Query tab to:

NOT JoinTable.TWPRNGSEC IS NULL

JoinTable is just a dummy name value. Use the field list to substitute the actual qualified join field name in the expression from the Joined table. This will filter the layer to only show sections that are actually in the table that the company owns. Then you would have to have the map showing an area where the company owns some sections and does not own other to see the full effect of those symbology settings. You could optionally use that expression to select company owned Sections with a Select By Attributes query also.

Re: how to make an address locator for township-range-section polygons

MatthewPainter1 — Fri, 24 Jan 2014 15:29:57 GMT

alright i totally understand what your talking about with editing the symbol and i can do it with any other file i load, but i cant edit anything on the joined file? its like invisible? while its loading when i make a change i can see that my land is displayed but its covered by the base map and wont display further? It acts as transparent but isnt.

Re: how to make an address locator for township-range-section polygons

RichardFairhurst — Fri, 24 Jan 2014 17:06:45 GMT

alright i totally understand what your talking about with editing the symbol and i can do it with any other file i load, but i cant edit anything on the joined file? its like invisible? while its loading when i make a change i can see that my land is displayed but its covered by the base map and wont display further? It acts as transparent but isnt.

I don't follow what you are describing. I assume the layer is above the base map in the table of contents so that it is not actually covered by the base map (layer order matters). Beyond that I am not sure what you have configured. Do you have any a transparent setting value in the layer Display tab? A picture of the map and table of contents and possibly the layer symbology tab would help me understand what you have set up.

But you are correct that by design joined data cannot be edited directly in a joined table, only the original table that the data was joined to can be edited in that view. The joined data has to be edited through the original unjoined table view of the join data source. There may be a delay for the refresh that has to be forced after each edit to make the join show the edit effect and the edit may have to be saved.

Or are you saying you cannot change the actual symbology? If that is the case create two copies of the layer and make one unjoined and the other joined. Use a simple symbol in both and make sure the joined version is on top of the other. Make the lower layer show the background symbol for all sections. Make the joined layer above it solid to cover up the lower layer and set the Definition Query tab to have the expression:

NOT joinedTable.TWPRNGSEC IS NULL

Only edit your owner table directly and force a refresh after editing it. See the pictures that hopefully explain the way I want you to set everything up. I did it with my Section data. In my case I used a field called UNIQNUM to create the join for my Owner_Test table.

Re: how to make an address locator for township-range-section polygons

MatthewPainter1 — Fri, 24 Jan 2014 18:58:08 GMT

Alright i do understand i would have to edit the original table to make changes like that and force a restart but thats not what im talking about. these photos will shed some light on my problem

First photo

This photo shows the entire sections that has been joined with my data now how to i get my data to display over my other layers disregarding null values so it doesnt cover everything?

Re: how to make an address locator for township-range-section polygons

RichardFairhurst — Fri, 24 Jan 2014 20:59:30 GMT

Alright i do understand i would have to edit the original table to make changes like that and force a restart but thats not what im talking about. these photos will shed some light on my problem

First photo

This photo shows the entire sections that has been joined with my data now how to i get my data to display over my other layers disregarding null values so it doesnt cover everything?

Do what I did. I told you to stop using a Categorized Symbology and make a layer with a single symbol that only shows the ownership and not every section. Use a Definition Query to do the filter to only show the company (see my second screen shot). Make the symbology less transparent by changing the setting on the Display tab (screen shot your Display tab for me. Do exactly what I did in my screenshots (except of course change the field name to match your table and filtering field).

If you still want all sections to display with a different transparent setting, add the Sections in again as a separate unjoined layer and make sure the unfiltered Section layer is below your company layer. If the base map covers everything, prove you can put any other layer layer on top of it. I don't use base maps. My County has all of the data that created ESRIs base maps so I create my own from the original data.

Your real layer order is not shown in the Table of Contents, since you are on the Source tab view of the Table of Contents. Switch to the Display view and show the real layer order. (The buttons at the top left corner of the Table of Contents dockable window.) The Source Tab view button is pressed (the one that looks like a database tank with a folder below it), The Display tab button looks like a folder tree.

Trash that layer and add the Section layer fresh, then add the join (don't add the table again or remove the table). If you add the layer again it should be really on the top level.

Re: how to make an address locator for township-range-section polygons

RichardFairhurst — Fri, 24 Jan 2014 21:45:19 GMT

Here is a screenshot of something I set up in about 10 minutes with a Basemap background, transparent hollow sections and solid company sections. If you can;t do this with your current map start a fresh map and do these steps in this order:

Switch your table of Contents to Display tab view.

First add the Basemap.

Now add two copies of the Section layer.

Change the symbology of the bottom Section layer properties to hollow on the Symbology tab and to 70% transparent on the Display tab.

On the top Section layer properties, change the layer name to your company name on the General Tab, change the single symbol on the Symbology tab to a solid color.

Join the top Section layer to your company data (don't add the table to the map, just use the browse button on the Join properties window to add the table to the join table slot) and complete the join.

Go to the Definition Query tab and make it:

NOT YourCompanyTable.TWPRNGSEC IS NULL

Substitute the table name you created for the YourCompanyName in the expression above by clicking that field in the field list to replace that text.

If that does not work, either something is wrong with ArcMap or your not following my instructions in the right order.

Re: how to make an address locator for township-range-section polygons

RichardFairhurst — Fri, 24 Jan 2014 21:50:25 GMT

Here is a screenshot of something I set up in about 10 minutes with a Basemap background, transparent hollow sections and solid company sections. If you can't do this with your current map start a fresh map and do these steps in this order:

Switch your table of Contents to Display tab view.

First add the Basemap.

Now add two copies of the Section layer.

Change the symbology of the bottom Section layer properties to hollow on the Symbology tab and to 70% transparent on the Display tab.

On the top Section layer properties, change the layer name to your company name on the General Tab, change the single symbol on the Symbology tab to a red solid color.

Join the top Section layer to your company data (don't add the table to the map, just use the browse button on the Join properties window to add the table to the join table slot) and complete the join. (if you do add the table to the map, switch the Table of Content view back from Source view to Display view, since adding a table automatically places you in Source view).

Go to the Definition Query tab and make it:

NOT YourCompanyTable.TWPRNGSEC IS NULL

Substitute the table name you created for the YourCompanyName in the expression above by clicking that field in the field list to replace that text.

If that does not work, either your not following my instructions in the right order or something is wrong with your ArcMap installation. After you get this to work to recreate what I have done then you can mess with the symbology to fit your exact design ideas.