aprsworld.net

Integrating the Aprsworld Database Into Your Application

APRS™ database and information
  Home   Find Station   About   News

This is a draft document, released 2003-09-25 04:30 UTC. Please e-mail kb0thn@aprsworld.net with any additions, corrections, or suggestions.

About aprsworld

photos of db.aprsworld.net The aprsworld.net project was started in March 2001 by James Jefferson Jarvis, KB0THN. The goal from the beginning has ben to parse the APRS internet stream into data that can be stored in a relational database system.

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.

Tour and introduction to aprsworld.net

Typically aprsworld is accessed through its web interface. To bring up details on a particular station its call sign is entered into the "Find Station" search box.

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.

An example web application

Here is an example from Adam Frisch, KB0VYO, put his current location and status information onto a web page. 

<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>
	 afrisch@jarviscomputer.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.

Maps

Information Image

The information image is a summary of a stations current status. Because it is an image it can easily be included in a normal HTML page.

MySQL Database

Accessing the database

Before you can directly access the database you will need to be issued a MySQL username and password. In the past db.aprsworld.net has allowed direct, anonymous, MySQL access. Currently anonymous access is not enabled because some poorly formed queries can and have brought the system to its knees and caused irreparable loss of data. To get a username and password you can contact kb0thn@aprsworld.net.

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

MySQL clients

There are numerous pieces of software that can access a MySQL database. More information can be found at www.mysql.com. Some of the more common combinations:

Things to avoid

Experimentation is encouraged with aprsworld data! But with that said, the database is fragile to bad queries. In general it is best to search or join only on an indexed column. Some tables in the database are tens of gigabytes large, so doing a query that causes a full table scan can take quite a while. In many cases a search can be limited on a date or time column which is indexed, such as a packet_date or packet_time column.

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;