topic Re: Dissolve by Ranges - Table Data in Spatial Data Science Questions

Dissolve by Ranges - Table Data

MarkVolz — Thu, 28 Jul 2016 15:36:39 GMT

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:

2^nd St 100-149

2^nd St 150-199

2^nd St 200-299

Tabular Dataset:

2^nd St 100-199

2^nd St 200-299

In theory I could either use dissolve or unsplit line to convert the GIS layer into a range such as 2^nd 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:

2^nd St 100-299, EMERGENCY ZONE 1

2^nd St 300-799, EMERGENCY ZONE 2

2^nd St 800-999, EMERGENCY ZONE 3

Tabular Dataset:

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

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

2^nd 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.

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

Re: Dissolve by Ranges - Table Data

DanPatterson_Retired — Fri, 29 Jul 2016 02:10:30 GMT

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.

Re: Dissolve by Ranges - Table Data

ChrisDonohue__GISP — Fri, 29 Jul 2016 02:55:14 GMT

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

Re: Dissolve by Ranges - Table Data

MarkVolz — Fri, 29 Jul 2016 13:43:19 GMT

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.