Latest Contributions by Bud

(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: --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

Oracle (with either SDE.ST_GEOMETRY or SDO_GEOMETRY FCs in mind): What are the options for storing additional shape information in a feature class? With single-part point information, we can simply create X and Y numeric columns. But what about lines, polygons, and multi-part points? Since having multiple geometry columns in a FC isn't supported, what datatypes would be suitable for storing additional shape information columns? (The idea being, ArcGIS would ignore any datatypes it doesn't recognize, which isn't the case for true geometry columns; they cause known issues in ArcGIS — i.e. versioning.) Some hypothetical ideas that come to mind (I could populate the columns via a trigger or, in some cases, attribute rules(?)): SDO_GEOMETRY_ARRAY MDSYS.VERTEX_SET_TYPE Nested table column OdciVarchar2List (varray) A custom user-defined object type SDO_ORDINATE_ARRAY (convert to text?) WKT in a text column JSON in a text column (or in a true JSON column) JSON, not GeoJSON, since the GeoJSON standard doesn't support M-enabled features. Note: Storing text-based formats like WKT or JSON would work. But from testing, converting from WKT or JSON to SDO_GEOMETRY is a bit slower than I'd like. Even more so for converting WKT to SDE.ST_GEOMETRY. I'm aware that there are alternatives like creating a separate/parallel FC to store additional true geometries. That would work, but the downside is that it requires adding dozens of new FCs to an already cluttered geodatabase. I want to explore what the alternative options are — as a learning exercise/experiment. Related: ArcGIS should ignore non-FC columns Event layers for lines, polygons, and multi-part points (via WKT) Event layers/query layers: Control caching settings for better performance ST_GEOMETRY FC: Options for using Oracle Spatial functions ... View more

Why does the search bar in the search page obscure my profile picklist?     ... View more

What is the purpose of the grey spacing in blog post comments? Examples: https://community.esri.com/t5/arcgis-pro-documents/arcgis-pro-roadmap-june-2022/tac-p/1187579/highlight/true#M285 https://community.esri.com/t5/arcgis-pro-blog/transformation-warning-what-does-it-mean-and-what/bc-p/1082862/highlight/true#M360 Thanks. ... View more

ST_GEOMETRY vertices should be easier to access (Oracle).  Currently, ST_GEOMETRY vertices are obscured in a BLOB/LOB column within the shape object, which makes accessing the vertices difficult. An alternative idea might be: store the vertex ordinates in a more transparent datatype, such as a varray, a nested table column, or some other table collection datatype — as an additional/separate attribute within the existing SHAPE object column (not to be confused with creating an entirely new column in the table). That would make it a lot easier to work with ST_GEOMETRY vertices. We're aware there are workarounds like cross joins and parsing WKT. But they're too clunky & slow. An easier-to-access collection datatype would be better. ... View more

When copy/pasting an object in Catalog: We should  be presented with a window to define the name of the object as part of the pasting process. We shouldn’t need to go looking for the object in Catalog and rename it after-the-fact, which can be a nuisance in GDBs with hundreds of objects. We should also have the option to specify the spatial type as part of the pasting process. For example, ST_GEOMETRY vs. SDO_GEOMETRY, etc. I'm aware those things can be done through GP tools, instead of copy/pasting. But copy/pasting is often the preferred method for duplicating FCs, rather than using GP tools. So it would be nice if that functionality could be added to copy/paste. I think ArcMap had that functionality, at least when copy/pasting in the same GDB when there’d be a name conflict (we can’t have two objects with the same name). If I remember correctly, Pro handles that scenario by automatically adding a “_1” suffix to the name, which isn’t ideal. I’d rather define the new name myself. ... View more

I have multiple connections to two different databases in ArcGIS Pro: DEV and PROD. The objects in the two DBs look identical. So it’s easy to mix up what connection/DB is being used by an object in the map. There are helpful practices we can use, like only using a single DB/connection in a given map. And that does help. But it’s still easy to get your wires crossed and accidentally edit/test in the wrong DB. We can also check an object’s source in the table of contents properties to see what DB the object is in. But that only helps us if we're actually aware that we might not be using the right connection/DB. There are still cases rare where we unknowingly do stuff using the wrong connection. Could Esri make it more obvious what connection is being used by a given object in Pro? For example, put the connection or DB name in brackets or greyed-out text — after the object name in the table-of-contents or in the attribute window. Something like that? Thoughts? We have that kind of functionality in SQL clients like Toad and SQL Developer. Tabs/queries have the DB name right in the tab title. We can also color-code connections & tabs, such as green for DEV and red for PROD. So those kinds of things help avoid confusion about what connection/DB we’re using. Whereas with ArcGIS Pro, it’s less obvious and easier to make mistakes. ... View more