Geolocalize a Device and Store Coordinates on Webserver

Emiliano Musso
9y
5.4k
0
1

Article

Introduction

In this article we'll see how to make a simple geolocalizing Windows Phone App and how to store the coordinates it acquires on a remote Web server for further examinations. To make the Windows Phone app we'll use C#, when the Web side will be realized in PHP + MySQL to make it usable on every system.

Prerequisites

To use the code presented in the article, you'll need:

Windows 8.1.
Visual Studio with Windows Phone 8.1 SDK.
A local or hosted web server, with Apache with PHP support enabled, plus a MySQL database (phpMyAdmin could be useful too). Alternatively, the web server part could be written in ASP/ASP.NET.

Configuring each one of the preceding prerequisites goes beyond the scope of the article, so I will not address this point assuming that everything was previously successfully configured and running.

If you need a brief reference on how to install an Apache plus PHP/MySQL server, you could refer to my article Quickly installing LAMP server on Debian or Ubuntu Linux.

Analyzing the scenario

Thinking about a demonstrative method to show geolocalization functions, we will keep this as simple as possible. Keep in mind that there are many security aspects that we won't see here (but maybe in the next article), so what you'll read is intended to provide a general insight, or a starting point to develop further solutions. What we'll consider here starts from a simple concept. We have a device (in my case a smart phone) with GPS sensor and network capabilities that must communicate data using the internet. On the other side, we'll have a program running on a web server, waiting to be called upon to receive the data and save it. In the following, we will create a C# app capable of acquiring the device geographical position (in terms of latitude/longitude), and sending this data to a specific URI. For the server-side, we will create a PHP script that will read the sent data as GET parameters, processing and then saving them in a MySQL table.

Database setup

