Australian G-NAF Dataset HELP!

tryharder · June 19, 2019, 12:12pm

Hi,

I have downloaded the Australian G-NAF Dataset and was hoping that someone here was familiar with it and how to validate my customer file against G-NAF File, I thought I could build a matchkey based on address and postcode information but unfortunately this only gave me a 10% match rate when the G-NAF file is almost all of Australia 14.5 million records. I have created a table from the 30 odd tables which make up the G-NAF DATsets condensing down to the list below under G-NAF File listing

the fields I need to match against are as follows,

G-NAF File
number_first
street_name
street_type_code
locality_name
state_abbreviation
postcode

client file
address_line_1
address_line_2
address_line_3
state
postcode

I just need to run my client file against the G-NAF and append the matched information but if we could incorporate the wildcard to allow for certain mis-spelling but making sure the postcode filed is not wildcarded, so how do I run a query against each of the fields from my client file to the G-NAF file without merging fields?

hopefully someone has experience of this!!

many thanks in advance
Sean

yosiasz · June 19, 2019, 1:02pm

Please post a good ser of sample data from both source if possible? Unless its confidential or maybe obfuscated data

harishgg1 · June 19, 2019, 1:46pm

please please explain what this means?
`

your note

so how do I run a query against each of the fields from my client file to the G-NAF file without merging fields?

`

this may help

fuzzy-matching-using-t-sql

or this may help

