From: Craig Ringer <craig@postnewspapers.com.au>
To: Andrej <andrej.groups@gmail.com>
Cc: Tarlika Elisabeth Schmitz <postgresql3@numerixtechnology.de>
Cc: sql pgsql <pgsql-sql@postgresql.org>
Subject: Re: extracting location info from string
Date: Mon, 23 May 2011 09:42:31 +0800
Message-ID: <4DD9BB87.7070000@postnewspapers.com.au> (raw)
In-Reply-To: <BANLkTikGzsrvXprLXeN3TxZwA=M8fzkn=g@mail.gmail.com>
References: <20110522210526.0f8b66fa@dick.coachhouse>
<20110522230041.28d1bc35@dick.coachhouse>
<BANLkTikGzsrvXprLXeN3TxZwA=M8fzkn=g@mail.gmail.com>
On 23/05/2011 9:11 AM, Andrej wrote:
> On 23 May 2011 10:00, Tarlika Elisabeth Schmitz
> <postgresql3@numerixtechnology.de> wrote:
>> On Sun, 22 May 2011 21:05:26 +0100
>> Tarlika Elisabeth Schmitz<postgresql3@numerixtechnology.de> wrote:
>>
>>> A column contains location information, which may contain any of the
>>> following:
>>>
>>> 1) null
>>> 2) country name (e.g. "France")
>>> 3) city name, region name (e.g. "Bonn, Nordrhein-Westfalen")
>>> 4) city name, Rg. region name (e.g. "Frankfurt, Rg. Hessen")
>>> 5) city name, Rg region name (e.g. "Frankfurt, Rg Hessen")
>>
>>
>> I also need to cope with variations of COUNTRY.NAME and REGION.NAME.
This is a hard problem. You're dealing with free-form data that might be
easily understood by humans, but relies on various contextual
information and knowledge that makes it really hard for computers to
understand.
If you want to do a good job of this, your best bet is to plug in 3rd
party address analysis software that is dedicated to this task. Most
(all?) such packages are commercial, proprietary affairs. They exist
because it's really, really hard to do this right.
> Another thing of great import is whether the city can occur in the
> data column all by itself; if yes, it's next to impossible to distinguish
> it from a country.
Not least because some places are both, eg:
Luxembourg
The Vatican
Singapore
(The Grand Duchy of Luxembourg has other cities, but still serves as an
example).
--
Craig Ringer
Tech-related writing at http://soapyfrogs.blogspot.com/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: craig@postnewspapers.com.au, andrej.groups@gmail.com, postgresql3@numerixtechnology.de
Subject: Re: extracting location info from string
In-Reply-To: <4DD9BB87.7070000@postnewspapers.com.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox