Who need JOIN table ( JOIN spatial layer ), the data audience or the data author ?
Data author
Who has knowledge of what table to JOIN and on which field to JOIN ?
Data author
Socrata and ESRI current provide who the capability of JOIN ?
Data author
Data audience need JOIN capability ?
No, but it is nice to have.
Data audience know what table to JOIN and on which field to JOIN ?
Most of time, no, unless the data audience are author as well.
Does Socrata and ESRI currently provide data audience the capability of JOIN ?
No
Would data audience wish Socrata and ESRI provide JOIN in API level rather than current administrator manual operation level ?
Yes, because in case of data author should but not do the JOIN job, audience can do their own.
Is there reason Socrata not providing $join keyword in SoQL API in last 10 years ?
Yes. When you join table, you need create a new entity as view ( a new 4 by 4 end point xxxx-xxxx ). Then you can use SoQL query editor to add JOIN statement there. Then you click SAVE and PUBLISH button. Socrata will save this new entity view on hard drive. If Socrata allow data audience to JOIN table on the fly via $JOIN API, so many instant JOIN request need heavy computing power and huge disk space to hold it, they can potentially killed the Socrata database server. Socrata currently limit the JOIN capability only for Site Administrator not the public. Socrata seems will not provide $JOIN keyword until circumstance change in the future. For example, use GPU database inhance computing power than current CPU database. Use huge RAM memory instead of current SSD hard drive to hold JOIN output etc.
Is there reason ESRI not provide JOIN on the fly ?
Complicated. Currently you can JOIN feature through GeoAnalytics service manually. When you JOIN two feature layers, ESRI will ask you to output the result into a new feature layer. This new feature layer have to have a identifier and occupy some disk space. JOIN process need heavy computing power and extra RAM and extra hard drive space to hold it. Too many of instant on the fly JOIN potentially can drag down the current Arcgis Server performance.
Spatial data by nature are different from panel data. JOIN by location is actually JOIN attribute table by location. Spatial data we want to see the shape, not the underneath attribute table. This is different from JOIN panel data. Panel data, JOIN is actually JOIN columns. You expend columns into two tables. Spatial JOIN you did expend columns into two tables, but user are more interested in the shape change than the columns change.
Current ESRI provide the way of JOIN feature is actually JOIN attribute table by location. This is only one way of spatial join. The result of join is still single feature layer, but have more columns in attribute table from the other layer. For example, if you join address with zoning, you will get a new address layer, the geometry point has no change, only the attribute table have extra column named 'zone name'. Before JOIN, you don't know this address belongs to what zone. After join, each address row in attrbute table have extra column as zone name. So you know each address belongs to what zone. Address geometry have no change.
There is another way of spatial join. Merge the shape of both layers. Since spatial join is join by location by nature. A better visual effect of join is merge of two kind of shape into one. For example, merge address point and zoning polygon into one shape file. This is impossible for shape file. Because shape file are typed, definition of shape file state that shape file have to be one of the type, either point or line or polygon, but not two of type. Modern format GeoJSON solved this problem. In GeoJSON format, you can specify type in each individual geometry. GeoJSON is a collection of different type of geometry. So point and polygon can exist in single geojson file, but not in single shape file.
The merge of address and zoning in GeoJSON becomes simply add zoning polygon into address point and get a merged geometry collection of both point and polygon. This is a better way user can visually see the spatial "JOIN". And this type of spatial join can perform instant join on the fly on the client browser without overhead CPU and RAM and hard drive space cost on arcgis server.
ArcMap can do similar things, called overlay. In arcmap, you add both address and zoning layer into your map frame. To idenfify overlay layer, you can use identify tool, then select which layer you want to target. Select zoning layer will tells you what zone the current address is located.
The "shape join" is more usefull than the "attribute table join". ESRI did not provide "shape join" due to limitation of shape file. However that is the most we wanted.
Due to my website's nature is internet borned, web based, use GeoJSON as major format. I decide not to provide "attribute table join". But only provide "shape join" in the client browser. ESRI plateform is originated from shape file, a desktop based format. ESRI's web api is originated from their successful desktop based software ArcMap.