From: Rob Sargent <robjsargent@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: extracting location info from string
Date: Wed, 25 May 2011 16:07:30 -0600
Message-ID: <4DDD7DA2.7040309@gmail.com> (raw)
In-Reply-To: <20110525221342.14e34046@dick.coachhouse>
References: <20110522210526.0f8b66fa@dick.coachhouse>
<20110522230041.28d1bc35@dick.coachhouse>
<BANLkTikGzsrvXprLXeN3TxZwA=M8fzkn=g@mail.gmail.com>
<4DD9BB87.7070000@postnewspapers.com.au>
<ircnj2$cpt$1@news.albasani.net>
<20110523163341.56edb1aa@dick.coachhouse>
<irgo1v$dbv$1@news.albasani.net>
<4DDD1F7C.5040008@gmail.com>
<20110525221342.14e34046@dick.coachhouse>
On 05/25/2011 03:13 PM, Tarlika Elisabeth Schmitz wrote:
> On Wed, 25 May 2011 09:25:48 -0600
> Rob Sargent<robjsargent@gmail.com> wrote:
>
>>
>>
>> On 05/24/2011 10:57 AM, Lew wrote:
>>> Tarlika Elisabeth Schmitz wrote:
>>>
>>>> CREATE TABLE person
>>>> (
>>>> id integer NOT NULL,
>>>> "name" character varying(256) NOT NULL,
>>>> "location" character varying(256),
>>>> CONSTRAINT person_pkey PRIMARY KEY (id)
>>>> );
>>>>
>>>> this was just a TEMPORARY table I created for quick analysis
>>>> of my CSV data (now renamed to temp_person).
>
> CREATE TABLE country
> (
> id character varying(3) NOT NULL, -- alpha-3 code
> "name" character varying(50) NOT NULL,
> CONSTRAINT country_pkey PRIMARY KEY (id)
> );
>
>
>> To minimize the ultimately quite necessary human adjudication, one
>> might make good use of what is often termed "crowd sourcing": Keep
>> all the distinct "hand entered" values and a map to the final human
>> assessment.
>
> I was wondering how to do just that. I don't think it would be a good
> idea to hard code this into the clean-up script. Take, for instance,
> variations of COUNTRY.NAME spelling. Where would I store these?
>
> I could do with a concept for this problem, which applies to a lot of
> string-type info.
>
I think you keep your current structures used for deducing the canonical
forms, but with each unique input encounter you add it to you
seen-thus-far list which becomes just one more check (possibly the first
such check).
create table address_input
(
id unique/sequence,
human_input character varying(256),
resolution character varying(256)
)
You may have to add a column for the type of input (if you know for
instance the input is for street address v. country) or you may want the
resolution to be portioned in to county, city and so on.
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: robjsargent@gmail.com
Subject: Re: extracting location info from string
In-Reply-To: <4DDD7DA2.7040309@gmail.com>
* 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