В работе

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

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.

Квалификация: Программирование баз данных, MySQL, PHP, SQL

Показать больше display google map using latitude and longitude in php, adding markers to google maps from database, using php/mysql with google maps, google maps php mysql demo, show multiple markers on google map from database php, google maps load markers from json, how to show markers location in google map dynamically from database in php, google maps api, load markers google maps, custom markers google maps numbers, numbered info markers google maps javascript example, add custom markers google maps database, pin markers google maps, popup windows markers google maps, load dinamic markers google maps markers, costum markers google maps numbers, view markers google maps, plotting circles google maps, load nearest markers google maps, query markers google maps

О работодателе:
( 1 отзыв ) London, United Kingdom

ID проекта: #19754023

25 фрилансеров(-а) в среднем готовы выполнить эту работу за £158

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 Больше

£90 GBP за 2 дней(-я)
(1823 отзывов(-а))
8.1
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 за 9 дней(-я)
(125 отзывов(-а))
7.7
ravichedwal

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

£400 GBP за 7 дней(-я)
(103 отзывов(-а))
7.4
seemasit

PHP [login to view URL] [login to view URL] [login to view URL] [login to view URL] [login to view URL] [login to view URL] [login to view URL]

£135 GBP за 7 дней(-я)
(69 отзывов(-а))
7.1
WebXcellance

Hello there, We are ready to design and develop a website for you, As we have 7 year experience in website design and developments. Please provide more detail about the project, Also see our recent work. https://thel Больше

£167 GBP за 3 дней(-я)
(152 отзывов(-а))
6.9
kyleevert

Hello dear How are you? I read your requirement carefully I am a back-end developer. I am very interested in your post project. I am sure i can give you great result. I have rich experience on it. so please discuss Больше

£200 GBP за 15 дней(-я)
(19 отзывов(-а))
5.9
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 Больше

£160 GBP за 7 дней(-я)
(22 отзывов(-а))
6.0
Topman123

*Dear manager*. *Brilliant and skillful developer*(including web and graphic design) is here. I read description of your project very carefully. I am a talented Full Stack developer who can finish your project perfe Больше

£150 GBP за 3 дней(-я)
(26 отзывов(-а))
5.7
yash71

Senior Web Developer, with over 5 years of industry experience. Based in Melbourne. I've worked on apps for top clients in Melbourne. Looking at your requirement, this project would perfect fit my skills and expertise Больше

£244 GBP за 3 дней(-я)
(7 отзывов(-а))
4.7
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 Больше

£100 GBP за 1 день
(18 отзывов(-а))
4.6
EliteMobile330

Hello,Hiring Manager. *****In SPECIAL very familiar with WEB expert who has 8 years. ************FULL Time Developer******************* I have read your description and have interests in your project. I have rich e Больше

£25 GBP за 3 дней(-я)
(12 отзывов(-а))
4.6
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 :) Больше

£25 GBP за 2 дней(-я)
(17 отзывов(-а))
4.5
indrasisdatta

Hello, I'd like to apply for the job post of redesigning an SQL query that finds stores based on some criteria mentioned. As a brief background description, I'm a seasoned Web developer with more than five years Больше

£25 GBP за 1 день
(12 отзывов(-а))
4.0
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 Больше

£60 GBP за 3 дней(-я)
(7 отзывов(-а))
3.9
xiaolong1112

How are you? I'm interested in your project. I have full experience in this field and maybe i can help you to build your project. Please contact me if you have a will to build that with me, so that we can discuss mor Больше

£150 GBP за 3 дней(-я)
(4 отзывов(-а))
3.3
vpsbohra

✅Hello, Nice to meet you. ✴✴If you really want the project to be completed with the best quality in a short time, I suggest that you consider me.✴✴ I always do my best to make clients satisfied with good quality and Больше

£225 GBP за 7 дней(-я)
(9 отзывов(-а))
3.5
kostya1207

Hi. thanks for your posting. I am a creditist. I'm very happy to have a chance of bidding your project. I have read carefully the descriptions and understood clearly what you want. I have experience in development of t Больше

£135 GBP за 7 дней(-я)
(7 отзывов(-а))
2.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 Больше

£150 GBP за 3 дней(-я)
(2 отзывов(-а))
2.5
jsorrisone

Greetings, I am a highly proficient and effective MS SQL Server expert with 8+ years of experience. I have a proven track record of working with clients of all sizes and have architected all levels of database solut Больше

£177 GBP за 1 день
(0 отзывов(-а))
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 за 7 дней(-я)
(0 отзывов(-а))
0.0