How to separate the address?

  • Hi Friends,

    Create Table Adrtbl (Address varchar(80))

    Create table SpltAdr (Prefix char(2),StreetName varchar(50),StreetType varchar(10))

    GO

    insert into Adrtbl

    select 'WESTLAKE DR'

    union

    select 'W BLK BROWN DEER RD'

    union

    select 'JOHNSON ST'

    union

    select 'FREDONIA AVE'

    union

    select 'BANK ONE'

    union

    select 'N MARTIN LUTHER KIND DRIVE'

    GO

    insert into SpltAdr

    select '','WESTLAKE' , 'DR'

    union

    select 'W', 'BLK BROWN DEER', 'RD'

    union

    select '', 'JOHNSON','ST'

    union

    select '','FREDONIA', 'AVE'

    union

    select '','BANK ONE',''

    union

    select 'N', 'MARTIN LUTHER KIND DRIVE',''

    GO

    select * from Adrtbl -- Source format

    select * from SpltAdr -- OutPut format

    I am looking for splitting the address. I have provided the sample script in here. I tried my own way but i failed. I have some listed Prefix and StreetType..Let me give you some examples...

    StreetType - Aly, Ave, Bch, Blf, Blvd,Rd,Dr,St

    Prefix - E,W,N,S,NE,NW,SE,SW

    the prefix and street type must come under these categories....

    Gimme your suggesstions, friends....Any function or any T-SQL statement can do this?

  • My suggestion... don't do that. Unless you're building some sort of address reporting engine that counts the number of references to street vs. road in official address usages, you will just be making yourself insane in trying to break down that info. People are going to write; street, str, st, s, stret, strt, stre... You get the idea. And that's just street. Imagine the mangling that will occur with boulevard. What about if they say South 5th Street or 5th Street South? In short, don't.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • i've used the free portion of this web service, which does a best-lookup of an address and converts it into the official standardized address pieces...if it's an invalid address, it tells youso.

    I think the free service is limited to a thousand records or something like that before it starts blocking your IP and asking for $, and there's another free service, owned by some university, that i cannot seem to find the link for.

    http://www.yurisw.com/YAddress.aspx

    i fiddled with the above because they had a CLR , so i could do it all via TSQL, which was fun.

    it chopped up this default address "506 Fourth Avenue Unit 1, Asbury Prk NJ" on their web site to these elements:

    Address Line 1:506 4TH AVE APT 1

    Address Line 2:ASBURY PARK, NJ 07712-6086

    Street Number:506

    Street Predirection:

    Street Name:4TH

    Street Suffix:AVE

    Street Postdirection:

    Secondary Abbreviation:APT

    Secondary Number:1

    City:ASBURY PARK

    State:NJ

    ZIP:07712

    ZIP + 4:6086

    County:MONMOUTH

    State FP:34

    County FP:025

    Census Tract:8070.03

    Census Block:1015

    Latitude:40.223571

    Longitude:-74.005973

    Geocode Precision:Exact

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • With that, I suppose it would then be possible to successfully break things down. It still depends on accurate entry, but having a mechanism to validate the entry changes things pretty radically. I'm still not sure I would store it that way unless I had a need for that type of meta-data about the address though.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Grant Fritchey (7/31/2013)


    With that, I suppose it would then be possible to successfully break things down. It still depends on accurate entry, but having a mechanism to validate the entry changes things pretty radically. I'm still not sure I would store it that way unless I had a need for that type of meta-data about the address though.

    This is not going to be in any official pages...This is just for the client's references....I know we can't not make 100% validate address....but i want to split as far as we can...Even if some of them are wrong after separation..it will not be a problem...So just looking for some SQL statements...:-)

  • Hi Lowell,

    I can not use this web page as you say it would do only about 1000 addresses . i have more than 30000 addresses to do that...I am not looking for any validation...it is just for to fill up the address fields (Prefix, StreetName, Type)

  • prakashr.r7 (7/31/2013)


    Hi Lowell,

    I can not use this web page as you say it would do only about 1000 addresses . i have more than 30000 addresses to do that...I am not looking for any validation...it is just for to fill up the address fields (Prefix, StreetName, Type)

    here's the other site i was having trouble finding.

    to conserve their resources they require you to do batches of 2500 records or less, so you'd just loop through your records in small batches to comply with their free service restrictions.

    from there, you'd need to write some code to automate that, i'm afraid it starts getting more specific, and i don't have na example; in my case i uploaded an exported access database when i was testing before, i don't remember any web service / clr to do it.

    http://geoservices.tamu.edu/Services/AddressNormalization/

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Lowell (7/31/2013)


    i've used the free portion of this web service, which does a best-lookup of an address and converts it into the official standardized address pieces...if it's an invalid address, it tells youso.

    I think the free service is limited to a thousand records or something like that before it starts blocking your IP and asking for $, and there's another free service, owned by some university, that i cannot seem to find the link for.

    http://www.yurisw.com/YAddress.aspx

    [/quote]

    This site is awesome....even i gave the wrong city name accidently..it came up with the correct one....

  • prakashr.r7 (7/31/2013)


    Lowell (7/31/2013)


    i've used the free portion of this web service, which does a best-lookup of an address and converts it into the official standardized address pieces...if it's an invalid address, it tells youso.

    I think the free service is limited to a thousand records or something like that before it starts blocking your IP and asking for $, and there's another free service, owned by some university, that i cannot seem to find the link for.

    http://www.yurisw.com/YAddress.aspx

    This site is awesome....even i gave the wrong city name accidently..it came up with the correct one....

    agreed; i think i threw twenty something dollars at them once to pay for the service and parse the files i needed. the value of the data after cleansing is worth paying for it with my own lunch money, even if i didn't get reimbursed.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Lowell - Thank you! Having address validation is a very good tool to have. I can't tell you how many times we do it and the USPS charges you for each and every address we validate. With some of the volumes we process, this could save some real money. Thanks again.

  • Ed Wagner (7/31/2013)


    Lowell - Thank you! Having address validation is a very good tool to have. I can't tell you how many times we do it and the USPS charges you for each and every address we validate. With some of the volumes we process, this could save some real money. Thanks again.

    glad i could help a little, Ed!

    One of those services costs a little cash after you learn to use it, the Texas A&M one has one that's free as long as you give em credit, and keep your batch sizes down.

    which one catches your eye, might i ask?

    those two just happen to be the ones i know of and played with, i don't own them or sell their services or anything.

    if you want to build something that hit's Texas A&M's web service, the details were here:

    http://geoservices.tamu.edu/Services/AddressNormalization/WebService/DeterministicNormalizationWebService.aspx

    if i get some free time over the weekend, i'll play with making a CLR that uses that web service; sounds like fun to me.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • The Texas A & M one sounds like the best one for me. Someone else here used a service once, but I don't know which one. It was giving timeouts and other kinds of trouble when you hit it with any volume, but when it worked, it was accurate. I think it was a few hundred dollars a year, which sure beats the USPS service rates.

    I'm going to check out your link. I may be able to do it from a CLR or from SQL. I've gotten SQL to consume a web service before, but there were a bunch of hoops I had to jump through to get it to work and I wasn't too enthused about some of the security settings it required. I think I'm going to play with this one.

  • Hi Lowell,

    The Texas A& M is superb. This site has an option of Batch processing. It just made me curious, how many addresses can we do ?

  • prakashr.r7 (8/1/2013)


    Hi Lowell,

    The Texas A& M is superb. This site has an option of Batch processing. It just made me curious, how many addresses can we do ?

    max batch size of 2500, with no limit on the # of batches.

    I would recommend being nice and do a batch every half hour or an hour or something, until you finish running through all your data. i'd think running all your batches back-to-back might not be true to the meaning behind the free services offering; plowing through a million records back to back, even in the correct batch size, to me would be a little mean spirited if you tried to do them all thesame day.

    Make sure you add The acknowledgement/tribute link on a web site, that's a trivial addition to comply with the terms of the free usage guidelines.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply