topic Re: dbf-to-shp attribute join gives dofferent result than shp-to-shp attribute join. in Data Management Questions

dbf-to-shp attribute join gives dofferent result than shp-to-shp attribute join.

DionGood — Fri, 29 Oct 2021 22:23:41 GMT

Why is it that a dbf-to-shp attribute join results in a different number of joins than a shp-to-shp attribute join of the same data? When I join the attribute table of one shapefile to another shapefile I get a certain number of joins. But when I export the table to dbf and join it the the same shapefile, I get a different (greater) number of joins. Shouldn't both routines result in the same number of joins? It is the same data, after all. Please help, this is really messing up my analysis. I am using ArcGIS Desktop.

Re: dbf-to-shp attribute join gives dofferent result than shp-to-shp attribute join.

DanPatterson — Fri, 29 Oct 2021 23:04:07 GMT

on the FID (aka object ID field) or are you using an attribute

multipart shapes will cause a difference.

you will need to elaborate on what fields you are joining and whether the values in both tables have unique entries for each join

Re: dbf-to-shp attribute join gives dofferent result than shp-to-shp attribute join.

DionGood — Sat, 30 Oct 2021 00:24:41 GMT

I am joining a shapefile of broadband deployment data aggregated to Census blocks to a shapefile of Census blocks containing urban/rural designations and households for each Census bock. The key field is not the FID field, it is the Census block number field. I don't know what you mean by a multi-part shapefile. The broadband data shapefile contains a total of 982,015 features, including multiple instances of many Census blocks, depending upon how many different providers are offering service in each, how many different technology types are deployed in each, and how many different downstream and upstream speed combinations are offered by providers in each. The Census blocks shapefile contains 519,723 features - one record for each unique Census block.

I want to find out how many urban and rural households are in Census blocks where the maximum advertised downstream and upstream speeds are at least 25mbps/3mbps. So I created a definition query in the broadband shapefile to show only Census blocks at 25/3 or greater. This reduced the size of the attribute table from 982,015 records to 654,582 records. Then I performed an attribute join of the broadband shapefile to the Census block shapefile, keeping only joined records. The join reduced the Census block shapefile from 519,723 features to 256,773 features. In other words, 256,773 of the 519,723 Census blocks in California have 25/3 broadband service somewhere in them. Then I summed the households field and got 10,289,120, meaning that there are 10,289,120 households in Census blocks where 25/3 service is offered in California. Then I created selection sets in the Census shapefile's attribute table to find out how many of the 10,289,120 households were in urban and rural areas. So far, so good.

However, upon checking my work by using a different method (exporting the definition query from the broadband shapefile to a stand alone dbf, then joining the dbf to the Census block shapefile) I arrived at the result that 25/3 broadband is available in 317,712 unique Census blocks containing 12,741,341 households. I remain shocked and perplexed, as I thought a shp-to-shp attribute join would be functionally the same in this case as a dbf-to-shp attribute join. Can you please explain why this is not the case? And which method gives the correct result without double-counting households? I am not looking to extract information from specific Census blocks, I am only interested in the overall totals of how many households are in Census blocks where ANY provider offers broadband at 25/3 or more.

Thanks.

Re: dbf-to-shp attribute join gives dofferent result than shp-to-shp attribute join.

DanPatterson — Sat, 30 Oct 2021 03:14:44 GMT

The order of the join is important

shp-dbf to dbf vs dbf to shp-dbf

because

Records from the Join Table can be matched to more than one record in the Input Table.

Join Field (Data Management)—ArcGIS Pro | Documentation

and

Add Join (Data Management)—ArcGIS Pro | Documentation

one-one vs one-many

Re: dbf-to-shp attribute join gives dofferent result than shp-to-shp attribute join.

DionGood — Sat, 30 Oct 2021 20:49:54 GMT

Yes, I am aware of this, but in this case it shouldn't matter because I am joining a shp table or dbf with multiple instances of specific Census blocks to a shapefile with only one instance of each unique Census block. I don't care which 25/3 record from the broadband file gets joined to the Census block file, as long as the Census block file registers a join for that Census block, meaning that 25/3 exists in the block. The only way I can make logical sense of this is if NO joins occur where there are many-to-one relationships. But why would this be the case for a shapefile table and not for a stand alone dbf? The same logic should apply.

Re: dbf-to-shp attribute join gives dofferent result than shp-to-shp attribute join.

ABishop — Mon, 01 Nov 2021 17:00:53 GMT

It sounds like you want a many to one relationship in your join? To accomplish this, you could use another method of analysis called overlay. I did this recently in ArcGIS Online. Do you have a way to test it?

