SQL command for retrieving info from Distance Calculator

[Amit Singh]

Hello group. I'm using MapInfo Professional 8.5. I have recently
learned the "Distance Calculator" feature of MapInfo which is really
cool and easy to use. I can identify from two different sets of data,
the locations that are closest to each other. What I don't know is how
to retrieve this information using SQL (or if there's another better
easier way to do this). I need to be able to retrieve the closest
pairs of data along with all of their corresponding info. Does someone
know how to do this? Thanks.

Amit

[erupnu]

You can use a custom scalar function to do this:

'=================================================================================
' Distance Calculator
'=================================================================================
' This function calculates the distance between two latitude/logitude
' coordinates. Disance can be returned as Kilometers or Miles.
'
' This function was designed for Microsoft SQL Server 2005
'
' Accepts:
' Lat1 = Latitude of point one (decimal, required)
' Lon1 = Longitude of point one (decimal, required)
' Lat2 = Latitude of point two (decimal, required)
' Lon2 = Longitude of point two (decimal, required)

'
' Provided by: http://www.zip-codes.com
'=================================================================================

CREATE function [dbo].[Distance]( @lat1 decimal(12, 6) , @long1 decimal
(12, 6) , @lat2 decimal(12, 6) , @long2 decimal(12, 6))
returns decimal(12,2)
as

begin

declare @DegToRad as float
declare @Ans as float
declare @Miles as decimal(12,2)

set @DegToRad = 57.29577951
set @Ans = 0
set @Miles = 0

if @lat1 is null or @lat1 = 0 or @long1 is null or @long1 = 0 or @lat2
is
null or @lat2 = 0 or @long2 is null or @long2 = 0

begin
return ( @Miles )
end

set @Ans = SIN(@lat1 / @DegToRad) * SIN(@lat2 / @DegToRad) + COS
(@lat1 / @DegToRad ) * COS( @lat2 / @DegToRad ) * COS(ABS(@long2 -
@long1 )/@DegToRad)

set @Miles = 3959 * ATAN(SQRT(1 - SQUARE(@Ans)) / @Ans)

return ( @Miles )

end


You do need lat/lon points for every location as well as the center
point. But just call it like this:

SELECT *, Distance(lat1 , long1, lat2, long2) AS varDistance FROM
Table ORDER BY varDistance ASC

You can repalce the lat/lon values with columns in the database. It's
fast, but if you have a lot of rows, it can take a good bit of
processor to sort them all. You can ease this by adding a WHERE with
the max miles under a given value.

- James Harris

[Peter Horsbøll Møller]

The Nearest statement is doing the work behind the Distance Calculator.

You might also want to have a look at the CartesianConnectObjects and the CartesianObjectDistance() function.
They can also be found in version without Cartesian if you are not working in a Cartesian coordinate system.

 

Peter Horsbøll Møller
Pitney Bowes Business Insight - MapInfo

 

 

2009/4/7 erupnu <eru...@gmail.com>

[Amit Singh]

Thank you Peter and James. The SQL query I’m performing is in EBMS itself. I’m not using a separate Microsoft query tool for two reasons. First, I don’t have access to it. Second, the results of Distance Calculator are in MapInfo. Besides, I’m not a SQL guy at all. Unfortunately, I’m the only one who is willing to take on these mapping project(s). But thanks for your feedback. If you or anyone else on this forum have any other ideas, please let me know. Thank you.

 

Amit

[Darren Chan]

Hi Amit

 

Have you tried Spider Graph tool in MapInfo?  It may do what you need with a little bit of filtering.

 

BR,

 

Darren Chan

---

From: mapi...@googlegroups.com [mailto:mapi...@googlegroups.com] On Behalf Of Amit Singh
Sent: Tuesday, April 07, 2009 5:55 PM

[pashoang]

i think your condition is very difficult with MI and it donot support
that function. In MI you can join 1 map table with some spatial
conditions : intersects, within ... don't have colsest function :).
Dear!

[Amit Singh]

Yes. In fact I really like the Spider Graph tool. The problem is that Spider Graph works well once you already know the exact routing solution. Majority (almost all) of the situations I’m trying to resolve is getting the best routing model for two sets of data. For example, I have 400+ theatres across the country and 21000 stores of a business scattered across the U.S. My task is to find the best routing solution where I can route the maximum number of stores (even all of the stores) to the least number of theatres to get the best ROI. I’ve previously used the buffer method by creating a large (typically 200 miles) around each theatre, exporting the results and then using pivot tables and extensive analysis to filter out the best theatre-stores model. However, that method, though it works well, is very time consuming and often crashed my computer due to overload. I recently played around with the Distance Calculator tool and it basically does 75% of the work for you by routing each store to the nearest theatre without any overlap or duplication error. I can even see these results in the window of Distance Calculator tool (e.g. Store #, Theatre # and Distance for each pair). Since I typically work with extremely large sets of data (10000+ records), it would be impractical to copy the solution on a notepad. I need to electronically extract this information in excel for further analysis. I have even gotten as far as to extract using SQL the results mentioned above. But since you can only choose one identifying column from two sets of data when using Distance Calculator, I don’t get the rest of the data associated with each table, e.g. Store address, Theatre Address, Store Manager, Theatre Name, etc. etc. It is this issue that has put the kibosh on really using this tool to the max. And since I’m not a SQL guy (everything I’ve learned so far is by checking out the forum J), it is not the easiest thing for me to resolve.

 

Thanks for the idea Darren. After reading the full explanation above, if you think there may be an easier way to do what I’m trying to do, please feel free to let me know. Thank you.

 

Amit

<BR