Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 SQRT / Pythagoras help please

Author  Topic 

My SQL Hell
Starting Member

3 Posts

Posted - 2014-05-14 : 06:18:30
Hi, can someone please put me out of my misery. I have been trying to teach myself databasing (don't ask why), and have so far succeeded at a very basic level. However, I am now, and have been for weeks, stuck on this probably quite simple problem:

I'm using MySQL Workbench and server v.6.1

I have created several tables which feed data into the table to which this query relates - lets call it LOC. This table's columns are:

a_ID | a_name | a_address | a_X_coord | a_Y_coord | b_ID | b_Name | b_X_coord | b_Y_coord
-----------------------------------------------------------------------------

NOTE: All the coords are simple single digit X&Y's (eg X=2, Y=8), NOT geolocation coords. I'll move onto more accurate / complex geolocational problems using radians / COS etc after I've got the basics.

The problem: I'm trying to create a query that, when a user selects any 'b_Name', the query returns all the 'a' data (but not the coords) with their distances from the selected 'b' location in a table that will look like:

b_Name | a_Name | a_address | Distance_of_a_from b |
----------------------------------------------

The formula I'm using to calculate a's distance from b is pythagoras: SQRT(((a_X - b_X) * (a_X - b_X)) + ((a_Y - b_Y) * (a_Y - b_Y))) which I think is correct.

1. Should this be a FUNCTION or PROCEDURE query?
2. I've lost count of my efforts to create this query and all have returned syntax errors and no amount of tweaking rectifies it, hence why I'm turning to the experts for help.

Any help will be greatly appreciated.

My SQL Hell
Starting Member

3 Posts

Posted - 2014-05-14 : 09:52:51
If it helps identify where I'm going wrong, here is my latest attempt and the query:

DELIMITER $$

CREATE procedure `Hotels_Within_15_miles` (Attraction varchar(45), Hotel varchar(45), Address varchar(45), Telno int, Distance decimal(3,2))

begin

SELECT 'T_Name' AS Attraction, A_Name AS Hotel, A_Address AS Address, A_Telno AS Telno, SQRT(((T_Lat - A_Lat) * (T_Lat - A_Lat)) + ((T_Lon - A_Lon) * (T_Lon - A_Lon))) AS Distance
FROM Locational_Data
WHERE Distance <= 15
Order by Distance asc
END

The error is showing at the line containing END. Additionally, the <=15 is because I only want returns for those hotels located within 15 miles of the attraction.
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-14 : 12:07:18
Someone might be able to help you, but this is a MS SQL Server specific forum. So, you might get better/more timely help from a My SQL forum.
Go to Top of Page

My SQL Hell
Starting Member

3 Posts

Posted - 2014-05-14 : 12:15:02
quote:
Originally posted by Lamprey

Someone might be able to help you, but this is a MS SQL Server specific forum. So, you might get better/more timely help from a My SQL forum.



Quite right, my mistake. Thanks Lamprey
Go to Top of Page
   

- Advertisement -