Latest Contributions by Bud

‎06-17-2022

Yeah, that's the source of my query. I mention it in the link in the original post.

‎06-17-2022

If I understand correctly, it's not possible to construct an ST_Geometry from ST_Point values. For example, if I want to extract the first segment of a polyline using ST_GEOMETRY functions: ...it would be nice if I could do something like this: select sde.st_geometry( sde.st_pointn(shape,1), sde.st_pointn(shape,2) ,26917) as shape from my_lines Error: ORA-06553: PLS-306: wrong number or types of arguments in call to 'ST_GEOMETRY' But instead, I think I need to go to the effort of extracting the X and Y coordinates of each ST_POINT vertex as numbers, then concatenate them together in a WKT string: select 'linestring('|| sde.st_x(sde.st_pointn(shape,1))||' ' || sde.st_y(sde.st_pointn(shape,1))||', ' || sde.st_x(sde.st_pointn(shape,2))||' ' || sde.st_y(sde.st_pointn(shape,2))||')' as wkt, sde.st_geometry('linestring('|| sde.st_x(sde.st_pointn(shape,1))||' ' || sde.st_y(sde.st_pointn(shape,1))||', ' || sde.st_x(sde.st_pointn(shape,2))||' ' || sde.st_y(sde.st_pointn(shape,2))||')' ,26917) as shape from my_lines That's pretty painful and inefficient. Especially in cases where we're dealing with many vertices (and multi-part geometries). Idea: Could Esri give us a way to construct ST_Geometries from ST_Points, instead of extracting & concatenating XYs as WKT? For example, enhance the ST_Geometry() constructor function to allow ST_Points as arguments. Or give us an aggregate function for aggregating features together, similar to what we have with other datatypes like SDO_Geometry. Thanks.

‎06-17-2022

What's the purpose of the ST_GeomCollection function? (Oracle) ST_Geometry function reference > ST_GeomCollection ST_GeomCollection constructs a geometry collection from a well-known text representation. The examples in the docs don't make much sense to me. Is ST_GeomCollection a function? If so, what's an actual example of it being used? i.e. SDE.ST_GeomCollection(...) Or is it a datatype? I don't think I understand.

‎06-17-2022

SDE.ST_GEOMETRY for PostgreSQL has an ST_GeomFromCollection function: --Return each linestring in the multilinestring SELECT sde.st_astext((sde.st_geomfromcollection(gst.shape)).st_geo) shapetext, ((sde.st_geomfromcollection(gst.shape)).path[1]) path FROM ghanasharktracks gst; shapetext path ----------------------------------------------------------------------------------------------------------- "LINESTRING Z ( 1.00000000 1.00000000 0.00000000, 1.00000000 6.00000000 0.00000000)" 1 "LINESTRING Z ( 1.00000000 3.00000000 0.00000000, 3.00000000 3.00000000 0.00000000)" 2 "LINESTRING Z ( 3.00000000 1.00000000 0.00000000, 3.00000000 3.00000000 0.00000000)" 3 "LINESTRING Z ( 4.00000000 1.00000000 0.00000000, 4.00000000 6.00000000 0.00000000)" 4 It would be really helpful if we had the same functionality in Oracle. For example, it would simplify this query: Select feature parts and vertices as rows (ST_GEOMETRY) I don't see why that couldn't be done in Oracle. The return type could be a nested table. In the FROM clause, we could cross join to the nested table to get the feature parts. Related: Cross-joining with a table object propagates rows (without needing Table() function) At what version did the TABLE() keyword for table collection expressions become optional? Cross join to varray without using Table() expression Could Esri consider creating an ST_GeomFromCollection function for Oracle?

‎06-16-2022

