Integrating the Aprsworld Database Into Your Application
APRS™ database and information
|This is a draft document, released 2003-09-25 04:30 UTC. Please e-mail firstname.lastname@example.org with any additions, corrections, or suggestions.|
As the time of writing (September 2003) about 1 million raw APRS packets traverse the internet stream each day. Each one of these packets is parsed and inserted into the appropriate table of the aprsworld.net database. These results in about 5 million inserts a day, with an average of about 60 inserts / queries per second. The database grows by about 6 gigabytes per month.
By using the aprsworld.net database you can save the trouble of collecting, parsing, and storing this large ammount of data. Simple operations like finding the last position of a APRS station are extremely easy - and more complex dataminning operations are possible with minimum effort.
After clicking on the search button a listing of matching stations comes up. In this example we search for any station whose call sign starts with WB4APR.
<HTML> <HEAD> <TITLE>Adam Frisch's web site</TITLE> </HEAD> <BODY> <H1>Adam's web site</H1> Here's my currect location: <P> <IMG SRC = "http://maps.aprsworld.net/mapserver/lastposition.php?call=kb0vyo-7" WIDTH=600 HEIGHT=350 ALT=""> <P> <IMG SRC = "http://maps.aprsworld.net/mapserver/lastposition.php?call=kb0vyo-7&scale=street" WIDTH=600 HEIGHT=350 ALT=""> <P> <IMG SRC = "http://db.aprsworld.net/datamart/info-image.php?call=kb0vyo-7" WIDTH=600 HEIGHT=50 ALT="My current status"> <P> email@example.com </BODY> </HTML>You can see this live at http://adamf.jarviscomputer.com The three dynamic elements of the page are images. This allows Adam to link to aprsworld maps and data without running any special software on his server. In the following text we will cover the use of these images.
Once you have a MySQL account you can access the main aprsworld database by point a mysql client to db.aprsworld.net. With MySQL included command line client that is as simple as:
mysql -hdb.aprsworld.net -uMYUSERNAME -pMYPASSWORD aprs
For APRS position, weather, and status information there are tables that contain only the latest information from a station. They are lastposition, lastweather, and laststatus. These tables are many orders of magnitudes smaller than the tables that contain full information. These small tables are only a few megabytes each and are cached in main memory. You can do pretty much any query on the lastposition, lastweather, and laststatus tables without worrying.
A generally useful step for determining what a query will do before performing it
is to use the MySQL "explain" command. Simply prefix your SQL query with "explain"
and MySQL will show you how it intends to execute the query. You will see immediately if an index will be used. Here is an example of a using the "explain" command:
mysql> explain SELECT * FROM position WHERE speed=62;