[GIS] SQL for spatial view of line start/end points (Oracle ST_GEOMETRY)

arcgis-desktopenterprise-geodatabaseoracle-dbmssqlst-geometry

How do I create a spatial view that generates dynamic point geometry of the start point and end point of a line feature class?

I have successfully made a similar view that is a buffer of a line feature class with this SQL:

SELECT OBJECTID, SDE.ST_BUFFER(SHAPE, 1) AS LINE_BUFFER FROM ENG.ROAD

I thought I'd be able to use similar SQL to generate start points of lines using the PointN function:

SELECT OBJECTID, SDE.ST_POINTN(SHAPE, 1) AS START_POINT FROM ENG.ROAD

The view creates successfully, however it only creates a tabular view, not a spatial view. What have I done wrong?

I'm also unsure how to get the end point index position, but I'm not even thinking about that yet.

Best Answer

As Vince suggested, the query needed to return ST_Geometry, not ST_Point:

SELECT OBJECTID, SDE.ST_GEOMETRY (SDE.ST_ASTEXT (SDE.ST_STARTPOINT (SHAPE)),300049) 
    AS START_POINT 
FROM ENG.ROAD

I'm surprised how many functions were needed to convert from lines to endpoints. I would have thought there would have been a single function to go from ST_Point to ST_Geometry, but the only way I could find to do it was to throw ST_ASTEXT in the middle. The view is quite slow, probably because of this.

Update:

There's an ESRI/Oracle bug that prevents query layers from successfully using ST_POINT in Arcmap:

Bug: Unable to define a query layer in ArcGIS where the data source uses an st_geometry subtype in Oracle

Get ArcMap to recognize ST_POINT (SDE.ST_GEOMETRY)

The bug explains why I'm having problems. If ArcMap query layers were able to recognize ST_POINT like they're supposed to, then this question would be unnecessary.

Related Question