Support multiple data sources for a FeatureServer / Web Feature Layer

[Anonymous User]

We would really like to create an Esri feature Server that has data coming from multiple datasources. Currently ArcGIS Pro Analyze gives 00062 "Map contains data that does not use the same connection to an Enterprise Geodatabase (Web feature Layer)"

We would like to combine data from multiple datasources and it would be great if we could achieve that without replicating data.

[MarcoBoeringa]

It is slightly unclear what exactly it is you try to achieve: a single Feature Service based on a database JOIN that combines data from tables in multiple (enterprise geo-)databases, or multiple Feature Services in a single webapplication, each showing data from a different (enterprise geo-)database?

Anyway, if trying to join data from multiple datasources, then using a MATERIALIZED VIEW might be an option. Oracle, SQL Server and PostgreSQL all support creating such views. Don't forget to index the view, including the spatial column. You can then either:

- Register the view with your geodatabase, making it fully "native" to ArcGIS

- Or create a Query Layer to access the view's data in ArcGIS.

To be honest, I do not know if you can easily incorporate a Query Layer in a web feature service in ArcGIS, you will have to look that up yourself.

Note that PostgreSQL also has a lesser known nifty feature called Foreign Data Wrappers (FDWs), that allows you to transparently access a wide range of (unstructured) datasources and use them like ordinary tables in PostgreSQL. So if you have exotic datasources you would like to make accessible, FDW might be the way to go.

FDW also seems to include the capability to treat remote tables in a certain PostgreSQL database as native / local to another PostgreSQL database, according to the link I posted, which would avoid replication.

[Anonymous User]

Thanks for your comments! What we would like to achieve is a single Feature Service that has multiple layers coming from different datasources. So Layer A is based on a featureclass in MS SQL Database X and Layer B is based on a featureclass in MS SQL Database Y. It is not about database joins or having multiple Feature Services In a web application. It is just a very simple Feature Service in which different layers use different data sources. Map Services do have support for our requirement, sadly Feature Services don’t.

We have been using materialized views but they are quite difficult to maintain in our opinion, especially when they cross databases. In addition, it is a form of data replication which we like to avoid.

We cannot use Postgres due to organizational restrictions

[MarcoBoeringa]

Hi Willem Jenniskens‌,

I don't know if this will serve your purpose, but this ESRI blog post talks about the ability to vary WHERE clause or even entire table name depending on view scale when using ArcGIS Query Layers:

Scale dependent multiple datasources for a single feature layer 

This will likely still require the different spatial tables coming from a single data source though, as the connection settings probably cannot be varied, so unless you could somehow "link" to the other database's table from within the current one, like is potentially possible in PostgreSQL, this wouldn't work for you. And of course, this is only useful if you have strict scale dependency of the different layers.

Alternatively, ESRI recently blogged about the ability to reference multiple data sources in one vector tile layer. I haven't been able to find that particular blog article again, but I think this is actually the relevant Pro Help page describing the functionality:

Multiple vector tile sources—ArcGIS Pro | Documentation 

Of course, this requires to spin up a vector tile service, which may or may not be suitable for you.

Note that I have tried neither of these options.