Đã hoàn thành

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.

Kĩ năng: Lập trình cơ sở dữ liệu, MySQL, PHP, SQL

Xem nhiều hơn: 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

Về Bên Thuê:
( 0 nhận xét ) Santa Pola, Spain

ID dự án: #19754023

Được trao cho:

pandaios

Hi, Nice to meet you. After reading your description carefully, I have understood your requirements fully. As a professional developer, I can complete it in the appointed time. My top skill is mysql query. If you want Thêm

£100 GBP trong 7 ngày
(0 Đánh Giá)
0.0

27 freelancer đang chào giá trung bình £156 cho công việc này

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 Thêm

£90 GBP trong 2 ngày
(1799 Nhận xét)
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 trong 9 ngày
(123 Nhận xét)
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 trong 7 ngày
(103 Nhận xét)
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 trong 7 ngày
(66 Nhận xét)
7.0
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 Thêm

£167 GBP trong 3 ngày
(149 Nhận xét)
6.9
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 Thêm

£150 GBP trong 3 ngày
(20 Nhận xét)
5.6
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 Thêm

£200 GBP trong 15 ngày
(15 Nhận xét)
5.6
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 Thêm

£160 GBP trong 7 ngày
(20 Nhận xét)
5.8
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 Thêm

£100 GBP trong 1 ngày
(16 Nhận xét)
4.5
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 Thêm

£244 GBP trong 3 ngày
(7 Nhận xét)
4.4
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 :) Thêm

£25 GBP trong 2 ngày
(14 Nhận xét)
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 Thêm

£25 GBP trong 1 ngày
(12 Nhận xét)
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 Thêm

£60 GBP trong 3 ngày
(7 Nhận xét)
3.9
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 Thêm

£225 GBP trong 7 ngày
(9 Nhận xét)
3.5
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 Thêm

£150 GBP trong 3 ngày
(4 Nhận xét)
3.3
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 Thêm

£25 GBP trong 3 ngày
(4 Nhận xét)
2.4
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 Thêm

£135 GBP trong 7 ngày
(4 Nhận xét)
2.3
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 Thêm

£150 GBP trong 3 ngày
(1 Nhận xét)
1.3
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 Thêm

£177 GBP trong 1 ngày
(0 Nhận xét)
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 trong 7 ngày
(0 Nhận xét)
0.0