FCC Database Downloads + SQL Developer?

[CanesFan95]

Understood, thanks. I think I just figured how to connect in MySQL Workbench. There's a thing you click on called Startup / Shutdown, then you click a button that says Start Server and then click the little double-arrows to collapse the Management pane and it works. This is weird because I'm not running any "server" and not trying to "connect" to anything. But it works.

 

[CanesFan95]

And here is the MySQL schema that I wrote many years ago...

Thanks for sharing this. I just tried it and it's perfect! Now I can copy and paste this into a query window and run it everytime I wanna update the data with a fresh new download off the FCC website. Those tables you've chosen are exactly the same ones that I was thinking of. Most of the other tables won't even be needed for what I'm doing. For some reason, MySQL Workbench won't let me have capital letters in the table names. I think another challenge will be identifying what fields are the primary / composite keys to be able to do table joins in a SELECT statement.

 

[CanesFan95]

Here is how we load the FCC data into MySQL. Note the PERL regular expression fixes to fix common problems in the raw text data.

foreach ($load as $toLoad) {
    if ($toLoad == "coast") {
        $filesToLoad = array("EM","EN","LO","FR","HD");
    }
    elseif ($toLoad == "bcast") {
        $filesToLoad = array("EN","HD");
    }
    else {
        $filesToLoad = array("EM","EN","LO","FR","HD","LM");
    }
    foreach ($filesToLoad as $fileToLoad) {
        if ($toLoad == "coast" && $fileToLoad == "LO") {
            shell_exec("perl -i -pe 's/\r\n/\n/g' ".$fccLoadDirs["$toLoad"]."/LO.dat");
            shell_exec("perl -i -pe 's/\r\n//g' ".$fccLoadDirs["$toLoad"]."/LO.dat");    
            shell_exec("perl -i -pe 's/\r\r//g' ".$fccLoadDirs["$toLoad"]."/LO.dat");
            shell_exec("perl -i -pe 's/\r/\r\n/g' ".$fccLoadDirs["$toLoad"]."/LO.dat");
        }
        $result = mysql_query("LOAD DATA LOCAL INFILE '".$fccLoadDirs["$toLoad"]."/".$fileToLoad.".dat' INTO TABLE $fileToLoad FIELDS TERMINATED BY '|'") or die ("Error->" . mysql_error());
    }
}

You'll then need to index all your columns that you plan to query against.

Thanks. Now this code is unfamiliar to me. I'm not sure what "PERL" is. Do I simply copy and paste this into a query window in MySQL Workbench and click the lightning bolt to run it? I have my .dat files all saved in C:\FCC Data Land Mobile - Private on my PC. So how with this code do I tell MySQL Workbench to go to "C:\FCC Data Land Mobile - Private"? Those array statements don't have the file extension .dat on them.

 

[KK6WTT]

Understood, thanks. I think I just figured how to connect in MySQL Workbench. There's a thing you click on called Startup / Shutdown, then you click a button that says Start Server and then click the little double-arrows to collapse the Management pane and it works. This is weird because I'm not running any "server" and not trying to "connect" to anything. But it works.

You are running a server technically. MySQL and most database engines employ the server-client design pattern. So when you access MySQL on your local computer, the client is actually making network requests to what is known as the loopback address, which is addressed with localhost or 127.0.0.1 (you may have noticed that your user was something like 'root'@'127.0.0.1'). The requests won't leave your computer but under the hood they're still treated like network requests.

If your firewall is off, your user is set to 'root'@'%' to allow access from anywhere, and your network is configured properly (the router must forward requests on port 3307 to the IP address of the computer hosting MySQL), you should be able to access the database from a remote computer (hackers too, so be careful).

 

[blantonl]

Thanks. Now this code is unfamiliar to me. I'm not sure what "PERL" is. Do I simply copy and paste this into a query window in MySQL Workbench and click the lightning bolt to run it? I have my .dat files all saved in C:\FCC Data Land Mobile - Private on my PC. So how with this code do I tell MySQL Workbench to go to "C:\FCC Data Land Mobile - Private"? Those array statements don't have the file extension .dat on them.

