[Chicago-talk] creating relational keys
tiger peng
tigerpeng2001 at yahoo.com
Fri Mar 2 09:31:09 PST 2012
You are right. I made one to make the keys and print the result.
:-) cat /tmp/tmp.txt
CITY|STATE|ZIP
SCHAUMBURG|IL|60194
MATTESON|IL|60443
WARRENTON|OR|97146
HOME|AR|72653
WORTH|TX|76107
CLEVELAND|MS|38732
WATERTOWN|SD|57201
CHUTE|WI|54913
:-)perl -aF"\\|" -nle '
BEGIN {$key{USA}=$key=0; print "0|USA|";}
next if $. < 2; # I don't know why the line# start from 1???
foreach $c (1,0,2) {
if (not exists $key{$F[$c]}) {
$key{$F[$c]}=++$key;
if ($c == 1) { #state column
print "$key{$F[1]}|$F[1]|0";
} elsif ($c == 0) { #city column
print "$key{$F[0]}|$F[0]|$key{$F[1]}";
} else { #zip code column
print "$key{$F[2]}|$F[2]|$key{$F[0]}";
}
}
}
' tmp.txt
0|USA|
1|IL|0
2|SCHAUMBURG|1
3|60194|2
4|MATTESON|1
5|60443|4
6|OR|0
7|WARRENTON|6
8|97146|7
9|AR|0
10|HOME|9
11|72653|10
12|TX|0
13|WORTH|12
14|76107|13
15|MS|0
16|CLEVELAND|15
17|38732|16
18|SD|0
19|WATERTOWN|18
20|57201|19
21|WI|0
22|CHUTE|21
23|54913|22
________________________________
From: Joseph Werner <telcodev at gmail.com>
To: Chicago.pm chatter <chicago-talk at pm.org>
Sent: Thursday, March 1, 2012 5:51 PM
Subject: Re: [Chicago-talk] creating relational keys
That is only a problem if you consider leaf nodes to be unique. There
is a quite valid possible tree representation of American zip codes.
On Thu, Mar 1, 2012 at 6:38 PM, tiger peng <tigerpeng2001 at yahoo.com> wrote:
> Do you have to use Perl? Let relational keys done by relational database may
> be easier.
> BTW,
> As I know, in US, some zip codes may cross cities and even state boarder. It
> cannot be simply represented in tree structure nor self-referenced table.
>
> ________________________________
> From: Chris Hamilton <cjhamil at gmail.com>
> To: Chicago.pm chatter <chicago-talk at pm.org>
> Sent: Thursday, March 1, 2012 2:08 PM
> Subject: Re: [Chicago-talk] creating relational keys
>
> Yeah, looks that way. :)
>
> It's too bad you're forced to use such a model, it's not going to make
> for very lovely queries, I don't imagine. Good luck!
>
> -Chris
>
> On Thu, Mar 1, 2012 at 2:06 PM, Jay Strauss <me at heyjay.com> wrote:
>> thanks Chris !
>>
>> We solved it essentially the same way it looks like.
>>
>> Jay
>>
>>
>> On Thu, Mar 1, 2012 at 1:57 PM, Chris Hamilton <cjhamil at gmail.com> wrote:
>>>
>>> Assuming that all keys at a given depth of the hash structure are
>>> unique (probably a big assumption with city names in a state), the
>>> attached script example should probably do what you need (assuming I'm
>>> correctly understanding your problem and you're trying to figure out
>>> how to iterate through the hash structure and do the necessary inserts
>>> with the correct parentIDs). If I'm wrong about which problem you're
>>> having I apologize, but hopefully this provides some insight.
>>>
>>> Thanks,
>>> -Chris
>>>
>>> On Thu, Mar 1, 2012 at 1:14 PM, Jay Strauss <me at heyjay.com> wrote:
>>> > Hi, yes (unfortunately) it all has to go in the same table.
>>> >
>>> > I'm using a vendor supplied data model, with a generic structure like;
>>> >
>>> > ID NAME ParentID
>>> >
>>> > Although, even if I could break it up into separate tables I'd still
>>> > need
>>> > the keys
>>> >
>>> >
>>> > On Thu, Mar 1, 2012 at 1:11 PM, Chris Hamilton <cjhamil at gmail.com>
>>> > wrote:
>>> >>
>>> >> Just out of curiosity, is there a strong need to put all of this data
>>> >> into the same table? At least with your example data it seems like
>>> >> the hierarchy is better managed through separate tables for city,
>>> >> state, and zip (with a 1:many relationship from state to city, and
>>> >> then from city to zip). In that form it would be easy enough to
>>> >> create the ID's and tie them up as you iterate through your hash keys.
>>> >> Just a thought.
>>> >>
>>> >> -Chris
>>> >>
>>> >> On Thu, Mar 1, 2012 at 12:58 PM, Jay Strauss <me at heyjay.com> wrote:
>>> >> > Imran,
>>> >> >
>>> >> > Thanks. I'm ok with building a tree, currently I'm using a hash. I
>>> >> > just
>>> >> > don't know how to unwind the tree and assign the proper ID and
>>> >> > parent
>>> >> > ID.
>>> >> >
>>> >> > Jay
>>> >> >
>>> >> >
>>> >> > On Thu, Mar 1, 2012 at 12:51 PM, imran javaid <imranjj at gmail.com>
>>> >> > wrote:
>>> >> >>
>>> >> >> One option would be use a tree data structure. Take a look at
>>> >> >> Tree::Simple. You will have USA in the first level, states in the
>>> >> >> second,
>>> >> >> cities in the third, and zip codes in the 4th (and then whatever
>>> >> >> else
>>> >> >> below
>>> >> >> that).
>>> >> >>
>>> >> >> -imran
>>> >> >>
>>> >> >> On Thu, Mar 1, 2012 at 12:40 PM, Jay Strauss <me at heyjay.com> wrote:
>>> >> >>>
>>> >> >>> Hi all,
>>> >> >>>
>>> >> >>> I have some data like:
>>> >> >>> CITY|STATE|ZIP
>>> >> >>> SCHAUMBURG|IL|60194
>>> >> >>> MATTESON|IL|60443
>>> >> >>> WARRENTON|OR|97146
>>> >> >>> MOUNTAIN HOME|AR|72653
>>> >> >>> FORT WORTH|TX|76107
>>> >> >>> CLEVELAND|MS|38732
>>> >> >>> WATERTOWN|SD|57201
>>> >> >>> GRAND CHUTE|WI|54913
>>> >> >>>
>>> >> >>> I want to load it into a relational database in such a way that I
>>> >> >>> have
>>> >> >>> the proper keys to build a hierarchy.
>>> >> >>>
>>> >> >>> so for example:
>>> >> >>> ID Name ParentID
>>> >> >>> 0 USA
>>> >> >>> 1 IL 0
>>> >> >>> 2 SCHAUMBURG 1
>>> >> >>> 3 60194 2
>>> >> >>> 4 MATTESON 1
>>> >> >>> 5 60443 4
>>> >> >>> 6 OR 0
>>> >> >>> 7 WARRENTON 6
>>> >> >>> 8 97146 7
>>> >> >>> ...
>>> >> >>>
>>> >> >>> I'm not sure of a good way to do this.
>>> >> >>>
>>> >> >>> I read the data an built a hash like:
>>> >> >>> USA => {IL => {SCHAUMBURG => {60194 => 0},
>>> >> >>> MATTESON => {60443 => 0}},
>>> >> >>> OR => {WARRENTON => {97146 => 0}}
>>> >> >>> ...
>>> >> >>> };
>>> >> >>>
>>> >> >>> I can't think of a good way to look through it and assign the
>>> >> >>> keys.
>>> >> >>>
>>> >> >>> Maybe someone has done this in the past and has an elegant
>>> >> >>> solution?
>>> >> >>>
>>> >> >>> Thanks
>>> >> >>> Jay
>>> >> >>>
>>> >> >>> _______________________________________________
>>> >> >>> Chicago-talk mailing list
>>> >> >>> Chicago-talk at pm.org
>>> >> >>> http://mail.pm.org/mailman/listinfo/chicago-talk
>>> >> >>
>>> >> >>
>>> >> >>
>>> >> >> _______________________________________________
>>> >> >> Chicago-talk mailing list
>>> >> >> Chicago-talk at pm.org
>>> >> >> http://mail.pm.org/mailman/listinfo/chicago-talk
>>> >> >
>>> >> >
>>> >> >
>>> >> > _______________________________________________
>>> >> > Chicago-talk mailing list
>>> >> > Chicago-talk at pm.org
>>> >> > http://mail.pm.org/mailman/listinfo/chicago-talk
>>> >> _______________________________________________
>>> >> Chicago-talk mailing list
>>> >> Chicago-talk at pm.org
>>> >> http://mail.pm.org/mailman/listinfo/chicago-talk
>>> >
>>> >
>>> >
>>> > _______________________________________________
>>> > Chicago-talk mailing list
>>> > Chicago-talk at pm.org
>>> > http://mail.pm.org/mailman/listinfo/chicago-talk
>>>
>>> _______________________________________________
>>> Chicago-talk mailing list
>>> Chicago-talk at pm.org
>>> http://mail.pm.org/mailman/listinfo/chicago-talk
>>
>>
>>
>> _______________________________________________
>> Chicago-talk mailing list
>> Chicago-talk at pm.org
>> http://mail.pm.org/mailman/listinfo/chicago-talk
> _______________________________________________
> Chicago-talk mailing list
> Chicago-talk at pm.org
> http://mail.pm.org/mailman/listinfo/chicago-talk
>
>
>
> _______________________________________________
> Chicago-talk mailing list
> Chicago-talk at pm.org
> http://mail.pm.org/mailman/listinfo/chicago-talk
--
Best Regards,
[Joseph] Christian Werner Sr
C 360.920.7183
H 757.304.0502
_______________________________________________
Chicago-talk mailing list
Chicago-talk at pm.org
http://mail.pm.org/mailman/listinfo/chicago-talk
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.pm.org/pipermail/chicago-talk/attachments/20120302/e2bbfd5d/attachment-0001.html>
More information about the Chicago-talk
mailing list