CODEWITHSUNDEEP
sqlsql-serverfunctiongeometryintersect
Trevor Hodgins
Trevor Hodgins

Reputation: 95

Intersect function with SQL

I'm trying to create a function that finds the intersecting geometries (matching rows) between two tables in SQL server. Except when I run the function it gives me an error.

"Implicit conversion from data type geometry to varchar is not allowed." - (ERROR)

Here is what I have so far. 

create function FindIntersectingZone
(
@location geometry
)
returns varchar(50)
as

begin
declare @NameOfZone varchar(50)

select @NameOfZone = geom from [planning_districts_2008_updated]

where
geom.STIntersects(@location) = 1

return @NameOfZone;

end

Any ideas? Much appreciated. Thanks.

Upvotes: 1

Views: 432

Answers (2)

Tj Kellie
Tj Kellie

Reputation: 6476

Looks to me like your SQL Syntaxt is just not quite right. Your error is coming from the line:

select @NameOfZone = geom from [planning_districts_2008_updated]

I think you want to cast to varchar for your variable and use the result of your select as the value. Try:

declare @NameOfZone varchar(50)

SET @NameOfZone = (
    select CAST(geom as varchar(50)) as geomVarchar  
    from [planning_districts_2008_updated]
    where geom.STIntersects(@location) = 1
)
return @NameOfZone;

Or just use the result of the select directly without the need of a variable at all:

create function FindIntersectingZone
(
@location geometry
)
returns varchar(50)
as

begin
    select CAST(geom as varchar(50)) as geomVarchar  
    from [planning_districts_2008_updated]
    where geom.STIntersects(@location) = 1
end

Upvotes: 2

Szymon
Szymon

Reputation: 43023

You cannot implicitly convert geometry to varchar in line

select @NameOfZone = geom from [planning_districts_2008_updated]

Change that to 

select @NameOfZone = geom.ToString() from [planning_districts_2008_updated]

and return value of the method and variable to nvarchar(max)

Upvotes: 2

Related Questions