FCC Database Downloads + SQL Developer?

[CanesFan95]

I just found a link on the FCC webpage that evidently has complete sets of data files containing ALL the licensing data. Evidently, this allows us to literally download the entire listing of all FCC licenses with all the data fields (frequency, licensee, callsign, etc.):

Daily Transaction Files: Downloads: Databases

This has the potential to be a goldmine of information for scanning purposes. I think the data sets that are most relevant for scanning is the one they call Land Mobile - Private and Land Mobile - Commercial. These are basically .zip files that contain .dat files with rows of data where each field is separated (delimited) by the pipe | character. They have other documents that explain the table, and one link that actually has SQL syntax to create the tables into a database schema. For example:

create table dbo.PUBACC_FR ( record_type char(2) null, unique_system_identifier numeric(9,0) not null, uls_file_number char(14) null, ebf_number varchar(30) null, call_sign char(10) null, frequency_action_performed char(1) null, location_number int null, antenna_number int null, class_station_code char(4) null, op_altitude_code char(2) null, frequency_assigned numeric(16,8) null, <SNIP>

So here's my question. There is a free program from Oracle called SQL Developer that lets you create and run your own database. Does anyone on here know if SQL Developer can work with the FCC .dat files to create a database and run SELECT queries on it? I think it would great to be able to create our own queries and get lists of stuff. For example, I could tell it to give me a list of all the active licenenses in county XYZ between 450 - 455 MHz.

Then export the Query results to Excel and save it. Now while you're searching around with the scanner and find an active frequency, you can just CTRL+F (search) that frequency in Excel and instantly find who / what it is. So will SQL Developer work with this, or is there some other database program we'd have to use?

 

[n5ims]

While you could roll your own, why. The FCC Advanced License Search is online, always uses the current database (not an older one downloaded at a previous time), and allows you to search as you want. They also have a geosearch function where you can find a license on a frequency (or within a range) that's X kilometers or miles from a desired location.

License Search - Advanced License Search

 

[CanesFan95]

Well, I've used the advanced and geosearches on there and the problem is, there's no way to tell it what fields you want and to just get a complete list showing the frequency without having to drill into each result row one-by-one. And, those searches run very very slow.

 

[MississippiPI]

What?

 

[Voyager]

Those files have been available for decades (literally).

 

[CanesFan95]

So is that a yes? What I'm trying to do would work?

 

[ecps92]

VisualLMR

So is that a yes? What I'm trying to do would work?

 

[wyShack]

I remember several years ago looking at doing the same thing but at the time I gave up on the idea as the daily update would tie up too much bandwidth. If I remember, everything is there to build a mirror of the FCC database and there are daily postings to keep the mirror updated without having to redownload the whole database. I have found the RR database to be more useful as the FCC records indicate what is licensed, rather than what is used. As an example for a statewide trunk system you would not get any talkgroup information and only the licensee's information. Another shortcoming would be all the licenses that are renewed but not used.

Not sure of the size of the files but I would guess even SQL Server Express could be used to 'host' a mirror and serve as the 'back end' a system like you are describing.

With today's home PC's, the response time of the back end may be quick enough for some interaction. You would likely want to filter the database to a 'radius' of 75 miles or so around your location to speed up searches.

 

[KK6WTT]

SQL Developer is only a client application; that is, it does not serve as the database engine itself. You would use it to connect to an instance of an Oracle database (Oracle would be overkill for this use case).

If you're not familiar with standing up a database instance on your own, a cloud-based option such as Amazon RDS or Microsoft Azure could work for you (both have free plans). Microsoft Access (part of Office Professional) would also be perfectly capable for a locally hosted database.

The .DAT file is probably just a tab delimited text file. Most database engines require some extra work to import simple data sets like these; Excel is actually fairly capable with massaging data into an acceptable format to import into a database. Microsoft Access does it very well too.

If you must use Microsoft Excel to query data, Excel is able to retrieve data from an external data source (https://support.office.com/en-us/ar...nal-data-89d44137-f18d-49cf-953d-d22a2eea2d46).



Sent from my SM-G920V using Tapatalk

 

[EIRPdotNET]

HomeBoys-Scanna. Im actually doing some imports into SQL server for customer queries similar to what you described. The ULS database is of more insert for my project, but if you know specifically what you want it would be pretty easy to get you something. SQL Server Express handles this stuff Fine. There are lots of import tools avaiable. If you need help send an email.

 

[CanesFan95]

That Visual LMR link looks really good, although it doesn't QUITE have what I'm looking to do.

And yeah, I just figured that out about SQL Developer. It's not the actual database so it's not gonna work. One of the FCC downloads I did was 1.77 Gb. Can MS Access handle that much data and does it let you do SQL and table joins? Excel has a row limitati if I remember correctly, which might be a problem.

I think the ultimate end goal is to get an Excel list of frequencies that have their transmit location in my county, and some additional columns of info, like the callsign, licensee name, coordinates, emission type (in plain English), etc. I wouldn't need to update it but maybe once or twice a year. This would make searching on the scanner a blast.

 

[KK6WTT]

That Visual LMR link looks really good, although it doesn't QUITE have what I'm looking to do.

And yeah, I just figured that out about SQL Developer. It's not the actual database so it's not gonna work. One of the FCC downloads I did was 1.77 Gb. Can MS Access handle that much data and does it let you do SQL and table joins? Excel has a row limitati if I remember correctly, which might be a problem.

I think the ultimate end goal is to get an Excel list of frequencies that have their transmit location in my county, and some additional columns of info, like the callsign, licensee name, coordinates, emission type (in plain English), etc. I wouldn't need to update it but maybe once or twice a year. This would make searching on the scanner a blast.

Microsoft Access does support SQL, table joins, etc. In fact you can even use it as a client into SQL Server instances or ODBC databases. Unfortunately it only supports 2 GB maximum file sizes and individual database objects can be a maximum of 1 GB. Normalization could drop the overall size of the imported data, but if one DAT file is already 1.7 GB, it's unlikely you'll reclaim 0.7 GB by normalizing.

It sounds like you'll need a full-fledged database engine, such as SQL Server Express. MySQL and PostgreSQL are two other free options, but I personally favor SQL Server. I was partially mistaken about the cloud-based options: the free tiers expire after 1 month for Azure and 1 year for AWS. It's not terribly difficult to set up SQL Server Express on a local machine. Use the "SQL Server Express with Tools" installer here, with comes with Management Studio as well (http://www.microsoft.com/en-us/server-cloud/products/sql-server-editions/sql-server-express.aspx)





Sent from my SM-G920V using Tapatalk

 

[Voyager]

If you only want one county, use the RR FCC data utilities.

Not that it should matter for you, but Excel is limited to 65535 lines. I ran into that roadblock many years ago.

 

[QDP2012]

...Excel is limited to 65535 lines...

In case it helps, Excel 2010+ allows 1,048,576 lines.

 

[theoldcop]

FCC license info.

https://groups.yahoo.com/neo/groups/ScanCom/conversations/messages

 

[CanesFan95]

Microsoft Access does support SQL, table joins, etc. In fact you can even use it as a client into SQL Server instances or ODBC databases. Unfortunately it only supports 2 GB maximum file sizes and individual database objects can be a maximum of 1 GB. Normalization could drop the overall size of the imported data, but if one DAT file is already 1.7 GB, it's unlikely you'll reclaim 0.7 GB by normalizing.

It sounds like you'll need a full-fledged database engine, such as SQL Server Express. MySQL and PostgreSQL are two other free options, but I personally favor SQL Server. I was partially mistaken about the cloud-based options: the free tiers expire after 1 month for Azure and 1 year for AWS. It's not terribly difficult to set up SQL Server Express on a local machine. Use the "SQL Server Express with Tools" installer here, with comes with Management Studio as well (SQL Server Express Edition | Microsoft)

Well, I went ahead and tried SQL Server Express with Tools, but there's one big problem. The import-export function doesn't let you actually save anything unless you buy one of the pay-for versions. The free Express version will let you create databases and tables, but you can't add rows of data to the tables. I was able to create one of the FCC tables by copying and pasting the SQL syntax from the FCC web page. But when you try to import the .dat file into the table in the Express version, you basically get a message saying you can't save it. So the table remains blank. Bummer.

 

[wyShack]

At this point you have several options, depending on what you have and how hard you want to work.

option one would be to install Visual Basic Express (the 'free' version of Visual Basic) and write your import code. While not trivial and a significant learning curve, you could then add the rest of your 'logic' to a VB application and be done.

option two would be to use Access as a 'front end' -the basic idea is to import into Access, then use Access to 'link' to SQL Server and move the data that way.

Option three would be to learn SQL to the point where you could write the import code in SQL Server's 'native' language (known as T-SQL). You may already know SQL, if not you will need to know it for the project anyway. I am assuming the .DAT files are some form of text file and therefore can be handled by SQL directly (would have to look, but the DAT file may be just comma delimited text).

Any of the above may require some learning but will 'get the job done'.

Good luck with the project

 

[KK6WTT]

Well, I went ahead and tried SQL Server Express with Tools, but there's one big problem. The import-export function doesn't let you actually save anything unless you buy one of the pay-for versions. The free Express version will let you create databases and tables, but you can't add rows of data to the tables. I was able to create one of the FCC tables by copying and pasting the SQL syntax from the FCC web page. But when you try to import the .dat file into the table in the Express version, you basically get a message saying you can't save it. So the table remains blank. Bummer.

Welcome to the world of ETL; you can find a whole career in this area. Import-export from text files is not entirely straightforward in SQL Server, even the paid version.

The Option 3 that wyShack recommended is not all that difficult by using Windows PowerShell. I wrote a small script that takes the DAT file and converts it into the necessary INSERT statement to insert the records into the table (find it here: https://gist.github.com/mattstrom/e12519e10763c4b0e56b07664e480812).

Example:

INSERT INTO vs_market (record_type,content_indicator,file_number,registration_number,unique_system_identifier,entity_type,entity_type_code,entity_type_other,licensee_id,entity_name,first_name,mi,last_name,suffix,phone,fax,internet_address,street_a
ddress,street_address2,po_box,city,state,zip_code,attention,frn) VALUES 
('EN','1603679','','',WPLM204,'L','L01097442','NPCR, Inc.','','','','','','','','','','','','','','','','',''),
('EN','1603680','','',WPLM204,'L','L01097442','NPCR, Inc.','','','','','','','','','','','','','','','','',''),
('EN','1603681','','',WPLM204,'L','L01097442','NPCR, Inc.','','','','','','','','','','','','','','','','','');

WARNING: PowerShell scripts have the ability to make serious modifications to your system. You should always be cautious when using scripts from untrusted sources. Understand the code in the script before running it.

 

[KE4ZNR]

Someone has been parsing the FCC database for awhile now:

Quick Scanner Links

and this link should be handy:

Using the raw FCC data

Marshall KE4ZNR

 

[theoldcop]

Message #15