2

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.

  • 1
    What does the documentation say the ST_PointN function returns? If it isn't an ST_Geometry, then that would explain that. The documentation also shows an ST_EndPoint function. So now you can look for a function to convert a ST_Point object to ST_Geometry, and you'll be set. – Vince Jul 11 '16 at 21:50
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.