table join one to many

johnpolo · ‎01-08-2015

I have a shapefile of points that are survey points for trees. I have a table that is imported from a .csv that holds prism counts for the points where trees were found. So some of the points have multiple records of trees and some of the points have no records, because no trees were within range. I want to join the prism data to the points in the shapefile. I came across this blog post and I'm attempting the steps given. Step 3 says:

"Click SQL. Write a statement similar to the one in the following graphic. This statement is basically saying, give me all records that match between the table and the feature class. Click OK."

There is a graphic of an SQL window with the statement " tracts.Name = Store2.Tract ". It seems those are field names for the example. So, in my situation, I have fields that are point numbers and the same field is present in the shapefile and the table. I was going to shapefile and table by this field. Using the Query Builder, I form the statement " "GruberGPSlocationsfinal.PT" = "GruberData_Prism_workwiththis_csv.Point" ". The field names populated with the quotes around them, I did not use them. I mention the quotes because the example in the graphic of the blog post shows no quotations marks. When I try to verify the statement, the following error comes up:

"There was an error with the expression.

An expected Field was not found or could not be retrieved properly.

[GruberData_Prism_workwiththis_csv]"

The offending field comes from the table I imported and is listed in the Query Builder window, which makes me think it's accessible. I can open up the Attribute Table from the TOC, so it seems to me that Arc should know what it is. Any ideas on what I'm doing wrong?

JamesFitzgerald · ‎01-08-2015

Hello,

Have you joined the CSV file the point.shp? Do you have an unique identifier to make the join? Does the CSV have LAT and LONG?

Thanks

J

JoshuaBixby · ‎01-08-2015

There is basically a bug in the expression builder. Try removing the quotations from around the fields. Even if you verify the expression and it fails, it can still run correctly. Not sure if this is exactly your problem, but I run into the errand quotations with the expression builder all the time.

JoshuaBixby · ‎01-08-2015

Another idea, are both your datasets in the same workspace? That is a requirement for this tool.

johnpolo · ‎01-08-2015

James Fitzgerald,

I have not joined the table and the shapefile. The Table Query is supposed to do that for me, I thought.

I thought the "Point" field would be my identifier, since I want to match the Point in the shapefile to the Point(s) in the table, i.e. physical location to local characteristics.

The CSV does have the coordinates. Decimal degrees. I have more tables with other data that I will want to join after this table, so it would help to be able to join data that is just in table form to an existing shapefile.

Joshua Bixby,

I tried removing the quotation marks. Still same error message.

The shapefile and the table are both in the same geodatabase.

I will try running the expression tomorrow, I'm done for the day.

JamesFitzgerald · ‎01-09-2015

Hello,

I used the table query to join a database to a point shapefile through 'Make Table Query.' Are you working with a CSV file or a database file. You may have to convert to a database file.dbf and export it into your geodatabase. You need a unique identifier "Column Header" to make the join between your shapefile and table. The 'Make Table Query' exports a table with all your column headers you want. I think the problem is your data originating from either you table, point shapefile, and/or unique identifiers.

BTW, I did not use quotations in my expression.

I can look at your data if you like to get a better idea. I created data to match your data. My data consisted of a File Geodatabase, Point, and a table.

Let me know if I can assist,

James

johnpolo · ‎01-09-2015

I used Conversion Tools>To dBase to make a new table. I could not import that new table to the geodatabase. I looked at the properties for that file and the table already in the geodatabase and the only difference I could see was under the Source tab was the new file from Conversion Tools was dBase Table and the file I had from before that was already in the geodatabase is a File Geodatabase Table. They're both Standalone Tables and they are both .dbf.

If I go to Catalog and click on the properties, the shapefile and the table have FDO_OBJECTID and it is characterized as unique in both cases.

I went to the Make Query Table again, selected the OBJECTID fields and the SQL builder verified the statement: "GruberData_Prism_workwiththis_csv.OBJECTID" = "GruberGPSlocationsfinal.OBJECTID". (I don't know why it keeps inserting quotes) It didn't work. I tried it again, but this time I manually removed the quotes. It worked.

But not the way I wanted it to. It matched up the OBJECTID and so it only got part of the table's data; the ones that have OBJECTID that match the OBJECTID in the shapefile. The shapefile has 188 features/OBJECTID. The data table has 1200. Not only did it just get part of the table, it mismatched points. I attached a screenprint to illustrate the point. I was thinking this was going to happen, that's why I was trying to make the join on the Point/PT fields. Not sure what to do next.

JamesFitzgerald · ‎01-14-2015

Hello,

So, I believe when you join the tables it is only joining to one of the matches in the OBJECTID. Do the 1200 features have the same OBJECTID's (features) as the 188 OBJECTID's (features). Are they different? About the mismatched points...I have no idea! I would be willing to look at your data.

James

johnpolo · ‎01-15-2015

James, the OBJECTIDs are all unique in the respective tables. So there are 1223 OBJECTIDs in one table and 188 OBJECTIDs in another table. When I refer to "Points" or "PTs", I mean the actual locations (GPS points) where the data were collected.

There is one table that is just the locations that make up the shapefile.This has 188 records.

ObjectID	Point
1	99
2	100
3	101
4	102

The second table is for the data. This has 1223 records. Several PTs are represented multiple times because of more than 1 tree at the location (#102). Some points (PTs) had no trees, so they don't show up (#100).

ObjectID	PT	DBH
1	99	6
2	101	3
3	102	5
4	102	8
5	102	5

So when I did the join on the OBJECTIDs, it joined OBJECTID 1 in the locations to the same OBJECTID # in the data. That works. But then it goes awry with the very next join, because location POINT 100 is joined to data PT 101. I need to get the match on the POINT/PT.

Thanks for the offer to look at the data. The snippet above is exemplary of the data. If you need more info, perhaps you can message me or something through the site? I haven't used this site much and am not aware of its features.

JamesFitzgerald · ‎01-15-2015

Hello,

You should not join the OBJECTID Header. You should only join the PT field. The only problem is that you will not carry over the PT's with no trees. Do you still want the points with no trees. If you do not care about the PT locations with No Trees then join with PT. Also, if the PT doesn't join, then check and make sure they are formatted either as number or text.

If you do need the location with no trees, then you need to determine how many have no trees.

TO DO THIS:

1. I would use ADD Field to the second table and name it CODE and populate it with 'A'

2. Join the first table to the second table.

3.Export table

3. Go to Attribute table and query the column CODE. Use Code= A. Click on 'Selected Records' button. Then click on 'switch selection.' This will show all of the PT with No Trees. I would then populate the DBH with 0 to show there was no tree DBH taken.

4. So now you can join the table to shapefile. Or Table Query.

Hope I have helped

J