I suppose one way to do it would be to modify the existing query: Details: (1) Keep only the horizontal lines. Exclude the vertical lines. where startpoint_y = endpoint_y Here's a sample horizontal line: OBJECTID STARTPOINT_X STARTPOINT_Y ENDPOINT_X ENDPOINT_Y SHAPE ---------- ------------ ------------ ---------- ---------- -------------- 219 0 0 0 1 [SDO_GEOMETRY] (2) Add an additional outer query where we generate 5 vertices (a polygon) from the existing 2 vertices (from the original horizontal line): startpoint_x, --vertex 1 (original vertex from the horizontal line) startpoint_y, endpoint_x, --vertex 2 (original vertex from the horizontal line) endpoint_y, endpoint_x, --vertex 3 endpoint_y+1, startpoint_x, --vertex 4 startpoint_y+1, startpoint_x, --vertex 5 (same as vertex 1) https://gis.stackexchange.com/q/433083/62572 startpoint_y, (3) Just like with the lines query, construct a polygon geometry by concatenating the coordinates together into WKT. sdo_geometry('polygon(('||startpoint_x||' '||startpoint_y||', '||endpoint_x||' '||endpoint_y||', '||endpoint_x||' '||(endpoint_y+1)||', '||startpoint_x||' '||(startpoint_y+1)||', '||startpoint_x||' '||startpoint_y||'))',26917) as shape (4) Now, the coordinates look like this (the vertex direction is counterclockwise; vertex 1 and 5 are both 0,0): (5) This is what the full query looks like: --create or replace view sdo_geom_grid_polygons_vw as ( select objectid, 'polygon(('||startpoint_x||' '||startpoint_y||', '||endpoint_x||' '||endpoint_y||', '||endpoint_x||' '||(endpoint_y+1)||', '||startpoint_x||' '||(startpoint_y+1)||', '||startpoint_x||' '||startpoint_y||'))' as wkt, sdo_geometry('polygon(('||startpoint_x||' '||startpoint_y||', '||endpoint_x||' '||endpoint_y||', '||endpoint_x||' '||(endpoint_y+1)||', '||startpoint_x||' '||(startpoint_y+1)||', '||startpoint_x||' '||startpoint_y||'))',26917) as shape from ( with dimension as ( select 0 as point from dual union all select level from dual connect by level <= 10 ), points as ( select a.point as startpoint, b.point as endpoint, c.point as fixed from dimension a cross join dimension b cross join dimension c where b.point - a.point = 1 ) select cast(rownum as number(38,0)) as objectid, startpoint_x, startpoint_y, endpoint_x, endpoint_y from ( select startpoint as startpoint_x, fixed as startpoint_y, endpoint as endpoint_x, fixed as endpoint_y from points union all select fixed as startpoint_x, startpoint as startpoint_y, fixed as endpoint_x, endpoint as endpoint_y from points ) order by startpoint_x, startpoint_y, endpoint_x, endpoint_y ) where startpoint_y = endpoint_y --get horizontal lines only --);

‎06-16-2022

SDE.ST_GEOMETRY; Oracle 18c; 10.7.1 EGDB; ArcGIS Pro 2.6.8: I have a feature class that I want to move to an existing feature dataset. I wasn't aware until recently, but it turns out it's possible to move a FC to a FD by dragging and dropping it into the FD via catalog, which is good. However, when I attempt to move the FC to the FD, I get an error: Move failed. The Spatial references do not match. I took a look at the properties of the FC and the FD. The spatial references appear to be identical: Does anyone know why I might be getting that error if the spatial references are the same? Note: The FC has lots of existing dependencies on it, so I don't want to workaround the issue by doing something like the following: 1. Rename the FC as <existing name>_OLD. 2. Create a new FC from scratch inside the FD using the <existing name>. 3. Load the data from the _OLD FC into the new FC. 4. Delete the _OLD FC. As mentioned, the dependencies would make that problematic/risky. I want to keep my existing FC.

‎06-16-2022

I have a SQL script that quickly generates grid line features, for testing purposes, using whatever grid dimensions I need: Generate SDO_GEOMETRY grid using SQL. The result is a spatial view (can be easily exported as a feature class). I'm aware that there are GP tools that can generate grids too. But for my use case, I like this SQL option. The lines are split at the intersections. So this 10x10 grid has 220 lines: Question: Is there a way to convert the grid lines to polygons? (i.e. a polygon for each grid cell) I tried creating a polygon envelope FC and then I looked for a GP tool that could split the polygon via multiple lines. But I haven't found a GP tool that can do that yet. Advanced license: ArcGIS Pro 2.6.8 and ArcMap 10.7.1. Oracle 18c EGDB.

‎06-16-2022

@DanPatterson As you mentioned, Z-values aren't enabled in the ArcToolbox GP tool, so that's good. I find it easier to use the Catalog method...but good to be reminded of the GP tool too.

‎06-16-2022

When creating a new feature class in Catalog, Z Values is enabled by default. None of our data is 3d, so we uncheck that Z box for each feature class we create. In my opinion, that checkbox should be disabled by default, since I would think organizations have more 2d FCs than 3d FCs. It might seem like a small thing, but I actually create new FCs many times per day — as part of testing and development work. So, unchecking that box becomes a bit of an annoyance. Could Esri consider disabling that checkbox by default? Or give us an optional setting somewhere to change that behavior?

‎06-16-2022

Add Geometry Attributes (Data Management) The values in these fields are not automatically recalculated after edits. If you edit the features, you'll need to run this tool again to update the field values. It would be great if those fields were dynamic, similar to the Shape_Length field in the attribute table, but as optional fields (they could be turned off by default, to avoid unnecessary performance hits). For example, it would be helpful to have dynamic fields for: Startpoint, midpoint, and endpoint Part count and point count This functionality wouldn't necessarily need to be a GP tool. Instead, what if we could just open the Design > Fields window in ArcGIS Pro, and then just click a checkbox to make the geometry attributes visible? That might be ideal...just like we can do for the dynamic Shape_Length field. I'm aware that those values can be calculated using attribute rules. That is technically possible, but some of those Arcade calculations are non-trivial, such as getting the midpoint. Many of the fields in the "Add Geometry Attributes" tool would be helpful to a large number of organizations. Why make of us all fumble around with custom Arcade scripts to calculate them? Why not just do it OOTB through ArcObjects/default fields?

