The complex query in query layer

4429
11
Jump to solution
04-08-2016 09:25 AM
Chang-HengYang
New Contributor III

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)

Tags (2)
1 Solution

Accepted Solutions
TanuHoque
Esri Regular Contributor

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.

View solution in original post

11 Replies
MichaelVolz
Esteemed Contributor

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
Esri Regular Contributor

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
New Contributor III

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

0 Kudos
TanuHoque
Esri Regular Contributor

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.

0 Kudos
Chang-HengYang
New Contributor III

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

0 Kudos
TanuHoque
Esri Regular Contributor

does any of your tables, that is participating in the query, have a field of 'geometry' type? or it is just x & y coordinates are stored in two separate fields?

0 Kudos
VinceAngelo
Esri Esteemed Contributor

Generating geometry on the fly is very expensive, both in raw performance cost and because it's not possible to build a spatial index, which effectively reduces the number of features supported by this sort of query below ten thousand rows (where a real geometry layer could easily support tens of millions of rows).

While it's possible for a database to do this sort of query on a regular basis, you really ought to evaluate using the query to populate a "materialized view" (a real table, which contains the data after all the crunching is done), which will give you optimal performance.

- V

0 Kudos
Chang-HengYang
New Contributor III

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

Thanks,

Hank

0 Kudos
Chang-HengYang
New Contributor III

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

0 Kudos