Using result of first select statement into anothe

Question

After a lot of reading I have written these 3 sql statement and I want to automate and combine these 3 sql statement.

select longitude,latitude from roadData;

SELECT geo_coordinates_latitude, geo_coordinates_longitude,
 ( 3959 * acos( cos( radians('-37.964637158') ) * cos( radians( geo_coordinates_latitude ) ) * 
 cos( radians( geo_coordinates_longitude ) - radians('144.408476215') ) + sin( radians('-37.964637158') ) *
 sin( radians( geo_coordinates_latitude ) ) ) ) AS distance 
 FROM tweetmelbourne HAVING distance < .1 ORDER BY distance LIMIT 0 , 20;

Insert into roadTweets(longitude,latitude) values(144.408476215,-37.964637158);

**

What basically i want to do is pick all the coordinates from roadData one by one and then find all the point in tweetMelbourne within 20 miles of it and insert those point into another table.

**

describe tweetmelbourne;

describe roadData;

Answer 1

I guess this should be:

    select * from tweetmelbourne t, roadData r 
where distance(r.longitude,r.latitude,t.geo_coordinates_longitude,t.geo_coordinates_latitude) < 20;

And either implement distance funcion or replace distance(r.longitude,r.latitude,t.geo_coordinates_longitude,t.geo_coordinates_latitude) with formula calculating distance so. In oracle you can use package SDO_GEOMETRY.

Answer 2

I'm not enterily sure on this, so please let me know if this works:

Insert into roadTweets(longitude,latitude) values(SELECT geo_coordinates_latitude, geo_coordinates_longitude,
 ( 3959 * acos( cos( radians(SELECT latitude FROM roadData) ) * cos( radians( geo_coordinates_latitude ) ) * 
 cos( radians( geo_coordinates_longitude ) - radians(SELECT longitude FROM roadData) ) + sin( radians(latitude) ) *
 sin( radians( geo_coordinates_latitude ) ) ) ) AS distance 
FROM tweetmelbourne HAVING distance < .1 ORDER BY distance LIMIT 0 , 20);