First things first, we must create a table to store the collected data. The following is the T-SQL script for the table creation that we could run in phpMyAdmin. I've created a new database, named "geolog", in which I'll create the table "entries". Here, we will store all the data that our smartphone will send, in other words the latitude/longitude, the device name (in case we want to track multiple devices, selecting each of them by name) and user annotations. The remaining fields (IdEntry and TimeStamp) will be automatically compiled by default constraints on columns. IdEntry is an auto-increment field, when TimeStamp will get its default value from the current timestamp occurring at the INSERT operation.

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
CREATE TABLE entries (
IdEntry int(9) NOT NULL AUTO_INCREMENT,
`TimeStamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
Latitude decimal(12,8) NOT NULL DEFAULT '0.00000000',
Longitude decimal(12,8) NOT NULL DEFAULT '0.00000000',
Device varchar(50) NOT NULL DEFAULT '',
Annotation text NOT NULL,
PRIMARY KEY (IdEntry),
KEY Idx_Device (Device),
KEY Idx_Entry (IdEntry,`TimeStamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

Now, on our MySQL instance, we should see our empty table and we are ready to jot down some PHP to do the server-side part. We will create a script to receive data from the external world and write it to our table and a second script to query the data. Let's start by creating a PHP page named index.php.

Storing data with PHP

Index.php must perform the very simple task of receiving a given GET request it reading every GET parameter to execute, then a T-SQL INSERT operation on the "entries" table, to store the passed values. Again, please note that I haven't implemented any security practices (apart from the use of prepared statements that correspond to parametrized queries in ASP.NET). If you wish to use those pages in a real scenario, you'd better secure them first.

The code is minimal and quite self-explanatory. First we'll open a connection for our database, proceeding then to read the GET parameters and executing our INSERT to finally close our connection. That's all that it takes for now.

<?php
//-- Connecting to "geolog" database, on "localhost" server: replace <USER> and <PASSWORD> variables with the ones which corresponds to your MySQL installation
$mysqli = new mysqli('localhost', '<USER>', '<PASSWORD>', 'geolog');
if ($mysqli->connect_errno) {
$mysqli->connect_errno . ") " . $mysqli->connect_error;
exit();
}
//-- Preparing parametrized INSERT
if (!($stmt = $mysqli->prepare("INSERT INTO entries(Latitude, Longitude, Device, Annotation) VALUES (?, ?, ?, ?)"))) {
$mysqli->errno . ") " . $mysqli->error;
}
//-- Acquire GET parameters
$latitude = $_GET['lt'];
$longitude = $_GET['ln'];
$device = $_GET['d'];
$annotation = $_GET['n'];
//-- Bind GET parameters to prepared statement's variables
if (!$stmt->bind_param("ddss", $latitude, $longitude, $device, $annotation)) {
$stmt->errno . ") " . $stmt->error;
}
//-- Execute INSERT query
if (!$stmt->execute()) {
$stmt->errno . ") " . $stmt->error;
}
//-- Closing connection / cleanup
$stmt->close();
mysqli_close($mysqli);
?>

What it means in practice is that if we open a browser calling a proper formed URL, we'll end up writing to our database. Let's see an example, assuming our webserver is running on localhost.

The /test subdirectory you see in the address bar is a virtual directory I've created on Apache to host the web application, it could be anything of your choice. Please refer to the Apache's online documentation for further information on VirtualHosts configuration.

Send geocoordinates to Web server

Now it's the time to write our Windows Phone 8.1 app. In the source code you can find the project labeled "FollowMe". We will have two pages in it, one dedicated to the check-in part, whereas the second one will be used for the app's settings, such as the URI to query to store the data. More on this later. Our app must do what we've done manually in some lines above, calculate where the device is and calling a properly formed URL (where our web application awaits).

Let's start with the settings page. It will contain a TextBox to specify the URI to be queried and a ComboBox to indicate the Culture to be used when sending coordinates. We could write it as in the following:

<Page
x:Class="FollowMe.Settings"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:local="using:FollowMe"
xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
mc:Ignorable="d"
Background="{ThemeResource ApplicationPageBackgroundThemeBrush}"
Loaded="Page_Loaded">
<Page.BottomAppBar>
<CommandBar>
<AppBarButton Icon="Accept" Label="Save" Click="AppBarButton_Click"/>
<AppBarButton Icon="Cancel" Label="Cancel" Click="CancelButton_Click"/>
</CommandBar>
</Page.BottomAppBar>
<Grid x:Name="LayoutRoot">
<Grid.ChildrenTransitions>
<TransitionCollection>
<EntranceThemeTransition/>
</TransitionCollection>
</Grid.ChildrenTransitions>
<Grid.RowDefinitions>
<RowDefinition Height="Auto"/>
<RowDefinition Height="*"/>
</Grid.RowDefinitions>
<StackPanel Grid.Row="0" Margin="19,0,0,0">
<TextBlock Text="FollowMe" Style="{ThemeResource TitleTextBlockStyle}" Margin="0,12,0,0"/>
<TextBlock Text="Settings" Margin="0,-6.5,0,26.5" Style="{ThemeResource HeaderTextBlockStyle}" CharacterSpacing="{ThemeResource PivotHeaderItemCharacterSpacing}"/>
</StackPanel>
<TextBlock HorizontalAlignment="Left" Margin="19,9.833,0,0" Grid.Row="1"TextWrapping="Wrap" Text="Web service URI" VerticalAlignment="Top" FontFamily="Segoe WP"FontSize="18"/>
<TextBox x:Name="txtWebUri" HorizontalAlignment="Left" Margin="19,38.833,0,0" Grid.Row="1"TextWrapping="Wrap" VerticalAlignment="Top" Width="362" PlaceholderText="http://localhost/"/>
<TextBlock HorizontalAlignment="Left" Margin="19,103.833,0,0" Grid.Row="1"TextWrapping="Wrap" Text="Culture for coordinates" VerticalAlignment="Top" FontFamily="Segoe WP" FontSize="18"/>
<ComboBox x:Name="cmbLang" HorizontalAlignment="Left" Margin="19,122.833,0,0" Grid.Row="1"VerticalAlignment="Top" Width="182">
<x:String>it-IT</x:String>
<x:String>en-US</x:String>
</ComboBox>
</Grid>
</Page>

Please note, I have added a CommandBar to manage two AppBarButtons, one to save our changes and the second to cancel them (and simply close the page).

The code-behind for our page will be:

using FollowMe.Common;
using System;
using Windows.Storage;
using Windows.UI.Xaml;
using Windows.UI.Xaml.Controls;
using Windows.UI.Xaml.Navigation;
namespace FollowMe
{
public sealed partial class Settings : Page
{
private NavigationHelper navigationHelper;
private ObservableDictionary defaultViewModel = new ObservableDictionary();
public Settings()
{
this.InitializeComponent();
this.navigationHelper = new NavigationHelper(this);
this.navigationHelper.LoadState += this.NavigationHelper_LoadState;
this.navigationHelper.SaveState += this.NavigationHelper_SaveState;
}
private void NavigationHelper_SaveState(object sender, SaveStateEventArgs e)
{
}
private void NavigationHelper_LoadState(object sender, LoadStateEventArgs e)
{
}
public NavigationHelper NavigationHelper
{
get { return this.navigationHelper; }
}
public ObservableDictionary DefaultViewModel
{
get { return this.defaultViewModel; }
}
#region NavigationHelper registration
protected override void OnNavigatedTo(NavigationEventArgs e)
{
this.navigationHelper.OnNavigatedTo(e);
}
protected override void OnNavigatedFrom(NavigationEventArgs e)
{
this.navigationHelper.OnNavigatedFrom(e);
}
#endregion
private void AppBarButton_Click(object sender, RoutedEventArgs e)
{
var ap = ApplicationData.Current.LocalSettings;
ap.Values["WebURI"] = txtWebUri.Text;
ap.Values["Language"] = cmbLang.SelectedValue.ToString();
Frame.Navigate(typeof(MainPage));
}
private void Page_Loaded(object sender, RoutedEventArgs e)
{
var ap = ApplicationData.Current.LocalSettings;
try
{
txtWebUri.Text = ap.Values["WebURI"].ToString();
cmbLang.SelectedValue = ap.Values["Language"].ToString();
}
catch {
}
}
private void CancelButton_Click(object sender, RoutedEventArgs e)
{
Frame.Navigate(typeof(MainPage));
}
}
}

Simply, the Settings page logic revolves around the Click event of the two AppBarButtons. The cancel button simply navigates back to the MainPage, wheeas the save button will write in the LocalSettings of our app (from the namespace Windows.Storage). The parameters we've specified for txtWebUri TextBox and cmbLang ComboBox also navigates back to the MainPage.

The MainPage page is the core of the app. It consists simply of a button, a TextBox for any notes the user may want to indicate and two TextBlocks in which we'll expose geocoordinates (for debugging purposes only, the TextBlocks are not really required). Let's see the XAML for MainPage:

<Page
x:Class="FollowMe.MainPage"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:local="using:FollowMe"
xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
mc:Ignorable="d">
<Page.BottomAppBar>
<CommandBar>
<AppBarButton Icon="Manage" Label="Settings" Click="AppBarButton_Click"/>
</CommandBar>
</Page.BottomAppBar>
<Grid Background="{ThemeResource ApplicationPageBackgroundThemeBrush}">
<Button Content="Check in" Click="button1_Click"
HorizontalAlignment="Left" Margin="10,381,0,0" Name="button1" VerticalAlignment="Top"Height="117" Width="380" />
<TextBlock HorizontalAlignment="Left" Margin="10,10,0,0" TextWrapping="Wrap"Text="FollowMe" VerticalAlignment="Top" FontFamily="Segoe WP" FontSize="28" FontWeight="Bold"/>
<TextBox Name="txtNotes" HorizontalAlignment="Left" Margin="10,69,0,0" TextWrapping="Wrap"VerticalAlignment="Top" Width="380" PlaceholderText="Type any notes here"/>
<TextBlock HorizontalAlignment="Left" Margin="10,130,0,0" TextWrapping="Wrap"Text="Latitude" VerticalAlignment="Top"/>
<TextBlock HorizontalAlignment="Left" Margin="10,152,0,0" TextWrapping="Wrap"Text="Longitude" VerticalAlignment="Top"/>
<TextBlock x:Name="lblLatitude" HorizontalAlignment="Left" Margin="104,130,0,0"TextWrapping="Wrap" Text="0.00000000" VerticalAlignment="Top"/>
<TextBlock x:Name="lblLongitude" HorizontalAlignment="Left" Margin="104,152,0,0"TextWrapping="Wrap" Text="0.00000000" VerticalAlignment="Top"/>
<Grid>
</Page>

An AppBarButton will allow us to access the Settings page and no other peculiarities are visible here. The only thing to cause us to consider the code-behind is the presence of a Click event on the sole Button of the page.

using System;
using Windows.UI.Xaml;
using Windows.UI.Xaml.Controls;
using Windows.Devices.Geolocation;
using Windows.Web.Http;
using Windows.UI.Popups;
using System.Globalization;
using Windows.Security.ExchangeActiveSyncProvisioning;
using Windows.Storage;
namespace FollowMe
{
public sealed partial class MainPage : Page
{
Geolocator geo = null;
public MainPage()
{
this.InitializeComponent();
}
private async void button1_Click(object sender, RoutedEventArgs e)
{
geo = new Geolocator();
bool isErr = false;
string errMsg = "";
button1.IsEnabled = false;
try
{
Geoposition pos = await geo.GetGeopositionAsync();
double lat = pos.Coordinate.Point.Position.Latitude;
double lon = pos.Coordinate.Point.Position.Longitude;
lblLatitude.Text = lat.ToString();
lblLongitude.Text = lon.ToString();
var ap = ApplicationData.Current.LocalSettings;
CultureInfo cI = new CultureInfo(ap.Values["Language"].ToString());
String devName = new EasClientDeviceInformation().FriendlyName;
HttpClient hwc = new HttpClient();
Uri myAddress = new Uri(ap.Values["WebURI"].ToString() + "?lt=" +lat.ToString(cI.NumberFormat) + "&ln=" + lon.ToString(cI.NumberFormat) + "&d=" + devName + "&n=" + txtNotes.Text);
HttpResponseMessage x = await hwc.GetAsync(myAddress);
}
catch (Exception ex)
{
isErr = true;
errMsg = ex.Message;
}
if (isErr)
{
var dialog = new MessageDialog(errMsg);
await dialog.ShowAsync();
}
button1.IsEnabled = true;
geo = null;
}
private void AppBarButton_Click(object sender, RoutedEventArgs e)
{
Frame.Navigate(typeof(Settings));
}
}
}

On clicking button1, a new instance of Geolocator is set. From it, we tried to retrieve asynchronously the device position, to read our current longitude/latitude, writing then to our TextBlocks. With the lines from 25 to 36, we've all it takes to geolocate us, the rest of the code is about reaching our webserver. First, we access the Application's LocalSettings to read our URI and Language parameters, initializing a Culture with the value of the latter. This will serve us in determining how to pass our geocoordinates to the webserver (mainly about decimal separator, if comma or dot).

Then, using the EasClientDeviceInformation class, we extract our device FriendlyName or the name of our device (line 44).

Next, we pack up a web request using the HttpClient class, calling asynchronously a previously forgot URI (line from 46 to 52), in which we've indicated our coordinates, device name and any annotations as GET parameters, the way our PHP page expects them.

In case an error occurs, a MessageDialog will be shown.

IMPORTANT: to make everything work, two fundamental things must be checked. The first one is to have location services active on your device. The second one is to enable location capability in the app itself. In your project, you must double-click on the package.appxmanifest file selecting the Capabilities tab. Then check Location in the Capabilities list. Without it, your app won't be able to geolocalize you.

That's it, as you have seen it is a very simple app with many improvements that could be made. However, the present one will suffice for our means.

Examining collected data

As we have seen, when our app makes a web request, a new record will be inserted into the remote database. Now it's time to query our database to exam data. The following code will realize a simple HTML page containing a table with a row per record. It will show the time a record was inserted, the coordinates, the device name, user's annotations (if any). Finally, each row will be terminated by a hyperlink to navigate to a page in which we can have a look at the map (together with a graphical mark on the check-in spot). We will name the following page "geolist.php".

<html>
<head>
<title>GeoLog</title>
</head>
<body>
<table>
<tr>
<th>Id</th>
<th>Date</th>
<th>Lat.</th>
<th>Long.</th>
<th>Device</th>
<th>Annotations</th>
<th>Map</th>
</tr>
<?php
//-- Connecting to "geolog" database, on "localhost" server
mysqli = new mysqli('localhost', '<USER>', '<PASSWORD>', 'geolog');
if ($mysqli->connect_errno) {
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
exit();
}
//-- Declaring and executing a SELECT on "entries" table, to retrieve all the records in it
$query = "SELECT * FROM entries ORDER BY IdEntry";
$result = $mysqli->query($query);
//-- For each retrieved record, we'll add to the DOM a table row, containing the read values
while($row = $result->fetch_array()){ ?>
<tr>
<td><?php echo $row['IdEntry'];?></td>
<td><?php echo $row['TimeStamp'];?></td>
<td><?php echo $row['Latitude'];?></td>
<td><?php echo $row['Longitude'];?></td>
<td><?php echo $row['Device'];?></td>
<td><?php echo $row['Annotation'];?></td>
<td>[<a href="map.php?lt=<?php echo $row['Latitude'];?>&ln=<?php echo $row['Longitude'];?>&d=<?php echo $row['Device'];?>&n=<?php echo $row['Annotation'];?>">Link</a>]</td>
</tr>
<?php }
//-- Close connection / cleanup
$result->close();
$mysqli->close();
?>
</body>

Omitting the styling part, what we've done here is to establish a connection to our MySQL database (namely, "geolog") to proceed in querying the "entries" table. For reach recordset we will create a table row with its children, six cells that will expose the read data. Please note that our final hyperlink targets a page named "map.php". That page is the one in which we'll render the map relative to the given coordinates. Let's see how the page looks.

<?php
$latitude = $_GET['lt'];
$longitude = $_GET['ln'];
$device = $_GET['d'];
$annotation = $_GET['n'];
?>
<html>
<head>
<title>GeoLog</title>
<meta name="viewport" content="initial-scale=1.0, user-scalable=no">
<meta charset="utf-8">
<style>
html, body, #map {
height: 100%;
margin: 0px;
padding: 0px;
width:100%;
}
</style>
<script src="https://maps.googleapis.com/maps/api/js?v=3.exp&signed_in=true&language=it"></script>
<script>
var initialize = function(){
var latlng new google.maps.LatLng(<?php echo $latitude, ",", $longitude;?>);
var options = { zoom: 18,
center: latlng,
mapTypeId: google.maps.MapTypeId.ROADMAP
};
var map = new google.maps.Map(document.getElementById('map'), options);
var marker = new google.maps.Marker({ position: latlng,
map: map,
title: '<?php echo $device;?>' });
}
window.onload = initialize;
</script>
</head>
<body>
<div id="map"></div>
</body>
</html>

To avoid requerying our database, we will use the GET method to pass to our map.php page all the information it needs. The page uses JavaScript with some functions from the Google Places's API (developers reference here). The main function works this way. It creates a map from the given latitude and longitude, assigning it to a DIV element for drawing. Then, a Marker is created to add a graphical placeholder on that specific location. Since we need to use GET parameters as JavaScript variables, we'll use a little trick here. JavaScript is executed on the client side, on the user's machine; PHP is executed server-side. So, if we add PHP instructions, they will be executed before the JavaScript part and when the page "arrives" on the user's device, it will be already modified by the remote PHP execution. Using this, we can simply echo the GET variables as JavaScript parameters and that will be written into the code that will run on the client-side allowing the viewing of the correct map.

Testing the entire package

Having finished coding, we can finally pack everything up and call for an overall test. First things first, we'll open up our WP app on our device, configuring it towards the URI we'll set up. Next, we'll click on the "Check-in" button to start the geolocalization and further web requests. In that phase, our app will build the URI to be queried proceeding in visiting it (and consequentely firing up the remote INSERT procedure).

setting

Now, we'll open a browser visiting the geolist.php page to examine the inserted records. Our recent check-in will be shown and we can click on its link to open the map.php page that will show graphically our previously saved position.

map