Re: SQL Work Needed at the City of Wilsonville

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