<html><body><div style="color:#000; background-color:#fff; font-family:Courier New, courier, monaco, monospace, sans-serif;font-size:10pt"><div><span>Do you have to use Perl? Let relational keys done by relational database may be easier.</span></div><div><span>BTW, <br></span></div><div><span>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.<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> Chris Hamilton <cjhamil@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 2:08 PM<br> <b><span style="font-weight: bold;">Subject:</span></b> Re: [Chicago-talk] creating relational keys<br> </font> </div> <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 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 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> </div> </div> </div></body></html>