Latest Contributions by Bud

We also need to be able to remove the Z dimension from ST_GEOMETRY. Related post here:  I need to remove Z dimension on spatial column (ST_GEOMETRY) with Esri on Oracle using the PL/SQL packages ... View more

This post is quite old. I wonder if anything has changed since then? Any enhancements added to ArcGIS, etc.? ... View more

Yeah, that’s what I meant. Hopefully it works. ... View more

I think it's a way of tricking SDE. If the system tables for that FC are broken, then if you create a new FC in it's place, SDE will replace the broken system table data with new system table data. So hopefully when you delete the new FC this time, the system table data will be deleted successfully too. And then the FC will disappear from Catalog, which is what you want.   My limited experience is: Sometimes these things take multiple attempts. And sometimes, if you just wait a few days, it resolves itself. I'm not an expert though. So I could be wrong. ... View more

After digging a bit more, I did come across a PDF that mentions 'MULTILINESTRING M': https://portal.ogc.org/files/?artifact_id=25355 (PDF download). Search the PDF for "MULTILINESTRING M". So, from that limited evidence, I suppose the extra M might be the correct syntax after all. Maybe the problem is SDO_GEOMETRY, not ST_GEOMETRY. Could Esri delete this idea? I don’t think I can delete it myself. ... View more

Esri confirmed it's a defect: BUG-000148390. ... View more

Thanks for the info. I talk more about my use case here: Practical GIS automation for non-IT users ... View more

For example, a query that generates rows for each vertex (3000 features):   1 SELECT a.ROAD_ID 2 ,b.NUMBERS VERTEX_INDEX 3 ,a.SDE.ST_X(SDE.ST_PointN(a.SHAPE, b.NUMBERS)) AS X 4 ,a.SDE.ST_Y(SDE.ST_PointN(a.SHAPE, b.NUMBERS)) AS Y 5 FROM ROAD a 6 CROSS JOIN NUMBERS b 7 WHERE b.NUMBERS <= SDE.ST_NumPoints(a.SHAPE) ---------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5996 | 1545K| | 262 (1)| 00:00:01 | | 1 | MERGE JOIN | | 5996 | 1545K| | 262 (1)| 00:00:01 | | 2 | INDEX FULL SCAN | R23715_SDE_ROWID_UK | 30 | 90 | | 1 (0)| 00:00:01 | |* 3 | SORT JOIN | | 3997 | 1018K| 2392K| 261 (1)| 00:00:01 | | 4 | TABLE ACCESS FULL| ROAD | 3997 | 1018K| | 34 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- " 3 - access(""B"".""NUMBERS""<=""SDE"".""ST_NumPoints""(""A"".""SHAPE""))" " filter(""B"".""NUMBERS""<=""SDE"".""ST_NumPoints""(""A"".""SHAPE""))"   That query takes 30 seconds to run, which is far too long. In comparison, pretty much any other query on the FC runs instantly.   Testing/benchmarking: I created two tables (both contain 10,000 rows): ROADS_BM and ROADS_STARTPOINT_BM. I ran simple queries on the tables using each of the functions that are involved. Here are the results:   +-----------+------------------+---------------------------------------------------------------------------+ | TIME(sec) | RETURN TYPE | QUERY | +--------------+-----------+------------------+---------------------------------------------------------------------------+ | ST_X | < 0.5 | Double precision | SELECT ROAD_ID FROM ( | | | | (Number) | SELECT ROAD_ID, SDE.ST_X(SHAPE) AS X FROM ENG.ROADS_STARTPOINT_BM | | | | | ) WHERE X IS NOT NULL ORDER BY ROAD_ID | +--------------+-----------+------------------+---------------------------------------------------------------------------+ | ST_Y | < 0.5 | Double precision | SELECT ROAD_ID FROM ( | | | | (Number) | SELECT ROAD_ID, SDE.ST_Y(SHAPE) AS Y FROM ENG.ROADS_STARTPOINT_BM | | | | | ) WHERE Y IS NOT NULL ORDER BY ROAD_ID | +--------------+-----------+------------------+---------------------------------------------------------------------------+ | ST_NumPoints | < 0.5 | Integer | SELECT ROAD_ID FROM ( | | | | | SELECT ROAD_ID, SDE.ST_NumPoints(SHAPE) AS NUM_POINTS FROM ENG.ROADS_BM | | | | | ) WHERE NUM_POINTS IS NOT NULL ORDER BY ROAD_ID | +--------------+-----------+------------------+---------------------------------------------------------------------------+ | ST_PointN* | **9.5** | ST_POINT | SELECT ROAD_ID FROM ( | | | | (ST_GEOMETRY | SELECT ROAD_ID, SDE.ST_PointN(SHAPE,1) AS ST_POINT FROM ENG.ROADS_BM | | | | subclass) | ) WHERE ST_POINT IS NOT NULL ORDER BY ROAD_ID | +--------------+-----------+------------------+---------------------------------------------------------------------------+   Function documentation: ST_X, ST_Y, ST_NumPoints, ST_PointN The result? ST_PointN is the problem. Its 9.5 second response time is slow compared to the other functions.  So I think that proves that the performance of ST_PointN needs to be improved.             ... View more