‎06-16-2022

That's an interesting idea. If I understand correctly, I think you're saying we could: ...reference those "Geometry Attributes" (fields in the attribute table but not in the underlying db table) right in Arcade. I think I've done the same sort of thing with the $feature.shape_length: https://community.esri.com/t5/arcgis-pro-ideas/hascurve-arcade-geometry-property/idc-p/1149449/highlight/true#M18873 Is that what you were thinking? Never mind. Those "Geometry Attribute" fields aren't dynamic. Add Geometry Attributes (Data Management) The values in these fields are not automatically recalculated after edits. If you edit the features, you'll need to run this tool again to update the field values.

‎06-16-2022

ArcGIS should use the new SDO_GEOMETRY spatial index type: SPATIAL_INDEX_V2. 5.1.1Using System-Managed Spatial Indexes Effective with Release 12.2, spatial indexes can be system-managed by specifying INDEXTYPE=MDSYS.SPATIAL_INDEX_V2 at index creation. You are strongly encouraged to use this index type for all new spatial indexes you create, regardless of whether the spatial table or the spatial index is partitioned, and you may also want to use it if you decide to re-create legacy spatial indexes. Currently, if I create a new SDO_GEOMETRY feature class via Catalog in ArcGIS Pro 2.6.8, it uses the old index type: CREATE INDEX "INFRASTR"."A7378_IX1" ON "INFRASTR"."PRO_SDO_FC" ("SHAPE") INDEXTYPE IS "SDE"."ST_SPATIAL_INDEX" PARAMETERS ('ST_GRIDS = 0 ST_SRID = 26917 ST_COMMIT_ROWS = 10000 PCTFREE 10 PCTUSED 90 INITRANS 4 STORAGE (FREELISTS 4 MINEXTENTS 1 PCTINCREASE 0)');

‎06-15-2022

This example might be helpful to some people: Arcade Code Review: Set polyline M-values to cumulative length of line

‎06-15-2022

What are the steps needed to convert a Oracle table to a FC in an EGDB? For example: 1. Take an existing Oracle table. 2. Add a geometry column if needed. 3. If it's an SDO_GEOMETRY column, then register with USER_SDO_GEOM_METADATA? 4. Add a spatial index using the Add Spatial Index GP tool? 5. Register with the geodatabase using Catalog in ArcMap? 6. Other? I've tried various combinations of the above steps, but I often have problems like the resulting FC's attribute table is empty when I view it in ArcMap. Or the spatial reference is broken. Or the geometry type (point/line/polygon) isn't properly defined, etc. I usually give up at that point and try something else, like create an empty FC via ArcMap, create a spatial view on the Oracle table, and then use the Load tool in Catalog to load the data into the empty FC. But that's pretty clunky...I'm hoping there's a better way. Thanks.

‎06-15-2022

Yeah, could be. Although, as a "Regular Contributor"/non-Esri staff person, I'm not aware of any special powers that I have. And the person who accidently did it to me today is a "New Contributor II"/non-Esri staff member. So in those two cases, my guess is neither of us have special ranking/abilities.

My Ideas

Latest Contributions by Bud

Re: Convert grid lines to polygons

Construct ST_Geometry from ST_Points

What's the purpose of the ST_GeomCollection function?

ST_GeomFromCollection function for Oracle

Re: Convert grid lines to polygons

Moving FC to feature dataset fails, despite spatial references being the same

Convert grid lines to polygons

Re: When creating a new FC, the Z-values checkbox should be disabled by default

Optional setting to disable Z-values by default

Add Geometry Attributes GP tool: Fields should be dynamic

Re: How to get the mid point (X,Y) of a polyline segment in Arcade ?

ArcGIS should use the new SDO_GEOMETRY spatial index type: SPATIAL_INDEX_V2

Re: Arcade: Get paths array from feature — why so much overhead?

Convert a Oracle table to a feature class?

Re: Why do I have the ability to accept a solution in someone else's post?

Send configurable email notifications from ArcGIS ...

Re: How reliable are ArcGIS Online webhooks?

How reliable are ArcGIS Online webhooks?

Sharepoint List Connector

AGOL or Portal Lists app, including Active Directo...

Experience Builder Tips and Tricks

GIS Jobs

Roads and Highways User Group (RHUG)

Mapping and Charting Enterprise Databases

ArcGIS Arcade