topic Assigning Address Ranges from nearby Address Points in Python Questions

Assigning Address Ranges from nearby Address Points

LangdonSanders3 — Mon, 05 Aug 2013 12:31:36 GMT

Hello,

I am a GIS Tech at a city gov. with our own 911 dispatch. I am working to update our street centerline ranges for use in our dispatch software. The geography is accurate; however, the address ranges are model types and do not fit reality, i.e. range 1-100, when there are only 4 houses on the block from 101 - 116. I would like to use dynamic segmentation to help fix these ranges.

I came across this white paper by Kelly Bigley (2003) http://proceedings.esri.com/library/userconf/proc03/p1077.pdf
Which has a process I would like to replicate:

Of course it is possible that an address point may be closer to a road that it is not addressed off of. An ArcINFO
AML script can be used to loop through each street name. For each name it creates a subset of the streets and
address points with that street name. It performs a NEAR command on those subset coverages and transfers the
unique street coverage COVER-ID to the address points. Then it appends the resulting point coverages into one.
The resulting coverage is basically the original address coverage with a few additional attributes, including the
COVER-ID of the street centerline and the X and Y coordinate along the closest arc.

- Kelly Bigley

I'm new to python and eager to learn so I am asking to be pointed in the right direction to write a similar script - comparing address point street names to centerline street names before performing the NEAR analysis. Thank you.

Once the NEAR is complete for Address --> Roads, I want to dynamically segment the roads based on those address points, with each segment having an address range determined by the address points along it.

Langdon

Re: Assigning Address Ranges from nearby Address Points

LangdonSanders3 — Tue, 06 Aug 2013 14:17:01 GMT

My theory for the workflow would be as follows

Define the two feature classes (street centerline & address points)

Define the field names I will be referencing for street name (STR_NAME in both)

Create a search cursor that cycles through each address point record, and identifying its street name (Main, Oak, Broadway, etc)

Output the street name from the address point

Run a definition query for the centerline where STR_NAME = the output from address point street name

Run the NEAR function for Address point to Centerline with this definition in effect(creating the appropriate fields)

Clear the centerline definition query

Next row in the Address Points

Am I on the right track? I am learning the code and formatting so will update as help is needed.

Re: Assigning Address Ranges from nearby Address Points

PaulCone — Mon, 12 Aug 2013 16:29:57 GMT

I maintain the street centerline for the City of Portland, Oregon, that is used in 911 dispatch.

A couple of thoughts. First, I'm intrigued by how you might end up solving your problem. I am fairly new to Python, and so we might find a need to do this ourselves at some point. We have fields on the segments for actual address ranges, but we have never populated those. Perhaps we could with your approach.

However, historically we have had the same issue with addresses not exactly matching theoretical address ranges, and those ranges are what we load into our systems for geocoding. In most cases addresses do not go up above 50, e.g the range for the block is 1000-1099, but the last address on the block is 1050. However, we also do quality control checks for contiguous ranges, and so if we did away with those, we'd be creating gaps where we might not want any. Have you thought about what the repercussions might be if you lose that contiguity? What happens if a new address is assigned above the actual range?

Also, since we went to a new CAD system (Versaterm) a couple of years ago, we now use address points as the primary method of geocoding. Address ranges are now the secondary method of geocoding. Using points instead of addresses seems to be the way geocoding is moving. So how important is it to have the ranges match the points?

Paul

---------------------------------------------------------
Paul Cone
Mapping and GIS
City of Portland, Bureau of Transportation

Re: Assigning Address Ranges from nearby Address Points

LangdonSanders3 — Tue, 13 Aug 2013 12:31:29 GMT

Hi Paul,

Thank you for your reply. I agree that address point geocoding is more accurate as you said and the way geocoding is headed. We do have an address point system with a fairly high degree of confidence (albeit with its own issues such as multi-tenant buildings).

Our fire department has decided upon the use of centerlines currently for throwing a broader net and the most likely chance of a �??hit�?? when creating an alarm. Often callers do not know their exact location or may say it in error which has been problematic when using the address points as the primary geocoding method. The points have higher spatial accuracy while streets give a better match rate. The address points are displayed to the dispatcher but are not the primary search model.

Our streets and addresses are fairly static as we are about 97%+ developed with very limited new growth. We would like to correct the ranges both for dispatch and future use of the centerline network. You also make a very important point for continuity which must be maintained. It is our hope to both improve our ranges (narrowing from the default 0-99) while maintaining continuity on each side of cross streets. The picture below from Kelly Bigley's process outlines what I'd like to accomplish. She used dynamic segmentation to then pull the first and last address on each street segment and then adjusts ranges based on percent distance between those endpoints and the cross streets.

[ATTACH=CONFIG]26667[/ATTACH]

Langdon

Re: Assigning Address Ranges from nearby Address Points

TOMKONG — Tue, 13 Aug 2013 20:02:46 GMT

My theory for the workflow would be as follows

Define the two feature classes (street centerline & address points)

Define the field names I will be referencing for street name (STR_NAME in both)

Create a search cursor that cycles through each address point record, and identifying its street name (Main, Oak, Broadway, etc)

Output the street name from the address point

Run a definition query for the centerline where STR_NAME = the output from address point street name

Run the NEAR function for Address point to Centerline with this definition in effect(creating the appropriate fields)

Clear the centerline definition query

Next row in the Address Points

repeat

Am I on the right track? I am learning the code and formatting so will update as help is needed.

The relationship between points (address) and lines (network) is "many-to-1", so you need:
1. Using "Spatial Join" function (also called Near - in old ArcInfo version) to join line class to point class (point as target layer), the output joined point layer should have the line ID field;
2.Open the joined the point layer's attribute table, go to the ID field (from line layer), using "Field Summarize" function to get the Min and Max values for the Address number field (if the Address field data type is Text, convert it to Intege first), this summarized output should be one standalone table;
3.Using "Common Field" (line ID) to join that summarized standalone table back to the original line feature class, export it as one joined feature class;
4.In the joined line feature class, the Min field should be the start address number and the Max field should be the end address number - you can merge both fields to one new field (call it as Address Range) to fit your application.

Re: Assigning Address Ranges from nearby Address Points

