Re: SQL Work Needed at the City of Wilsonville

I mentioned this at the PDX PUG meeting tonight, but I just got a few
more details. Asking on behalf of a friend, the fine GIS folks at the
City of Wilsonville need some SQL assistance. I *believe* that this
would be a paying gig, but possibly a short one.

In their words: "Take 2 tables with the same data in different columns
(owners, addresses, etc) massage the data in SQL then drop it into a
fresh table that we use to update our production database."

If you are interested, contact:
Daniel J Stark AICP, GISP
GIS Manager
City of Wilsonville
voice: 503-570-1533
stark(at)ci(dot)wilsonville(dot)or(dot)us

Lindsey

Sounds more like a Perl job to me.

Tom
MMI DNA Services Core Facility
503-494-2442
kellert at ohsu.edu
Office: 6588 RJH (CROET/BasicScience)

On Jul 21, 2011, at 10:10 PM, Lindsey Smith wrote:

> I mentioned this at the PDX PUG meeting tonight, but I just got a few
> more details. Asking on behalf of a friend, the fine GIS folks at the
> City of Wilsonville need some SQL assistance. I *believe* that this
> would be a paying gig, but possibly a short one.
>
> In their words: "Take 2 tables with the same data in different columns
> (owners, addresses, etc) massage the data in SQL then drop it into a
> fresh table that we use to update our production database."
>
> If you are interested, contact:
> Daniel J Stark AICP, GISP
> GIS Manager
> City of Wilsonville
> voice: 503-570-1533
> stark(at)ci(dot)wilsonville(dot)or(dot)us
>
> Lindsey
>
> --
> Sent via pdxpug mailing list (pdxpug(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pdxpug

> On Jul 21, 2011, at 10:10 PM, Lindsey Smith wrote:

>> In their words: "Take 2 tables with the same data in different columns
>> (owners, addresses, etc) massage the data in SQL then drop it into a
>> fresh table that we use to update our production database."

Tom commented:
> Sounds more like a Perl job to me.

Yeah... and deceptively simple. I might call them and find out some
more details.

Anybody interested in a group PDXPUG hackfest/fundraiser/helping out
the local gov, or is that a totally insane idea?

gabrielle

I have to do this every once and a while at work, and would enjoy a hackfest
to learn if other PDXPUG folks have a better way to do it...
shawn

On Tue, Jul 26, 2011 at 10:26 AM, gabrielle <gorthx(at)gmail(dot)com> wrote:

> > On Jul 21, 2011, at 10:10 PM, Lindsey Smith wrote:
>
> >> In their words: "Take 2 tables with the same data in different columns
> >> (owners, addresses, etc) massage the data in SQL then drop it into a
> >> fresh table that we use to update our production database."
>
> Tom commented:
> > Sounds more like a Perl job to me.
>
> Yeah... and deceptively simple. I might call them and find out some
> more details.
>
> Anybody interested in a group PDXPUG hackfest/fundraiser/helping out
> the local gov, or is that a totally insane idea?
>
> gabrielle
>
> --
> Sent via pdxpug mailing list (pdxpug(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pdxpug
>

On Jul 26, 2011, at 10:26 AM, gabrielle wrote:

>> On Jul 21, 2011, at 10:10 PM, Lindsey Smith wrote:
>
>>> In their words: "Take 2 tables with the same data in different columns
>>> (owners, addresses, etc) massage the data in SQL then drop it into a
>>> fresh table that we use to update our production database."
>
> Tom commented:
>> Sounds more like a Perl job to me.
>
> Yeah... and deceptively simple. I might call them and find out some
> more details.
>
> Anybody interested in a group PDXPUG hackfest/fundraiser/helping out
> the local gov, or is that a totally insane idea?
>
> gabrielle
>

I was thinking a join or union deal and a select into. I could be grossly underestimating how complex their data is.

I like the idea of a hackfest for this. It would be really interesting to see how everyone goes about this.

Brent D.

>> Anybody interested in a group PDXPUG hackfest/fundraiser/helping out
>> the local gov, or is that a totally insane idea?
>>
>> gabrielle

Wow, what an idea! I will give Dan at the City of Wilsonville a shout
and invite him to the mailing list. He should be able provide more
details.

Lindsey

On Tue, 26 Jul 2011 10:37:41 -0700, Brent Dombrowski
<brent(dot)dombrowski(at)gmail(dot)com> wrote:

> I was thinking a join or union deal and a select into. I could be
> grossly underestimating how complex their data is.
>
> I like the idea of a hackfest for this. It would be really interesting
> to see how everyone goes about this.
>
> Brent D.

That does sound like fun. Would they buy us lunch or anything?

--
Melissa Hollingsworth
melissa(at)fastanimals(dot)com
+1 512 619 1237

On 07/26/2011 12:42 PM, Melissa Hollingsworth wrote:
> On Tue, 26 Jul 2011 10:37:41 -0700, Brent Dombrowski
> <brent(dot)dombrowski(at)gmail(dot)com> wrote:
>
>
>> I was thinking a join or union deal and a select into. I could be
>> grossly underestimating how complex their data is.
>>
>> I like the idea of a hackfest for this. It would be really interesting
>> to see how everyone goes about this.
>>
>> Brent D.
>
> That does sound like fun. Would they buy us lunch or anything?

If it was for open source work, specifically PostgreSQL work, PgUS would
probably get behind this.

JD

--
Command Prompt, Inc. - http://www.commandprompt.com/
PostgreSQL Support, Training, Professional Services and Development
The PostgreSQL Conference - http://www.postgresqlconference.org/
@cmdpromptinc - @postgresconf - 509-416-6579

On 07/26/11 10:26, gabrielle wrote:
>> On Jul 21, 2011, at 10:10 PM, Lindsey Smith wrote:
>>> In their words: "Take 2 tables with the same data in different columns
>>> (owners, addresses, etc) massage the data in SQL then drop it into a
>>> fresh table that we use to update our production database."
> Tom commented:
>> Sounds more like a Perl job to me.
> Yeah... and deceptively simple. I might call them and find out some
> more details.
>
> Anybody interested in a group PDXPUG hackfest/fundraiser/helping out
> the local gov, or is that a totally insane idea?
>
> gabrielle
>

Sounds like a great idea.

On Wed, 27 Jul 2011 08:36:01 -0700, Michael Ewan <michael(dot)ewan(at)intel(dot)com>
wrote:

>> Anybody interested in a group PDXPUG hackfest/fundraiser/helping out
>> the local gov, or is that a totally insane idea?
>>
>> gabrielle
>>
>
> Sounds like a great idea.

So who's going to pitch it to them?

I nominate Gabrielle.

--
Melissa Hollingsworth
melissa(at)fastanimals(dot)com
+1 512 619 1237

On Wed, Jul 27, 2011 at 9:33 AM, Melissa Hollingsworth
<melissa(at)fastanimals(dot)com> wrote:
> On Wed, 27 Jul 2011 08:36:01 -0700, Michael Ewan <michael(dot)ewan(at)intel(dot)com>
> wrote:
>
>
>>> Anybody interested in a group PDXPUG hackfest/fundraiser/helping out
>>> the local gov, or is that a totally insane idea?
>>>
>>> gabrielle
>>>
>>
>> Sounds like a great idea.
>
> So who's going to pitch it to them?
>
> I nominate Gabrielle.

+1 :D

Mark

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.

On Wed, Jul 27, 2011 at 9:33 AM, Melissa Hollingsworth
<melissa(at)fastanimals(dot)com> wrote:
> On Wed, 27 Jul 2011 08:36:01 -0700, Michael Ewan <michael(dot)ewan(at)intel(dot)com>
> wrote:
>
>
>>> Anybody interested in a group PDXPUG hackfest/fundraiser/helping out
>>> the local gov, or is that a totally insane idea?
>>>
>>> gabrielle
>>>
>>
>> Sounds like a great idea.
>
> So who's going to pitch it to them?
>
> I nominate Gabrielle.
>
>
> --
> Melissa Hollingsworth
> melissa(at)fastanimals(dot)com
> +1 512 619 1237
>
> --
> Sent via pdxpug mailing list (pdxpug(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pdxpug
>

This could be our August meeting, if no speaker has yet
stepped forward.

On Wed, 27 Jul 2011 22:18:06 -0700, 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.

--
Melissa Hollingsworth
melissa(at)fastanimals(dot)com
+1 512 619 1237

On 07/28/2011 10:17 AM, Melissa Hollingsworth wrote:
> This could be our August meeting, if no speaker has yet
> stepped forward.

Have you guys had a performance talk recently?

>
>
> On Wed, 27 Jul 2011 22:18:06 -0700, 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.
>

--
Command Prompt, Inc. - http://www.commandprompt.com/
PostgreSQL Support, Training, Professional Services and Development
The PostgreSQL Conference - http://www.postgresqlconference.org/
@cmdpromptinc - @postgresconf - 509-416-6579

Fun!

On 07/28/2011 10:17 AM, Melissa Hollingsworth wrote:
> This could be our August meeting, if no speaker has yet
> stepped forward.
>
>
> On Wed, 27 Jul 2011 22:18:06 -0700, 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.
>

On Thu, Jul 28, 2011 at 10:28 AM, Joshua D. Drake <jd(at)commandprompt(dot)com> wrote:
> On 07/28/2011 10:17 AM, Melissa Hollingsworth wrote:
>>
>> This could be our August meeting, if no speaker has yet
>> stepped forward.
>
> Have you guys had a performance talk recently?

Not recently. Is there a particular month you'd like to speak?

Regards,
Mark

On 07/29/2011 08:42 PM, Mark Wong wrote:
> On Thu, Jul 28, 2011 at 10:28 AM, Joshua D. Drake<jd(at)commandprompt(dot)com> wrote:
>> On 07/28/2011 10:17 AM, Melissa Hollingsworth wrote:
>>>
>>> This could be our August meeting, if no speaker has yet
>>> stepped forward.
>>
>> Have you guys had a performance talk recently?
>
> Not recently. Is there a particular month you'd like to speak?

I have my practical postgresql performance talk. If you don't have a
speaker for August, I could give it then.

>
> Regards,
> Mark
>

--
Command Prompt, Inc. - http://www.commandprompt.com/
PostgreSQL Support, Training, Professional Services and Development
The PostgreSQL Conference - http://www.postgresqlconference.org/
@cmdpromptinc - @postgresconf - 509-416-6579

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

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
>
> --
> Sent via pdxpug mailing list (pdxpug(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pdxpug
>

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

On Mon, 1 Aug 2011, daniel stark wrote:

> 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.

Dan,

Just out of curiosity, is this a one-time manipulation or an on-going
process?

Rich

Hi group,

Here is a paper from ESRI on how they parse the elements of the address. It
gives lists of which words and aliases go with which part of the address
(like all the different way people spell street (st, str, street, strt,
etc.). It's pretty specific to their software, but gives some background on
how to think about the different parts of the address.

Just one idea,
Shawn

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
>
> --
> Sent via pdxpug mailing list (pdxpug(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pdxpug
>

Whoops, I didn't include the link:
http://resources.arcgis.com/gallery/file/geocoding/details?entryID=51EE55F8-1422-2418-7F8B-FD82DE958878

On Tue, Aug 2, 2011 at 7:41 AM, Shawn Helm <helm(dot)shawn(at)gmail(dot)com> wrote:

> Hi group,
>
> Here is a paper from ESRI on how they parse the elements of the address.
> It gives lists of which words and aliases go with which part of the address
> (like all the different way people spell street (st, str, street, strt,
> etc.). It's pretty specific to their software, but gives some background on
> how to think about the different parts of the address.
>
> Just one idea,
> Shawn
>
> 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
>>
>> --
>> Sent via pdxpug mailing list (pdxpug(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pdxpug
>>
>
>

I see a script to normalize addresses as part of the postgis tiger geocoder:
http://www.postgis.org/documentation/manual-svn/Extras.html

maybe this would be something to look at?

Shawn

On Tue, Aug 2, 2011 at 7:42 AM, Shawn Helm <helm(dot)shawn(at)gmail(dot)com> wrote:

> Whoops, I didn't include the link:
> http://resources.arcgis.com/gallery/file/geocoding/details?entryID=51EE55F8-1422-2418-7F8B-FD82DE958878
>
> On Tue, Aug 2, 2011 at 7:41 AM, Shawn Helm <helm(dot)shawn(at)gmail(dot)com> wrote:
>
>> Hi group,
>>
>> Here is a paper from ESRI on how they parse the elements of the address.
>> It gives lists of which words and aliases go with which part of the address
>> (like all the different way people spell street (st, str, street, strt,
>> etc.). It's pretty specific to their software, but gives some background on
>> how to think about the different parts of the address.
>>
>> Just one idea,
>> Shawn
>>
>> 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
>>>
>>> --
>>> Sent via pdxpug mailing list (pdxpug(at)postgresql(dot)org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pdxpug
>>>
>>
>>
>

Here is a better link on postgis address normalization with actual code:
http://svn.osgeo.org/postgis/trunk/extras/tiger_geocoder/tiger_2010/normalize/

On Tue, Aug 2, 2011 at 7:46 AM, Shawn Helm <helm(dot)shawn(at)gmail(dot)com> wrote:

> I see a script to normalize addresses as part of the postgis tiger
> geocoder: http://www.postgis.org/documentation/manual-svn/Extras.html
>
> maybe this would be something to look at?
>
> Shawn
>
>
> On Tue, Aug 2, 2011 at 7:42 AM, Shawn Helm <helm(dot)shawn(at)gmail(dot)com> wrote:
>
>> Whoops, I didn't include the link:
>> http://resources.arcgis.com/gallery/file/geocoding/details?entryID=51EE55F8-1422-2418-7F8B-FD82DE958878
>>
>> On Tue, Aug 2, 2011 at 7:41 AM, Shawn Helm <helm(dot)shawn(at)gmail(dot)com> wrote:
>>
>>> Hi group,
>>>
>>> Here is a paper from ESRI on how they parse the elements of the address.
>>> It gives lists of which words and aliases go with which part of the address
>>> (like all the different way people spell street (st, str, street, strt,
>>> etc.). It's pretty specific to their software, but gives some background on
>>> how to think about the different parts of the address.
>>>
>>> Just one idea,
>>> Shawn
>>>
>>> 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
>>>>
>>>> --
>>>> Sent via pdxpug mailing list (pdxpug(at)postgresql(dot)org)
>>>> To make changes to your subscription:
>>>> http://www.postgresql.org/mailpref/pdxpug
>>>>
>>>
>>>
>>
>

On Tue, Aug 2, 2011 at 7:52 AM, Shawn Helm <helm(dot)shawn(at)gmail(dot)com> wrote:
> Here is a better link on postgis address normalization with actual code:
> http://svn.osgeo.org/postgis/trunk/extras/tiger_geocoder/tiger_2010/normalize/

If the data was loaded into a postgres db with postgis + geocoder
installed, you would have not just the address normalization routines
there to use almost seamlessly, but also lots and lots of other useful
things for string manipulation built into Postgres. Postgres and
Postgis install easily on a windows machine, though (obviously ;)) a
Unix variant is far superior.

(I am being flip about Unix, but big data pipelines are much easier to
script in a Unix environment, though trickier to get used to the
command line. Sorry for any preaching to the converted....)

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.

Thanks Eli! I worked with Dean a little bit back in my Marion County
days (94-99). 15+ years later I am still consistently impressed by
his accomplishments. My reasons for getting on this list arose from
my need to do the same thing that I did when working with Dean all
those years ago: merge data from two counties into our database while
each one stores data in different fields with different standards. My
existing processes use the ESRI model builder to do some geographic
stuff and an SQL script to do some data massaging. I need some expert
help to enhance the processing done within the SQL script as it has
outgrown my skills.

I am looking to replace my existing AGIS Server implementations this
fiscal year and I plan to aggressively research open source
alternatives to the ESRI+Business Partner approach. That research
just began in earnest at the UC this year and I hope to find more time
to spend soon.

On Tue, Aug 2, 2011 at 11:53 AM, Eli Adam <EAdam(at)co(dot)lincoln(dot)or(dot)us> wrote:
> Dan,
>
>  Welcome to the list.  If you like open source and GIS, there is a consortium of several Counties, Cities, and State Agencies that jointly develop and use some open source GIS tools.  You can contact Dean or I for more information.  You can see some of it in action here on Dean's site, http://apps.co.polk.or.us/geomoose2/index.html
>
> Best Regards, Eli
>
>
>
>
> --
>
> Eli L Adam
> GIS Analyst
> Lincoln County
> (541)574-1289
>
>
>>>> On 8/1/2011 at 9:45 PM, in message
> <CALGiQrCviBP9vXhjxRA6XrtvCYXtExgmjoustKnz_PUhB07vJg(at)mail(dot)gmail(dot)com>, 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
>
>

Two things (and apologies for top posting):

In case nobody has mentioned it yet, here is the PDX open source GIS
group with monthly meetings: http://groups.google.com/group/pdx-osgis

Also, if you are researching open source alternatives, you would
probably want to talk to someone at Tri Met about their experiences,
in case you don't know that already.

Forgive my redundancy, if it is such...

On Tue, Aug 2, 2011 at 1:00 PM, daniel stark <dan(dot)j(dot)stark(at)gmail(dot)com> wrote:
> Thanks Eli!  I worked with Dean a little bit back in my Marion County
> days (94-99).  15+ years later I am still consistently impressed by
> his accomplishments.  My reasons for getting on this list arose from
> my need to do the same thing that I did when working with Dean all
> those years ago: merge data from two counties into our database while
> each one stores data in different fields with different standards.  My
> existing processes use the ESRI model builder to do some geographic
> stuff and an SQL script to do some data massaging.  I need some expert
> help to enhance the processing done within the SQL script as it has
> outgrown my skills.
>
> I am looking to replace my existing AGIS Server implementations this
> fiscal year and I plan to aggressively research open source
> alternatives to the ESRI+Business Partner approach.  That research
> just began in earnest at the UC this year and I hope to find more time
> to spend soon.

Sorry, GIS cross-talk is for context only, don't mean to be off-topic.
The task at hand is crafting some SQL to tackle an issue with a
table.

Both your referrals are very strong and should not be overlooked.

On Tue, Aug 2, 2011 at 3:10 PM, Webb Sprague <webb(dot)sprague(at)gmail(dot)com> wrote:
> Two things (and apologies for top posting):
>
> In case nobody has mentioned it yet, here is the PDX open source GIS
> group with monthly meetings:  http://groups.google.com/group/pdx-osgis
>
> Also, if you are researching open source alternatives, you would
> probably want to talk to someone at Tri Met about their experiences,
> in case you don't know that already.
>
> Forgive my redundancy, if it is such...
>
> On Tue, Aug 2, 2011 at 1:00 PM, daniel stark <dan(dot)j(dot)stark(at)gmail(dot)com> wrote:
>> Thanks Eli!  I worked with Dean a little bit back in my Marion County
>> days (94-99).  15+ years later I am still consistently impressed by
>> his accomplishments.  My reasons for getting on this list arose from
>> my need to do the same thing that I did when working with Dean all
>> those years ago: merge data from two counties into our database while
>> each one stores data in different fields with different standards.  My
>> existing processes use the ESRI model builder to do some geographic
>> stuff and an SQL script to do some data massaging.  I need some expert
>> help to enhance the processing done within the SQL script as it has
>> outgrown my skills.
>>
>> I am looking to replace my existing AGIS Server implementations this
>> fiscal year and I plan to aggressively research open source
>> alternatives to the ESRI+Business Partner approach.  That research
>> just began in earnest at the UC this year and I hope to find more time
>> to spend soon.
>

No need to apologize, I just thought you might get some PostGIS type
help at the other group too. At some point you might want to make
your addresses into points and query how far they are from something
and then we are sort of back into GIS land.

W

On Tue, Aug 2, 2011 at 4:12 PM, daniel stark <dan(dot)j(dot)stark(at)gmail(dot)com> wrote:
> Sorry, GIS cross-talk is for context only, don't mean to be off-topic.
>  The task at hand is crafting some SQL to tackle an issue with a
> table.
>
> Both your referrals are very strong and should not be overlooked.
>
> On Tue, Aug 2, 2011 at 3:10 PM, Webb Sprague <webb(dot)sprague(at)gmail(dot)com> wrote:
>> Two things (and apologies for top posting):
>>
>> In case nobody has mentioned it yet, here is the PDX open source GIS
>> group with monthly meetings:  http://groups.google.com/group/pdx-osgis
>>
>> Also, if you are researching open source alternatives, you would
>> probably want to talk to someone at Tri Met about their experiences,
>> in case you don't know that already.
>>
>> Forgive my redundancy, if it is such...
>>
>> On Tue, Aug 2, 2011 at 1:00 PM, daniel stark <dan(dot)j(dot)stark(at)gmail(dot)com> wrote:
>>> Thanks Eli!  I worked with Dean a little bit back in my Marion County
>>> days (94-99).  15+ years later I am still consistently impressed by
>>> his accomplishments.  My reasons for getting on this list arose from
>>> my need to do the same thing that I did when working with Dean all
>>> those years ago: merge data from two counties into our database while
>>> each one stores data in different fields with different standards.  My
>>> existing processes use the ESRI model builder to do some geographic
>>> stuff and an SQL script to do some data massaging.  I need some expert
>>> help to enhance the processing done within the SQL script as it has
>>> outgrown my skills.
>>>
>>> I am looking to replace my existing AGIS Server implementations this
>>> fiscal year and I plan to aggressively research open source
>>> alternatives to the ESRI+Business Partner approach.  That research
>>> just began in earnest at the UC this year and I hope to find more time
>>> to spend soon.
>>
>

So -- when are we going to get together to hack on this?

The August meetup will be a performance presentation, assuming
that worked out. We could wait until September, but personally
I'd be happy to get together some other time.

On Thu, Aug 4, 2011 at 9:32 AM, Melissa Hollingsworth
<melissa(at)fastanimals(dot)com> wrote:
> So -- when are we going to get together to hack on this?
>
> The August meetup will be a performance presentation, assuming
> that worked out.  We could wait until September, but personally
> I'd be happy to get together some other time.

Still waiting to confirm on our speaker for August (which actually
won't be Josh, but he will speak in a coming slot.) But we should
plan for an extracurricular weeknight meeting.

Does a weeknight in Portland work for the City of Wilsonville folks?
Would City of Wilsonville people like to propose some date and times?
Keep in mind we are likely to get food provided if you're thinking
about what to do about dinner.

Regards,
Mark

On Thu, Aug 4, 2011 at 9:53 AM, Mark Wong <markwkm(at)gmail(dot)com> wrote:
> On Thu, Aug 4, 2011 at 9:32 AM, Melissa Hollingsworth
> <melissa(at)fastanimals(dot)com> wrote:
>> So -- when are we going to get together to hack on this?
>
> Does a weeknight in Portland work for the City of Wilsonville folks?
> Would City of Wilsonville people like to propose some date and times?
> Keep in mind we are likely to get food provided if you're thinking
> about what to do about dinner.

Just thought I'd ping again. :)

Regards,
Mark

On Mon, Aug 8, 2011 at 5:30 PM, Mark Wong <markwkm(at)gmail(dot)com> wrote:
>> On Thu, Aug 4, 2011 at 9:32 AM, Melissa Hollingsworth
>> <melissa(at)fastanimals(dot)com> wrote:
>>> So -- when are we going to get together to hack on this?
>>
>> Does a weeknight in Portland work for the City of Wilsonville folks?
>> Would City of Wilsonville people like to propose some date and times?
>> Keep in mind we are likely to get food provided if you're thinking
>> about what to do about dinner.
>
> Just thought I'd ping again. :)

If you guys go the hackathon route, I'll chip in for some pizza or beer.

Lindsey

On Tue, 09 Aug 2011 09:41:11 -0700, Lindsey Smith
<lindsey(dot)smith(at)gmail(dot)com> wrote:

> On Mon, Aug 8, 2011 at 5:30 PM, Mark Wong <markwkm(at)gmail(dot)com> wrote:
>>> On Thu, Aug 4, 2011 at 9:32 AM, Melissa Hollingsworth
>>> <melissa(at)fastanimals(dot)com> wrote:
>>>> So -- when are we going to get together to hack on this?
>>>
>>> Does a weeknight in Portland work for the City of Wilsonville folks?
>>> Would City of Wilsonville people like to propose some date and times?
>>> Keep in mind we are likely to get food provided if you're thinking
>>> about what to do about dinner.
>>
>> Just thought I'd ping again. :)
>
> If you guys go the hackathon route, I'll chip in for some pizza or beer.

Wilsonville should feed us, IMO. I'd also like T-shirts that say
something like "I saved Wilsonville using open source software --
ask me how." But just the food is also good.

When are we actually going to get together and do this? Is
anyone more or less organizing this?

--
Melissa Hollingsworth
melissa(at)fastanimals(dot)com
+1 512 619 1237

On Tue, Aug 9, 2011 at 11:06 AM, Melissa Hollingsworth
<melissa(at)fastanimals(dot)com> wrote:
> On Tue, 09 Aug 2011 09:41:11 -0700, Lindsey Smith <lindsey(dot)smith(at)gmail(dot)com>
> wrote:
>
>> On Mon, Aug 8, 2011 at 5:30 PM, Mark Wong <markwkm(at)gmail(dot)com> wrote:
>>>>
>>>> On Thu, Aug 4, 2011 at 9:32 AM, Melissa Hollingsworth
>>>> <melissa(at)fastanimals(dot)com> wrote:
>>>>>
>>>>> So -- when are we going to get together to hack on this?
>>>>
>>>> Does a weeknight in Portland work for the City of Wilsonville folks?
>>>> Would City of Wilsonville people like to propose some date and times?
>>>> Keep in mind we are likely to get food provided if you're thinking
>>>> about what to do about dinner.
>>>
>>> Just thought I'd ping again. :)
>>
>> If you guys go the hackathon route, I'll chip in for some pizza or beer.
>
> Wilsonville should feed us, IMO.  I'd also like T-shirts that say
> something like "I saved Wilsonville using open source software --
> ask me how."  But just the food is also good.
>
> When are we actually going to get together and do this?  Is
> anyone more or less organizing this?
>
>
> --
> Melissa Hollingsworth
> melissa(at)fastanimals(dot)com
> +1 512 619 1237

Was away from the list for a bit but glad to see such interest. The
City would be happy to contribute fuel for any endeavor -
unfortunately we cannot provide fermented food stuff but would be
happy to send enough other provisions to get you through a 'thon of
any sort. I don't think I need to take part - my skills lie squarely
in the the geospatial realm. I know just enough SQL to cut and paste
some updated code into my existing script(s).

I can provide food, a database and a goal. If this sounds like
something the group would like to pursue that would be great. In
addition, I'd be happy to work with an individual (or more) that would
like to take on piece-work like this on an ongoing basis for the City.
I'm looking for a resource for help when we need it as much as help
on this particular task.

Thanks for all the interest and let me know if you would like to proceed.

Oh yes- If the T-Shirts say something like "I saved Wilsonville *GIS*
using open source software -- ask me how", I think I might be able to
put something together!

On Tue, Aug 9, 2011 at 3:33 PM, daniel stark <dan(dot)j(dot)stark(at)gmail(dot)com> wrote:
> On Tue, Aug 9, 2011 at 11:06 AM, Melissa Hollingsworth
> <melissa(at)fastanimals(dot)com> wrote:
>> On Tue, 09 Aug 2011 09:41:11 -0700, Lindsey Smith <lindsey(dot)smith(at)gmail(dot)com>
>> wrote:
>>
>>> On Mon, Aug 8, 2011 at 5:30 PM, Mark Wong <markwkm(at)gmail(dot)com> wrote:
>>>>>
>>>>> On Thu, Aug 4, 2011 at 9:32 AM, Melissa Hollingsworth
>>>>> <melissa(at)fastanimals(dot)com> wrote:
>>>>>>
>>>>>> So -- when are we going to get together to hack on this?
>>>>>
>>>>> Does a weeknight in Portland work for the City of Wilsonville folks?
>>>>> Would City of Wilsonville people like to propose some date and times?
>>>>> Keep in mind we are likely to get food provided if you're thinking
>>>>> about what to do about dinner.
>>>>
>>>> Just thought I'd ping again. :)
>>>
>>> If you guys go the hackathon route, I'll chip in for some pizza or beer.
>>
>> Wilsonville should feed us, IMO.  I'd also like T-shirts that say
>> something like "I saved Wilsonville using open source software --
>> ask me how."  But just the food is also good.
>>
>> When are we actually going to get together and do this?  Is
>> anyone more or less organizing this?
>>
>>
>> --
>> Melissa Hollingsworth
>> melissa(at)fastanimals(dot)com
>> +1 512 619 1237
>
> Was away from the list for a bit but glad to see such interest. The
> City would be happy to contribute fuel for any endeavor -
> unfortunately we cannot provide fermented food stuff but would be
> happy to send enough other provisions to get you through a 'thon of
> any sort. I don't think I need to take part - my skills lie squarely
> in the the geospatial realm.  I know just enough SQL to cut and paste
> some updated code into my existing script(s).
>
> I can provide food, a database and a goal.  If this sounds like
> something the group would like to pursue that would be great. In
> addition, I'd be happy to work with an individual (or more) that would
> like to take on piece-work like this on an ongoing basis for the City.
>  I'm looking for a resource for help when we need it as much as help
> on this particular task.
>
> Thanks for all the interest and let me know if you would like to proceed.

Emma would like to host and can provide beverages. We just need to
pick a date (except the 18th, which is the pdxpug night). If that's
agreeable, then we can take a head count for how many people can show
up.

Regads,
Mark

On Tue, 09 Aug 2011 17:45:43 -0700, Mark Wong <markwkm(at)gmail(dot)com> wrote:

> Emma would like to host and can provide beverages. We just need to
> pick a date (except the 18th, which is the pdxpug night). If that's
> agreeable, then we can take a head count for how many people can show
> up.

How about a Saturday or Sunday? Then we could meet at a decent
hour without worry about work, and not have too big a problem
if we ran over.

--
Melissa Hollingsworth
melissa(at)fastanimals(dot)com
+1 512 619 1237