[FRC] Mysql

[Bryan Fields]

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