On 10/7/16 11:30 PM, Jon Pearl - W4ABC wrote:
I've got Mysql loaded in my shack computer to manage my logbook and you're right... a relational database beats the heck out of hunting and picking through a static .pdf file, any day. There are a lot of old entries in this file that could be culled but as you say, if this database was the product of real-time information, it would speed up the search process for amateurs hunting pairs.
I did just this with FCC data looking for commercial pairs. This selects frequencies at base stations between 464 and 465 from a central point, and orders them by furthest to nearest. A query such as this could be run via a web interface after clicking "here" on a map and ranking all frequencies in use in furthest to near from that point. ie, you click on a point and it tells you the frequencies in use there sorted by distance.
SELECT f.frequency_assigned,f.call_sign, f.location_number,f.unique_system_identifier, l.dec_lat, l.dec_long, (ROUND( 3956 * acos( cos( radians(lat) ) * cos( radians(l.dec_lat) ) * cos( radians(l.dec_long) - radians(lon)) + sin(radians(lon)) * sin( radians(l.dec_lat))) )) AS distance FROM `FREQUENCY` f right join LOCATION l on l.location_number = f.location_number and l.unique_system_identifier = f.`unique_system_identifier` WHERE f.frequency_assigned >=464 and f.frequency_assigned <=465 and l.dec_lat is not null AND f.frequency_assigned NOT in (SELECT distinct f.frequency_assigned FROM `FREQUENCY` f right join LOCATION l on l.location_number = f.location_number and l.unique_system_identifier = f.`unique_system_identifier` WHERE f.frequency_assigned >=464 and f.frequency_assigned <=465 and l.dec_lat is not null AND ROUND( 3956 * acos( cos( radians(lat) ) * cos( radians(l.dec_lat) ) * cos( radians(l.dec_long) - radians(lon)) + sin(radians(lat)) * sin( radians(l.dec_lat))) ) < 15 ORDER BY `f`.`frequency_assigned` ASC) ORDER BY `f`.`frequency_assigned`, `distance` DESC
-- Bryan Fields 727-409-1194 - Voice http://bryanfields.net