Hi all,
Thank you for your time in advance. I am wondering if any tool or layer in the ArcGIS could work with the SQL query directly. In my previous post, Mody Buchbinder mentioned the MakeQueryTable might work with the complex query listed below. However, it did not work. Now, I was told that the Query layer might work with the complex query. Or you would suggest to execute the complex query in the MSSQL server to get the data and export the data to ArcMap.
Let me know your valuable opinions.
Thanks,
Hank
SELECT
t.POINT_ID,
t.CollectionDate,
t.HistoricDate,
t.U,
t.LAT,
t.LONG,
t.WellDepth,
t.DataSource
FROM
(SELECT POINT_ID, MAX(CollectionDate) AS MaxValue
FROM
NM_Water_Quality.dbo.MASTER_UNION_Water_Quality
WHERE
(U IS NOT NULL) AND (U > 0)
GROUP BY POINT_ID) AS x INNER JOIN
NM_Water_Quality.dbo.MASTER_UNION_Water_Quality AS t ON x.POINT_ID = t.POINT_ID AND x.MaxValue = t.CollectionDate
WHERE
(t.U IS NOT NULL) AND (t.U > 0)
Solved! Go to Solution.
Cross-database queries usually perform poorly. If you have spatial data somewhere, you'd be much better off accessing that data locally, or by establishing a process to replicate it (little 'r' "replicate", vice big 'R' "ArcGIS Geodatabase Replication") to the database to which you do have access.
- V
@Chang-HengYang this has been a while but this use case is becoming more useful so I figured I'd share something we've learned. We do this sort of thing quite often and can validate what @VinceAngelo is saying about performance and replication etc. We have found a pattern that allows us to pull information from various instances and deliver a feature class with great performance. The one draw back is that it's not live (real-time views of the data in their tables). However, if your needs allow a regular update interval (daily or weekly etc) then give this a try.
NOTE: There is a way to do it without registering it as versioned but you have to do some extra steps to handle the managed OBJECTID field in a way that leaves it in a consistent state with the Geodatabase (https://desktop.arcgis.com/en/arcmap/10.7/manage-data/using-sql-with-gdbs/inserting-a-value-into-a-g...)
This allows the database to chug away at a hefty process when we don't have any users in the system and provide standard feature classes with spatial indexes to our users.