HavlenaBlog

This article contains code example of searching and saving GPS coordinates for addresses stored in the database. For this purpose we will use Google Maps API and Geocoding Service (Geocoding is process of converting addresses into geographic coordinates).

The geocoding service may only be used in conjunction with displaying results on a Google map.

Geocoding Service

Geocoding Service provides way to get geographic coordinates via an HTTP request. We must send a request to http://maps.google.com/maps/geo? with several parameteres. We are interested in these parameters:

• q (required) – the address to which you want get coordinates
• key (required) – your API key (you can obtain it here)
• sensor (required) – true if the request is sent from a device with location sensor, otherwise false
• output (required) – the format of output (the options are xml, kml, csv or json)
• oe (optional) – output encoding (It’s recommended set this parameter to utf8)
• …a další

The input data should be encoded in utf-8.

CSV output format

Output data can be generated in these formats:

• json (default)
• kml
• xml
• csv – data are separated by semi-colon

In our situation is CSV ideal because it returns only 4 blocks of data separated by semi-colon:

1. HTTP status code
   • 200 - succes
   • 500 – server error
   • 602 – unknown address
   • 610 – bad API key
   • 620 – too many queries (>5000 per day or too many requests in too short a period of time)
   • a další…
2. Accuracy
   • 0 – unknown
   • 1 – country level
   • …
   • 4 – city (village) leel
   • 5 – ZIP level
   • 6 -street level
   • 7 – intersection level
   • 8 – address level
   • 9 – building level
3. Latitude
4. Longitude

For example:

PHP: example of getting GPS coordinates from CSV file

We assume having table in db with buildings’ addresses to which we can find their GPS coordinates. The table could look like this:

Then our PHP script for filling up GPS coordinates could look like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
<?php

$conn = mysql_connect("localhost", "user", "passwd");

if (!$conn) {

  die("Could not connect: ". mysql_error());

}

if (!mysql_select_db("database", $conn)) {

  die("Could select db: ". mysql_error());

}



// YOUR DOMAIN API KEY

$api_key = "ABCDEFGHIJK";



$query = "select * from buildings where building_latitude is null and building_longitude is null order by building_id";

$result = mysql_query($query);



while ($row = mysql_fetch_array($result)) {



  // SET ADDRESS

  $address = urlencode($row["building_street"]." ".$row["building_street_nr"]." ".$row["building_city"]." Czech republic");



  // URL TO HTTP REQUEST

  $link = "http://maps.google.com/maps/geo?q=".$address."&key=".$api_key."&sensor=false&output=csv&oe=utf8";



  // WE GET FILE CONTENT

  $page = file_get_contents($link);



  // WE OBTAIN DATA FROM GIVEN CSV

  list($status, $accuracy, $latitude, $longitude) = explode(",", $page);



  // IF EVERYTHING OK AND ACCURANCY GREATER THEN 3 WE SAVE COORDINATES

  if (($status == 200) and ($accuracy>=4)) {

    $query_edit = "update buildings set building_latitude = '".$latitude."',

    building_longitude = '".$longitude."'

    where building_id = '".$row["building_id"]."'";

    $result_edit = mysql_query($query_edit);

    echo $row["building_id"]." - OK<br />";

  } else {

    echo $row["building_id"]." - ERROR<br />";

  }



  // TIMER BECAUSE GOOGLE DOESN'T LIKE TO BE QUERIED IN SHORT TIME

  sleep(3);

}



mysql_close($conn);

?>

In following article will be introduced MySQL procedure which returns list of the nearest buildings calculated from GPS.

Matouš Havlena (e-mail, web)

Přečteno 2198x
Štítky: csv, geocoding, google maps, gps, php

Publikováno dne 17.09.2009 v %I:%M %p a zařazeno do kategorie PHP 5, Tips and tricks. Reakce na článek můžete sledovat přes RSS 2.0. Můžete také zanechat reakci nebo trackback z vašich stránek.