SQL CLR

Question

SQL CLR

SSNEW

SSC Veteran

Points: 200
More actions
April 5, 2012 at 7:05 am

#266873

Hi
I am looking to get GEO spatial data for supplied postode's for this I want to use SQL cLR,I manged to get them by SSIS but i want to use SQL CLR.
I trying this with BING.
Can any one know anything about how to do this,which way is better,or is there any best resources to learn SQL CLR.
P.S.If any one know better way to get spatial data,looking for free resources for U.K.,please let me know
Thanks a lot

Viewing 3 posts - 1 through 2 (of 2 total)

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

Ant-Green SSC Guru Points: 113509 More actions · Answer 1

could take a look at the attachment, this is what I have used in the past to populate a postcode table and then do a lookup to it for the lats and longs to plot on maps etc

Lowell SSC Guru Points: 323518 More actions · Answer 2

small world, i'm playing with the exact same thing, and it is working beautifully.

YAddress has posted a nice CLR on codeplex for US addresses , and it hits a free webservice they are supplying;

i'm using it to validate a database of addresses right now;

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

http://yaddressudf.codeplex.com/

i'm using this code to do 5 addresses at a time every 30 seconds so i don't overload the service and get blocked out;

it returns a table value function with a LOT of nice data.

here's the column list from the CLR:

ERRORCODE
ERRORMESSAGE
ADDRESSLINE1
ADDRESSLINE2
NUMBER
PREDIR
STREET
SUFFIX
POSTDIR
SEC
SECNUMBER
CITY
STATE
ZIP
ZIP4
COUNTY
COUNTYFP
CENSUSTRACT
CENSUSBLOCK
LATITUDE
LONGITUDE
GEOCODEPRECISION

and my code example:

DECLARE @err varchar(100),

@i int

DECLARE @RESULTS INT

SET @RESULTS = 1

WHILE @RESULTS >= 1

BEGIN

INSERT INTO BASECONTACTS.dbo.GeoAddresses

select top 5

CT.ID,

CT.FirstName,

CT.LastName,

CT.Address1,

CT.City AS RawCity,

CT.State AS RawState,

CT.Zip AS RawZip,

CT.Phone,

CT.Email,

CT.DataSource,

CT.MailError,

CT.IsValidEmail,

myfn.*

from BASECONTACTS.dbo.RawContacts CT

LEFT OUTER JOIN BASECONTACTS.dbo.GeoAddresses GEO

ON CT.ID = GEO.id

cross apply dbo.ProcessAddress(CT.Address1,CT.City + ', ' + CT.State, NULL) myfn

where GEO.ID IS NULL

AND CT.Address1 <> ''

AND CT.City <> ''

and CT.State <> ''

SELECT @RESULTS = @@ROWCOUNT

--pause for 1 min 15 secs so we don't overload our friendly service.

SELECT @i = COUNT(*) FROM BASECONTACTS.dbo.GeoAddresses

SET @err = 'Progress So Far: Rows ' + convert(varchar(30),ISNULL(@i,1)) + ' completed.'

raiserror (@err,0,1) with nowait

waitfor delay '000:00:30'

--SET @RESULTS=0

END

SELECT @i = COUNT(*) FROM BASECONTACTS.dbo.GeoAddresses

SET @err = 'Progress So Far: Rows ' + convert(varchar(30),ISNULL(@i,1)) + ' completed.'

raiserror (@err,0,1) with nowait

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!