LangdonSanders3 — Wed, 14 Aug 2013 14:15:52 GMT

The relationship between points (address) and lines (network) is "many-to-1", so you need:
1. Using "Spatial Join" function (also called Near - in old ArcInfo version) to join line class to point class (point as target layer), the output joined point layer should have the line ID field;
2.Open the joined the point layer's attribute table, go to the ID field (from line layer), using "Field Summarize" function to get the Min and Max values for the Address number field (if the Address field data type is Text, convert it to Intege first), this summarized output should be one standalone table;
3.Using "Common Field" (line ID) to join that summarized standalone table back to the original line feature class, export it as one joined feature class;
4.In the joined line feature class, the Min field should be the start address number and the Max field should be the end address number - you can merge both fields to one new field (call it as Address Range) to fit your application.

Thank you, I did not think of using a join then summarize. This may be a simpler answer than what I anticipated. Thank you and I'll give it a try.

Langdon

Re: Assigning Address Ranges from nearby Address Points

RichardFairhurst — Wed, 14 Aug 2013 14:37:24 GMT

Thank you, I did not think of using a join then summarize. This may be a simpler answer than what I anticipated. Thank you and I'll give it a try.

Langdon

It will not reliably work if you need continuous addresses on adjoining segments. It would only work well where the addresses are located close to the segment ends and the addresses at the ends represent the range across the segment's full length.

Where the addresses are far from the ends of the segment or you only have one address on a long segment of road in an area where additional developed could occur, or you have one segment with no addresses between two segments that have addresses, you would expect additional addresses to be created on the ends outside of the range of the current address(es). Determining positions along the lines relative to the ends of the lines is what Linear Referencing can much more easily analyze than a simple Spatial Join or Near tool match. It also is the only method that can deal with ordering your chains of segments that need to match up the ranges where their ends meet in a logical, sortable manner.

Linear Referencing is the only method that is capable of ordering adjoining road segments and lists of intersections along a given road according to the way you would actually drive a road from one end to the other. It is therefore ideal for this kind of problem.

Re: Assigning Address Ranges from nearby Address Points

TOMKONG — Wed, 14 Aug 2013 18:18:08 GMT

Thank you, I did not think of using a join then summarize. This may be a simpler answer than what I anticipated. Thank you and I'll give it a try.

Langdon

