I am using ArcGIS Desktop and ArcGIS Server version 10.3. I have also created an enterprise geodatabase in Oracle 12C.
In the SDE schema I have a table containing a road network (about 10000 links), for the sake of brevity let's assume that the table has the following structure:
LINKS_TABLE
ID (NUMBER(10,0)), SHAPE (ST_GEOMETRY)
Furthermore, I have a second table which holds link-related attributes (e.g. vehicles per hour). In this second table I store data for different scenarios (e.g. vehicles per hour in weekdays, in weekends, during summer, during winter, etc). The table structure is the following:
SCENARIO_TABLE
ID (NUMBER(10,0)), LINK_ID (NUMBER(10,0)), SCENARIO_ID(NUMBER(4,0)), SCENARIO_VALUE(NUMBER(10,0)).
I want to display the aforementioned network on a web application using either leaflet (ESRI Leaflet) or OpenLayers. I need to be able to select different scenarios (most likely more than one at the same time) and display them as different layers. This means the datasource for each layer will be something like
SELECT LINKS_TABLE.ID, LINKS_TABLE.SHAPE, SCENARIO_TABLE.SCENARIO_VALUE FROM LINKS_TABLE INNER JOIN SCENARIO_TABLE ON LINKS_TABLE.ID = SCENARIO_TABLE.LINK_ID WHERE SCENARIO_TABLE.LINK_ID = XXX
I want to deliver the output as an ArcGIS FeatureAccess layer.
The SCENARIO_TABLE will constantly be updated by users (or by background processes), so I cannot export the required services beforehand.
How can I create an ArcGIS Service where I can pass the scenario_id as an argument during runtime?
The web application will most probably (99%) be written in ASP.NET using C#.