Using geometry stored in SQL Server table instead of WKT as input to function

Question

I am new to SQL Server,

I have a function called STVectorize that takes a geometry and breaks it down into its constituent vectors:

SELECT e.[id], e.[element_id], e.[subelement_id], e.[vector_id], 
       e.[sx], e.[sy], e.[ex], e.[ey], 
       e.length, geom.STAsText() as geomWKT
 FROM [dbo].[STVectorize] (geometry::STGeomFromText(
         'COMPOUNDCURVE ((2172514.83221109 255830.454793766 0 10000,2172513.51769371 255834.852536798 10004.59,2172260.0640846 256682.786431864 0 10889.5959),
          CIRCULARSTRING (2172260.0640846 256682.786431864 0 10889.5959,2172227.98611614 256834.458935502 0 0,2172221.11092693 256989.333969623 0 11200))', 2274)) as e

Output results for geomWKT:

LINESTRING (2172514.83221109 255830.454793766, 2172513.51769371 255834.852536798)
LINESTRING (2172513.51769371 255834.852536798, 2172260.0640846 256682.786431864)
CIRCULARSTRING (2172260.0640846 256682.786431864, 2172227.98611614 256834.458935502, 2172221.11092693 256989.333969623)

This is all well and good, but I would like the STVectorize function to operate on the geometry stored in a SQL Server table. The table is called Ramp_A and the geometry column is called GeomCol1.

How would you modify the SQL statement to use the geometry in GeomCol1 rather than specify its WKT in geometry::STGeomFromText?

I would like to be able to use any geometry from any SQL Server table as input to this function, by specifying the table name and the geometry column name in the query.

---

After @jport's answer, I did a little digging and found this web page that explains how to use CROSS APPLY (scroll down to the section "Joining table and table valued function using CROSS APPLY")

SELECT A.author_name, B.id, B.book_name, B.price
FROM Author A
CROSS APPLY fnGetBooksByAuthorId(A.Id) B

Applying this (more or less) gives

SELECT e.[id], e.[element_id], e.[subelement_id], e.[vector_id], 
       e.[sx], e.[sy], e.[ex], e.[ey], 
       e.length, geom.STAsText() as geomWKT
 FROM Ramp_A a
CROSS APPLY [dbo].[STVectorize] (a.[GeomCol1]) e

Which is exactly what I was looking for. It gives the exact same results as my original SQL statement with the WKT. This also works on tables with multiple records and multiple geometries (I tried it with 2 records / 2 geometries).

The STVectorize function I have linked does not accept curved geometries.

You can get the full STVectorize function plus 195 other useful SQL Server functions related to geoprocessing, COGO, LRS and others by searching for SQL Server Spatial TSQL Stored Procedures.

Answer 1

You can use a cross apply. I wouldn't advise running this code on a table with lots of records but in a case where you have a single geometry this works.

select * from  yourTable cross apply dbo.STVectorize(YourGeomField)

If you have a table of multiple geometries then consider setting a variable

declare @test geometry
set @test = (select yourGeomField
from yourTable
where yourFilterField = 'YourFilterValue')

and then run the top query.