September 10, 2013 at 12:09 pm
I need to find the name of the county based on longitude and latitude values. Could someone give some ideas on how to do it? We will buy data somehow .
I have done some coding but all I get is the nearest county. That may not be the right county because of boundaries.
Any help would be greatly appreciated.
hello
September 10, 2013 at 7:12 pm
Hi
You can do a query like the following
DECLARE @pointgeo GEOGRAPHY = Geography::STGeomFromText('POINT (-122.56153600 45.603118166)', 4326)
SELECT c.Name
FROM Country c
WHERE c.Shape.STIntersects(@pointgeo) = 1
Just make sure that you SRIDs match.
Here's a link to the Geography data type documentation if you want it
September 11, 2013 at 6:54 am
I think .STContains() is the proper function to use.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 11, 2013 at 1:10 pm
Either or will work, the difference being that STIntersects will also work on 2008 as well where STContains will not.
There could also be an arguement for using STWithin
declare @container geography = geography::STGeomFromText('POLYGON((10 10, 11 10, 11 11, 10 11, 10 10))',4326)
declare @point geography = geography::STGeomFromText('POINT(10.5 10.5)',4326)
select @container.STContains(@point), @container.STIntersects(@point),
@point.STIntersects(@container), @point.STWithin(@container)
----- ----- ----- -----
1 1 1 1
(1 row(s) affected)
The one you need to be careful with is Filter. This will do a quick intersection based on the spatial index if available and may return items that do not intersect.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply