How to join table to a feature layer?

638
2
Jump to solution
12-12-2022 12:13 PM
mikAMD
by
Occasional Contributor II

I have a feature layer "assets" (points) that contains a field "categories_id" which is populated by IDs that refer to the "id" field of the "categories" table (no spatial data). There is another field in this second table which contains a "name" string field. The "assets.categories_id" field is related to the "categories.id".

I would like to count how many different categories (categories_id) exist in the "asset" table and get the name from the "categories" tables.

asset table:

idsite_idcategories_idinfo
111text1
211text2
312text3

 

categories table:

idname
1category 1
2category 2
3category 3
4category 4

 

I realize I can use the "group_by_fields_for_statistics" and "out_statistics" parameters from the FeatureLayer.query function to get the count of every assets.categories_id, but I also need to get the name. What is the best way to accomplish this? Get the stats then do a join?

As a side question: I also wish to do a similar operation with two feature layers (sites and assets). Is there a way to join features based on field value (a basic SQL JOIN) rather than the spatial_relationship (as described here) ? Anyway to do that?

Thank you!

1 Solution

Accepted Solutions
mikAMD
by
Occasional Contributor II

I can't seem to get what I want from the API. I can't seem to figure it out. How can something as simple as joining tables or layers and grouping them to get a count per group is so complicated.

Plus, the documentation on the API is really poor, with lots of errors.

I'm just gonna query the tables and layers and do the operations locally, but it's too bad, I wished I could've done it directly on the data and return the values I wanted.

View solution in original post

2 Replies
mikAMD
by
Occasional Contributor II

I just found this arcgis.geoanalytics.summarize_data module. Gonna play around with it, see if it meets my needs.

0 Kudos
mikAMD
by
Occasional Contributor II

I can't seem to get what I want from the API. I can't seem to figure it out. How can something as simple as joining tables or layers and grouping them to get a count per group is so complicated.

Plus, the documentation on the API is really poor, with lots of errors.

I'm just gonna query the tables and layers and do the operations locally, but it's too bad, I wished I could've done it directly on the data and return the values I wanted.