Dissolve by Ranges - Table Data

1463
3
07-28-2016 08:36 AM
MarkVolz
Occasional Contributor III

Hello,

Is there a way to dissolve tabular data by a range?  It would be best if there was a way to detect gaps.

Thank You!

Example

Original Tabular Data

Street NameLowHigh
2nd St100199
2nd St300399
2nd St400599

Desired Result - Gaps in data detected

Header 1Header 2Header 3
2nd St100199
2nd St300599

Acceptable Result

Header 1Header 2Header 3
2nd St100599

More detailed Background:

I am trying to compare the emergency responders between a GIS shapefile that I have and a tabular dataset.  I was able to create a unique id so I could join the tabular data to the GIS data. The Unique ID is more or less: [Street]+[Address From]+[Address To]. My initial join has a match rate of about 15%.

While inspecting the datasets I noticed that the Shapefile layer and Tabular dataset break down the low and high ranges differently – which is causing most of my mismatches. For example:

GIS:

2nd St 100-149

2nd St 150-199

2nd St 200-299

Tabular Dataset:

2nd St 100-199

2nd St 200-299

In theory I could either use dissolve or unsplit line to convert the GIS layer into a range such as 2nd St 100-299.  However, I need to find a way to do the same on the tabuar dataset.

Question:  Is there any function in ArcGIS or even another program that will dissolve tabular datasets by rangesso that I can compare the GIS layer and the tabular dataset?  It would even be better if I could check for unusual gaps in the tabular dataset such as:

GIS:

2nd St 100-299, EMERGENCY ZONE 1

2nd St 300-799, EMERGENCY ZONE 2

2nd St 800-999, EMERGENCY ZONE 3

Tabular Dataset:

2nd St 100-299, EMERGENCY ZONE 1 – This record will join.

2nd St 400-499, EMERGENCY ZONE 2  - This record will not join because there is a gap.  We will need to fix it.

2nd St 700-799, EMERGENCY ZONE 2 – This record will not join because there is a gap.  We will need to fix this record in the database.

2nd St 800-999, EMERGENCY ZONE 3 – This record will join.

0 Kudos
3 Replies
DanPatterson_Retired
MVP Emeritus

an interesting problem.  I would suggest that you first identify the unique values in the from and to fields (if they are indeed unique fields) then it is possible to 'digitize' into a new set of ranges.  If you had a slightly larger data set and what you expect the standards to be.  I would presume that you would want to use the road segment address ranges as the final standard.

0 Kudos
MarkVolz
Occasional Contributor III

Dan,

I apologize I should have further explained I ultimately would like to compare a street network with tabular data to a 3rd party tabular (non gis).  I was able to figure out how to create similar unique names by using a combination of the full street name and the address low and address high range.   In theory that should have been enough to join the two datasets together.  However, I found out that the GIS layer has different range breaks than the tabular file.

Since my ranges are broken differently between the tabular dataset and GIS dataset, I figure I can at least try to compare the lowest address to the highest address for each street.  In GIS this can be done quite quickly by using dissolve and setting a statistics field for the min start address, and the max end address.  However, in order to compare these datasets I need to also dissolve the tabular dataset by the address range.  Once the tabular dataset is dissolved by range, then I should have common unique ids to compare the spatial dataset to the tabular dataset.

Update:

I am working with "Pivot Table" in Excel to dissolve the tabular dataset.  I can do this by adding all the variables I need as rows, and changing the sum values to min of low and max of high.  Pivot table is not as user friendly as I would like to be as I need to modify each rows field setting so that:

  • Subtotals are set to None
  • Items are shown in tabular form
  • Repeat item labels is set to on

Hopefully pivot table will allow me to at least compare the address range for each street and verify if they have the same emergency service providers.

0 Kudos
ChrisDonohue__GISP
MVP Alum

Let me add in a group of people who work with this sort of data alot and probably have run into similar issues:  Addressing

Also, I almost wonder if there is not already an ESRI LGIM addressing tool/function available that detects for these sort of gaps in address ranges.

Chris Donohue, GISP

0 Kudos