Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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 ?

share|improve this question
add comment

2 Answers

up vote 2 down vote accepted

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.

share|improve this answer
    
I'll try to be clear. I'm using OpenLayers to show OSM map of a specific area. I want to be able to click on the map and "get info" from it that I'll store in MSSQL (2008 or 2012 , doesn't matter for me). My plan is to get the clicked point geometry using draw feature and with that query the DB and bring back result. Question is , How I get the closet geographic object from the DB for that specific Point. –  Alophind Dec 13 '12 at 20:17
1  
In that case you could filter your data with the shown map extent first, and then run the STDistance query on the result. –  bhell Dec 13 '12 at 20:29
    
Can you please show me query example? Never used spatial before. –  Alophind Dec 14 '12 at 5:50
    
The stackexchange thread which I linked to has a complete example. As I mentioned, this is not a trivial query, and it will depend on how your data model looks like. –  bhell Dec 14 '12 at 12:34
    
Basically it was VERY easy. Thank you. –  Alophind Dec 17 '12 at 20:24
add comment

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.

share|improve this answer
    
According to the documentation, STDistance works with geography, not geometry points. As distances are calculated differently for geographical and cartesian coordinates, the result may be unexpected (I don't have access to a DB right now to test myself). If you stick to one coordinate system (which you should because of computation overhead for re-projecting), there is no need for another DBMS here. PostGIS, Oracle Spatial and SQL Server Spatial are all very capable solutions, and have all their own advantages and disadvantages. –  bhell Dec 14 '12 at 12:41
    
I currently use MS SQL 2008 R2, and i will change it at first change to PostGIS. Why, Because MS SQL spatial just dosent have all tools needed. And you are correct after double checking that in databsae i get NULL as distance. (Similiar query in PostGIS works, it can even transform between projections). But at the end, it is what you need. If you want to do soem analysis in databse then postgis , if not , you can manage with MSSQL –  simplexio Dec 14 '12 at 14:27
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.