Problem with Latitude and Longitude Distance calc

Question

Post reply

Problem with Latitude and Longitude Distance calc

DaveBriCam

SSCarpal Tunnel

Points: 4298

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: 246962 More actions · Answer 1

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

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

DaveBriCam SSCarpal Tunnel Points: 4298 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: 246962 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?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

DaveBriCam SSCarpal Tunnel Points: 4298 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, 8 months ago by DaveBriCam.

Ken McKelvey SSCoach Points: 18843 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: 4298 More actions · Answer 6

Test data :

This reply was modified 2 years, 8 months ago by DaveBriCam.

ratbak Ten Centuries Points: 1359 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: 18843 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: 214 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.