Re: SQL Work Needed at the City of Wilsonville

On Mon, Aug 1, 2011 at 9:45 PM, daniel stark <dan(dot)j(dot)stark(at)gmail(dot)com> wrote:
> On Mon, Aug 1, 2011 at 7:23 PM, Lindsey Smith <lindsey(dot)smith(at)gmail(dot)com> wrote:
>> I invited him to join but I will try again so any momentum picked up
>> so far isn't lost.
>>
>> On Mon, Aug 1, 2011 at 5:43 PM, gabrielle <gorthx(at)gmail(dot)com> wrote:
>>> On Wed, Jul 27, 2011 at 10:18 PM, Lindsey Smith <lindsey(dot)smith(at)gmail(dot)com> wrote:
>>>> I'm more than willing to ride shotgun with Gabrielle to discuss what
>>>> needs to be done. Dan at the City of Wilsonville is a personal friend
>>>> and I *think* they'd be receptive to any offer of help.
>>>
>>> I'm still picking up pieces of my shattered post-OSCON brain :)  Did
>>> you get Dan on the mailing list?
>>>
>>> gabrielle
>>>
>
> Hello to all-
>
> I've joined the list and would be thrilled to work with the group,
> individuals, or all of the above to accomplish the work we need to get
> done.  This can be a paying gig for someone or a group, and I'd be
> more than happy to buy lunch or dinner, host meetings or sponsor a
> hackfest.  I'm a big open source fan and would be solidly behind any
> efforts to further it's application at the City.  Feel free to shoot
> me ideas as a group or individually.
>
> We are a very small IT group and often need to rely on experts for
> specialized tasks.  In this case we have tables coming from multiple
> sources that need to be manipulated so that they match the tables in
> our production SQL databases. There are several different data sets
> that we need to manipulate but I'll provide an example of one
> (deceptively simple one) that could start the conversation.
>
> I have a table with property owner addresses.  The address is one
> field that includes street number, name, type, direction, plus
> apartment/suite/room/.  I need to strip off the apartment/suite/room
> portion of the field and dump it into it's own "suite" field.  There
> are about 12 or so types of "suites" and I have an all inclusive list.
>  It should be fairly straight forward to look for those 12 keywords
> and remove the keyword plus any trailing numbers or characters (ie
> Floor 2 Room 12E), then place them into the suite field.
>
> I'll keep it brief tonight and end here as way of an introduction and
> hope to hear more.  Feel free to contact me on or off the list.
>
> Best,
> dan
>
> GIS Manager
> City of Wilsonville
> voice: 503-570-1533
> stark(at)ci(dot)wilsonville(dot)or(dot)us
>

For the curious I have uploaded a sample of the data that needs the
work described above:

http://www.box.net/shared/grrxp8gtpxsgze542x8i

The field "OWNERADDR" needs to be split into "OWNERADDR" and "OWNERSUITE"

The list of text that identifies a "suite" includes:
#
STE #
STE
UNIT
ROOM
MS#
RM
APT
BOX
FSC

I am sure more will appear over time in future updates of the database
(hence the ongoing nature of this work) so ideally this list would be
spelled out in the code so that it could be easily edited.