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.