Integrating the Aprsworld Database Into Your Application

APRS™ database and information

Home

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

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:

C: libmysql (included with MySQL distribution)
C++: MySQL++
PHP: includes MySQL functions
Perl: can use DBI module
Java: MySQL Connector/J
Windows applications: can use Connector/ODBC which makes it a standard ODBC database

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;

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE position ALL NULL NULL NULL NULL 658073531 Using where

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	position	ALL	NULL	NULL	NULL	NULL	658073531	Using where

This is a very bad query because no indexes will be used (indicated in the KEYS column) and a very large number of rows will be returned. Here is an example of "explain" with a better query:

mysql> explain SELECT * FROM position WHERE source='N0AN-7' AND packet_date >= '2003-09-17' AND packet_date <= '2003-09-18' LIMIT 2000;

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE position range source,packet_date packet_date 8 NULL 1 Using where

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	position	range	source,packet_date	packet_date	8	NULL	1	Using where

This is a better query because an index is being used and LIMIT of 1000 rows is in place. That means that you won't be deluged with more data than expected and the database can quit searching once it gets to 2000 found rows.

Database layout

mysql> SHOW tables;

Tables_in_aprs

lastposition

laststatus

lastuserdefined

lastweather

messages

position

qa_hosts

raw

status

telemetry

telemetry_coefficients

telemetry_labels

telemetry_units

userdefined

watch_programs

watch_users

weather

Tables_in_aprs
lastposition
laststatus
lastuserdefined
lastweather
messages
position
qa_hosts
raw
status
telemetry
telemetry_coefficients
telemetry_labels
telemetry_units
userdefined
watch_programs
watch_users
weather

mysql> DESCRIBE lastposition;

Field Type Null Key Default Extra

packet_id int(11) unsigned NO NULL 0 NULL

packet_date datetime NO NULL 0000-00-00 00:00:00 NULL

source varchar(9) NO PRI NULL NULL

latitude double NO MUL NULL NULL

longitude double NO MUL NULL NULL

course smallint(5) unsigned YES NULL NULL NULL

speed smallint(5) unsigned YES NULL NULL NULL

altitude mediumint(9) YES NULL NULL NULL

status tinytext YES NULL NULL NULL

symbol_table char(1) YES NULL NULL NULL

symbol_code char(1) YES NULL NULL NULL

Field	Type	Null	Key	Default	Extra
packet_id	int(11) unsigned	NO	NULL	0	NULL
packet_date	datetime	NO	NULL	0000-00-00 00:00:00	NULL
source	varchar(9)	NO	PRI	NULL	NULL
latitude	double	NO	MUL	NULL	NULL
longitude	double	NO	MUL	NULL	NULL
course	smallint(5) unsigned	YES	NULL	NULL	NULL
speed	smallint(5) unsigned	YES	NULL	NULL	NULL
altitude	mediumint(9)	YES	NULL	NULL	NULL
status	tinytext	YES	NULL	NULL	NULL
symbol_table	char(1)	YES	NULL	NULL	NULL
symbol_code	char(1)	YES	NULL	NULL	NULL

mysql> DESCRIBE lastweather;

Field Type Null Key Default Extra

packet_id int(11) unsigned NO NULL 0 NULL

source char(9) NO PRI NULL NULL

packet_date datetime NO NULL 0000-00-00 00:00:00 NULL

wind_direction smallint(3) unsigned YES NULL NULL NULL

wind_speed tinyint(4) unsigned YES NULL NULL NULL

wind_gust tinyint(4) unsigned YES NULL NULL NULL

wind_sustained tinyint(4) unsigned YES NULL NULL NULL

temperature tinyint(4) YES NULL NULL NULL

rain_hour float YES NULL NULL NULL

rain_calendar_day float YES NULL NULL NULL

rain_24hour_day float YES NULL NULL NULL

humidity tinyint(4) unsigned YES NULL NULL NULL

barometer double(4,2) unsigned YES NULL NULL NULL

luminosity smallint(5) unsigned YES NULL NULL NULL

