Latest Contributions by Bud

In ArcGIS Pro, it seems like XY Event Layer functionality has been removed from context menus. It was easier to create XY Event Layers in ArcMap. Just right-click the layer in the TOC: Whereas in Pro, right clicking gets us a "Display XY Data" tool, but that's misleading because that tool produces a static output in a FC. That's not what I want; I want to create a dynamic event layer. The "Add Data" context menu in Pro doesn't have an XY event layer option either:   The same thing applies to linear referencing Route Event Layers. That functionality has been removed from menus too. Is there a reason why event layers were made less convenient? (removed from the menus) ... View more

I came across a mechanism that can help with slow Oracle queries in some scenarios. I thought I'd share. If we have a slow Oracle query, we can add the /*+ result_cache */ hint to the SQL. That hint tells Oracle to cache the result of the query until the next time the data is edited. Query Result Cache in Oracle Database I think that's a pretty powerful concept. Why re-compute a query every time we use it? Why not save the results until the underlying data changes? --get st_geometry line startpoint select /*+ result_cache */ objectid, sde.st_x(sde.st_startpoint(shape)) as startpoint_x, sde.st_y(sde.st_startpoint(shape)) as startpoint_y from sidewalks --initial load takes 20 seconds due to slow st_geometry functions (14,000 features) I was quite happy when I figured that out! With that said, there are significant limitations: A big problem is: The query can't output a geometry column (object column)...or rather, it can, but the result cache hint won't be invoked. In other words, the query can't be a traditional spatial query. That's a real bummer. (link1, link2, link3) With that said, we can still use geometry columns in the query, like I did in the above startpoint query. We just can't output a geometry column in the final result. But there is a workaround to #1 above...when the geometry type is points: Make an XY Event Layer in ArcGIS that converts the X & Y number columns to points in the map. I've found XY Event Layers to be surprisingly fast...so that solution seems to work well. But of course, that doesn't help us when we want the output to be lines, polygons, or multi-part points, since we can't create an Event Layer for those geometry types: Idea: Event layers for lines, polygons, and multi-part points (via WKT). I'm not aware of a workaround. And to be clear, the Query Result Cache works perfectly fine for non-spatial queries, which I have lots of. If the underlying data is edited often, then this might not be the right solution. For example, for my startpoint query above, it takes 20 seconds to re-cache the result, which feels like an eternity. If editing doesn't happen often, then it's not a problem...I can deal with the slow re-querying once and a while. But if editing happens regularly, then the entire query needs to be re-computed, even if only a single row was affected by edits. Whereas other solutions, such as populating a helper FC via attribute rules, database triggers, a materialized view, etc...only need to sync the edited rows, not the entire dataset. So those options might be better in some cases. Options for computing fields. For what it's worth, ArcGIS Pro seems to be a lot better at handling a cached XY Event Layer than ArcMap. ArcMap re-queries too often when making un-versioned edits...it re-queries after any mouse-click that modified a feature, even if unsaved, which is extremely aggravating. And when re-querying is happening in ArcMap, the mouse is captive to the loading icon...we can't do anything while we're waiting. Whereas ArcGIS Pro loads the layer in the background, allowing us to continue working while it loads. And in Pro, un-versioned edits won't trigger a re-query until the edits are saved, which is good. Note: Both ArcGIS Pro and ArcMap handle versioned edits properly — the XY Event Layer doesn't re-query until the edits are saved. There could be other limitations too. And I'm sure there are other database-related things to consider when storing results in the Oracle cache. Consult your DBA if in doubt, etc... Summary: As with any performance improvement mechanism, there are lots of tradeoffs...it's useful in some cases, but not others. As mentioned, there are lots of alternative techniques we can use that have their own pros/cons. Maybe the benefit to this one is that it's dead-easy to implement (just add a line to your query), doesn't require begging the DBA team for stuff,  and doesn't require custom code/additional FCs to manage. I really like the idea of caching, rather than constantly re-computing for no reason. I submitted a similar idea, but for caching in the application in ArcGIS: Event layers/query layers: Control caching settings for better performance.  Cheers. ... View more

