Re: SQL Work Needed at the City of Wilsonville

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