It is easy to join tables on SQL server, 

SELECT column_name(s) FROM table1 INNER JOIN table2 

ON table1.column_name = table2.column_name;

 

 

 But how would you do the same thing over the HTTP protocol?

 

 

Generally speaking, there are two direction you can go.

 

Option 1Use SQL injection

Neither Socrata SODA API (SoQL) nor ESRI arcgis REST API allow you to embed the full SQL statement (with JOIN) in the URL as a string due to security reason. 

 

 

 

 

Option 2. Developing a sophisticated mechanism to fullfil the join

 

 

 You can JOIN table with Socrata SoQL query editor  watch this video Socrata Level 3 Training Video - SoQL Join Statements  https://www.youtube.com/watch?v=ik14YcVQfRw

        However      

 On the API level, there is no  $join   keywords provided in SoQL. Check SoQL keywords.  SoQL query editor is manual operation.

We still need Socrata provide   $join   API in the future to automate JOIN process on the fly. 

 

 

 

 

 

ESRI Arcgis REST API allow you join feature with GeoAnalytics 

https://developers.arcgis.com/rest/services-reference/enterprise/join-features.htm

Also check article Join feature on Portal for ArcGIS

https://gislab.depaul.edu/portal/portalhelp/en/portal/latest/use/geoanalytics-join-features.htm

        However      

Most of organization do not have a  GeoAnalytics service. 

  GeoAnalytics service API is very complicated, not fit for you and me. 

This is how you can join two layer. First you submit a join task URL via REST API. Then you get a job ID as token. You periodically check job status by send URL with token until you get a response tells you that job is successfully completed. Then you can access result feature layer by job ID as token. You can automated the process but its way complicated. 

We need ESRI provide a  join   API in the same style as current feature service.  

One URL to do all jobs on the fly, no waitting time, no token, no job ID needed.      

 

 

  

by

Please log in or register