Message-ID: <4DDD7DA2.7040309@gmail.com>
Date: Wed, 25 May 2011 16:07:30 -0600
From: Rob Sargent <robjsargent@gmail.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.14) Gecko/20110221 SUSE/3.1.8 Lightning/1.0b2
	Thunderbird/3.1.8
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
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>
In-Reply-To: <20110525221342.14e34046@dick.coachhouse>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Subject: Re: extracting location info from string


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.