July 31, 2013 at 6:57 am
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?
July 31, 2013 at 7:24 am
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
July 31, 2013 at 8:16 am
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
July 31, 2013 at 8:50 am
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
July 31, 2013 at 10:11 am
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...:-)
July 31, 2013 at 10:14 am
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)
July 31, 2013 at 10:23 am
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
July 31, 2013 at 10:30 am
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.
[/quote]
This site is awesome....even i gave the wrong city name accidently..it came up with the correct one....
July 31, 2013 at 10:34 am
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.
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
July 31, 2013 at 2:02 pm
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.
July 31, 2013 at 2:12 pm
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:
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
July 31, 2013 at 2:17 pm
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.
August 1, 2013 at 4:34 am
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 ?
August 1, 2013 at 5:12 am
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
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply