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

    Question

  • 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 20-70, and i know the distance of my point is 100 meters from begining of section, then the aprrox house number is 20 + (100/300) * (70-20) --> house number 36

     

    any idea how to do it with sql 2008?

    any external code that can help me>

     

    Rafi

     

    Tuesday, November 02, 2010 8:45 AM

Answers

  • Hi there,

    Before going any further, is there a good reason why you're choosing to implement your own reverse-geocoding function rather than using one of the many webservices already available? (e.g. The Bing Maps Geocode Service). To get a really good reverse-geocoder 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/en-GB/sqlspatial/thread/cb094fb8-07ba-4219-8d3d-572874c271b5

    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 "mid-point" numbers as well.


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Tuesday, November 02, 2010 10:02 AM
    Answerer

All replies

  • Hi there,

    Before going any further, is there a good reason why you're choosing to implement your own reverse-geocoding function rather than using one of the many webservices already available? (e.g. The Bing Maps Geocode Service). To get a really good reverse-geocoder 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/en-GB/sqlspatial/thread/cb094fb8-07ba-4219-8d3d-572874c271b5

    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 "mid-point" numbers as well.


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Tuesday, November 02, 2010 10:02 AM
    Answerer
  • 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

     

    Tuesday, November 02, 2010 1:23 PM
  • 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#

    Thursday, November 04, 2010 11:13 PM
  • @vIndEx - Maybe I've missed something, but how is your code better than the one-line 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
    Friday, November 05, 2010 9:14 AM
    Answerer
  • You're right, i didn't check the link...
    Friday, November 05, 2010 6:18 PM
  • :-)


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Friday, November 05, 2010 7:15 PM
    Answerer
  • that sigle line is correct formula but it is very "heavy" because it must generate a buffer which may consiste on hundresds of points.

    i need to to this calculation as fast as possible. therfore i look for a pure mathematical formula.

    Thursday, November 18, 2010 8:10 AM
  • 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
    Thursday, November 18, 2010 8:49 AM
    Answerer
  • 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
    Wednesday, January 26, 2011 3:34 PM