For those line segs without joined address numbers, you can:
1. Extract those segs and export them as one feature class - class A, and export the another part segs (with address number) as the another feature class - class B;
2. Convert the class B to the point feature class - class C, then join (Intesect Spatial Join) the Class C to the class A - joined point class D;
3. Then join the class D table back to the Class A by using common field (line ID) and the output as Class E;
4. Open the Class E table, you can transfer the Min & Max number from joined fields (you need to know which one is Min & Max due to double Min & Max values joined - the larger Min value from both Min values is Max value for this seg, and less Max value from bothe Max values is Min value for this seg.

After above process, you may couldn't assign address for all non-address segs (ig. two adjacent non-address lines), you may need to repeat above process.

Re: Assigning Address Ranges from nearby Address Points

RichardFairhurst — Wed, 14 Aug 2013 18:26:31 GMT

For those line segs without joined address numbers, you can:
1. Extract those segs and export them as one feature class - class A, and export the another part segs (with address number) as the another feature class - class B;
2. Convert the class B to the point feature class - class C, then join (Intesect Spatial Join) the Class C to the class A - joined point class D;
3. Then join the class D table back to the Class A by using common field (line ID) and the output as Class E;
4. Open the Class E table, you can transfer the Min & Max number from joined fields (you need to know which one is Min & Max due to double Min & Max values joined - the larger Min value from both Min values is Max value for this seg, and less Max value from bothe Max values is Min value for this seg.

After above process, you may couldn't assign address for all non-address segs (ig. two adjacent non-address lines), you may need to repeat above process.

That simply does not work. If I have 4 addresses on a block, nothing says the block should begin and end with those addresses for creating ranges. That won't align them using geocoding either if there are many potential addresses between the last actual address and the block end. For example, if I have blocks at 1000 to 1100 and 1100 to 1200 as far as ranges, but addresses 1038 and 1058 as my min and max in the center of one segment and 1138 and 1158 as the min and max in the center of the other segment, the min and max values make no sense as the ranges. The whole reason to use ranges is potential addresses, so excluding them makes little sense. Better to use address points to only have exact matches and positions.

Re: Assigning Address Ranges from nearby Address Points

LangdonSanders3 — Thu, 15 Aug 2013 12:19:46 GMT

Determining positions along the lines relative to the ends of the lines is what Linear Referencing can much more easily analyze than a simple Spatial Join or Near tool match. It also is the only method that can deal with ordering your chains of segments that need to match up the ranges where their ends meet in a logical, sortable manner.

Linear Referencing is the only method that is capable of ordering adjoining road segments and lists of intersections along a given road according to the way you would actually drive a road from one end to the other. It is therefore ideal for this kind of problem.

Thank you Richard,

You're right and Linear Referencing has been my goal since the start. I would like to use it to both limit our ranges and maintain continuity on either side of cross streets. The address range should be updated based on the distance of the block not just the address number. Additional distance from the start and end of address events should be added to the address range. The first step was making sure the correct points are referenced to the correct streets. The paper I was working from was written in 2003 using the NEAR function by street name as a precursor to Lin. Ref. which may now be unnecessary.

[ATTACH=CONFIG]26722[/ATTACH]

Bigley later details her script process after linear referencing is performed:
"For each street name, it selects the min and max events that occur on that street name. For each event (in order from lowest to highest measure), it keeps track of the measure value and the address number for both the current event and the previous event. It selects the arc segments whose FMEAS and TMEAS encompass the previous measure value through to the current measure value. It then loops
through each of those selected arcs (in order from lowest to highest measure). It calculates the difference in measure values between the current and previous event and determines the percentage of that distance that occurs on that arc. That percentage is multiplied by the difference in address numbers. The result is added to the address number and put into the range field. The script keeps track of this new range number to add onto it for the next arc until it reaches the last arc for those events. The loop increments to the next event until there are no more events on that street name. The resulting address ranges represent more than the existing addresses and allow for very useful geocoding solutions."

The default address ranges is currently 100 units, 0-99, but often this can be narrowed to say 20 units, 0-19 (addresses 2-15) then the next street 20-39 (with addresses 24-37). These narrower ranges better our geocoding but maintain continuity.

Many of these ranges have been corrected manually by essentially the same process I hope to automate, looking at the nearby address points, then rewriting the ranges to a better match.

Thank you for all your input.

Langdon

Re: Assigning Address Ranges from nearby Address Points

RichardFairhurst — Thu, 15 Aug 2013 16:30:58 GMT

I use the Locate Features Along Route tool to get Routes and measures assigned to address points. This tool accomplishes all that the Near tool does (telling you the distance from the road and if it is left or right of the road) and adds the Route and Measure information.

For the underlying Centerlines I extract their end points using the Feature Vertices to Points tool and then use the Locate Features Along Routes with those points. I find that points work better than line segments with the tool and feel that the end points will work better for getting nearby addresses on each side of the segment end. I uncheck the Keep Only Closest Route Location option to get all matches and reselect the set where the Route ID matches up with the Route ID of the given centerline and export that to a validated event table. Converting from a point event table back to a line event table is possible using the Summary Statistics tool or the Pivot Table tool on the Route ID and segment Unique ID case field values and a Min and Max of the point Measure field values.

For my address points, I uncheck the Keep Only Closest Route Location option to get all matches within a 500 foot radius of an address in a relatively urban area. I transfer route information about the street name to the matched points so that I can select the set of matches where the main street name spelling is the same (excluding suffixes like Ave, Dr, Rd, St, etc.) and export that set to a validated table.

I then perform additional validation to examine unmatched addresses, since I do not want to drop address numbers that might affect the ranges and I don't want bad street name data. To do validation I perform a relate from the exported event table back to the address points on the unique address ID field. Then I switch the address selection and perform a Select By Location of that address selection that fall within 500 feet of the routes. This gives me the set of Addresses where the address street name is misspelled, the Route street name is misspelled, or Route associated with addresses is missing within the selection radius of other routes.

Oddball and obvious misspellings are easy to detect with the above method, and I use a geocoded recorded maps layer to research names that are hard to know the correct spelling (with or without spaces between words, plural vs. singular, words with spelling variants, etc.). Yesterday, in one area with 900 unique street names and 15,000 addresses I found about 100 addresses and 2 streets with misspelled names using this method.

Now I am trying to think through a method of extracting the addresses on one side of the road that are closest to the segment ends and validating even/odd side matching. The event table makes this validation possible. For example, I can get addresses with even house numbers that are on the left side of the road using the SQL:

"HOUSE_NUMBER" = ROUND( "HOUSE_NUMBER" / 2, 0 ) * 2 AND "Distance" > 0

To get odd house numbers I change the HOUSE_NUMBER field expression to not equal (<>) and to get addresses on the right side of the road I change the Distance field expression to less than (<). In my case, Cul-de-sac ends will almost always cause exceptions to these rules where they bend to one side, since survey centerlines have a short segment at a 90 degree angle to the main road that messes up the right/left odd/even arrangement.

Next I will begin working on the issue of getting the closest House numbers on each side of a segment end. I will use the measure information to determine the proportions of offsets of the closest addresses on each side and the segment end measure for dividing the range at the segment end location. I am still thinking through a way to do this using geoprocessing tools only and not cursors, since I prefer that approach. I am sure I will be using Merge, Summary Statistics, and Overlay Route Events tools to move forward. I will share what I may come up with that works.

Re: Assigning Address Ranges from nearby Address Points

RichardFairhurst — Thu, 15 Aug 2013 22:44:21 GMT

I think I have figured out a way to get the houses on each side of the ends of the lines. Working with 4 sets:

Even House Numbers on the Left Side of the Street
Even House Numbers on the Right Side of the Street
Odd House Numbers on the Left Side of the Street
Odd House Numbers on the Right Side of the Street

Here are the steps.

I created 2 fields with one named EVEN_ODD and the other named LEFT_RIGHT to simplify the selection and allow sorting that ignores the actual numeric values of the House Number and the Distance field. I calculated them to be:

Parser: VB Script

Show Codeblock: Checked

Prelogic Script Code:

If [HOUSE_NUMBER] = Round( [HOUSE_NUMBER] / 2, 0) * 2 Then   Output = "EVEN_HOUSE" Else   Output = "ODD_HOUSE" End If

EVEN_ODD = Output

For the LEFT_RIGHT field I changed the Prelogic Script Code to:

If [Distancec] > 0 Then   Output = "LEFT_SIDE" Else   Output = "RIGHT_SIDE" End If

Next I selected the set of Even Houses on the Left Side of the street and exported it to a new table named Address_Even_Left using the SQL:

"EVEN_ODD" = 'EVEN_HOUSE' AND "LEFT_RIGHT" = 'LEFT_SIDE'

Then in ArcCatalog I Load data into the Address_Even_Left table to duplicate the records from the original Locate Features Along Route output using the SQL above to limit the records to match the criteria for that table.

Then I run the Sort Tool on the Address_Even_Left with the sort set for:

RID Ascending
Measure Ascending
House_Number Ascending.

In the Sorted output I added a field called LINE_NUMBER. For tables where the first ObjectID is numbered one (1) I calculated it to be (reverse the logic if the first ObjectID is 0 for dbf tables):

Parser: VB Script

Show Codeblock: Checked

Prelogic Script Code:

If [OBJECTID] = Round( [OBJECTID] / 2 , 0) * 2 Then   Output = [OBJECTID] + 1 Else   Output = [OBJECTID] End If

LINE_NUMBER = Output

Next I run the Summary Statistics to covert the points to line events. The Summary Statistic settings are:

Input Table: Address_Even_Left_Sort

Output Table: Address_Even_Left_Lines

Statistics Fields:
ObjectID Min
ObjectID Max
Meas Min
Meas Max

Case Fields:
RID
LINE_NUMBER

In the output from that tool I added a field called FROM_HOUSE_NUMBER and TO_HOUSE_NUMBER. I joined the MIN_OBJECTID field to the ObjectID field of the Address_Even_Left_Sort and calculate the FROM_HOUSE_NUMBER field to equal the House_Number of the sorted table. I then broke that join and rejoined the MAX_OBJECTID field to the ObjectID field of the Address_Even_Left_Sort and calculate the TO_HOUSE_NUMBER field to equal the House_Number of the sorted table. The line event table has zero length lines at each end of the set pf house numbers or where only one house number occurred on the entire route.

Now I have a Line event table of where the Even house numbers on the Left of the line are listed for the correct ends of the lines as the measures increase on the route. House numbers can end up ascending or descending. Now I can use the Overlay Route Events tool with my road segment end point events to find the match of the lines and house numbers to the segment ends. I calculate a copy of the segment end measure to preserve it in the overlay so I can work out its proportion to the line ends. I also calculate a duplicate of the MIN and MAX measures into two fields called FROM_MEAS and TO_MEAS so I can preserve one set of Measure field values in the Overlay output.

Re: Assigning Address Ranges from nearby Address Points

LangdonSanders3 — Fri, 16 Aug 2013 11:13:54 GMT

Thanks Richard!

I will be working through the process you have detailed.

Langdon

Re: Assigning Address Ranges from nearby Address Points

RichardFairhurst — Sat, 11 Dec 2021 13:53:38 GMT

The only part of the set up I left out regards the Centerline Ends output before doing the Overlay Route Features step at the end.

After running the The Feature Vertices to Points tool with the ENDS option on my Centerlines, I add a field called FROM_OR_TO that is 8 characters long. I calcualte this field so I can sort based on the line end position and keep track of that. For 1 based ObjectIDs like in file geodatabase tables, I calculate its value using the formula:

Parser: VB Script
Show Codeblock: Checked
Prelogic Script Code:

If [OBJECTID] <> ROUND([OBJECTID] / 2, 0) * 2 Then
  Output = "From_End"
Else
  Output = "To_End"
End If

FROM_OR_TO = Output

That completes the steps I was outlining in the previous e-mail. Moving on to where I am now, which is working on assigning House number values to the Overlay Route Events output.

After the Overlay Route Events is complete I perform a sort on it on these fields:

RID
MEAS
HOUSE_NUMBER
ORIG_OID
FROM_TO

One thing I want to point out about this approach that is cool is that all segment line ends that had a House number on both sides of them are in the table and sorted according to drive direction. This is true even for all such segments that had no house numbers on them. So we are ready to interpolate all segment end House numbers between the first House number on the route and the last in the table. The only post processing of segment ends will occur on the ends of the route outside of the first and last house number on the route.

In the Centerline field set I already had fields named L_F_ADD, L_T_ADD, R_F_ADD, and R_T_ADD which contained my house number ranges and these are contained in the overlay output. In my table they are text fields. So I will blank these out with the Field Calculator to recreate them or create a new set of fields to hold similar values that are blank to compare them at the end.

My first attempt a filling the field L_F_ADD field in for the Ends_Even_Left_Overlay was the following:

Fist I have to select only the records that were the from end of the centerline segments. I also have to exclude any zero length lines that were contained in the House number data, where two more more houses fell at the same position as a line end. Typically this only occurs for addresses that fall at or beyond one of the line ends, such as Cul-de-Sacs (which will be a special case). So I use the SQL:

"FROM_OR_TO" = 'From_End' AND "FROM_MEAS" <> "TO_MEAS"

My first attempt to calculate the L_F_ADD (since my original set of addresses were on the Left side) was:

Round(( [TO_HOUSE_NUMBER] - [FROM_HOUSE_NUMBER] ) * ( [meas] - [FROM_MEAS] ) / ( [TO_MEAS] - [FROM_MEAS] ) / 2, 0) * 2 + [FROM_HOUSE_NUMBER]

This gives me the correct even house number that falls exactly at the end point if it had be a reverse geocoded point. This same formula works to get the reverse geocoded even house number for the L_T_ADD field also. However, This formula will assign the same value to two centerline segments that meet at their To and From ends. What I want is to have the house numbers separated by 2 houses at these meeting segment ends. So this has to be done as a Codeblock calculation with more advanced logic.

This is what I came up with for both of the From Address fields, L_F_ADD and R_F_ADD, for both odd and even house number ranges:

Parser: VB Script
Show Codeblock: Checked
Prelogic Script Code:

Raw_House = ( [TO_HOUSE_NUMBER] - [FROM_HOUSE_NUMBER] ) * ( [meas] - [FROM_MEAS] ) / ( [TO_MEAS] - [FROM_MEAS] ) + [FROM_HOUSE_NUMBER] 
Int_House = Round(( [TO_HOUSE_NUMBER] - [FROM_HOUSE_NUMBER] ) * ( [meas] - [FROM_MEAS] ) / ( [TO_MEAS] - [FROM_MEAS] ) / 2, 0) * 2 + [FROM_HOUSE_NUMBER] 

If [FROM_HOUSE_NUMBER] < [TO_HOUSE_NUMBER] Then
  If Int_House < Raw_House Then
    Output = Int_House
  Else
    Output = Int_House - 2
  End If
Else
  If Int_House > Raw_House Then
    Output = Int_House
  Else
    Output = Int_House + 2
  End If
End If

L_F_ADD = Output

Here is the calculation that works for both of the To Address fields, L_T_ADD and R_T_ADD, for both odd and even house number ranges:

Parser: VB Script
Show Codeblock: Checked
Prelogic Script Code:

Raw_House = ( [TO_HOUSE_NUMBER] - [FROM_HOUSE_NUMBER] ) * ( [meas] - [FROM_MEAS] ) / ( [TO_MEAS] - [FROM_MEAS] ) + [FROM_HOUSE_NUMBER] 
Int_House = Round(( [TO_HOUSE_NUMBER] - [FROM_HOUSE_NUMBER] ) * ( [meas] - [FROM_MEAS] ) / ( [TO_MEAS] - [FROM_MEAS] ) / 2, 0) * 2 + [FROM_HOUSE_NUMBER] 

If [FROM_HOUSE_NUMBER] < [TO_HOUSE_NUMBER] Then
  If Int_House < Raw_House Then
    Output = Int_House + 2
  Else
    Output = Int_House
  End If
Else
  If Int_House > Raw_House Then
    Output = Int_House - 2
  Else
    Output = Int_House
  End If
End If

L_F_ADD = Output

After calculating the L_F_ADD and L_T_ADD for my Even Left Addresses (I would not calculate the Right side fields) I can get the range in a single row for each centerline segment by running the Summary Statistics tool with the following settings summary and sort field settings:

Summary fields:
Meas Min
Meas Max
L_F_ADD Max
L_T_ADD Max
FROM_HOUSE_NUMBER MIN
FROM_HOUSE_NUMBER MAX
TO_HOUSE_NUMBER MIN
TO_HOUSE_NUMBER MAX

Case Fields:
RID
ORIG_OID (Created by the Feature Vertices to Points tool from the Centerlines).

If you want any other fields in the summary output for joining or validation, if they came from the Centerlines data they can be placed in the Case fields, while any fields from the house number lines has to be placed in the Summary fields.

The centerlines with only one segment end between the first and last house numbers and the other end outside of the first and last house numbers will have a blank in either the MAX_L_F_ADD or the MAX_L_T_ADD fields. You can also check for any House numbers on really short segments and where house numbers failed to space apart by looking for MAX_L_F_ADD = MAX_L_T_ADD records, which will be zero length line events, and records that crossed over in the wrong direction, i.e.,

(MIN_FROM_HOUSE_NUMBER < MIN_TO_HOUSE_NUMBER AND MAX_L_F_ADD > MAX_L_T_ADD) OR (MIN_FROM_HOUSE_NUMBER > MIN_TO_HOUSE_NUMBER AND MAX_L_F_ADD < MAX_L_T_ADD)

You can work with the summary output as a line event table to see your recreated Centerlines with even left house number ranges where both ends fell between the actual house numbers as a preview before committing them to your original centerlines.

So working out Cul-de-Sacs and segments that fell outside of the actual House numbers will be for later posts.

Re: Assigning Address Ranges from nearby Address Points

RichardFairhurst — Fri, 16 Aug 2013 16:04:18 GMT

Here is a screen shot of the results of what I have done so far. The Even Left house address points are shown as blue circles next to the full centerline network. The thickened purple lines have full address ranges assigned on each end. The Red circle points on the line ends have L_F_ADD numbers, but not L_T_ADD numbers and the Green triangle points on the line have L_T_ADD numbers, but no L_F_ADD numbers. The other end of these larger points on the line fell outside of the actual house ranges and have to be projected using a different method.

Several of the cul-de-sacs could have had house numbers assigned, but were affected by the zero length line effect due to having multiple houses fall around the bulb part of the Centerline.

Re: Assigning Address Ranges from nearby Address Points

RichardFairhurst — Sat, 11 Dec 2021 13:53:41 GMT

Now I am moving on to the issue of addressing segments that fall outside of the actual address points. To do that I need to extract the first and last address in a range. This will only work if the addresses are continuously increasing or decreasing. A range split due to jurisdictional changes or where the direction of addresses changes, like many cities do to divide roads that are north/south or east/west of a central road, have to be isolated and handled separately on each side of the address split.

I used the Summary Statistics tool on the Even Left Address Range line segment events I created for all of those house numbers. Here are the summary fields and case fields:

Summary Fields:
FROM_MEAS MIN
TO_MEAS MAX
MIN_HOUSE_NUMBER MIN
MAX_HOUSE_NUMBER MAX

Case Fields:
RID

The result of the summary is shown in a screen shot as a line event with thick pink lines indicating the actual coverage of a set of Even Left address points over a route.

The Min and Max measures and house numbers will not necessary pair together, but will be used to detect ascending ranges, descending ranges and split ascending/descending ranges.

To do that I first add a text field with 10 characters called ASC_DESC to the Summary table. Then I joined the Summary to the set of Even Left Points that were sorted on the RID fields. Then To find Ascending Routes I used first use the following selection:

ADDRESS_EVEN_LEFT_Sort.MEAS = ADDRESS_EVEN_LEFT_Lines_Sum.MIN_FROM_MEAS AND ADDRESS_EVEN_LEFT_Lines_Sum.MIN_MIN_HOUSE_NUMBER = ADDRESS_EVEN_LEFT_Sort.HOUSE_NUMBER

Then I break the join and create a relate between the two tables on the RID field and perform the relate from the selected set of records in the ADDRESS_EVEN_LEFT_Sort table to the ADDRESS_EVEN_LEFT_Lines_Sum table and then back the other way around. The I rejoin the tables as I had done previously and perform a Select from the Current Selection query using the following expression:

ADDRESS_EVEN_LEFT_Sort.MEAS = ADDRESS_EVEN_LEFT_Lines_Sum.MAX_TO_MEAS AND ADDRESS_EVEN_LEFT_Lines_Sum.MAX_MAX_HOUSE_NUMBER = ADDRESS_EVEN_LEFT_Sort.HOUSE_NUMBER

Then I break that join, recreate the relate between the two tables and perform it again from the selected set of records in the ADDRESS_EVEN_LEFT_Sort table to the ADDRESS_EVEN_LEFT_Lines_Sum table. I calculate the ASC_DESC field of these routes as Ascending.

Then I perform the same step only this time I use these two queries (first one as new selection and second as select from current selection):

ADDRESS_EVEN_LEFT_Sort.MEAS = ADDRESS_EVEN_LEFT_Lines_Sum.MIN_FROM_MEAS AND ADDRESS_EVEN_LEFT_Lines_Sum.MAX_MAX_HOUSE_NUMBER = ADDRESS_EVEN_LEFT_Sort.HOUSE_NUMBER

and

ADDRESS_EVEN_LEFT_Sort.MEAS = ADDRESS_EVEN_LEFT_Lines_Sum.MAX_TO_MEAS AND ADDRESS_EVEN_LEFT_Lines_Sum.MIN_MIN_HOUSE_NUMBER = ADDRESS_EVEN_LEFT_Sort.HOUSE_NUMBER

After performing the relates back and forth I sort descending on the ASC_DESC field. Any records that already contain an "Ascending" value are actually routes with zero length measures. I will reselect the routes with an "Ascending" value and recalculate them as "No Length". I perform the relate to the Summary table again, deselect the routes with a value of "No Length" in the ASC_DESC field and calculate the rest of the ASC_DESC values of the Summary table for these routes as "Descending".

The rest of the routes that are still null are not Ascending, Descending, or No Length. They are mixed addresses that reversed direction at some point within the address set. So I select where the ASC_DESC field is null and calculate the ASC_DESC field as "Mixed".

As a final check I did a selection in my summary table for all cases where ASC_DESC IN ('Ascending', 'Descending') AND MIN_MIN_HOUSE_NUMBER = MAX_MAX_HOUSE_NUMBER, but did not find any. had I found some I would have recalculated the ASC_DESC value of these records as "One House".

I resymbolized the summary line events to show the ASC_DESC categories on my routes. The legend shows the pink lines are Ascending, the yellow lines are Descending. The other two categories were rare and do not occur within the current extent, but they do occur elsewhere in my data. The No Length lines will have Event errors and will not appear at all.

Next I calculate the measure distance as a unit length separating the average house number in one house number intervals. So I add a double field called HOUSE_INTERVAL. I then select all of the Ascending and Descending Routes and calculate the value of this field as:

If [ASC_DESC] = "Ascending" then
  Output = ([MAX_TO_MEAS] - [MIN_FROM_MEAS]) / ( [MAX_MAX_HOUSE_NUMBER] - [MIN_MIN_HOUSE_NUMBER])
ElseIf [ASC_DESC] = "Descending" then
  Output = ([MIN_FROM_MEAS] - [MAX_TO_MEAS]) / ( [MAX_MAX_HOUSE_NUMBER] - [MIN_MIN_HOUSE_NUMBER])
End If

I then set the table view to only show selected records and sort the HOUSE_INTERVAL field Ascending to find any extremely small intervals (less than 2 feet to -2 feet apart) and will exclude them for examination later. I also exclude the largest intervals (more than 10 feet or less than -10 feet apart). Large separations could apply in commercial districts, but I would want to examine them, because they could also indicate that those addresses contain a hidden Mixed ascending/descending address range set. They also could indicate an odd ball address is skewing the interval.

I am going to stop this post here. But next I will go about assigning address ranges to unassigned segments that do not fall between actual address points based on these intervals, where the route ascending/descending value makes sense.

Re: Assigning Address Ranges from nearby Address Points

LangdonSanders3 — Mon, 19 Aug 2013 18:40:08 GMT

Thank you Richard,

Your work on this is clear, well written, and greatly appreciated. I have a couple other tickets I'm working on this week so my update here on this project will be a bit delayed.

Langdon

Re: Assigning Address Ranges from nearby Address Points

RichardFairhurst — Sat, 11 Dec 2021 13:53:44 GMT

Continuing forward with assigning to the full set of centerline segments. I found that I had not created line segment events from the Centerline end points. To do that I run the Summary Statistics tool on the Ends point Locate Features on Route run with the following settings:

Summary Field:
MEAS MIN
MEAS MAX

Case Field:
RID
ORIG_FID
STNAME (My Street Name Field)
L_F_ADD
L_T_ADD
R_F_ADD
R_T_ADD

I Joined these events on the ORIG_FID to the events I had created using the Overlay Route Events tool that merged the Centerline Segments to the Address Point data. I transfer the L_F_ADD and L_T_ADD values over with the field calculator. Then I break that join.

Now I create a relate on the RID field from the selected Summary that were Ascending and Descending to the Centerline segment events table. The selection should match:

"ASC_DESC" IN ( 'Ascending', 'Descending') AND (("HOUSE_INTERVAL" > 2 AND "HOUSE_INTERVAL" < 10) OR ("HOUSE_INTERVAL" > -10 AND "HOUSE_INTERVAL" < -2))

Then I Select from the Current Selection those Centerline events where L_F_ADD is blank.

"L_F_ADD" <= ' '

Then I join that selection to the Summary Table on the RID fields. Then I calculate the L_F_ADD field to be:

Parser: VB Script
Show Codeblock: Checked
Prelogic Script Code:

If [ADDRESS_EVEN_LEFT_Lines_Sum.ASC_DESC] = "Ascending" AND [CL_ENDS_LINES.MIN_MEAS] < [ADDRESS_EVEN_LEFT_Lines_Sum.MIN_FROM_MEAS] Then
  Raw = ([CL_ENDS_LINES.MIN_MEAS] - [ADDRESS_EVEN_LEFT_Lines_Sum.MIN_FROM_MEAS]) * [ADDRESS_EVEN_LEFT_Lines_Sum.HOUSE_INTERVAL]  + [ADDRESS_EVEN_LEFT_Lines_Sum.MIN_MIN_HOUSE_NUMBER] 
  Output = Round(([CL_ENDS_LINES.MIN_MEAS] - [ADDRESS_EVEN_LEFT_Lines_Sum.MIN_FROM_MEAS]) * [ADDRESS_EVEN_LEFT_Lines_Sum.HOUSE_INTERVAL]/2, 0) * 2  + [ADDRESS_EVEN_LEFT_Lines_Sum.MIN_MIN_HOUSE_NUMBER] 
  If Output < Raw Then Output = Output + 2
ElseIf [ADDRESS_EVEN_LEFT_Lines_Sum.ASC_DESC] = "Ascending" AND [CL_ENDS_LINES.MIN_MEAS] > [ADDRESS_EVEN_LEFT_Lines_Sum.MAX_TO_MEAS] Then
  Raw = ([CL_ENDS_LINES.MIN_MEAS] - [ADDRESS_EVEN_LEFT_Lines_Sum.MAX_TO_MEAS]) * [ADDRESS_EVEN_LEFT_Lines_Sum.HOUSE_INTERVAL]  + [ADDRESS_EVEN_LEFT_Lines_Sum.MAX_MAX_HOUSE_NUMBER] 
  Output = Round(([CL_ENDS_LINES.MIN_MEAS] - [ADDRESS_EVEN_LEFT_Lines_Sum.MAX_TO_MEAS]) * [ADDRESS_EVEN_LEFT_Lines_Sum.HOUSE_INTERVAL]/2, 0) * 2  + [ADDRESS_EVEN_LEFT_Lines_Sum.MAX_MAX_HOUSE_NUMBER] 
  If Output < Raw Then Output = Output + 2
ElseIf [ADDRESS_EVEN_LEFT_Lines_Sum.ASC_DESC] = "Descending" AND [CL_ENDS_LINES.MIN_MEAS] < [ADDRESS_EVEN_LEFT_Lines_Sum.MIN_FROM_MEAS] Then
  Raw = ([CL_ENDS_LINES.MIN_MEAS] - [ADDRESS_EVEN_LEFT_Lines_Sum.MIN_FROM_MEAS]) * [ADDRESS_EVEN_LEFT_Lines_Sum.HOUSE_INTERVAL]  + [ADDRESS_EVEN_LEFT_Lines_Sum.MAX_MAX_HOUSE_NUMBER] 
  Output = Round(([CL_ENDS_LINES.MIN_MEAS] - [ADDRESS_EVEN_LEFT_Lines_Sum.MIN_FROM_MEAS]) * [ADDRESS_EVEN_LEFT_Lines_Sum.HOUSE_INTERVAL]/2, 0) * 2  + [ADDRESS_EVEN_LEFT_Lines_Sum.MAX_MAX_HOUSE_NUMBER] 
  If Output > Raw Then Output = Output - 2
ElseIf [ADDRESS_EVEN_LEFT_Lines_Sum.ASC_DESC] = "Descending" AND [CL_ENDS_LINES.MIN_MEAS] > [ADDRESS_EVEN_LEFT_Lines_Sum.MAX_TO_MEAS] Then
  Raw = ([CL_ENDS_LINES.MIN_MEAS] - [ADDRESS_EVEN_LEFT_Lines_Sum.MAX_TO_MEAS]) * [ADDRESS_EVEN_LEFT_Lines_Sum.HOUSE_INTERVAL]  + [ADDRESS_EVEN_LEFT_Lines_Sum.MIN_MIN_HOUSE_NUMBER] 
  Output = Round(([CL_ENDS_LINES.MIN_MEAS] - [ADDRESS_EVEN_LEFT_Lines_Sum.MAX_TO_MEAS]) * [ADDRESS_EVEN_LEFT_Lines_Sum.HOUSE_INTERVAL]/2, 0) * 2  + [ADDRESS_EVEN_LEFT_Lines_Sum.MIN_MIN_HOUSE_NUMBER] 
  If Output > Raw Then Output = Output - 2
Else
  Output = ""
End If

L_F_ADD = Output

Then I break the join and perform the relate on the RID field from the selected Summary that were Ascending and Descending to the Centerline segment events table. The selection should match:

"ASC_DESC" IN ( 'Ascending', 'Descending') AND (("HOUSE_INTERVAL" > 2 AND "HOUSE_INTERVAL" < 10) OR ("HOUSE_INTERVAL" > -10 AND "HOUSE_INTERVAL" < -2))

Then I Select from the Current Selection those Centerline events where L_T_ADD is blank.

"L_T_ADD" <= ' '

Then I join that selection to the Summary Table on the RID fields. Then I calculate the L_T_ADD field to be:

Parser: VB Script
Show Codeblock: Checked
Prelogic Script Code:

If [ADDRESS_EVEN_LEFT_Lines_Sum.ASC_DESC] = "Ascending" AND [CL_ENDS_LINES.MAX_MEAS] < [ADDRESS_EVEN_LEFT_Lines_Sum.MIN_FROM_MEAS] Then
  Raw = ([CL_ENDS_LINES.MAX_MEAS] - [ADDRESS_EVEN_LEFT_Lines_Sum.MIN_FROM_MEAS]) * [ADDRESS_EVEN_LEFT_Lines_Sum.HOUSE_INTERVAL]  + [ADDRESS_EVEN_LEFT_Lines_Sum.MIN_MIN_HOUSE_NUMBER] 
  Output = Round(([CL_ENDS_LINES.MAX_MEAS] - [ADDRESS_EVEN_LEFT_Lines_Sum.MIN_FROM_MEAS]) * [ADDRESS_EVEN_LEFT_Lines_Sum.HOUSE_INTERVAL]/2, 0) * 2  + [ADDRESS_EVEN_LEFT_Lines_Sum.MIN_MIN_HOUSE_NUMBER] 
  If Output > Raw Then Output = Output - 2
ElseIf [ADDRESS_EVEN_LEFT_Lines_Sum.ASC_DESC] = "Ascending" AND [CL_ENDS_LINES.MAX_MEAS] > [ADDRESS_EVEN_LEFT_Lines_Sum.MAX_TO_MEAS] Then
  Raw = ([CL_ENDS_LINES.MAX_MEAS] - [ADDRESS_EVEN_LEFT_Lines_Sum.MAX_TO_MEAS]) * [ADDRESS_EVEN_LEFT_Lines_Sum.HOUSE_INTERVAL]  + [ADDRESS_EVEN_LEFT_Lines_Sum.MAX_MAX_HOUSE_NUMBER] 
  Output = Round(([CL_ENDS_LINES.MAX_MEAS] - [ADDRESS_EVEN_LEFT_Lines_Sum.MAX_TO_MEAS]) * [ADDRESS_EVEN_LEFT_Lines_Sum.HOUSE_INTERVAL]/2, 0) * 2  + [ADDRESS_EVEN_LEFT_Lines_Sum.MAX_MAX_HOUSE_NUMBER] 
  If Output > Raw Then Output = Output - 2
ElseIf [ADDRESS_EVEN_LEFT_Lines_Sum.ASC_DESC] = "Descending" AND [CL_ENDS_LINES.MAX_MEAS] < [ADDRESS_EVEN_LEFT_Lines_Sum.MIN_FROM_MEAS] Then
  Raw = ([CL_ENDS_LINES.MAX_MEAS] - [ADDRESS_EVEN_LEFT_Lines_Sum.MIN_FROM_MEAS]) * [ADDRESS_EVEN_LEFT_Lines_Sum.HOUSE_INTERVAL]  + [ADDRESS_EVEN_LEFT_Lines_Sum.MAX_MAX_HOUSE_NUMBER] 
  Output = Round(([CL_ENDS_LINES.MAX_MEAS] - [ADDRESS_EVEN_LEFT_Lines_Sum.MIN_FROM_MEAS]) * [ADDRESS_EVEN_LEFT_Lines_Sum.HOUSE_INTERVAL]/2, 0) * 2  + [ADDRESS_EVEN_LEFT_Lines_Sum.MAX_MAX_HOUSE_NUMBER] 
  If Output < Raw Then Output = Output + 2
ElseIf [ADDRESS_EVEN_LEFT_Lines_Sum.ASC_DESC] = "Descending" AND [CL_ENDS_LINES.MAX_MEAS] > [ADDRESS_EVEN_LEFT_Lines_Sum.MAX_TO_MEAS] Then
  Raw = ([CL_ENDS_LINES.MAX_MEAS] - [ADDRESS_EVEN_LEFT_Lines_Sum.MAX_TO_MEAS]) * [ADDRESS_EVEN_LEFT_Lines_Sum.HOUSE_INTERVAL]  + [ADDRESS_EVEN_LEFT_Lines_Sum.MIN_MIN_HOUSE_NUMBER] 
  Output = Round(([CL_ENDS_LINES.MAX_MEAS] - [ADDRESS_EVEN_LEFT_Lines_Sum.MAX_TO_MEAS]) * [ADDRESS_EVEN_LEFT_Lines_Sum.HOUSE_INTERVAL]/2, 0) * 2  + [ADDRESS_EVEN_LEFT_Lines_Sum.MIN_MIN_HOUSE_NUMBER] 
  If Output < Raw Then Output = Output + 2
Else
  Output = ""
End If

L_T_ADD = Output

I am now double checking the calculations that separated each range by 2 where s From and To measure meet on separate segments, since that is the most confusing thing to get correct and I may have made an error in one or more of the calculations when adjusting up or down by 2 house numbers. It appears when sequential houses are equal or reversing order.

I see a few instances, particularly when the order is descending house numbers relative to ascending measures. That would be the most confusing case. The error occurred in one of the calculations in a previous post when I was doing the ranges within the address point values.

Both calculations turned out to be in error for the From and To measures in the case of a descending House number order. The correct calculations are:

For the From House Numbers between actual addresses the calculation should have been:

Raw_House = ( [TO_HOUSE_NUMBER] - [FROM_HOUSE_NUMBER] ) * ( [meas] - [FROM_MEAS] ) / ( [TO_MEAS] - [FROM_MEAS] ) + [FROM_HOUSE_NUMBER] 
Int_House = Round(( [TO_HOUSE_NUMBER] - [FROM_HOUSE_NUMBER] ) * ( [meas] - [FROM_MEAS] ) / ( [TO_MEAS] - [FROM_MEAS] ) / 2, 0) * 2 + [FROM_HOUSE_NUMBER] 

If [FROM_HOUSE_NUMBER] < [TO_HOUSE_NUMBER] Then
  If Int_House < Raw_House Then
    Output = Int_House + 2
  Else
    Output = Int_House
  End If
Else
  If Int_House > Raw_House Then
    Output = Int_House - 2
  Else
    Output = Int_House
  End If
End If

For the To House Numbers between actual addresses the calculation should have been:

Raw_House = ( [TO_HOUSE_NUMBER] - [FROM_HOUSE_NUMBER] ) * ( [meas] - [FROM_MEAS] ) / ( [TO_MEAS] - [FROM_MEAS] ) + [FROM_HOUSE_NUMBER] 
Int_House = Round(( [TO_HOUSE_NUMBER] - [FROM_HOUSE_NUMBER] ) * ( [meas] - [FROM_MEAS] ) / ( [TO_MEAS] - [FROM_MEAS] ) / 2, 0) * 2 + [FROM_HOUSE_NUMBER] 

If [FROM_HOUSE_NUMBER] < [TO_HOUSE_NUMBER] Then
  If Int_House < Raw_House Then
    Output = Int_House
  Else
    Output = Int_House - 2
  End If
Else
  If Int_House > Raw_House Then
    Output = Int_House 
  Else
    Output = Int_House + 2
  End If
End If

After rerunning these calculations, summarizing the line events from the centerlines actually between addressed and transferring the house numbers between actual ranges to the full set of centerline segments, all seems correct now.

A few ranges went negative, which would have to also be checked.

Re: Assigning Address Ranges from nearby Address Points

RichardFairhurst — Mon, 19 Aug 2013 19:25:37 GMT

Here is a screen shot of the result for the Even Left Addresses. Lines that are dark blue have normal address ranges. Lines that are red have no address on at least one end of the segment. The light blue selected lines have negative addresses.

Re: Assigning Address Ranges from nearby Address Points

RichardFairhurst — Tue, 20 Aug 2013 21:07:44 GMT

I have examined my outcome some more and found a few other situations I had not anticipated. When I extracted the Ends from the Centerlines and calculated which point was the From end and which was the To end, I had not accounted for some segments being oriented in the opposite direction from my route. While I would retain the identification of the original end point orientation for the final transfer back to the actual centerlines, for purposes of assigning addresses I would disregard the actual orientation of the segment and use the route orientation of the segments (low From measure to high To measure). This keeps addresses that are left of the route on the left of every segment and simplifies assigning ranges significantly.

So after assigning a From and To measure for each end based on the tool extraction order (From end first, To end second), I should have done the Locate Feature on Route tool, built the line segments and then joined those line segments back to the end points to select reversed centerline end points. If the Min_Meas of the line event is equal to the To end point and the Max_Meas of line events is equal tp the From end point, the centerline is reversed. I would also have added a Route_From_To field and recalculated the From and To point to be opposite of the original line for the reversed centelines, while confirming the From and To orientation of segments that went with the route. Then I should have sorted the end points on the Route based orientation field before doing the Overlay Route Events operation. Keeping everything consistent with the route's one orientation makes confirmation of correct address patterns and detection of anomalies much easier.

At the final step when I would transfer my ranges back to the original centerlines, I would still have to separate out all of the centerline segments where the segment orientation was opposite of the route orientation. These segments would reverse both Left and Right side addresses and From and To addresses. As a result, at the final step, L_F_ADD of the route would fill in R_T_ADD of the centerline, L_T_ADD of the route would fill in R_F_ADD of the centerline, R_F_ADD of the route would fill in L_T_ADD of the centerline and R_T_ADD of the route would fill in L_F_ADD of the centerline.