Database Reference
In-Depth Information
Geocoding and reverse-geocoding
using the GeoNames datasets
In this recipe, you will write two PL/PostgreSQL PostGIS functions that will let you
perform geocoding and reverse-geocoding using the GeoNames datasets.
GeoNamesisadatabaseoftheplacenamesoftheworld,containingovereightmil-
lion records that are available for download free of charge. For the purpose of this
recipe, you will download a part of the database, load it in PostGIS, and then use it
within twofunctions toperform geocoding andreverse-geocoding. Geocoding isthe
processoffindingcoordinatesfromgeographicaldatasuchasanaddressoraplace
name, while reverse-geocoding is the process of finding geographical data such as
an address or place name from its coordinates.
You are going to write the two functions using PL/pgSQL, which adds on top of the
PostgreSQLSQLcommandstheabilitytotiemorecommandsandqueriestogether,
a bunch of control structures, cursors, error management, and other goodness.
Getting ready
DownloadaGeoNamesdataset.Atthetimeofdownloading,youcanfindsomeofthe
datasets ready to be downloaded from
http://download.geonames.org/export/dump/
.
Youmaydecidewhichdatasetyouwanttouse;ifyouwanttofollowthisrecipe,itwill
beenoughtodownloadtheItaliandataset,
IT.zip
file(includedinthebook'sdata-
set, inthe
chp08
directory).
IncaseyouwanttodownloadthefullGeoNamesdataset,youneedtodownloadthe
allCountries.zip
file; it will take a long time as it is about 250 MB.
How to do it...
Carry out the following steps:
1. Unzip the
IT.zip
file to the
working/chp08
directory. Two files will be
extracted: the
readme.txt
file that contains information on the GeoNames
database structure—you can read it to get some more information—and the