1

I am trying to build a rather complicated query in SQL, and being a beginner i would immensely appreciate some help to build it.

I am trying to achieve the following:

population_postcodes table

enter image description here

1/ Calculate the distance between a postcode in the target_postcodes table - say E1 1AA - and all the postcodes in the the population_postcodes table using Cartesian latitude and longitude coordinates using Pythagoras:

SQRT( POW(MY_Y_AXIS - Y_AXIS, 2) + POW(MY_X_AXIS-X_AXIS, 2) )

2/ Create a new column with those distance values,

not sure how to do that step

2-bis/ Sort postcodes in the population_postcodes by the distance value we obtained,

not sure how to do that step

3/ Beginning with the closest postcode, add the value in the population column to a running_count column UNTIL running_count > Number_of_beds of E1 1AA,

proposed query for running count - but missing the above breaking condition:

SELECT distance, Population,
 (SELECT sum(population_postcodes.Population)) AS Total

FROM population_postcodes
WHERE population_postcodes.distance <= T1.distance) AS Total

FROM population_postcodes AS T1

4/ Create a new table that contains the postcode E1 1AA (target_postcode) and the distance value of the last postcode added to our running count.

Finally, i would need to loop this query over the whole target_postcodes table.

Thank you for you very much for helping a newbie out!

Max
  • 163
  • 1
  • 12
  • This is SQL Server, but it may give you some ideas http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/sql-server-zipcode-latitude-longitude-pr – Fionnuala Jan 08 '14 at 11:36
  • Are you using MS Access or MySQL as your database? – Fionnuala Jan 08 '14 at 11:38
  • i am using Access but i figure the sql query will be similar? – Max Jan 08 '14 at 11:41
  • It will not be sufficiently similar, for example, the current answer will not run in MS Access. It is best remove the MySQL tag unless you want MySQL answers. – Fionnuala Jan 08 '14 at 11:46
  • Could you please tell me how it differs / how i could correct the query? – Max Jan 08 '14 at 12:03

1 Answers1

0

1., 2. To bring tables together and perform operations between them, you need to use Join http://dev.mysql.com/doc/refman/5.0/en/join.html otherwise your formula is correct. To create it as a column in your query, just write it in the projection(select) part. Example:

select 
population_postcodes.*, 
target_postcodes.*, 
SQRT( POW(population_postcodes.longitude- target_postcodes.longitude, 2) + POW(population_postcodes.latitude-target_postcodes.latitude, 2) ) as distance
from population_postcodes JOIN target_postcodes

points 2 bis. End with Order by column_name asc/desc http://dev.mysql.com/doc/refman/5.0/en/sorting-rows.html

point 3. Write everything as a sub-query, and select only what you need in the top query. Also look at HAVING http://dev.mysql.com/doc/refman/5.0/en/subqueries.html http://dev.mysql.com/doc/refman/5.0/en/group-by-extensions.html

point 4. look at ways to create tables and apply what you nearned 

create table mytablename
select ... my projection columns
from ...

http://dev.mysql.com/doc/refman/5.1/en/create-table.html

AdrianBR
  • 2,762
  • 1
  • 15
  • 29
  • Adrian thanks so much, i'll try to implement this and let you know in a bit. Again thank you! – Max Jan 08 '14 at 11:41