[Matching records from two tables - the JOIN ... inner join left join right join etc

hope it helps

tryharder · June 19, 2019, 2:11pm

Hi,

please see samples below,

client file

Address Line 1	Suburb	State	Postcode
55 Park Avenue	ROSEVILLE	NSW	2069
47 KALIMNA STREET	CARRUM	VIC	3197
29 Walker St	REDFERN	NSW	2016
11 Balfour Pl	NOBLE PARK NORTH	VIC	3174
41 Cooper St.	ALEXANDRA	VIC	3714
3/10 Crown Avenue	MORDIALLOC	VIC	3195
18 Southern Rd	HEIDELBERG HEIGHTS	VIC	3081
6 Corsican St	FRANKSTON NORTH	VIC	3200
1 Huntingdale Cl	CRANBOURNE	VIC	3977
8 Carrington Cresent	LEUMEAH	NSW	2560
4a Oyster Ponit Road	BANORA POINT	NSW	2486
PO Box 13	OLINDA	VIC	3095
2 Acton Street	HURLSTONE PARK	NSW	2193
17 Graham Dr	LANDSBOROUGH	QLD	4550
17 Wyndella St	LAUNCESTON	TAS	7250
7 Greenwood Street	NEWCOMB	VIC	3219
PO Box 146	ERSKINEVILLE	NSW	2043
1 Rope St	DUNDAS VALLEY	NSW	2117
PO Box 139	BUDERIM	QLD	4556
31 Moorgate St	MACGREGOR	QLD	4109
25 Hallows St	GLEN WAVERLEY	VIC	3150
25 Hayward Rd	LAKE MACDONALD	QLD	4563
3 Narrabri Crt	GAVEN	QLD	4211
64 Shaftesbury St	TARRAGINDI	QLD	4121
PO Box 196	CAMMERAY	NSW	2062
8/2 Lindman Street	DROUIN	VIC	3818
PO Box 18	WAROONA	WA	6215

G_NAF File

NUMBER_FIRST	STREET_NAME	STREET_TYPE_CODE	LOCALITY_NAME	STATE_ABBREVIATION	POSTCODE
NULL	DEVONCOURT	ROAD	DRILLHAM	QLD	4424
2	KENTIA	STREET	CARSELDINE	QLD	4034
42	PLEASANT	DRIVE	REDBANK PLAINS	QLD	4301
56	PLEASANT	DRIVE	REDBANK PLAINS	QLD	4301
NULL	KALLIWA BEND	ROAD	MINGO	QLD	4625
3	RAILWAY	AVENUE	THE GEMFIELDS	QLD	4702
13	LAKE WEYBA	DRIVE	NOOSAVILLE	QLD	4566
23	CONDAMINE	STREET	SIPPY DOWNS	QLD	4556
28	CONDAMINE	STREET	SIPPY DOWNS	QLD	4556
31	TEEMANGUM	STREET	CURRUMBIN	QLD	4223
31	TEEMANGUM	STREET	CURRUMBIN	QLD	4223
159	STANLEY	STREET	TOWNSVILLE CITY	QLD	4810
3277	BEAUDESERT NERANG	ROAD	BIDDADDABA	QLD	4275
2	TANGO	STREET	CABOOLTURE	QLD	4510
102	QUEEN	STREET	SOUTHPORT	QLD	4215
1521	IPSWICH BOONAH	ROAD	PEAK CROSSING	QLD	4306
1531	IPSWICH BOONAH	ROAD	PEAK CROSSING	QLD	4306
NULL	WELLS STATION	ROAD	BIARRA	QLD	4313
27	NARACOORTE	PLACE	PARKINSON	QLD	4115
27	WAVELL	DRIVE	TINAROO	QLD	4872
NULL	BUNGEWORGORAI	LANE	DARGAL ROAD	QLD	4455
56	CHARLES	STREET	AITKENVALE	QLD	4814
406	MOUNTAIN VIEW	ROAD	MALENY	QLD	4552
13	LAKE WEYBA	DRIVE	NOOSAVILLE	QLD	4566
15	WAKEHAM	STREET	KALLANGUR	QLD	4503
3	WAKEHAM	STREET	KALLANGUR	QLD	4503
NULL	FLINDERS	HIGHWAY	HUGHENDEN	QLD	4821
NULL	KIRK RIVER	ROAD	RAVENSWOOD	QLD	4816
NULL	KIRK RIVER	ROAD	RAVENSWOOD	QLD	4816
14	BUTTERNUT	CIRCUIT	THORNLANDS	QLD	4164
13	SUTTOR	STREET	NEBO	QLD	4742
8	MOONAH	STREET	BOULIA	QLD	4829

appreciate any help with this.. you will notice on the client file some descriptions for Road or street have be abbreviated so need either soundex function or fuzzy match

thanks
Sean

tryharder · June 19, 2019, 2:14pm

I merged the fields into a new table from a view I created to make a matchkey but it didn't work, so need to run each element of the client address against each field in the G-NAF file using either soundex or fuzzy logic, please see new post!

thanks
sean

tryharder · June 19, 2019, 2:27pm

Sorry forgot to paste postcode for G-NAF file but is present

thanks
sean

harishgg1 · June 19, 2019, 3:06pm

One idea For Matching..

Checksum ..

Hope it helps

tryharder · June 19, 2019, 3:17pm

Will have a look and thanks one quick question I'm merging the G-NAF file number_first, street_name & street_type_code into a varchar but getting error message
"Error converting data type varchar to numeric." why would that be as Varchar is best for alphanumeric and my data has no decimal places?

cheer
sean

harishgg1 · June 19, 2019, 3:34pm

If any of 3 columns is numeric
Then throws that error

Solution
Cast ( columnnumeric as varchar)
+
Column123varchar

tryharder · June 19, 2019, 3:38pm

YES STREET_NUMER IS NUMERIC,

SO THE CODE GOES, Cast ([STREET_NAME] as varchar) what am I missing?

sorry been away from SQL over a year now so a little rusty, appreciate your help!!

harishgg1 · June 19, 2019, 3:49pm

Cast street number as varchar

Not
Street name

Hope I am right

tryharder · June 19, 2019, 3:52pm

thank but getting error message

"Msg 156, Level 15, State 1, Line 15
Incorrect syntax near the keyword 'as'."

Cast street_number as varchar

harishgg1 · June 19, 2019, 4:04pm

Cast ( streetnumber as varchar)

Looks like you are a lot rusty
,

tryharder · June 19, 2019, 4:12pm

tell me about it, start new job next week which is all SQL, hopefully will come flooding back once I get going!!

still not working, field is number_first, going made

/****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP (1000) [NUMBER_FIRST]
,[STREET_NAME]
,[STREET_TYPE_CODE]
,[LOCALITY_NAME]
,[STATE_ABBREVIATION]
,[POSTCODE]
,[ADDRESS_LINE_1]
,[JAMM_NUMBER]
FROM [AUS].[dbo].[AUS_MERGED_ADDRESS_FIELDS]

Cast (number_first as varchar)

Msg 102, Level 15, State 1, Line 12
Incorrect syntax near 'number_first'.

harishgg1 · June 19, 2019, 4:17pm

Select cast( column as varchar) ,
Column1 ,
Column2 ,
Column3
From table

Make sure the commas are there

Can we chat somewhere live
It will be very quick to help you

I am not sure I understand you

yosiasz · June 19, 2019, 4:43pm

ok help us help you by presenting the data not as embedded table but the following DDL and DML way

create table #client(AddressLine1 varchar(50), suburb varchar(50), 
State varchar(50), Postcode int)

insert into #client
select '55 Park Avenue', 'ROSEVILLE', 'NSW',  2069 union 
--etc
create table #gnaf(NUMBER_FIRST int , STREET_NAME varchar(50), 
STREET_TYPE_CODE varchar(50), LOCALITY_NAME  varchar(50), 
STATE_ABBREVIATIONvarchar(50))

insert into #gnaf
--same here as same as client

this way we do not need to do the above for all of your data set.

yosiasz · June 19, 2019, 4:56pm

What version of SQL Server will you be dealing with. 2017 has this NEAR function.
Also look into the Levenshtein function that some have used in the past.

https://www.sqlteam.com/forums/topic.asp?TOPIC_ID=66781

Also maybe TSQL is not your solution? Could you leverage other programming languages to do this for you? Python? c#