topic Re: Join versus Relate in ArcGIS JavaScript Maps SDK Questions

Join versus Relate

ChristopherJohnson1 — Sun, 07 Feb 2016 04:41:33 GMT

Good evening. I am trying to decide between using a join versus a relate in ArcMap. I understand that a join supports a one-to-one relationship, but it also says it supports a many-to-one relationship, which may be what I need. Keep reading to help me decide. I also understand that a relate supports/implies a one-to-many relationship. That's the theory. Can you help me apply it? Here's my situation...

I have a shapefile that I imported into a SQL Server database as a feature class that contains spatial data.

I have another table in the same SQL Server database, which is not a geodatabase, that contains non-spatial data for the locations in the feature class. This table of non-spatial data has many rows for the same location. Additionally, it will be updated frequently/outside of my knowledge by some automated process. Currently, I am joining the non-spatial data to the spatial data in ArcMap. I realize that I will only get the first match between the two tables, but my front-end, which uses the ArcGIS JS API, sends a rather restrictive query to the map service created from the document in ArcMap, which should only yield one row for each location. This implementation is working fine for me, but I am just wondering about best practices. Should i really be using a relate or should I just reverse the order of the join -- join the spatial data to the non-spatial data? And, in so doing, will I see any improvements in the performance of my map service? It is not horrible, but I am just wondering, generally/best practices-wise speaking.

Any thoughts/suggestions/help would be appreciated.

Thanks...Chris

Re: Join versus Relate

MichaelKowalczyk — Sun, 07 Feb 2016 05:53:32 GMT

You might try a Query Table. I have been recently using them in lieu of one-to-many joins.

You add both tables, and then you input a SQL expression that contains the relationship. It will then "duplicate" records in the spatial table to match the entries in the non-spatial table, and visa versa.

A relate would be significantly different than your current set up. You would select the record in your spatial table, and the matching records would be selected in the non-spatial table, but they would still appear as two separate tables, unlike a join. This may impact your workflows. A query table, however, would function much like the original table, (although it would not support editing). The query table would even show the features if you include the SHAPE@ field!

Re: Join versus Relate

ChristopherJohnson1 — Sun, 07 Feb 2016 05:56:32 GMT

Thanks for the reply, Michael.

Yes, I am wondering how different my workflow, in my front-end code, would be...testing it out now.

Thanks...Chris

Re: Join versus Relate

ChristopherJohnson1 — Tue, 16 Feb 2016 02:26:45 GMT

More thoughts on your suggestion, Michael. Using the query table, since the data in my non-spatial table could change at anytime, that would mean that I would have to automate the building of the query table.

Thanks...Chris