It's a sub-portion of our overall PHP script that downloads the FCC data, imports it into MySQL.

You should just consider it psuedo-code and write your own import routine.

 

[blantonl]

I think another challenge will be identifying what fields are the primary / composite keys to be able to do table joins in a SELECT statement.

You'll want to focus on unique_system_identifier for most queries.

Again, make sure that you create an index for every column where you are going to implement a "WHERE clause" on.

Some of the queries will become pretty complex, and because of the sheer amount of data involved you'll need a pretty powerful machine, and you'll need to configure MySQL with far more upgraded performance and tuning parameters (query, index, key caches, bufferpool etc)

You'll notice that our FCC database implementation if pretty fast. It's because we have a very powerful database server that is dedicated to crunching through FCC queries on the data. Google's search engine along plugs through our FCC license pages 24 hours a day keeping things busy.

 

[CanesFan95]

I see. Well, that goes well beyond my knowledge, so I may end up pulling the plug on this whole idea. This project is not as straightforward as I hoped at first.

 

[blantonl]

We're also looking at implementing some more complex queries like doing emission searches within a county etc (example: show me all licenses and frequencies in a county using TRBO emissions)... but even as something as simple as that sounds is actually a very performance intensive query which we're working hard to optimize.

 

[CanesFan95]

One thing I was wanting to do in my querying was to have a column with the emission designator but, but to also have another column showing the emission description so you don't have to go and keep looking it up. I believe this would mean manually preparing a simple table (maybe in Excel) with the 2 columns (emission designator and emission description) and then importing that as a table in the database. Then it could be joined to the EM table to show the description in another column. What I like about the emission descriptions is, they give you more of a plain-English description even with brand names like NXDN, TRBO, or iDEN, etc. in the description.

The FCC data search on RadioReference works faster than the FCC website though. I am gaining an appreciation for the technical knowledge and work it takes to set it all up and wish I was that savvy.

 

[NebraskaCoder]

We're also looking at implementing some more complex queries like doing emission searches within a county etc (example: show me all licenses and frequencies in a county using TRBO emissions)... but even as something as simple as that sounds is actually a very performance intensive query which we're working hard to optimize.

I know that you said this is in the works and nothing easy, do you think this is still being worked on and planned on today or has this been scrapped?

 

[belvdr]

I know that you said this is in the works and nothing easy, do you think this is still being worked on and planned on today or has this been scrapped?

If the query gets too complex, MySQL will choke. It's a single-threaded database server (i.e. any query can only utilize up to one core), so nothing is really fast on large data sets with MySQL.

 

[NebraskaCoder]

When you go beyond the basic license it allows for multithread.

Sent from my SM-G965U using Tapatalk

 

[belvdr]

When you go beyond the basic license it allows for multithread.

Sent from my SM-G965U using Tapatalk

That is incorrect. Enterprise or Community: they are all single-threaded.

I've been down this road many times with Oracle asking when they are going to allow parallelization, as we would love to have it on our warehouses. They say it's a popular feature request but it's unknown if it will ever be implemented. At this point, we're going to a different platform that allows for parallelization. I doubt Oracle will ever deliver.

 

[NebraskaCoder]

That is incorrect. Enterprise or Community: they are all single-threaded.

I've been down this road many times with Oracle asking when they are going to allow parallelization, as we would love to have it on our warehouses. They say it's a popular feature request but it's unknown if it will ever be implemented. At this point, we're going to a different platform that allows for parallelization. I doubt Oracle will ever deliver.

Sorry, I guess I am use to Microsoft SQL (which is different). I haven't used MySQL for awhile.

Sent from my SM-G965U using Tapatalk

 

[belvdr]

Sorry, I guess I am use to Microsoft SQL (which is different). I haven't used MySQL for awhile.

Sent from my SM-G965U using Tapatalk

No worries. I'm more in tune to SQL Server as well, but have had to use MySQL these past few years. Warehousing in MySQL is a thorough pain.