Query Layer help

[CoreyKalinoski]

I have a DB2 table that has Lat/Long data within fields in decimal form. Instead of having a field with a spatial data reference, i.e. point representation of these fields. I have attempted to write a query that utilizes the DB2 function to convert these fields into a point, this function is db2gse.point_st . Within a Jupyter Notebook this seems to work fine and I create a field that is added with the spatial reference for the point based on the lat/long. However if I try to include this function as part of my query within ESRI Query Layer, it will not show the field to select as the spatial reference. Any advice on this?

[JakeSkinner]

Hi Corey,

Instead of creating a Query Layer, would an XY Event Layer work for you? 

[CoreyKalinoski]

I am trying to map live data. Our database is updating about every 5 minutes and I am setting the query to pull the most recent lat/long entry for each object. I think I would need it to be a query layer for this, as ultimately I would like to host this layer on our portal if possible, after getting it to work properly within ArcGIS Desktop.

[JakeSkinner]

You will be able to achieve all of this with an XY Event Layer.

[JoshuaBixby]

It appears the OP eventually wants to publish this data/layer as a service so it can be accessed within a Portal.  If so, the following warning message will likely be relevant, at least if there are many point locations involved:

10043: Layer's data source is an XY Event Table

A layer in your document is using an XY event table as a data source, which can perform slowly in many situations.

More information

XY event data sources are commonly used to draw point data originating from a data source that is not spatially enabled. In this respect, XY event data sources are a powerful way to integrate simple point data into your map. However, the simplicity of this integration comes with a cost in that XY event sources cannot take advantage of spatial indexing that makes spatial data sources perform well. Consider converting your data to a spatial data source such as a file geodatabase or enterprise geodatabase. If the data resides in a relational database that cannot be geodatabase enabled, consider using the database's native spatial storage type and drawing it with a query layer as an alternative to XY events.

In this case, I think a query layer will run into the exact same issue since the spatial points are being generated dynamically, and hence won't be indexed.

[Asrujit_SenGupta]

DB2 data types supported in ArcGIS—Help | ArcGIS Desktop 

ArcGIS works with specific data types. When you access a database table through a Database Connection or a query layer, ArcGIS filters out any unsupported data types. ArcGIS will not display unsupported data types and you cannot edit them through ArcGIS.

So make sure that the Data Type is supported.

[JoshuaBixby]

I don't work with DB2 enough to know whether this will work.  Regardless of whether you can get it technically working, you aren't going to want to take this approach.  The problem with generating the spatial points on-the-fly or using an XY event layer is that the spatial points are never indexed, since they are generated dynamically.  Without a spatial index on the spatial data, the performance of the layer will tank.  If you are working with a few tens or hundreds of points, you might be able to get away with it.  If you are working with tens of thousands of points or more, be prepared to wait.

Typically, text representations of spatial coordinates are used just for storing or data-interchange, not doing any type of visualization or analysis/processing.  In this case, can your workflow be changed so the points are natively stored as points, and when you need a text representation, you dump it at that time?

[CoreyKalinoski]

We were trying to get around having the Database Developers actually intervene and create new fields in the database and natively storing the data as points in DB2 (not even sure if they can). I am on a separate team that does some ad-hoc data analysis, network redesign, testing new solutions, etc. I believe what I could do is query this data into a Postgres table and geo enable it with the POSTGIS extension. Then setup ESRI to query from that table..... possibly having a job run every so often to have my Postgres table query and update from the DB2 table. This seems inefficient but may be a possibility that could avoid having other do any work........ should this be able to work?

[CoreyKalinoski]

Also, this project is mapping a smaller set of objects. Less than 100 objects will be mapped based on their most recent lat/long entry only.

[JoshuaBixby]

Given the small number of objects/points involved, not having the points spatially indexed might not matter.  I would invest some time trying to get this to work technically, either with a query layer or XY event layer, before committing to the larger investment of having a PostgreSQL reporting database stood up.

As I said, I don't work with DB2, so I don't have any specific pointers, but I do encourage you to explore XY event layers as Jake suggests to see if they will work for you.