nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

The complex query in query layer

Chang-HengYang

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)

Find more posts tagged with

Accepted answers

TanuHoque

yes, it should work with query layer.

query layers are meant to support this kind of complex query.

as long as you don't have something in your sql that is not supported by your underline database in sub-queries then you should be fine.

All comments

MikeVolz

What RDBMS are you storing the data in?

Can you create a view in the database with this query and just add the view into ArcMap as a query layer?

TanuHoque

yes, it should work with query layer.

query layers are meant to support this kind of complex query.

as long as you don't have something in your sql that is not supported by your underline database in sub-queries then you should be fine.

Chang-HengYang

The Query layer is working smoothly with the complex query. Now, I do not need to export to csv file by python. Instead, I used the Query Layer to export to the MSSQL as the database feature class. Then I can publish these data.

Thanks,

Hank

TanuHoque

if your workflow requires you to come data into a feature class, then it is okay.

otherwise, you can directly publish a map with query layers as a map service and skip the export.

Chang-HengYang

Hi Tanu,

Thank you for the quick updating. In the master view created in MSSQL server, there is no field "Geometry". Therefore, I need to use the "Display X, Y Data" to create the feature class. I am wondering if you would recommend to put the field "Geometry" into the master view. Then I add the "Geometry" in my complex query. Otherwise, could you recommend any better idea?

Thanks,

Hank

Screen Shot 2016-04-11 at 10.08.17 AM.png

Chang-HengYang

The master view (not the table) created in the MSSQL only contains x & y coordinates stored in two separate fields.

Thanks,

Hank

Chang-HengYang

There is no table existed with the field "Geometry". There are some feature class tables with "Geometry" living in another database.

VinceAngelo

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

DougGreen

@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.

Create a feature class in your Enterprise GDB with the same schema as the query layer you're trying to make.
Register it as versioned but indicate in metadata or other documentation that it is not to be edited by end users.
Create a stored procedure that can be run by a SQL Agent Job on a schedule that does the following:
1. Based on https://desktop.arcgis.com/en/arcmap/10.7/manage-data/using-sql-with-gdbs/edit-versioned-data-using-sql-sqlserver.htm
2. Deletes all rows from the multi-version view (Same as the feature class name with '_evw' appended at the end)
3. Inserts the results of your query into the multi-version view
Running a compress on the database after these operations will help this perform better so that all of these new edits can be compressed into the base table.

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-global-id-column-in-sql-server-using-sql.htm)

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.

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.