topic Re: ArcGIS Enterprise 10.9 Datetime Handling for Dynamic Query Layer in ArcGIS Enterprise Questions

ArcGIS Enterprise 10.9 Datetime Handling for Dynamic Query Layer

cle444 — Thu, 14 Oct 2021 03:19:30 GMT

Call for ArcGIS Enterprise experts! This is a queston about UTC and local time handling when publishing registered dynamic query layers to Enterprise.

Use case:
Publish a map image service that contains XY event layer that is based on a referenced SQL query table. The SQL query table is a simple SELECT query with a field of datetime data type. The datetime value is stored as a plain local time value without timezone and daylight saving information.

When pulishing to ArcGIS Enterprise via ArcGIS Pro Share as Web Layer, the local time zone and daylight saving option are configured in the sharing wizard.

The issue:
ArcGIS Enterprise doesn't respect the time zone and daylight saving configuration for such dynamic layers. Instead, it treats the value as UTC and convert it to local e.g. adding 13hours for NZ daylight saving time.

However, there is no issue if published from a static feature class from a file gdb. Extactly same data and same publish process.

Software:
Client - ArcGIS Pro 2.8
Server - ArcGIS Enterprise 10.9

Test: Publish a map with two layers of exactly same data, one from registered feature class and one based on a SQL query table.

1. Prepare workspace as below. Both layers are registered on the target ArcGIS Server.

2. Check value from local ArcGIS Pro using identify and popup. Same value 13/10/2021, 12:33pm for both layers.

3. Publish as map image layer.

configure timezone and daylight saving

4. Publish to Enterprise is successful. Check on map viewer from Portal.

Question:
With timezone and daylight saving configured during publish process, why Enterprise treats feature class layer correctly but not Event layer based on SQL query table?

For the query table and XY event layer, no matter what timezone you choose during the publish stage, NZ+12:00 or UTC or <None> or any other, Enterprise always treats it as UTC and converts it to local time, in my case, +1300.

Any ideas and insights would be much appreciated!

Thanks,
Hua

Re: ArcGIS Enterprise 10.9 Datetime Handling for Dynamic Query Layer

cle444 — Wed, 13 Oct 2021 23:14:03 GMT

To extend my original question.

I am aware of a workaround to solve the popup issue by configuring a custom expression like below

However, it doesn't solve the problem of using other widgets e.g. the export csv from attribute table widget, the filter widget etc. Those widgets are all based on the 'converted' datetime.

Ideally, when specifying the timezone and daylight saving options during the publish process, ArcGIS should handle it as suggested.

Re: ArcGIS Enterprise 10.9 Datetime Handling for Dynamic Query Layer

TanuHoque — Wed, 13 Oct 2021 23:31:45 GMT

@cle444

It appears a bug to me.

Would you mind give me a bit more details on the xy event layer?
Is this how you created that layer:

add a non-spatial table (non-registered to a geodatabase) from an enterprise database to a map.
- that makes the source of this table a query layer
then you used Make XY Event Layer geoprocessing tool to create a xy event layer

Re: ArcGIS Enterprise 10.9 Datetime Handling for Dynamic Query Layer

cle444 — Thu, 14 Oct 2021 03:20:13 GMT

@TanuHoque That's correct. I suspect it is a bug too.

Steps for making the non-spatial query table as per below.

Table added

The last step is to create layer using Make XY Event Layer geoprocesssing tool (NOT add XY point data or XY table to point), just to confirm.

Re: ArcGIS Enterprise 10.9 Datetime Handling for Dynamic Query Layer

cle444 — Sun, 17 Oct 2021 21:54:55 GMT

Any chance that the ESRI ArcGIS Enterprise team can comment on this, please?

Re: ArcGIS Enterprise 10.9 Datetime Handling for Dynamic Query Layer

TanuHoque — Tue, 19 Oct 2021 19:17:25 GMT

@cle444

I was able to reproduce this issue. As I mentioned before, it appears to me as a bug. I will log a bug for us internally.

As a workaround, may I suggest the following. In fact, in some case, I don't consider that as a workaround rather a solution.

use this sql query (or similar query depending on your underlying database flavor) to generate geometries on the fly from x,y coordinates.
- as you know it is functionally equivalent to XY Event Layer in many cases. It is just that geometries are generated at the database level, instead of in Pro.

-- SQL Server select objectid, geometry::Point(x_coord, y_coord, 3857) as shape_derived, date_time_field from myTable WHERE aFld = 'value'

Please let me if this is acceptable to you.

Re: ArcGIS Enterprise 10.9 Datetime Handling for Dynamic Query Layer

cle444 — Wed, 20 Oct 2021 11:02:18 GMT

@TanuHoque Tested, and I can confirm it is working for us. Much appreciated the solution.

It would be great to share a little more insight about this, e.g. is it a Pro issue rather than the Enterprise issue? Is the 'Make XY Event Layer' a helper method that can handle different database technologies? For the SQL server, what you provided is how 'Make XY Event Layer' works behind the scene?

Generating geometries on the fly at the DB level creates a layer of a query feature class. Using the 'Make XY Event Layer' in the ArcGIS Pro geoprocessing tool creates a layer of XY Event type. What is the main difference between the two layer types? In what scenarios you would use a query feature class layer instead of an XY Event layer, and vice versa?

Thanks!

Re: ArcGIS Enterprise 10.9 Datetime Handling for Dynamic Query Layer

TanuHoque — Wed, 20 Oct 2021 15:27:03 GMT

Thanks @cle444 . I'm glad it worked for you.

