Design and optimise an SQL query for plotting markers on Google Maps
£20-250 GBP
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.
Project ID: #19754023
About the project
19 freelancers are bidding on average £155 for this job
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
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
Dear sir, I am very good in Database handling and have very good experience in Development please review my profile Thanks
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
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
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!
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
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
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.
I've worked extensively with SQL on multiple occasions, and I can deliver on your requirements within a week.