Completed

Mysql geo-lookup stored procedure needed

This is all simplified, but should be enough to complete the task :-

I have a primary table;

`providers`

id (bigint)

name

location (point)

radiusKm (int)

I use a stored procedure to retrieve any providers within catchment (simplified);

SELECT id, name, location, degToMeter(st_distance(@checkPoint, location)) AS distance FROM providers WHERE degToMeter(st_distance(@checkPoint, location)) < radiusKm

However, I have now needed to add an additional table to account for additional depots;

`depots`

id (bigInt)

providerId (bigint)

location (point)

radiusKm (smallint)

The procedure must now return all providers that have a depot within the vicinity of a given point. The resulting fields I will need are :-

id - The provider ID

name - The provider name

depotId - ID of the provider's closest depot

location - location of the provider's closest depot

distance - The distance of the checkPoint to the selected depot

So we need a grouping of the depots for each provider, ordered by distance, with only the closest one being returned. i.e. If two depots of a single provider are within catchment, the returned provider would only show the closest one, and not return the same provider twice.

I expect this project to take a suitable candidate and hour at most to complete. I'm looking for something efficient and elegant. Many thanks.

Skills: MySQL

See more: software write mq4, software write chip epson, useful software write book, software write web specs, free software write user guide, software write edid, free software write company profile, software write websites idea, software write book images, php mysql delete fields, software write books, sort mysql table fields click field, click mysql table fields sort fields, software write protection, php mysql looping fields, php mysql insert fields, free software write book, software write book, php loop mysql table fields, geo mysql redirect script, software77 geo mysql, joomla mysql repeating fields, mysql compare fields fuzzy, processing forms php mysql dynamic fields, convert sql stored procedure oracle stored procedures

About the Employer:
( 2 reviews ) London, United Kingdom

Project ID: #11354709

Awarded to:

davedcb

Hello, I am interested in your project. I am a software engineer and I have professional experience with MySQL. Thank you, Best regards, David

£50 GBP in 0 days
(15 Reviews)
3.2

4 freelancers are bidding on average £25 for this job

webxtor

Hello. Would you need this as a separate procedure to return only depots and their providers? Do you have the depots table ready? Would you give me the access to the real database? I'm ready to start. Thanks More

£22 GBP in 0 days
(86 Reviews)
5.7
£18 GBP in 1 day
(5 Reviews)
2.2
rehan828

Hi there, I am a professional Graphic Designer & wordpress expert with 2+ years experience. I am also a Student of Software Engineering in Pakistan. I have read and understand your project Description. I can mak More

£10 GBP in 1 day
(0 Reviews)
0.0