Design and optimise an SQL query for plotting markers on Google Maps

In Progress Posted 4 years ago Paid on delivery
In Progress Paid on delivery

UPDATE: Just to clarify, I ONLY need the SQL query, I do not need any PHP or other code. I have a working solution already and just need to expand the SQL. I am a developer and will add your SQL into my existing code.

I have a website that uses Google Maps to plot custom markers from a SQL database. The markers consist of store locations for ~10,000 currency suppliers in the UK. Visitors can enter their current location and see a list of currency suppliers within a 30-mile radius along with the supplier's corresponding exchange rate for a given currency.

The problem I have is some suppliers have dozens of stores within a 30-mile radius of the search location which can look messy when the markers are plotted on the map.

What I would like is to redesign the SQL query that finds these stores so that it does the following:

1) Find all stores within a 10-mile radius of the search location.

2) If there are multiple stores found for the same supplier, only return the first 5 for that supplier based on distance from the search location (i.e. the 5 closest stores).

3) Return a maximum of 50 results.

The data is stored across three tables:

Table 1 contains supplier information (name, website etc.).

Table 2 contains marker information (including lat, long coordinates) for each store.

Table 3 contains exchange rate information.

The primary key in table 1 is a secondary key in tables 2 and 3. The final query must be fairly optimised (<1 second) as it will be placed on a medium-traffic website. I am open to changing the structure of one or more tables if necessary but would prefer not to.

Database Programming MySQL PHP SQL

Project ID: #19754023

About the project

19 proposals Remote project Active 4 years ago

19 freelancers are bidding on average £155 for this job

adeelpirzada

Have a good day! I am very interested in your job. I have been working as a full stack web developer for over 10 years. I am highly skilled in MySQL and PHP so I feel confident that I can do your job without fail. I a More

£160 GBP in 7 days
(76 Reviews)
9.4
TakeReal

Hello, My name is Sergey. I am Freelancer from Saint Petersburg, Russia. I can help you as an experienced PHP/MySQL programmer to update/rewrite SQL query and PHP code according to your description: 1. 10 miles ra More

£90 GBP in 2 days
(2082 Reviews)
8.3
ominfowave

Dear Client, Please keep trust on us we can surely do your project. We are really interested to work with you and build a healthy longterm relationship. Warm Regards, Om Infowave

£250 GBP in 9 days
(148 Reviews)
7.9
ravichedwal

Dear sir, I am very good in Database handling and have very good experience in Development please review my profile Thanks

£400 GBP in 7 days
(117 Reviews)
7.5
sudesharora0111

Hi, I Would like to work for you on these query changes. I have gone through to the details that you have shared and its clear to me what should be the out out of new query to get the expected results. I am experti More

£100 GBP in 1 day
(21 Reviews)
5.0
roadroll

Hello, i'll implement distance calculation in mysql stored function, this solution allows posibilty to perform all calculations using mysql server instead of php and will work very fast. SQL only, no any other code :) More

£25 GBP in 2 days
(22 Reviews)
4.7
VirtualBrainInc

Hello! I have briefly read the description on Design-optimise-SQL-query development project, and I can deliver as per the requirements however I need us to discuss for more clarity on the details, deadline and b More

£60 GBP in 3 days
(7 Reviews)
3.9
vikasp007

I've 7+ years of experience in Web Development/Web design, Mobile App Development(IOS/Android), Graphic design, Logo Design Business Cards & Stationery, Flyers & Brochures, Packaging Design, Web & Mobile Design, Socia More

£150 GBP in 3 days
(2 Reviews)
2.4
maanyait

Hi, I can do this task very quickly and effectively. I have hands on experience in working with AJAX, PHP & database. Lets do this. Looking forward to work with you!

£200 GBP in 7 days
(0 Reviews)
0.0
techlinesols6

Dear Prospect Hiring Manager. Thank you for giving me a chance to bid on your project. i am a serious bidder here and i have already worked on a similar project before and can deliver as u have mentioned "I can do th More

£135 GBP in 7 days
(1 Review)
0.0
maiquemalmeida

This project has a low dificult curve. I'm starting on freelancer platform, and a see here a great chance to work in something I like. I'm programmer too, but I don't will put myself in your project. I swear! (if you d More

£50 GBP in 7 days
(0 Reviews)
0.0
vw335261vw

Hello, I have over 15yrs experience writing SQL Code, along with lots of experience designing and implementing database solutions. I can definitely provide you with the required SQL Query for your project.

£200 GBP in 7 days
(0 Reviews)
0.0
anthonydaly

I've worked extensively with SQL on multiple occasions, and I can deliver on your requirements within a week.

£250 GBP in 7 days
(0 Reviews)
0.0