Field	Type	Null	Key	Default	Extra
packet_id	int(11) unsigned	NO	NULL	0	NULL
source	char(9)	NO	PRI	NULL	NULL
packet_date	datetime	NO	NULL	0000-00-00 00:00:00	NULL
wind_direction	smallint(3) unsigned	YES	NULL	NULL	NULL
wind_speed	tinyint(4) unsigned	YES	NULL	NULL	NULL
wind_gust	tinyint(4) unsigned	YES	NULL	NULL	NULL
wind_sustained	tinyint(4) unsigned	YES	NULL	NULL	NULL
temperature	tinyint(4)	YES	NULL	NULL	NULL
rain_hour	float	YES	NULL	NULL	NULL
rain_calendar_day	float	YES	NULL	NULL	NULL
rain_24hour_day	float	YES	NULL	NULL	NULL
humidity	tinyint(4) unsigned	YES	NULL	NULL	NULL
barometer	double(4,2) unsigned	YES	NULL	NULL	NULL
luminosity	smallint(5) unsigned	YES	NULL	NULL	NULL

mysql> DESCRIBE laststatus;

Field Type Null Key Default Extra

packet_id int(11) unsigned NO NULL 0 NULL

packet_date datetime NO NULL 0000-00-00 00:00:00 NULL

source varchar(9) NO PRI NULL NULL

comment tinytext YES NULL NULL NULL

power tinyint(4) YES NULL NULL NULL

height smallint(5) unsigned YES NULL NULL NULL

gain tinyint(4) YES NULL NULL NULL

directivity tinyint(4) YES NULL NULL NULL

rate tinyint(4) YES MUL NULL NULL

symbol_table char(1) YES NULL NULL NULL

symbol_code char(1) YES NULL NULL NULL

Field	Type	Null	Key	Default	Extra
packet_id	int(11) unsigned	NO	NULL	0	NULL
packet_date	datetime	NO	NULL	0000-00-00 00:00:00	NULL
source	varchar(9)	NO	PRI	NULL	NULL
comment	tinytext	YES	NULL	NULL	NULL
power	tinyint(4)	YES	NULL	NULL	NULL
height	smallint(5) unsigned	YES	NULL	NULL	NULL
gain	tinyint(4)	YES	NULL	NULL	NULL
directivity	tinyint(4)	YES	NULL	NULL	NULL
rate	tinyint(4)	YES	MUL	NULL	NULL
symbol_table	char(1)	YES	NULL	NULL	NULL
symbol_code	char(1)	YES	NULL	NULL	NULL

mysql> DESCRIBE position;

Field Type Null Key Default Extra

packet_id int(10) unsigned NO PRI 0 NULL

source char(9) NO MUL NULL NULL

packet_date datetime NO MUL 0000-00-00 00:00:00 NULL

time_of_fix int(10) unsigned YES NULL NULL NULL

latitude double YES NULL NULL NULL

longitude double YES NULL NULL NULL

course smallint(5) unsigned YES NULL NULL NULL

speed smallint(3) unsigned YES NULL NULL NULL

altitude mediumint(5) YES NULL NULL NULL

Field	Type	Null	Key	Default	Extra
packet_id	int(10) unsigned	NO	PRI	0	NULL
source	char(9)	NO	MUL	NULL	NULL
packet_date	datetime	NO	MUL	0000-00-00 00:00:00	NULL
time_of_fix	int(10) unsigned	YES	NULL	NULL	NULL
latitude	double	YES	NULL	NULL	NULL
longitude	double	YES	NULL	NULL	NULL
course	smallint(5) unsigned	YES	NULL	NULL	NULL
speed	smallint(3) unsigned	YES	NULL	NULL	NULL
altitude	mediumint(5)	YES	NULL	NULL	NULL

mysql> DESCRIBE weather;

Field Type Null Key Default Extra

packet_id int(11) unsigned NO PRI 0 NULL

source char(9) NO MUL NULL NULL

packet_date datetime NO MUL 0000-00-00 00:00:00 NULL

wind_direction smallint(3) unsigned YES NULL NULL NULL

wind_speed tinyint(4) unsigned YES NULL NULL NULL

wind_gust tinyint(4) unsigned YES NULL NULL NULL

wind_sustained tinyint(4) unsigned YES NULL NULL NULL

temperature tinyint(4) YES NULL NULL NULL

rain_hour float YES NULL NULL NULL

rain_calendar_day float YES NULL NULL NULL

rain_24hour_day float YES NULL NULL NULL

humidity tinyint(4) unsigned YES NULL NULL NULL

barometer double(4,2) unsigned YES NULL NULL NULL

luminosity smallint(5) unsigned YES NULL NULL NULL

Field	Type	Null	Key	Default	Extra
packet_id	int(11) unsigned	NO	PRI	0	NULL
source	char(9)	NO	MUL	NULL	NULL
packet_date	datetime	NO	MUL	0000-00-00 00:00:00	NULL
wind_direction	smallint(3) unsigned	YES	NULL	NULL	NULL
wind_speed	tinyint(4) unsigned	YES	NULL	NULL	NULL
wind_gust	tinyint(4) unsigned	YES	NULL	NULL	NULL
wind_sustained	tinyint(4) unsigned	YES	NULL	NULL	NULL
temperature	tinyint(4)	YES	NULL	NULL	NULL
rain_hour	float	YES	NULL	NULL	NULL
rain_calendar_day	float	YES	NULL	NULL	NULL
rain_24hour_day	float	YES	NULL	NULL	NULL
humidity	tinyint(4) unsigned	YES	NULL	NULL	NULL
barometer	double(4,2) unsigned	YES	NULL	NULL	NULL
luminosity	smallint(5) unsigned	YES	NULL	NULL	NULL

mysql> DESCRIBE raw;

Field Type Null Key Default Extra

packet_id int(10) unsigned NO PRI NULL auto_increment

packet_time int(10) unsigned NO MUL 0 NULL

ip_source varchar(255) YES NULL NULL NULL

source varchar(9) NO NULL NULL NULL

destination varchar(9) NO NULL NULL NULL

digipeater_0 varchar(9) YES NULL NULL NULL

digipeater_1 varchar(9) YES NULL NULL NULL

digipeater_2 varchar(9) YES NULL NULL NULL

digipeater_3 varchar(9) YES NULL NULL NULL

digipeater_4 varchar(9) YES NULL NULL NULL

digipeater_5 varchar(9) YES NULL NULL NULL

digipeater_6 varchar(9) YES NULL NULL NULL

digipeater_7 varchar(9) YES NULL NULL NULL

information tinytext YES NULL NULL NULL

hostid tinyint(3) unsigned NO NULL 0 NULL

Field	Type	Null	Key	Default	Extra
packet_id	int(10) unsigned	NO	PRI	NULL	auto_increment
packet_time	int(10) unsigned	NO	MUL	0	NULL
ip_source	varchar(255)	YES	NULL	NULL	NULL
source	varchar(9)	NO	NULL	NULL	NULL
destination	varchar(9)	NO	NULL	NULL	NULL
digipeater_0	varchar(9)	YES	NULL	NULL	NULL
digipeater_1	varchar(9)	YES	NULL	NULL	NULL
digipeater_2	varchar(9)	YES	NULL	NULL	NULL
digipeater_3	varchar(9)	YES	NULL	NULL	NULL
digipeater_4	varchar(9)	YES	NULL	NULL	NULL
digipeater_5	varchar(9)	YES	NULL	NULL	NULL
digipeater_6	varchar(9)	YES	NULL	NULL	NULL
digipeater_7	varchar(9)	YES	NULL	NULL	NULL
information	tinytext	YES	NULL	NULL	NULL
hostid	tinyint(3) unsigned	NO	NULL	0	NULL

mysql> DESCRIBE messages;

Field Type Null Key Default Extra

packet_id int(10) unsigned NO NULL 0 NULL

source varchar(9) NO MUL NULL NULL

packet_date datetime NO NULL 0000-00-00 00:00:00 NULL

addressee varchar(10) YES MUL NULL NULL

text varchar(73) YES NULL NULL NULL

id varchar(10) YES NULL NULL NULL

Field	Type	Null	Key	Default	Extra
packet_id	int(10) unsigned	NO	NULL	0	NULL
source	varchar(9)	NO	MUL	NULL	NULL
packet_date	datetime	NO	NULL	0000-00-00 00:00:00	NULL
addressee	varchar(10)	YES	MUL	NULL	NULL
text	varchar(73)	YES	NULL	NULL	NULL
id	varchar(10)	YES	NULL	NULL	NULL

mysql> DESCRIBE telemetry;

Field Type Null Key Default Extra

packet_id int(11) NO PRI 0 NULL

source char(10) NO MUL NULL NULL

packet_date datetime NO NULL 0000-00-00 00:00:00 NULL

sequence int(3) NO NULL 0 NULL

analog_0 tinyint(3) unsigned NO NULL 0 NULL

analog_1 tinyint(3) unsigned NO NULL 0 NULL

analog_2 tinyint(3) unsigned NO NULL 0 NULL

analog_3 tinyint(3) unsigned NO NULL 0 NULL

analog_4 tinyint(3) unsigned NO NULL 0 NULL

digital tinyint(3) unsigned NO NULL 0 NULL

Field	Type	Null	Key	Default	Extra
packet_id	int(11)	NO	PRI	0	NULL
source	char(10)	NO	MUL	NULL	NULL
packet_date	datetime	NO	NULL	0000-00-00 00:00:00	NULL
sequence	int(3)	NO	NULL	0	NULL
analog_0	tinyint(3) unsigned	NO	NULL	0	NULL
analog_1	tinyint(3) unsigned	NO	NULL	0	NULL
analog_2	tinyint(3) unsigned	NO	NULL	0	NULL
analog_3	tinyint(3) unsigned	NO	NULL	0	NULL
analog_4	tinyint(3) unsigned	NO	NULL	0	NULL
digital	tinyint(3) unsigned	NO	NULL	0	NULL

mysql> DESCRIBE telemetry_coefficients;

Field Type Null Key Default Extra

source char(10) NO MUL NULL NULL

packet_date datetime NO NULL 0000-00-00 00:00:00 NULL

a_0 double YES NULL NULL NULL

b_0 double YES NULL NULL NULL

c_0 double YES NULL NULL NULL

a_1 double YES NULL NULL NULL

b_1 double YES NULL NULL NULL

c_1 double YES NULL NULL NULL

a_2 double YES NULL NULL NULL

b_2 double YES NULL NULL NULL

c_2 double YES NULL NULL NULL

a_3 double YES NULL NULL NULL

b_3 double YES NULL NULL NULL

c_3 double YES NULL NULL NULL

a_4 double YES NULL NULL NULL

b_4 double YES NULL NULL NULL

c_4 double YES NULL NULL NULL

Field	Type	Null	Key	Default	Extra
source	char(10)	NO	MUL	NULL	NULL
packet_date	datetime	NO	NULL	0000-00-00 00:00:00	NULL
a_0	double	YES	NULL	NULL	NULL
b_0	double	YES	NULL	NULL	NULL
c_0	double	YES	NULL	NULL	NULL
a_1	double	YES	NULL	NULL	NULL
b_1	double	YES	NULL	NULL	NULL
c_1	double	YES	NULL	NULL	NULL
a_2	double	YES	NULL	NULL	NULL
b_2	double	YES	NULL	NULL	NULL
c_2	double	YES	NULL	NULL	NULL
a_3	double	YES	NULL	NULL	NULL
b_3	double	YES	NULL	NULL	NULL
c_3	double	YES	NULL	NULL	NULL
a_4	double	YES	NULL	NULL	NULL
b_4	double	YES	NULL	NULL	NULL
c_4	double	YES	NULL	NULL	NULL

mysql> DESCRIBE telemetry_labels;

Field Type Null Key Default Extra

packet_date datetime NO MUL 0000-00-00 00:00:00 NULL

source varchar(10) NO MUL NULL NULL

a_0 tinytext NO NULL NULL NULL

a_1 tinytext NO NULL NULL NULL

a_2 tinytext NO NULL NULL NULL

a_3 tinytext NO NULL NULL NULL

a_4 tinytext NO NULL NULL NULL

d_0 tinytext NO NULL NULL NULL

d_1 tinytext NO NULL NULL NULL

d_2 tinytext NO NULL NULL NULL

d_3 tinytext NO NULL NULL NULL

d_4 tinytext NO NULL NULL NULL

d_5 tinytext NO NULL NULL NULL

d_6 tinytext NO NULL NULL NULL

d_7 tinytext NO NULL NULL NULL

Field	Type	Null	Key	Default	Extra
packet_date	datetime	NO	MUL	0000-00-00 00:00:00	NULL
source	varchar(10)	NO	MUL	NULL	NULL
a_0	tinytext	NO	NULL	NULL	NULL
a_1	tinytext	NO	NULL	NULL	NULL
a_2	tinytext	NO	NULL	NULL	NULL
a_3	tinytext	NO	NULL	NULL	NULL
a_4	tinytext	NO	NULL	NULL	NULL
d_0	tinytext	NO	NULL	NULL	NULL
d_1	tinytext	NO	NULL	NULL	NULL
d_2	tinytext	NO	NULL	NULL	NULL
d_3	tinytext	NO	NULL	NULL	NULL
d_4	tinytext	NO	NULL	NULL	NULL
d_5	tinytext	NO	NULL	NULL	NULL
d_6	tinytext	NO	NULL	NULL	NULL
d_7	tinytext	NO	NULL	NULL	NULL

