asked on

find locations near zip code

Hi,

We have an intranet application that looks up our retail locations based on the customers zip code (so we can direct them to the nearest store). It's actually an MS SQL stored procedure.

We want to port this to a MySQL/PHP application. Here is the stored procedure that determines the nearest locations:

------------------------------------------------------
CREATE Procedure sp_RetailerDistance

      (
            @pLAT float,
            @pLNG float
      )

As
SELECT TOP 20
ARX_RetailerLocatorList.CustomerNumber,
ARX_RetailerLocatorList.ShipToCode,
ARX_RetailerLocatorList.CustomerName,
ARX_RetailerLocatorList.AddressLine1,
ARX_RetailerLocatorList.City,
ARX_RetailerLocatorList.State,
ARX_RetailerLocatorList.ZipCode,
ARX_RetailerLocatorList.PhoneNumber,
ZipLocation.LAT,
ZipLocation.LNG,
STR(3959 * acos(sin(LAT/57.3)*sin(@pLAT/57.3) + cos(LAT/57.3) * cos(@pLAT/57.3) * cos(@pLNG/57.3 - LNG/57.3)),7,1) As Distance
FROM
dbo.ARX_RetailerLocatorList
INNER JOIN
dbo.ZipLocation
ON
LEFT(dbo.ARX_RetailerLocatorList.ZipCode, 5) = dbo.ZipLocation.ZIP_CODE
ORDER BY
Distance ASC

return
GO
----------------------------------------------------

I guess @pLAT and @pLNG are passed in as parameters? IF so, then I should figure out how those values are determined first... Basically I would like suggestions or a solution on how to port this to mysql. Maybe there's already a solution out there that does something similar. I would rather not do it as a stored procedure (and would prefer a php-based solution), but whatever works is good for me...

Thanks!

MaritimeSource

ASKER

I've determined there's a "ziplocation" table that has the following columns:

zip, city, state, lng, lat

So the script first gets the lng and lat based on the zip provided. Then it passes those to the stored procedure.

Rob_Jeffrey

The stored procedure does take the Latitude and Logitude from the other table as parameters.
You can write this as a PHP function relatively easily. The first part would be to get the data into an equivilantly structured MySQL tables.

The newest versions of MySQL server allow for stored procedures as well so this could be ported directly into the MySQL server as a stored procedure.

MaritimeSource

ASKER

Right, but stored procedure syntax is different amongst various db's right? This is MS SQL SERVER:

STR(3959 * acos(sin(LAT/57.3)*sin(@pLAT/57.3) + cos(LAT/57.3) * cos(@pLAT/57.3) * cos(@pLNG/57.3 - LNG/57.3)),7,1) As Distance

Would that work directly in MYSQL?

SOLUTION

Rob_Jeffrey

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

SysTurn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SysTurn

Thanks for the A ;) Glad to hear that your problem has been solved.

Kind Regards
Bakr