Problem with Latitude and Longitude Distance calc

Question

Post reply

Problem with Latitude and Longitude Distance calc

DaveBriCam

SSCarpal Tunnel

Points: 4294

March 14, 2022 at 5:35 pm

#3999257

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];

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

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

Phil Parkin SSC Guru Points: 246695 More actions · Answer 1

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.

DaveBriCam SSCarpal Tunnel Points: 4294 More actions · Answer 2

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.

Phil Parkin SSC Guru Points: 246695 More actions · Answer 3

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.

DaveBriCam SSCarpal Tunnel Points: 4294 More actions · Answer 4

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 2 years, 1 month ago by DaveBriCam.

Ken McKelvey SSCoach Points: 18768 More actions · Answer 5

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.

DaveBriCam SSCarpal Tunnel Points: 4294 More actions · Answer 6

Test data :

This reply was modified 2 years, 1 month ago by DaveBriCam.

ratbak Ten Centuries Points: 1333 More actions · Answer 7

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

Ken McKelvey SSCoach Points: 18768 More actions · Answer 8

DaveBriCam wrote:

Test data :

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

izhar-azati SSC Veteran Points: 207 More actions · Answer 9

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.