We host a SQL Server database that stores the detailed results of sample data. The database schema is moderately complex.
I would like to develop a widget that would allow a user to "query" the database. I envision the widget giving the user the option to select several options - like date range, chemical name, sample matrix, etc, then having the widget return a "layer" showing the location of the results.
I'd like this to be a completely ArcGIS Online solution (i.e. no need to host my own instance of ArcGIS Server).
Where would I start developing something like this? I can think of numerous pathways to get to this endpoint. The two (very rough) ideas that I have come up with so far:
- Create a stored procedure on the SQL server side that returns query results as JSON.
- Build a Geoprocessing script that
- passes the inputs to SQL Server
- builds a query table
- builds an event theme
- converts event theme to a temporary feature class
The second option seems easy to build, but not very practical, and I'm not sure if there's any way to leverage the concept of temporary or "in memory" feature classes on ArcGIS Online.
I feel like a solution to this must already exist in some form, and I'm not really wed to any particular approach... I'm just trying to develop the correct questions or "Google Syntax" so that I can hone in on work that others have already done.
Thanks for the help.