Chapter 11: Building a Community Site
then obtains the latitude and longitude for each item by looking up the postal code, town, and
country fragment extracted from the output. The assembled information can be inserted
directly into the database that is used to generate the map.
The information generated by Google Local is not easy to dissect, but you can see the informa-
tion for a single restaurant in this raw HTML fragment:
a href=”/maps?q=restaurant,+grantham&output=html&hl=en& ;
latlng=52915423,-640277,10098272919349949403”> ;
The Market Cross Fish Bar & Restaurant ;
Grantham, NG31 6LJ, United Kingdom ;
0.2 mi SW
The latitude and longitude in this output is not useful when dealing with multiple restaurants
because it refers to the middle point of all the results, not the single result shown here. The rest
of the information is easy to identify — you can spot the title, telephone number, and address
information. The second fragment of address information includes the town, postal code, and
country; this can be used, with another request, to find the precise latitude and longitude for
each item.
Storing and Creating the Data
To store the information, you create a database according to the following SQL statement.
Here, a single table is being created that includes a unique ID, the latitude, longitude, entity
type, title, telephone number, and address information:
create table ch11 (entityid int auto_increment not null primary key,
lat float,
lng float,
type varchar(80),
title varchar(80),
tel varchar(80),
adda varchar(80),
addb varchar(80))
Each record in the database contains all of the information extracted, along with the entity
type (restaurant, bank, and so on).
The script that extracts all of the information from the Google Local search and inserts it into
the database is as follows:
#!/usr/bin/perl
use strict;
use LWP::UserAgent;
use URI::Escape;
use Data::Dumper;