SQL - query that dependent on 2 tables?

Question

I have a database that contains 2 tables:

Businesses.
Coupons

Every row in Businesses have the following fields:

{id, details, image, name, location_x, location_y},

where location_x and location_y is the latitue and longitue coordiates of google maps.

In the Coupons Table, every row have the following fields:

{id, business_id, image, details, goneDate, goneHour}.

id is the coupon id,

and business_id is the id of the business that this coupon belongs to.

My question:

By quering Coupons , how can I get all the coupons that their owner business location radius is less then 10km from a given coordinates?

or in other words, it's someone like that:

how to take every coupons business id , then go to the business table , and check if that business id location's radius is less than 10km from given location?

I think it related with inner join, not sure.

I'm working in hibernate framework in Java.

Please help me, thanks in advance!

As long as you know the formula for distance calculation based on map coordinates, the rest is a trivial `INNER JOIN`. — PM 77-1, Dec 11 '13 at 21:12
What database engine are you using? SQL Server for example has support for spatial coordinates built in. — acfrancis, Dec 11 '13 at 21:13
Sql server? no francis OP stated he is using nhibernate, he needs link or hql. By the way in the where clause put WHERE (X between coordinate - 10km and coordinate + 10km) AND(samething to Y) — jean, Dec 11 '13 at 21:18
In that case, this might help: http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions.html — acfrancis, Dec 11 '13 at 21:23

SpartanElite · Answer 1 · 2013-12-12T16:23:54.300

1

This can be accomplished with math!
First we know that given a Point P(x, y),
P lies within a Circle with Center C(a,b) and Radius r

if it satisfies the equation (x-a)^2 + (y - b)^2 <= r^2.

So in your case the center would be the the initial coordinates provided.

In order to get all coupons for businesses that lie with the given radius, this query should do the trick.

SELECT 
    bus.id, coup.id 
FROM 
    Businesses AS bus INNER JOIN 
    Coupons AS coup 
WHERE
    bus.id = coup.business_id AND
    (acos(sin(radians(business.location_y)) * sin(radians(a)) + cos(radians(business.location_y)) * cos(radians(a)) * cos(radians(business.location_x) - radians(b)) * 6371) <= 10;

In the above query a is input_y and b is input_x and r is the radius = 10. Query is assuming Earth's radius as 6371 KM.

EDITED: After discussion with @acfrancis, I have rewritten the query using equation that is more appropriate for calculating distance between points on a Sphere. This should work (even though earth is not perfect sphere) for your use case. All functions used are postgres specific. Details can be seen here http://en.wikipedia.org/wiki/Great-circle_distance.

edited Dec 12 '13 at 16:23

answered Dec 11 '13 at 21:35

SpartanElite

624
4
13

Don't confuse latitude/longitude with X/Y coordinates on a cartesian plane. For example, the distance between two points at the same latitude and 1 longitude degree apart depends on the latitude. Close to the poles, the distance is very small but on the equator, it's quite large. The correct calculation isn't trivial. That's why I recommend using built in features, if possible. – acfrancis Dec 11 '13 at 21:45
Yes you are absolutely correct. But in the usecase the OP described at distances of 10km the margin of error using this equation is negligible. If the usecase required high precision, then yes coming up with a query would be little tough :) – SpartanElite Dec 11 '13 at 21:52
It's not a matter of precision. Your formula returns the same distance applied to these two sets of points, in (lat, long) degrees: [(0, 0); (0, 0.1)] and [(180, 0); (180, 0.1)]. The first set is on the equator and the actual distance is about 11km. The second second is on the North Pole and the distance is zero. – acfrancis Dec 11 '13 at 22:04
Firstly there is no such thing as latitude 180 degrees. the valid range is -90 to +90. Secondly even if you meant (long, lat)=(180,0), that is still not the north pole. The north pole is (90,0). Anyhow this solution is not perfect obviously, but will do the job if not taking extreme cases. – SpartanElite Dec 12 '13 at 14:45
As for the rest, location_x, location_y, a and b are (most likely) in decimal degrees. Comparing them (left side of the where clause expression) to km (right side) doesn't make sense. – acfrancis Dec 12 '13 at 15:50

score 0 · Answer 2 · answered Dec 11 '13 at 21:19

I don't think this can be done in SQL alone as you will need to do some computation to figure out the distances between the businesses and the given location.

What could work would be to query businesses, calculate all the disances, throw out ones over 10km, and then query coupons.

SELECT * FROM coupons WHERE business_id IN {array of your <10km business ids}

If you just want to get a result with coupons matched with businesses inner join would work for you

SELECT * FROM coupons INNER JOIN businesses ON coupons.business_id = businesses.id

score 0 · Answer 3 · edited May 23 '17 at 10:25

0

You will have to calculate the distance with a formula. There is an answer to your question here: Finding a geography point within a range of another - SQL Server

edited May 23 '17 at 10:25

Community

1
1

answered Dec 11 '13 at 21:28

Christian Kuetbach

15,850
5
43
79

SQL - query that dependent on 2 tables?

My question:

3 Answers3