Problem with Latitude and Longitude Distance calc

  • I have an ad hoc block of code that is performing well, but when

    I place the logic into an insert into a temp table I get NULLS in my Distance field:

    This works well:

     Declare @source geography = geography::Point(35.93099821, -79.95852298, 4326); 
    Declare @destination geography = geography::Point(36.08000000, -79.44000000, 4326);

    Select @source.STDistance(@destination) / 1609.344 as Miles;

    This gives me NULLS in my Distance field (my static table has all the longitude and latitude values for my source location:

    Declare @destination geography  = geography::Point(35.93099821, -79.95852298, 4326); 
    Declare @source geography

    SELECT TOP (1000) [SiteName]
    ,[Address1]
    ,[City]
    ,[ProviderName]
    ,[Latitude]
    ,[Longitude]
    ,@source.STDistance(@destination) / 1609.344 as [Distance]
    INTO #TempTableProviders FROM [ProvidersDirectory].[dbo].[ProvidersTable];

     

     

  • Your variable @source is not being set, hence the NULLs.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • If I assign a value to @source in my declaration how do I 'reset' it in my insert to the temp table? The exercise is to assign a geographical location to the destination and calculate each row's distance.

  • Does [ProvidersDirectory].[dbo].[ProvidersTable] have the source data column?

    If so, use that.

    If not, where does the source data come from?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • The [ProvidersDirectory].[dbo].[ProvidersTable] does not have a 'Distance' field. This is a calculated field to be only placed in the temp table. The [ProvidersDirectory].[dbo].[ProvidersTable] only has the longitude and latitude. In the declaration for @destination I am passing in a set of longitude and latitude coordinates to be compared with each record in the [ProvidersDirectory].[dbo].[ProvidersTable] to calculate a distance the way this bellow does so reliably. I am only declaring the @Source because the function appears to require it:

     Declare @source geography = geography::Point(35.93099821, -79.95852298, 4326); 
    Declare @destination geography = geography::Point(36.08000000, -79.44000000, 4326);

    Select @source.STDistance(@destination) / 1609.344 as Miles;

    • This reply was modified 3 months, 2 weeks ago by  DaveBriCam.
  • You need to create the point from the Latitude and Longitude for each row. Something like:

    (geography::STPointFromText('POINT(' + Latitude + ' ' + Longitude + ')', 4326)).STDistance(@destination) / 1609.344 as [Distance]

    If that does not work split it up into CTEs. As you cannot be bothered providing test data you will not get tested code.

     

  • Test data :

     

     

     

    • This reply was modified 3 months, 2 weeks ago by  DaveBriCam.
  • Test data (not sure how to display it):

    As insert statement(s) -- e.g.,

    INSERT INTO #table (Column1, Column2, etc.)
    SELECT Column1, Column2, Column3
    UNION ALL 
    SELECT Column1, Column2, Column3;

    -- or

    INSERT INTO #table (Column1, Column2, etc.)
    VALUES(Column1, Column2, Column3),
    (Column1, Column2, Column3);

    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help for a great summary

  • DaveBriCam wrote:

    Test data :

    You have over 4000 points and have been shown how to post test data on numerous occasions.

  • When you give an image instead of data, text, and when in the image you display only two digits after the decimal point (so that most of the records are actually in the same place), even those who want to help, find it difficult.

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply