Location Tracking/Database

From SOWNWiki
Jump to: navigation, search

Note User:AndrewWillmott is still learning about this and not all information is correct

Whilst most SOWN projects use the MySQL database the Location project uses Postgres with GIS addons in this combination it is known as PostGIS. The majority of geographic systems such as GeoServer, Mapnik interface well with PostGIS making it an idial choice. At a simple level Postgres is the same as MySQL with simple queries such as SELECT, INSERT etc being nearly identical. MySQL is somwhat more relaxed when it comes to syntax using expressions like || instead of OR. When interfacing with Postgres in PHP almost all the mysql commands can be used by changing the mysql_ to pg_. For example to connect to a Postgres server use pg_query(); instead of mysql_query();

Installation on Ubuntu


#mkdir sown
#cd sown
#sudo apt-get install build-essentials
#wget http://download.osgeo.org/geos/geos-3.0.3.tar.bz2
#tar -jxf geos-3.0.3.tar.bz2
#cd geos-3.0.3
#sudo make install
apt-get install postgresql-8.3-postgis postgis

I do not think the geos download is necessary as it appears to be integrated into the PostGIS package now.

You will need to run the following as the postgres user (the sudo -u postgres does this)

sudo -u postgres createdb location_database

Setup the PostGIS libs:

sudo -u postgres createlang plpgsql location_database
sudo -u postgres psql -d location_database -f /usr/share/postgresql-8.3-postgis/lwpostgis.sql

if there were no errors (if the last line of output is COMMIT), then

sudo -u postgres psql -d location_database -f /usr/share/postgresql-8.3-postgis/spatial_ref_sys.sql

You can now import a shape file. You can get one from the installation of GeoServer:

sudo -u postgres shp2pgsql -D myshape.shp mytable location_database | psql location_database

Now this is done you can use a program like GeoServer to chack that the data is imported correctly and that everyting is working.

General Use

You can log in to the Postgres console as follows:

sudo -u postgres psql postgres

You can change a users SQL password with the following command:

ALTER USER <username> WITH ENCRYPTED PASSWORD ' <password> ';

Where <username> is the username of the user and <password> is what you want to set as their password. For example to set the Postgres user's password to changeme we would use the following command:


To leave the psql prompt type


To create a new superuser use the following command:

sudo -u Postgres createuser --superuser <username>

Where username is the name of the user you want to add.

If you want to administer the system graphically you can use a tool called [pgadmin3]. This tool will allow you to graphically view all parts of the database such as stored procedures and views. Having previously use MySQL solely as a data store I found this tool to be good for looking at all the things Postgres can do. Like phpMyAdmin the tool will show you the SQL it is about to execute.

Tables are stored in Servers->server_name->Databases->database_name->Schemas->public->Tables

You can create Views in the Views 'folder'. Views allow you to create virtual tables. For example because location information for access points is stored with a start date and end date when you want to get a list of current access point locations you would have to run a complex query to limit the date range. In addition programs like GeoServer do not perform join queries so instead you create a view for them to use. The following code should generate a view which contains all the currently valid locations for access points:

 SELECT mac_location.location, wifi_ap.ssid, wifi_ap.public, wifi_ap.cost_min, wifi_ap.cost_mb, wifi_ap.dhcp
   FROM mac_location, wifi_ap
  WHERE mac_location.interface_id = wifi_ap.interface_id 
          AND (mac_location.end_date IS NULL OR mac_location.end_date < now()) 
          AND (wifi_ap.end_date IS NULL OR wifi_ap.end_date < now());

ALTER TABLE current_ap OWNER TO Postgres;
COMMENT ON VIEW current_ap IS 'This view shows all the current Access Points and their configuration';

GeoServer can use this table to produce its data. I also believe that Postgres caches the result so repeated queries should be serviced quickly.

The resulting data is as follows:

                     location                            SSID     Public cost_min cost_mb  DHCP
0101000020E61000000970F6DE4ABCF7BF0616FA3F8B754940    apollosoton    0    £0.00    £0.00    0
0101000020E6100000EEDEBCFE45BCF7BF0616FA3F8B754940    jacs.1         0    £0.00    £0.00    0
0101000020E6100000E8640C203ABCF7BF3C7905608B754940    ElvinChloe     0    £0.00    £0.00    0
0101000020E6100000075428A0C3BBF7BF7749FF3F8C754940    BT-Bat-Hub     0    £0.00    £0.00    0

Of interest here is the format of the location. This is a representation of a geographic location, you can use PostGIS's built in commands to convert it into something useful such as KML. The command ST_AsKML will produce the KML output as in the following PHP script:

$conn_string = "host=localhost port=5432 dbname=location_database user=postgres";
pg_connect($conn_string) or die(pg_last_error());
$query = "SELECT ST_AsKML('0101000020E61000000970F6DE4ABCF7BF0616FA3F8B754940');";
$result = pg_query($query) or die(pg_last_error());
$result = pg_fetch_array($result);
echo $result[0];

This will produce the following output:

<Point>1° 29' 0.49" S, 50° 55' 5.92" E</Point>

To create a geometry point you can use the following command:

GeomFromEWKT('SRID=4326;POINT($lon $lat $alt)')

Where $lon is the longitude, $lat is the latitude and $alt is the altitude. SRID=4326 means that we are using the 'normal' cordinate system as opposed one of the others. For more info on SRIDs see http://en.wikipedia.org/wiki/SRID.

The POINT geometry type represents a single point. There is also:

LINESTRING($lon1 $lat1 $alt1,$lon2 $lat2 $alt2,$lon3 $lat3 $alt3)

This represents a line with $lon1 $lat1 $alt1 being the start of the line, $lon2 $lat2 $alt2 being a turing point in the line and $lon3 $lat3 $alt3 being the end of the line.

POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1))

This represents a polygon eg a triangle. Not to sure about it at the moment.

The following MULTIx are multiple versions of the above. For example to store several points in one entry use MULTIPOINT. You might do this if you wanted to record the locations of all the trees in a forest with no particular need to reference a single tree.

MULTIPOINT($lon1 $lat1 $alt1,$lon2 $lat2 $alt2)

Where $lon1 $lat1 $alt1 are the cordinates of the first point and $lon2 $lat2 $alt2 are the cordinates of the second point.

MULTILINESTRING((0 0,1 1,1 2),(2 3,3 2,5 4))
MULTIPOLYGON(((0 0,4 0,4 4,0 4,0 0),(1 1,2 1,2 2,1 2,1 1)), ..)

A geometry collection is a collection of diferent types of geometry. For example it could contain data from a drawing consisting of severl dots lines and polygons.

Inherited Tables

Postgres can have tables in it which inherit from other tables. This is often used if some data in a database is accessed frequently and large parts are accessed infrequently. You can create a 'master' database which contains no data but is inherited by the frequent access database and the archive database. Data can thus be effectivly archieved knowing that the two tables are identical. We will use inheritance for a sligtly diferent purpose. In our case we have an object(table) which represents a WiFi access point. It stores things like the SSID that the access point is broadcasting. When an access point is observed these details are recorded. Once that data has been processed an access point is created which contains all this information. So that we can easily add more details to the WiFi access point object we create a master table which stores the common details about the access point. We then create two table which inherit from this and provide their own details. For example the table for obeserverd access points contains a link to an observed interface whereas the table for the actual AP contains a link straight to the interface table.

The SQL to create these tables is shown below. I used pgAdminIII to do the editing.

The following creates the master table:

CREATE TABLE master_wifi_ap
   ap_id bigserial, 
   ssid character varying(255), 
   hidden_ssid boolean DEFAULT false, 
   encryption_id smallint DEFAULT 0, 
   public smallint DEFAULT 0, 
   cost_min money, 
   cost_mb money, 
   dhcp smallint DEFAULT 0, 
   channel smallint DEFAULT 0, 
   CONSTRAINT ap_pkey PRIMARY KEY (ap_id)
ALTER TABLE master_wifi_ap OWNER TO postgres;
COMMENT ON COLUMN master_wifi_ap.ap_id IS 'This is the ID of the AP';
COMMENT ON COLUMN master_wifi_ap.ssid IS 'This is the SSID broadcast by this access point.  ';
COMMENT ON COLUMN master_wifi_ap.hidden_ssid IS 'If true the SSID is hidden';
COMMENT ON COLUMN master_wifi_ap.encryption_id IS 'This represents the type of encryption being used:
0 - No encryption
1 - WEP
2 - WPA - PSK
3 - WPA - Enterprise
4 - 802.1x - No/Weak encryption';
COMMENT ON COLUMN master_wifi_ap.public IS 'Whether or not this access point is public:
0 - Not known
1 - Free
2 - Commercial
3 - Private';
COMMENT ON COLUMN master_wifi_ap.cost_min IS 'Cost per minuite to use this access point.  -1 indicates unknown.  ';
COMMENT ON COLUMN master_wifi_ap.cost_mb IS 'Cost per Mb of data transfered to use this AP.  -1 indicates unknown';
COMMENT ON COLUMN master_wifi_ap.dhcp IS 'Whether or not this node has DHCP:
0 - Unknown
1 - Yes
2 - No';
COMMENT ON COLUMN master_wifi_ap.channel IS 'This is the channel that the AP is broadcasting on.  0 indicates unknown.  ';
COMMENT ON TABLE master_wifi_ap IS 'This is the master table for Wireless Access points.  Actual and observation tables inherit from this.  '; 

The following creates the wifi_ap table which inherits from the master table:

-- Inherited:   ap_id bigint NOT NULL DEFAULT nextval('master_wifi_ap_ap_id_seq'::regclass),
-- Inherited:   ssid character varying(255),
-- Inherited:   hidden_ssid boolean DEFAULT false,
-- Inherited:   encryption_id smallint DEFAULT 0,
-- Inherited:   public smallint DEFAULT 0,
-- Inherited:   cost_min money,
-- Inherited:   cost_mb money,
-- Inherited:   dhcp smallint DEFAULT 0,
-- Inherited:   channel smallint DEFAULT 0,
  interface_id bigint, -- This is the id of the interface which responsibe for this AP
  start_date timestamp without time zone, -- This is the date and time that the interface first began/was observed to be broadcasting this SSID
  end_date timestamp without time zone, -- This is the date and time that the interface ceaced broadcasting this SSID
  CONSTRAINT wifi_ap_pkey PRIMARY KEY (ap_id),
  CONSTRAINT interface_fkey FOREIGN KEY (interface_id)
      REFERENCES interface (interface_id) MATCH SIMPLE
INHERITS (master_wifi_ap)
ALTER TABLE wifi_ap OWNER TO postgres;
COMMENT ON TABLE wifi_ap IS 'This table stores all the details of actual access points as opposed to those observed.  ';
COMMENT ON COLUMN wifi_ap.interface_id IS 'This is the id of the interface which responsibe for this AP';
COMMENT ON COLUMN wifi_ap.start_date IS 'This is the date and time that the interface first began/was observed to be broadcasting this SSID';
COMMENT ON COLUMN wifi_ap.end_date IS 'This is the date and time that the interface ceaced broadcasting this SSID';

The following creates the observed_wifi_ap table which also inherits from the master:

CREATE TABLE observation_wifi_ap
   observation_interface_id bigint, 
   CONSTRAINT observation_ap_pkey PRIMARY KEY (ap_id), 
   CONSTRAINT observaton_index_fkey FOREIGN KEY (observation_interface_id) 
     REFERENCES observation_interface (observation_interface_id)    
INHERITS (master_wifi_ap)WITH (OIDS=FALSE)
ALTER TABLE observation_wifi_ap OWNER TO postgres;
COMMENT ON COLUMN observation_wifi_ap.observation_interface_id IS 'This is the reference to the interface observed';
COMMENT ON TABLE observation_wifi_ap IS 'This table stores the observations of wireless access points';

Now that that is done if we want to add a new field to the access point database we just have to add it to the master_wifi_ap table and the other tables will automaticaly update.


This is a Java based server which serves out map tiles based on data in (among other things) a PostGIS database. It can serve the data out as a Web Map Server. Map display tools like OpenLayers and Google Maps can take this data and combine it with other sources to provide overlays. The advantage of using a WMS over using a points file is that the data can be streamed onto the screen rather than having to be loaded at startup.

Firstly you will have to setup your Java enviroment correctly. This can be done as follows. Firstluy find out what Java you have running:

sudo update-java-alternatives -l

The chose one of the options and enable it, for example for Java 6:

sudo update-java-alternatives -s java-6-sun

You will also have to set your Java Home:

export JAVA_HOME=/usr/lib/jvm/java-6-sun
export PATH=$PATH:$JAVA_HOME/bin

Now you can download GeoServer:

wget http://downloads.sourceforge.net/geoserver/geoserver-1.7.1a-bin.zip
unzip geoserver-1.7.1a-bin.zip
cd geoserver-1.7.1/bin

Wait a bit and then browse to hxxp://localhost:8080/geoserver/. The server will perform a few one time tasks and then give you a display. You can login by clicking on login. The username and password are as follows:

Username: admin
Password: geoserver

More info can be found at http://geoserver.org/display/GEOSDOC/Documentation.

Database Details

The database consists of the following tables:

interface		This table contains details of all the interfaces observered by the system.
mac_location          	This table records the location of an interface through time
wifi_ap          	This is a table which describes the features available on the AP


CREATE OR REPLACE VIEW current_team_members AS 
 SELECT users.username, users.domain, team.team_id
   FROM users, team, users_team
  WHERE users_team.start_date < now() 
   AND (users_team.end_date > now() OR users_team.end_date IS NULL) 
   AND users.user_id = users_team."user" 
   AND team.team_id = users_team.team;

Using PostGIS

Geographic information is stored as a geometry types. These can be points and can be created as follows:

GeomFromEWKT('SRID=4326;POINT(-1.48334086 50.91834259 100)')

The SRID should almost allways be 4326 unless you happen to be drilling for oil in Argentina. POINT is the cordinates of the location in the format(longitude, latitude, altitude). Altitude is optional. To insert the data into a table the following query is used:

INSERT INTO mac_location (mac_id, location, start_date) 
	VALUES ('4', GeomFromEWKT('SRID=4326;POINT(-1.48334086 50.91834259)'), '2004-09-26 00:00:00');