I don't have answers for all the questions you have asked at least not at the detail level you hoped for. Let me try.

is it a Pro issue rather than the Enterprise issue?

The bug you ran into is a map service bug on the Enterprise side.

Is the 'Make XY Event Layer' a helper method that can handle different database technologies?

If I recall correctly, it is not.

For the SQL server, what you provided is how 'Make XY Event Layer' works behind the scene?

No, it doesn't. 'Make XY Event Layer' is a complete different implementation.

What is the main difference between the two layer types?

If I understands correctly, a 'XY Event Layer' reads rows from the table and generates geometries using x/y coordinates on the client side. Whereas the approach that I provided generates the geometries at the database level. Pro/map service simply gets the geometries (just like for any other query layer or feature class) and draws them.

In what scenarios you would use a query feature class layer instead of an XY Event layer, and vice versa?

Unfortunately I don't have any answer for this 😞 I need to think through, if I find any I will reply back here.

As you might have figured already, functionaly they are pretty similar. Of course one draw back for the query-layer approach is that you need to know sql and database specific spatial functions. Itonly works when your data are stored in an enterprise database. This approach wouldn't work for file gdb or shape files etc.

hope you will find this helpful!

Re: ArcGIS Enterprise 10.9 Datetime Handling for Dynamic Query Layer

cle444 — Wed, 20 Oct 2021 20:51:01 GMT

Great stuff! My major concern is performance but looking at the page loading profiles, not much difference that I can tell. Really good to know what's happening under the hood. Thanks for sharing the knowledge!

Re: ArcGIS Enterprise 10.9 Datetime Handling for Dynamic Query Layer

TanuHoque — Wed, 20 Oct 2021 21:54:54 GMT

hmm... You might see performance issue with very large tables since they don't have any spatial index and Pro/map service makes queries with spatial filter.

If I recall correctly, XY Event Layer actually does try to mimic the spatial filter using x/y coordinate fields -- something like where x_coordinate >= xmin and x_coordinate <= xmax and y_coordinate >= ymin and y_coordinate <= ymax.

That said, when those x/y coord fields don't have indices, I'm not sure how the query will perform.

Re: ArcGIS Enterprise 10.9 Datetime Handling for Dynamic Query Layer

cle444 — Wed, 23 Feb 2022 00:12:44 GMT

@TanuHoque Greetings. I am revisiting this thread just wondering if this bug is resolved in 10.9.1? If not, is there a known bug page that we can track? This is due to our GIS professionals tend to use this function more. Query feature layer on a SQL spatial view also has the same problem.

Thanks,
Hua

Re: ArcGIS Enterprise 10.9 Datetime Handling for Dynamic Query Layer

TanuHoque — Thu, 24 Feb 2022 21:28:43 GMT

hi @cle444

Unfortunately it was too late to have it fixed for 10.9.1.

That said, we have it in for the next release. Fingers crossed for fixing it before it releases -- hopefully at the end of Q2 or the beginning of Q3. The bug is still internal. Let me see if I can make it public and if I do, I will share that with you.

Re: ArcGIS Enterprise 10.9 Datetime Handling for Dynamic Query Layer

TanuHoque — Thu, 24 Feb 2022 21:47:26 GMT

BUG-000147165

Re: ArcGIS Enterprise 10.9 Datetime Handling for Dynamic Query Layer

LSaunders — Thu, 11 Aug 2022 21:50:28 GMT

Have you had any success with doing this type of query on Oracle?

Re: ArcGIS Enterprise 10.9 Datetime Handling for Dynamic Query Layer

TanuHoque — Thu, 11 Aug 2022 22:05:09 GMT

@LSaunders
yes you should be able to do this type of query layer with parameters against any enterprise database.

Re: ArcGIS Enterprise 10.9 Datetime Handling for Dynamic Query Layer

TanuHoque — Thu, 11 Aug 2022 22:05:40 GMT

also just to let you know that this is something got fixed in ArcGIS Enterprise 11.0 release

Re: ArcGIS Enterprise 10.9 Datetime Handling for Dynamic Query Layer

LSaunders — Thu, 11 Aug 2022 22:16:49 GMT

I haven't had any luck. I'm having serious issues with being able to use xy event layers in a service to feed into a Dashboard that has to show up-to-date data. A table has no refresh ability yet, and the xy event layer data throws a data access error in the indicator widget. I previously got around this by publishing an msd in json to ArcGIS Server, but in prep for an eventual upgrade to 11, it seems that I've lost all ability to do this. So I wanted to test with the method that you suggested, but Oracle is quite different than SQL and they have what seems to be no easy way to create geometry on the fly with sdo_geometry...I hope that I'm just missing something as I'm grasping at straws and will be forced to stay on 10.9.1 and ArcMap Runtime services for the unforeseeable future.

Re: ArcGIS Enterprise 10.9 Datetime Handling for Dynamic Query Layer

TanuHoque — Thu, 11 Aug 2022 23:04:02 GMT

... but Oracle is quite different than SQL and they have what seems to be no easy way to create geometry on the fly with sdo_geometry...

I'm not an expert in Oracle by any means... still want to pass it to you... did you try this and not work for you?

select objectid, SDO_GEOMETRY(2001, 3857, MDSYS.SDO_POINT_TYPE( x_coord, y_coord, NULL), NULL, NULL) as shape_derived, /* the 2nd parameter is the id for coordinate system; 3857 is the wkid for WGS 1984 Web Mercator (auxiliary sphere) projection */ date_time_field from myTable WHERE aFld = 'value'