Re: dbf-to-shp attribute join gives dofferent result than shp-to-shp attribute join.

DionGood — Mon, 01 Nov 2021 18:13:49 GMT

Yes, I want a many-to-one relationship, but I am only interested in the tabular data, not the spatial dimension.

Re: dbf-to-shp attribute join gives dofferent result than shp-to-shp attribute join.

ABishop — Mon, 01 Nov 2021 18:17:49 GMT

What version of desktop are you using?

Re: dbf-to-shp attribute join gives dofferent result than shp-to-shp attribute join.

DionGood — Mon, 01 Nov 2021 18:46:09 GMT

10.6.1

Re: dbf-to-shp attribute join gives dofferent result than shp-to-shp attribute join.

ABishop — Mon, 01 Nov 2021 18:50:57 GMT

In ArcGIS Pro its called "Overlay"... and in ArcGIS Desktop its called "Intersect". The overall aspect of the tool is a spatial function, but the output is your key to generating the data table you want. After you intersect the layers, the data will be output to a single feature (shapefile) which you can then use the table to table geoprocessing tool to extract the tabular data of your choice. Here is a link to read up on exactly what it does:

https://desktop.arcgis.com/en/arcmap/10.6/tools/analysis-toolbox/how-intersect-analysis-works.htm

Re: dbf-to-shp attribute join gives dofferent result than shp-to-shp attribute join.

DionGood — Mon, 01 Nov 2021 23:35:31 GMT

I am familiar with intersect, but I am not sure that the correct records will get matched. I have not used it in this way before.

Re: dbf-to-shp attribute join gives dofferent result than shp-to-shp attribute join.

ABishop — Tue, 02 Nov 2021 11:59:59 GMT

Maybe just try it and see if it works? Its worth a shot.

Re: dbf-to-shp attribute join gives dofferent result than shp-to-shp attribute join.

DionGood — Wed, 03 Nov 2021 20:27:35 GMT

I have accepted the dbf-to-shp attribute join as the correct result, although I still don't understand why this would be. I can't consider this case "resolved" until I do. The articles were not really helpful.

Re: dbf-to-shp attribute join gives dofferent result than shp-to-shp attribute join.

ABishop — Wed, 03 Nov 2021 20:40:07 GMT

Just a suggestion and you may have already done this... but sometimes doing a google search based on keywords of what you are trying to accomplish with the words ArcGIS Desktop 10.6.1 included. GeoNet is a great resource but you may find another source with the information you are looking for.

Re: dbf-to-shp attribute join gives dofferent result than shp-to-shp attribute join.

RichardFairhurst — Wed, 24 Nov 2021 07:13:41 GMT

Attribute Joins do not display the full set of records that actually result from a one-to-many or many-to-many join. The table shown by ArcMap is functionally a one-to-one or many-to-one set of records, meaning that new records are not created in the parent table to match all of the records in the joined table. For all intents and purposes it is hiding from you the true number of denormalized records that would result from the full output of that relational join for performance reasons.

However, when you do an export the true denormalized record set of the one-to-many or many-to-many records is generated, which inserts records that you would not see in a layer attribute join. This is a desired behavior, since this is the best way to make ArcMap behave like Access when outputting a result from a one-to-many or many-to-many relationship. The export increases the number of records to convert these relationships to a true one-to-one output. Creating a one-to-one representation of the data in the ?-to-many relationships is often useful for a variety of analysis purposes, and I have made use of this export behavior many times.

The difference in your results is exposing an erroneous assumption on your part that the Census Block shape file has only unique values for each Census Block field value. If you do a Summary of your Census Block field in the Census Block table you almost certainly will discover their are duplicate values for one or more of your Census Blocks. If even one Census Block value is duplicated in the shape file that should only have unique values it will double the number of records in the export for that value, and if it is duplicated on 3 features it will triple the values in the output of the export for that value. So if the shapefile that allows duplicate values has 100,000 features with that Census Block value you will end up with 100,000 more records than you expect when you do an export if that value is duplicated and not actually unique.

You need to clean up your features to merge together all parts of the Census Block into a single multipart feature to actually have a many-to-one relationship like the Attribute Join shows you. The Dissolve tool can do the merge and output a new feature class or you can Edit the existing FC by using the Merge option under the Editor button when you have selected all features for a single Census Block value. That will make that shapefile conform to your relationship assumption and it will behave the same as the Attribute Join when you do an export, since then it will create a many-to-one join and not a many-to-many join with denormalized records hidden by the Attribute Join.

Re: dbf-to-shp attribute join gives dofferent result than shp-to-shp attribute join.

DionGood — Wed, 24 Nov 2021 16:44:54 GMT

