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