SQL Server Geography datatype nearest point on line
I am trying to build a query, but I am having some difficulty.
I have a SQL Server 2008 database with a table that includes, among other fields, a geography field that describes road segments. (This dat开发者_如何学Ca has been imported from TIGER/Line data from the U.S. Census.)
I have another fixed point describing a user's location. I want to find the closest road segment in the database to that point, but I can't seem to figure out how to accomplish this. Furthermore, I want to find the closest point on that segment to the user location point. This is what I want to select and return back in my query.
Does anyone have any experience with the geography/geometry functionality that can help me?
Thanks!
You can store your objects in a GEOGRAPHY
column and create a SPATIAL INDEX
over this column.
Unfortunately, SQL Server
implements spatial indexes by tiling the surface and storing the tile identifiers in a plain B-Tree
index, so plain ORDER BY STDistance
won't work (well, it will work but won't use the index).
Instead, you'll have to make a query similar to this:
DECLARE @mypoint GEOGRAPHY
SET @mypoint = geography::STGeomFromText('POINT(@mylat, @mylon)', 4326);
WITH num (distance) AS
(
SELECT 1000
UNION ALL
SELECT distance + 1000
FROM num
WHERE distance <= 50000
)
SELECT TOP 1 m.*
FROM num
CROSS APPLY
(
SELECT TOP 1 *
FROM mytable
WHERE myroad.STDistance(@mypoint) <= distance
ORDER BY
STDistance(@mypoint)
) m
This way, SQL Server
will first search roads within 1
kilometer from your point, then within 2
kilometers, etc., each time using the index.
Update:
If you have multiple points in a table and want to find the closest point for each of them:
WITH num (distance) AS
(
SELECT 1000
UNION ALL
SELECT distance + 1000
FROM num
WHERE distance <= 50000
)
SELECT mp.mypoint, m.*
FROM @mypoints mp
CROSS APPLY
(
SELECT TOP 1 m.*
FROM num
CROSS APPLY
(
SELECT TOP 1 *
FROM mytable
WHERE myroad.STDistance(@mypoint) <= distance
ORDER BY
STDistance(@mypoint)
) m
) m
How are your road segments stored? lat and long? if so, you can convert them to radians, and do the math:
- http://www.zipcodeworld.com/samples/distance.mssql.html
- http://www.petefreitag.com/item/622.cfm
- http://johndyer.name/post/2005/07/11/LatitudeLongitude-Distance-Calculation-in-SQL-Server.aspx
精彩评论