In ArcSDE, SQL Studio Manager 2012, How to get pixel data using a point layer?

Question

I was using 'where' and 'ST_Intersects' with polygon data to get values 'under' my point layer.

Example:

Select rd.[FORESTNAME] as Forest, Count(Distinct morel.[OBJECTID]) AS Observations
From [dbo].[RANGERDISTRICTS] rd, [dbo].[ACTUALMOREL] morel
where rd.Shape.STIntersects(morel.[Shape]) = 1
Group by rd.[FORESTNAME]

I think ST_Raster.getValue looks promising but I want to use a point layer to get the data, not enter each coordinate pair for each point.

SO now I have a layer (Slope raster) that I want to find the values under my point layer.

I THINK I was able to get the value using cursors: (I just don't remember which table I get the coordinates from :/ )

Declare morelx double
Declare morely double
Declare morelcoord CURSOR FOR
Select Shape.STX, Shape.STY from table actmorels 
Open morelcoord
Fetch next from morelcoord into morelx, morely
while (00FETCH_STATUS=0)
Begin
 SELECT image.getValue(1,0,morelx,morely) Aspect,
 FROM [dbo].[ASPPROJ]
 WHERE image.raster_id = 1;
 Group by Aspect
 Fetch Next from morelcoord into morelx, morely
End

• Michael Stimson: I would use the 'Sample' geoprocessing tool. Is there a reason why you want to do this in SQL?
• Lauren: class work, it is a SQL GIS class. I am going to join a bunch of the statements and create a view next. I have already asked my instructor and classmates but they didn't know.
• Michael Stimson: can you cursor through each record (point) and getValue for each one, possibly updating their value?
• Michael Stimson: I don't currently have the ability (or the time) to test that but it looks like it should work. Perhaps put that in as an answer (and format the code) for the benefit of future users.
• Michael Stimson: That depends on your geometry type, some databases have PG_GEOMETRY, ST_GEOMETRY, WKT or WKB for the table... there will be a function to get the coordinates of the point but it depends on your database and geometry type... that would be worthy of a new question: how do I get the X and Y coordinate of a point in XXXX with YYYY geometry type? Related: gis.stackexchange.com/questions/135949/…