Thanks. I don't fully understand your reply, specifically regarding denormalization, but I do know that my data contains duplicates. That is the whole point - I am using an attribute join to get rid of duplicates, so that only one instance of each unique Census block results.

Re: dbf-to-shp attribute join gives dofferent result than shp-to-shp attribute join.

RichardFairhurst — Thu, 25 Nov 2021 00:02:13 GMT

Use the Dissolve tool (Data Management Tools, Generalization Toolbox) on the shapefile that is only supposed to have one shape for each Census Block. Only use the Census Block field as a Dissolve field in the tool and add the Census Block field as a Statistics field with a Statistics Type of Count The output will have a single shape for each Census Block and export the results you are are wanting. Anything in the output Count field with a value greater that 1 was split into more than one piece in your original Census Block shapefile. Below is how I would set up the tool for my Census Block feature class and the output with all Census Blocks that collapsed from many features for those Census Block values to a single feature.

Re: dbf-to-shp attribute join gives dofferent result than shp-to-shp attribute join.

DionGood — Thu, 25 Nov 2021 00:30:21 GMT

No, I don't want or need splitting or dissolving of Census block boundaries. The two files both contain identical 2020 Census block boundaries - one with 519,723 unique features (one feature for each unique Census block) and containing population, households, and housing units data in the attribute table; the other with 1,054,633 features (which include duplicate features for each Census block) and containing broadband data in the attribute table. I want to calculate how many households "have access to" any kind of broadband over 200kbps (the FCC standard). If I join the Census data to the broadband data, the households in each Census block will be counted multiple times when I sum that field. So I must join the broadband data to the Census data so that the households in each Census block get counted only once. However, the number of features joined varies depending on whether I join shapefile-to-shapefile or attribute table-to-shapefile. I have already determined that the method which yields the correct results is to join the broadband attribute table to the Census shapefile. I have done this same exercise with smaller data sets in the past and got the same results with both methods. My question is "why is it not working now?"

Re: dbf-to-shp attribute join gives dofferent result than shp-to-shp attribute join.

RichardFairhurst — Thu, 25 Nov 2021 07:37:22 GMT

Your Census Block feature are not unique in the shapefile or the dbf table that you say has only one feature/record per Census Block value. The only way I will accept that you have proven me wrong is if you do the Dissolve I have shown you or you do a Summary of the Census Blocks and find that the count of every Census Block value is 1. If you refuse to do that your on your own to figure this out. But I can assure you that the problem is not with the software, it is with your unproven assumptions about your data.

A difference in the presence or absence of an FID field in the shapefile and table is another factor that can affect the export behavior, but it would be unusual for either if those file types to lack an FID field.

Re: dbf-to-shp attribute join gives dofferent result than shp-to-shp attribute join.

RichardFairhurst — Thu, 25 Nov 2021 15:28:08 GMT

I reexamined your methodology and think there my be an alternative explanation of this behavior that still involves the difference in the behavior of a layer attribute join and the export of that join. The Attribute Join of a layer in Arcmap is in reality a one-to-one join that only considers the first record in the one-to-many relationship when it applies the definition query. So if the first record it encounters does not meet your query criteria the record is filtered out, even if the second record of the join would have met your criteria. However, an export examines all records in the joined table and will include matches that fail to meet your criteria on the first record, but succeed on the second record or any other records than the first. The export may also create duplicates of the Census Blocks if more than one record in the join table meets your query definition (if the inputs of the join came from the same file geodatabase this duplication of records would definitely occur). Both methods can produce incorrect results for what you want to achieve, which makes the use of ?-to many joins unreliable and unsuitable for this kind of analysis.

The proper way to do this analysis to ensure all records that meet your criteria are included and no duplication happens is to do the following steps:

Create a definition query in the broadband shapefile to show only Census blocks at 25/3 or greater. This reduces the size of the attribute table from 982,015 records to 654,582 records. Perform a Summary on the Census Blocks field of that shapefile to get a table that contains only one record for each unique Census Block value. You could include the first and last or min and max values in the 24/3 fields in the output, but that is optional. Then perform an attribute join of the Census Block shapefile as the target table to the Summary output as the join table, keeping only joined records. Both the Attribute Join and the export will show the same number of records that the Summary table contains (provided all of the Census Block shapefile features are in fact unique) since both will be the output of a true one-to-one join. This methodology will reliably and consistantly give the sum of households for all Census Blocks that actually meet your criteria.

MS Access would also require you to do the same step of creating a Summary query first, and then using that summary output for creating a one-to-one relationship with the Census Blocks in order to get the sum of households you want, so this is the standard database methodology for solving this problem. MS Access is incapable of providing you with the sum of households you want directly from any ?-to-many relationship.