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 Name | Low | High |
---|---|---|
2nd St | 100 | 199 |
2nd St | 300 | 399 |
2nd St | 400 | 599 |
Desired Result - Gaps in data detected
Header 1 | Header 2 | Header 3 |
---|---|---|
2nd St | 100 | 199 |
2nd St | 300 | 599 |
Acceptable Result
Header 1 | Header 2 | Header 3 |
---|---|---|
2nd St | 100 | 599 |
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.
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.
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:
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.
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