I've noticed that some organizations forget to enable SPATIAL_VECTOR_ACCELERATION in Oracle databases...now that Oracle Spatial is free. Oracle Spatial Vector Acceleration: Flick the Switch So for any of us who use SDO_GEOMETRY, we need to remember to enable that parameter — to make use of it's performance boosting functionality. (It's enabled by default in 21c+.) I just thought I'd mention that...for those of us who were unaware.   Note: I've asked the Oracle Spatial team if there's a way to check if SPATIAL_VECTOR_ACCELERATION is enabled (as a non-dba). But I haven't found a solution yet. ... View more

(edited) Oracle 18c 10.7.1 EGDB: I thought I'd share a test I did recently — for anyone who might be trying to decide between SDE.ST_GEOMETRY and SDO_GEOMETRY. Select the startpoint X & Y from 14,000 single-part lines: SDE.ST_GEOMETRY: select sde.st_x(sde.st_startpoint(shape)) as startpoint_x, sde.st_y(sde.st_startpoint(shape)) as startpoint_y from sidewalks STARTPOINT_X STARTPOINT_Y ------------ ------------ 668287.112 4869366.88 ... Time: 20 seconds   SDO_GEOMETRY select     sdo_util.get_coordinate(shape,1).sdo_point.x as startpoint_x,     sdo_util.get_coordinate(shape,1).sdo_point.y as startpoint_y from     sidewalks STARTPOINT_X STARTPOINT_Y ------------ ------------ 668287.112 4869366.88 ... Time: 0.9 seconds   Result: Getting the startpoint XY from SDO_GEOMETRY is 20x faster than SDE.ST_GEOMETRY in this case. That's consistent with the testing I've done over the last couple of years. I've found ST_GEOMETRY functions to be slower than SDO_GEOMETRY in general. Of course, there are benefits to ST_GEOMETRY too, such as it being easier to learn than SDO_GEOMETRY (in my experience).   I just thought I'd share those results in case they're useful to anyone.   ... View more

If I understand correctly, ArcGIS Pro stores blank M-values as negative infinity/NaN: Bud_0-1656934387029.png --Oracle 18c SDE.ST_GEOMETRY EGDB select sde.st_m(sde.st_endpoint(shape)) as endpoint_m from my_line ENDPOINT_M ---------- -Infinity It seems weird to me that blank M-values would be stored as negative infinity/NaN. It wouldn't occur to me to query for negative infinity/NaN using SQL. I would query for null, since null means unknown or missing. Could that behavior be changed, so that blank M-values are stored as null?   Edited Aug 16, 2023 Related: Documentation about empty strings, nulls, and NaN in ArcGIS Make it possible to store NaN values for Z values ... View more

What are some tips about nulls in SQL you can share? As GIS professionals, not SQL developers, it's easy to make mistakes when dealing with nulls in SQL. For example: We can't do math on nulls: 1 + NULL = NULL. Sometimes, people assume nulls will be automatically treated as zero. But that's not the case. We would need to explicitly convert nulls to something else (like zero) using COALESCE(), NVL(), etc.. NULL doesn't equal NULL. More specifically, null is neither equal to, nor is it not equal, to null. WHERE NULL = NULL won't return any results. Neither will WHERE NULL <> NULL.  Further, be careful for cases like WHERE A <> B. Let's say A is 1 and B is null. That row won't get selected, because null is neither equal to, nor is it not equal, to anything. Joining on null values won't work either. Related:  IS NOT DISTINCT FROM SQLite: Null-safe equals comparison using IS instead of =. Aggregate functions such as AVG() will ignore nulls. AVG(4,4,4,4,4,NULL) will evaluate to 4 (not 3.33). But be careful when doing math on the result of aggregate functions. Example: SUM(A) + SUM(B). Remember, we can't do math on nulls. So if the result of either of those aggregate functions is null, then the entire expression will evaluate to null. Different databases concatenate nulls differently. For example, in Oracle, nulls will be ignored in this concatenation: 'FOO ' || NULL || 'BAR' …will evaluate to… FOO BAR. But in other databases, that would result in NULL. Different databases treat empty strings differently. For example, in Oracle, SELECT '' FROM MY_TABLE will result in null. That's important to know for things like #1 above. We wouldn't want to replace NULL with '', since '' would simply result in null. With that said, putting a space ' ' between the single quotes would work; that won't get automatically converted to null. Are there any other gotchas or tips about nulls you can share? Or any improvements or corrections to the points above?   Related: NULL in SQL: Indicating the Absence of Data Oracle Documentation - NULLS The Three-Valued Logic of SQL ... View more