How can I get nearest road to a lat lon in MSSQL?

Question

I've updated osm data (ways) in MSSQL and got geographic objects. Now I want to get the closet road to any lat lon a user will query.

Which SQL spatial statement is used for that ?

Answer 1

Your question is a little vague, and I assume that you mean SQL Server 2012 with data stored as geography. You may want to look at "find closest lat long to a input lat long Sql server 2008", which may be a duplicate to your question. This is not the most trivial SQL query, and it will depend (of course) on your dataset and database model, which you don't tell us anything about.

Just a guess, but if the reason for your question is address coding of arbitrary points, a home-grown solution will probably fall short in comparison to e.g. Google's Maps API.

The simplest (though certainly not most effective) approach to do this is to look at the distance to each of the features in your roads table using STDistance (documentation). Order the query result by ascending distance and pick the first record.

Depending on your data, a more effective way could be to first filter out potentially interesting, nearby roads. This could e.g. be done with an intersect query using a rectangle around the point. Depending on your data and desired results (e.g. are points in the middle of the world ocean supposed to have a closest road at all?), the size of that box may vary. Then you only run the STDistance query against these nearby roads.

Answer 2

Assuming geometry type geometry

Query calculates distance to point x=11 y=11 in SRID 4326 (WGS84)( because my data uses 4326 ) for every geom in table a orders them by distance

SELECT TOP 1
       [ID]
      , a.geom.STDistance(geometry::STGeomFromText('POINT(11 11 )' , 4326) )  as distance
      ,[geom]
  FROM [dbo].[geoms] as a
  ORDER BY a.geom.STDistance(geometry::STGeomFromText('POINT(11 11 )' , 4326) ) ASC

And i recommend that you use PostGIS if you can. It has in database Repojection and lots of useful functions.