none
Find the average distance between the house of one employee and other employee's houses in the column RRS feed

  • Question

  • I am working on a practice question in SQL. A sample database of employees is provided.

    The screenshot attached contains the details of the employees. It also includes their addresses.

    Can someone please tell me how to Find the average distance between the house of the employee with ssn'123456789' and the other employees houses?

    The addresses and the <g class="gr_ gr_434 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="434" id="434">ssn</g> numbers of the employees are mentioned below

     731 Fondren, Houston, TX
    638 Fondren, Houston, TX
    5631 Fondren, Houston, TX
    975 Fondren, Houston, TX
    450 Fondren, Houston, TX
    21 Fondren, Houston, TX
    980 Fondren, Houston, TX
    3321 Fondren, Houston, TX
    123456789
    333445555
    453453453
    666884444
    888665555
    987654321
    987987987
    999887777

    Saturday, January 19, 2019 11:53 AM

All replies

  • I cant see any screenshot in the post

    To get distance using address you've to use some third party APIs

    As an example Google Maps Distance Matrix API

    An example usage is given in the below link

    https://gooroo.io/GoorooTHINK/Article/16723/MS-SQL-Google-Maps-API/22112#.XEQJilUzapo

    You need to make it into UDF for your purpose like

    CREATE FUNCTION [dbo].[CalculateDistance] ( 
    @ToAddress NVARCHAR(100) = '', 
    @FromAddress NVARCHAR(100) = ''
    ) 
    RETURNS @DistanceistanceInKm FLOAT 
    AS
    
    BEGIN
    
    DECLARE @Object INT 
    DECLARE @ResponseonseText NVARCHAR(4000) 
    DECLARE @StatuserviceUrl NVARCHAR(500)
    
    SET @StatuserviceUrl = 'http://maps.googleapis.com/maps/api/distancematrix/xml?origins=' + @ToAddress + '&destinations=' + @FromAddress +'&mode=driving&language=en-EN&units=metric;'
    
    EXEC sp_OACreate 'MSXML2.XMLHTTP', 
    @Object OUT; 
    EXEC sp_OAMethod @Object, 'open', NULL, 'get', @StatuserviceUrl, 'false' 
    EXEC sp_OAMethod @Object, 'send' 
    EXEC sp_OAMethod @Object, 'responseText', @ResponseonseText OUTPUT
    
    DECLARE @Response XML
    
    SET @Response = CAST(CAST(@ResponseonseText AS NVARCHAR(MAX)) AS XML)
    
    DECLARE @Status NVARCHAR(20) DECLARE @Distance NVARCHAR(20)
    
    SET @Status = @Response.value('(DistanceMatrixResponse/row/element/status)[1]', 'NVARCHAR(20)')
    
    IF(@Status = 'ZERO_RESULTS') 
    SET @Distance = NULL 
    ELSE 
    SET @Distance = @Response.value('(DistanceMatrixResponse/row/element/distance/value)[1]', 'NVARCHAR(20)')
    
    SET @DistanceistanceInKm = ROUND(CAST(@Distance AS FLOAT) / 1000, 1)
    
    RETURN(@DistanceistanceInKm)
    
    END

    Once this is setup, you can pass the address and get distance between them like below

    DECLARE @RefAddress varchar(100)
    
    SELECT @RefAddress = Address
    FROM EmployeesTable
    WHERE SSN = ''
    
    
    UPDATE t
    SET DistanceBetween = [dbo].[CalculateDistance](Address,@RefAddress)
    FROM EmployeesTable
    

    If you want repeat this for each addresses taking it as a reference, then you would need to use APPLY operator

    To give more details I need to understand how your table is and how you want distance to be calculated for each


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Sunday, January 20, 2019 5:46 AM
  • IMPORTANT! check this document which I quote:
    https://developers.google.com/maps/documentation/distance-matrix/get-api-key

    Reminder: To use the Distance Matrix API, you must get an API key and you must enable billing.


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    Sunday, January 20, 2019 6:28 AM
    Moderator
  • Visakh, I don't think that this is the right answer. Note that Karanhinocha8 said this was a practice question. An awfully bad one, at least with the data in the post. Maybe the table also has the address in a column of the geography data type, in which case it is a matter of using the type methods for the spatial data types.

    But it is telling that all addresses appear to be on the same street. I say appear, since "Fondren" does not sound like a street name on its own. Then again, that is the typical format of a street address in the US. Furthermore, in the very most cities in the US, street numbers start on a new hundred for every new block, and if it is a perfect grid, each block is 100 m long. Oops! The US unitwise mentally retarded, so that may be 100 yards. But there are not really any guarantees. There may be different grids that are in angle to each other, and this can lead to irregularties where one or two hundreds are skipped; I've seen this in Seattle.

    And even if we overlook this, this would mean that we should extract the street number from the address, and extracting data from free text is an awfully bad idea, since sooner or later there is data that deviates from the pattern.

    I think Karanhinocha8 should skip this one and move to the next task. Unless he/she wants to play with the spatial data types, and in such case look up the coordinates manually through Google maps.

    And once you go to Google Maps, you find that it is even more confusing. I entered "3321 Fondren, Houston, TX" and there were five different streets with that number and name. I tried 3321 Fondren Road, but Street View reveals there are only shops on this address so there are no employees living there at all!


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Sunday, January 20, 2019 11:16 AM
  • select avg(abs(hno-(select hno from employee

    # say 123456789

    where ssn ='123456789' ))) from employee where ssn !='123456789';

     

    • Edited by Nailcutter Sunday, December 8, 2019 12:15 PM
    Sunday, December 8, 2019 12:08 PM
  • Please RESIST the urge to reply to old threads whose poster has lost interest. This is one that involves "practice" so that is even more reason to let it die. And your suggestion just won't work in the real world. You can't use math on house numbers (presumably - what else would "hno" refer to) to determine distance.
    Sunday, December 8, 2019 12:40 PM