.STIntersects

Question

Hi All

I am trying to find all of the buildings which are within a flood zone area and have the following two tables

• BLD_FTS_BUILDING
• EA_FLOOD_ZONE_3

But am getting the following error..

Msg 4121, Level 16, State 1, Line 1

Cannot find either column "bldg" or the user-defined function or aggregate "bldg.STIntersects", or the name is ambiguous.

SELECT BLD_FTS_BUILDING.osid AS OSID,BLD_FTS_BUILDING.SHAPE AS bldg, EA_FLOOD_ZONE_3.OBJECTID, EA_FLOOD_ZONE_3.SHAPE AS flood FROM [BLD_FTS_BUILDING]  join [EA_FLOOD_ZONE_3]      on bldg.SHAPE.STIntersects(flood.SHAPE) = 1

Can anyone tell me what is wrong. I am new to SQL and the spatial side of it so any advice welcome. Also if anyone is able to recommend some reliable and good spatial SQL courses in the UK that would be great

Answer 1

SELECT BLD_FTS_BUILDING.osid AS OSID,BLD_FTS_BUILDING.SHAPE AS bldg, 
       EA_FLOOD_ZONE_3.OBJECTID, EA_FLOOD_ZONE_3.SHAPE AS flood 
FROM [BLD_FTS_BUILDING]  join [EA_FLOOD_ZONE_3]      
      on bldg.SHAPE.STIntersects(flood.SHAPE) = 1

You define an alias "bldg" in the SELECT clause; you can't use this alias in the JOIN condition nor WHERE clause.