mysql> DESCRIBE telemetry_units;

Field Type Null Key Default Extra

source varchar(10) NO MUL NULL NULL

packet_date datetime NO MUL 0000-00-00 00:00:00 NULL

a_0 tinytext NO NULL NULL NULL

a_1 tinytext NO NULL NULL NULL

a_2 tinytext NO NULL NULL NULL

a_3 tinytext NO NULL NULL NULL

a_4 tinytext NO NULL NULL NULL

d_0 tinytext NO NULL NULL NULL

d_1 tinytext NO NULL NULL NULL

d_2 tinytext NO NULL NULL NULL

d_3 tinytext NO NULL NULL NULL

d_4 tinytext NO NULL NULL NULL

d_5 tinytext NO NULL NULL NULL

d_6 tinytext NO NULL NULL NULL

d_7 tinytext NO NULL NULL NULL

Field	Type	Null	Key	Default	Extra
source	varchar(10)	NO	MUL	NULL	NULL
packet_date	datetime	NO	MUL	0000-00-00 00:00:00	NULL
a_0	tinytext	NO	NULL	NULL	NULL
a_1	tinytext	NO	NULL	NULL	NULL
a_2	tinytext	NO	NULL	NULL	NULL
a_3	tinytext	NO	NULL	NULL	NULL
a_4	tinytext	NO	NULL	NULL	NULL
d_0	tinytext	NO	NULL	NULL	NULL
d_1	tinytext	NO	NULL	NULL	NULL
d_2	tinytext	NO	NULL	NULL	NULL
d_3	tinytext	NO	NULL	NULL	NULL
d_4	tinytext	NO	NULL	NULL	NULL
d_5	tinytext	NO	NULL	NULL	NULL
d_6	tinytext	NO	NULL	NULL	NULL
d_7	tinytext	NO	NULL	NULL	NULL

Example SQL Queries

Using SQL to retrieve the last position of a station

This query will show you the last received position of a station. Because the lastposition table contains only the latest position for each station, querying it is very fast.

mysql> SELECT * FROM lastposition WHERE source='KB0THN-7';

packet_id packet_date source latitude longitude course speed altitude status symbol_table symbol_code

112693181 2010-11-24 22:19:12 KB0THN-7 44.0915 -91.116333 60 70 222 In Service Kenwood D700 / k

packet_id	packet_date	source	latitude	longitude	course	speed	altitude	status	symbol_table	symbol_code
112693181	2010-11-24 22:19:12	KB0THN-7	44.0915	-91.116333	60	70	222	In Service Kenwood D700	/	k

If you need the raw packet information to go with the position you can use the packet_id to join against the raw table.

mysql> SELECT lastposition.*, raw.* FROM lastposition, raw WHERE lastposition.source='KB0THN-7' AND lastposition.packet_id = raw.packet_id;

Using SQL to retrieve the last weather report from a station

mysql> SELECT * FROM lastweather WHERE source='WB9BJQ';

packet_id source packet_date wind_direction wind_speed wind_gust wind_sustained temperature rain_hour rain_calendar_day rain_24hour_day humidity barometer luminosity

2144050871 WB9BJQ 2012-02-04 10:40:42 45 0 0 NULL -6 NULL NULL NULL 84 99.99 NULL

packet_id	source	packet_date	wind_direction	wind_speed	wind_gust	wind_sustained	temperature	rain_hour	rain_calendar_day	rain_24hour_day	humidity	barometer	luminosity
2144050871	WB9BJQ	2012-02-04 10:40:42	45	0	0	NULL	-6	NULL	NULL	NULL	84	99.99	NULL

It is important to keep in mind that not all weather stations report all parameters. Some fields will be NULL and should not be confused with a "zero" reading.

Using SQL to find the nearest City / Feature to a point

The gazetteer database is currently in a state of flux. This section will be updated once new data is online.

Web accesses

Almost all of the web pages on aprsworld.net can be linked to directly. The URL for the web page, as accessed, can be copied from the browser location bar or can be found near the bottom of the HTML source. Data that is generated for specific view, such as maps, track files, and weather data, is available for three days after the time it was generated. If you plan of referencing this data you should download it to your local computer or make it available from another web site.

Retrieving last position through in CSV (Comma Separated Vertical) format

The last position of a station, as well as its closest city, and links to maps of it can be retrieved with a simple web request. All that needs to be specified is the call sign to search for. The call sign can include the % wildcard character. For example, to get a listing of the location of all of my stations:

