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


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)

    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

  • I would use the 'Sample' geoprocessing tool. Is there a reason why you want to do this in SQL? May 5, 2015 at 0:22
  • 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.
    – Lauren
    May 5, 2015 at 0:24
  • 1
    can you cursor through each record (point) and getValue for each one, possibly updating their value? May 5, 2015 at 0:26
  • 1
    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. May 6, 2015 at 0:41
  • 1
    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/… May 6, 2015 at 1:38


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.