Answered by:
reverse geocoding: how to determine on which side of the street is my point? and find house number?

i have a table with street sections { LINESTRING(.....) } and i have a reverse geocoding function that finds the closes section to my point. it works good.
now i want to find the best house number. i have 4 fields: from_left, to_left, from_right, to_right
that gives me the range of house numbers of each side of the street. (most records contain only one section, but some records contain multiple sections)
1) how can i calculate on which side of the street is my point ?
2) assume mu point is 20 meters from section. how do i find the closes point on section and how to calculate the distance of that point from start of section. with that info i can estimate the houe number.
for example: if i have a section with length of 300 meters with house range 2070, and i know the distance of my point is 100 meters from begining of section, then the aprrox house number is 20 + (100/300) * (7020) > house number 36
any idea how to do it with sql 2008?
any external code that can help me>
Rafi
Question
Answers

Hi there,
Before going any further, is there a good reason why you're choosing to implement your own reversegeocoding function rather than using one of the many webservices already available? (e.g. The Bing Maps Geocode Service). To get a really good reversegeocoder requires a lot of data and some reasonably clever interpolation functions (as you're discovering)... so you don't need to recreate the wheel!
But if you really want to go ahead here's some answers to your questions:
1.) If I drive north up my street then my house is on the right, but if I drive south then it's on the left. So deciding which side of the street your point lies depends on the direction in which you defined the street in your database.
2.) See http://social.technet.microsoft.com/Forums/enGB/sqlspatial/thread/cb094fb807ba42198d3d572874c271b5
p.s. The road on which I live has blocks of flats at one end and houses at the other, so the distance between consecutive numbered houses is far from consistent. Rather than having just the house numbers at each end of the street, you might need to incorporate "midpoint" numbers as well.
Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290 Marked as answer by Alex Feng (SQL)Moderator Tuesday, November 09, 2010 11:35 AM
All replies

Hi there,
Before going any further, is there a good reason why you're choosing to implement your own reversegeocoding function rather than using one of the many webservices already available? (e.g. The Bing Maps Geocode Service). To get a really good reversegeocoder requires a lot of data and some reasonably clever interpolation functions (as you're discovering)... so you don't need to recreate the wheel!
But if you really want to go ahead here's some answers to your questions:
1.) If I drive north up my street then my house is on the right, but if I drive south then it's on the left. So deciding which side of the street your point lies depends on the direction in which you defined the street in your database.
2.) See http://social.technet.microsoft.com/Forums/enGB/sqlspatial/thread/cb094fb807ba42198d3d572874c271b5
p.s. The road on which I live has blocks of flats at one end and houses at the other, so the distance between consecutive numbered houses is far from consistent. Rather than having just the house numbers at each end of the street, you might need to incorporate "midpoint" numbers as well.
Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290 Marked as answer by Alex Feng (SQL)Moderator Tuesday, November 09, 2010 11:35 AM

thanks for answer.
1) i need to process milions of points per day, so external service is out of the question. i am migrating shapefiles into sql 2008 and use a very simple procedure to find the nearest street record to my point.
2) street segments are represented in db as a sequence of point and i dont have the street width or number of lanes.
vehicle can be at a parking lot nearby the road and its direction can be to any direction

To get the closest point to a segment:
 @SEGMENT GEOMETRY (LINESTRING, 2 points)  @POINT GEOMETRY (POINT) declare @srid int = @segment.STSrid declare @DISTANCE float = @SEGMENT.STDistance(@POINT) declare @S1 geometry = @SEGMENT.STPointN(1) declare @S2 geometry = @SEGMENT.STPointN(2) declare @result geometry IF (@S1.STDistance(@POINT)=@DISTANCE) SET @result = @S1 ELSE BEGIN IF (@S2.STDistance(@POINT)=@DISTANCE) SET @result=@S2 ELSE BEGIN declare @x0 float = @S1.STX declare @y0 float = @S1.STY declare @x1 float = @S2.STX declare @y1 float = @S2.STY declare @x2 float = @POINT.STX declare @y2 float = @POINT.STY declare @x10 float = @x1  @x0 IF (@x10 = 0) set @result = geometry::Point(@x0,@y2,@srid) ELSE BEGIN declare @y10 float = @y1  @y0; IF (@y10 = 0) set @result = geometry::Point(@x2,@y0,@srid) ELSE BEGIN declare @mp float = @x10 / @y10 declare @x float = (((@y2  @mp * @x2  @y0) / @y10) + (@x0 / @x10)) / ((1 / @x10)  (@mp / @y10)) declare @y float = (@x  @x0) * (@y10 / @x10) + @y0 set @result = geometry::Point(@x,@y,@srid) END END END END  return / select @result
Sorry but I'm pretty sure that the code have mistakes, I can't test in on Sql Server and it's translated from C#

@vIndEx  Maybe I've missed something, but how is your code better than the oneline solution to find the closest point on any geometry to another I linked to:
SELECT @POINT.STBuffer(@POINT.STDistance(@SEGMENT)).STIntersection(@SEGMENT)
@RafiNewman  Have we answered your question yet? You now know how to calculate both the closest street and the closest point on that street.
Using STLength() will tell you the total length of the street, and using @PointOnStreet.STDistance(@Street.STStartPoint()) will tell you how far along the street that point is. These two numbers will allow you to calculate the approximate house number. The questions about "sides" of the street relate entirely to the direction in which you store the street information in your database.
Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290 



Not quite sure what you mean by a "pure mathematical" formula... STDistance(), STBuffer() etc. are mathematical formulae.
You could try BufferWithTolerance() instead of STBuffer()  it will give you less points and may be faster.
Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290 
Hello Tanoshimi,
Can you suggest me how do i get the reverse geocoder if i provide the direction with lat longs?
For eg: My truck is on left side of road in the middle. I need to use the reverse geocoder to get the nearest house information on left side. that means i need to get base don direction of truck.
Thanks
Manasvi Kommuri