http://db.aprsworld.net/datamart/csv.php?call=kb0thn%

"packet_id","source","latitude","longitude","course","speed","altitude","symbol_table","symbol_code","status","closest_city","mapserver_url_street","mapserver_url_regional","packet_date"
"284383043","KB0THN-2","46.784000","-90.706833","","","","/","_",".../001g007t069#000030PIC","","http://maps.aprsworld.net/mapserver/map.php?lat=46.784000&lon=-90.706833&label=KB0THN-2&icon=aprs_pri_95&scale=street","http://maps.aprsworld.net/mapserver/map.php?lat=46.784000&lon=-90.706833&label=KB0THN-2&icon=aprs_pri_95&scale=regional","2003-09-16 14:34:06"
"256280313","KB0THN-4","44.555833","-123.100333","269","105","62","/",">","Off duty Kenwood D7","","http://maps.aprsworld.net/mapserver/map.php?lat=44.555833&lon=-123.100333&label=KB0THN-4&icon=aprs_pri_62&scale=street","http://maps.aprsworld.net/mapserver/map.php?lat=44.555833&lon=-123.100333&label=KB0THN-4&icon=aprs_pri_62&scale=regional","2003-08-19 21:05:14"
"283730369","KB0THN-7","42.012000","-93.649167","295","5","301","/","k","In Service Kenwood D700","","http://maps.aprsworld.net/mapserver/map.php?lat=42.012000&lon=-93.649167&label=KB0THN-7&icon=aprs_pri_107&scale=street","http://maps.aprsworld.net/mapserver/map.php?lat=42.012000&lon=-93.649167&label=KB0THN-7&icon=aprs_pri_107&scale=regional","2003-09-15 22:34:48"

Using the CSV export is a very easy way to bring data into your custom application or into many off the shelf packages. From the unix-like command line such a request could be made as:

wget -q -O - http://db.aprsworld.net/datamart/csv.php?call=kb0thn%

Under windows it is possible to enter a URL directly into some applications. With Microsoft Excel you can enter the URL directly and it will download the appropriate data and then start a wizard for importing it.

Retrieving a station track over the web

The station track page makes available ESRI Shapefile data that can be used with professional GIS software or amateur radio software such as Xastir. A "shapefile" consists of three individual files. Two of those files, the .shp and the .shx, describe the geometry of the track. The third file, the ".dbf", is a DBase formatted file which can be imported with most spreadsheet software.

Here is an example of a track page:

http://db.aprsworld.net/datamart/track.php?call=KE4NYV-15&hours=72

Example track of Jason KE4NYV

Retrieving weather history over the web

When weather history plots are requested a text data file is generated before the graphs are generated. For a station that transmits weather often, this can be a large number of data points, and as a result it may take some time before the page will display. A link to this data file in included on the weather plot display page, toward the bottom. An example weather plot URL would be:

http://db.aprsworld.net/datamart/gnuplot/weather-plot.php?call=kb0vyo-12&start_date=2003-09-16&end_date=2003-09-17

There are three important parameters to the weather-plot page. The call sign of the station, the start date, and the end date. The dates are specified in "YYYY-MM-DD HH:MM:SS" format. If the dates are omitted it will default to the last week. An optional fourth parameter is metric. If metric=0 imperial units will be used, if metric=1 then SI units will be used.

The data file referenced on the weather plot page is in a tab separated format. Here is an example of the first few lines of a datafile.

packet_date	temperature	wind_direction	wind_speed	wind_gust	rain_hour	rain_day	rain_midnight	barometer	humidity
2003-09-16 00:05:11	48.20	000	1.86	3.73	 0.000	 0.000	 0.000	0.00	 0
2003-09-16 00:08:08	48.20	000	2.49	3.73	 0.000	 0.000	 0.000	0.00	 0
2003-09-16 00:17:02	48.20	000	1.86	3.73	 0.000	 0.000	 0.000	0.00	 0
2003-09-16 00:20:01	48.20	000	1.86	2.49	 0.000	 0.000	 0.000	0.00	 0
2003-09-16 00:22:58	48.20	000	1.86	3.73	 0.000	 0.000	 0.000	0.00	 0

The data file can be imported directly in spreadsheet programs or can easily be parsed with most programming languages. In PHP the line could be split into an array using the "explode" function and then each column could be referenced directly. For example:

$column = explode("\t",$line_of_data_file);
printf("Date: %s Temperature: %s\n",$column[0],$column[1]);