I am new to GIS and using GIS in SQLServer, so apologies if this is a rudimentary question, but I cannot find the answer so far. I am trying to use STIntersects() or STContains to determine what state a given lat/long combination is in. My table named SpatialTable has polygons for all the states and my example below is coded for a point inside my polygon named AL02.

I need to understand why this code (cloned from example code on the Microsoft TechNet pages) works fine:

DECLARE @g geography;
DECLARE @h geography;
select @g=GeogCol2 from SpatialTable where name = 'AL02';
SET @h = geography::Parse('POINT(-88.09 31.24)');
SELECT @g.STIntersects(@h);

and this code does not:

-- this will not work as it sees my parameter as nvarchar(max)
--error message is: Cannot call methods on nvarchar(max).
SELECT a.name
FROM SpatialTable a
WHERE a.GeogCol2.STIntersects(geography::Parse('POINT(-88.09 31.24)')) = 1
and a.name = 'AL02';

I also tried STGeomFromText instead of Parse, but still get the nvarchar error.

SELECT a.name
FROM SpatialTable a
WHERE a.GeogCol2.STIntersects(geography::STGeomFromText('POINT(-88.09 31.24)',4326)) = 1
and a.name = 'AL02';

Even if I pass @h into the SQL query with @h declared as geography, the error shows that the database is treating the parameter as nvarchar. How can I dynamically load my lat and long without passing nvarchar?

Any help would be greatly appreciated. Thanks.

  • Would removing the " = 1" at the end of the WHERE clause help? I'm guessing not, but... – Martin F Nov 9 '13 at 19:08
  • No, removing the =1 takes away the boolean test needed in the where clause. I now think the problem is with trying to use a.GeogCol2 directly in the SQL without casting it as a geography, or something along those lines. – woodycinco Nov 10 '13 at 14:09
  • Figured this out; needed to use GeogCol1 which is the large HEX field that represents the polygon, not the nvarchar in column2. Like I said, I am new to this GIS data world. – woodycinco Nov 10 '13 at 14:27
  • (1) According to technet.microsoft.com/en-us/library/bb933899.aspx, STIntersects() does return boolean, so i don't think you need to test it via the equality operator. (2) Getting back to the solution, you're allowed (and encouraged) to post your own one as an answer. And even accept it, after a while. – Martin F Nov 10 '13 at 21:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.