Geo-Coding - Capturing the
Location from Business Tabular Data
A number of options exist for the linking of a specific record in business (tabular or database)
data to a physical spatial location (GPS coordinate). The process is referred to as Geocoding. The
appropriate method of achieving this depends on many factors such as the type of available data,
as well as possible confidentiality issues which might exclude the use of open public-domain
For one specific project the Dutch Reformed Church of Moreleta Park had a need to strategically
look at the spatial location of their congregation members, in order to enable them to do better
planning and decision making. Having the necessary GIS and database skills, SiQ offered their assistance
in this regard.
The confidentiality of the data was of high importance and thus the process was completed inhouse
without using web-based systems. In order to capture the spatial location of each congregation
member it was necessary to source some base datasets from the local municipality. These
datasets included the erf boundaries, as well as the street address dataset. The latest member
database was also sourced from the church.
The records in the member database had to be linked to a corresponding address point in the
street address dataset. Because the datasets didn’t correspond in many ways, it was necessary to
embark on a thorough cleaning and quality control exercise, involving both datasets. Both datasets
needed to be cleaned for the following reasons:
- The addresses in the member database were captured in Afrikaans, whilst the addresses in
the street address dataset of the municipality, were only available in English. Therefore many
items in the street addresses in the municipal dataset had to be translated into Afrikaans, for
example the word "street" had to be translated to "straat".
- There was a lot of inconsistency in the way that the addresses were captured in the member
database. It was therefore necessary to convert all the addresses in the database to the same
Because of the high number of records to be cleaned, and to ensure repeatability of the cleaning
process, it was decided that the datasets would be imported into a SQL Server database and the
cleaning processes handled with SQL scripts. Within this environment, even individual records
that needed to be changed were handled within a script. The benefit of this approach is that
cleaning updated member databases becomes a highly repeatable process, which is hugely beneficial
with regards to the sustainability of the project.
After both datasets where cleaned, the records in the member dataset were matched (where
possible) to the appropriate street address in the municipal dataset. The final step in the process
was to create a spatial dataset using the X, Y coordinates of the linked data.
In order to enable the relevant church personnel to view the spatial data, it was provided within
an ArcReader document, which enabled them to view the data and do basic queries, as well as
"The benefit of this approach is
that cleaning updated member
databases becomes a highly repeatable
process, which is hugely
beneficial with regards to the sustainability
of the project."