[Chicago-talk] creating relational keys

Thu Mar 1 15:51:57 PST 2012

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