How to compare two tables

[Suchy]

Hello,
At first I'm sorry for my poor english :)
I have a problem with comparing two tables in MapInfo. To compare I
used this query:

Select * from table1, table2 where table1.streetname =
table2.streetname into Selection

It works ok, but when I have 50.000 records, this query compare for me
~30.000 records. Other, non-compared records is look like this:

table1:
streetname = "Tadeusza Kosciuszki"

table2:
streetname = "Kosciuszki Tadeusza"

It's the same street, but in table2 in streetname a surname is first
(words inversion), and the query that i use compares only identical
phrases.

I need a query, that compare theese streets :) I make this query, but
it's not working:

Select * from table1, table2 where table1.streetname Like
table2.streetname into Selection

Why I'm comparing this tables? In first table I have address points,
in second table are street names and post codes for theese streets. I
must assign a post code for every address points.

Any help is appreciated :)

Martin

[Bill Thoen]

Suchy wrote:
> Select * from table1, table2 where table1.streetname =
> table2.streetname into Selection
>
> It works ok, but when I have 50.000 records, this query compare for me
> ~30.000 records. Other, non-compared records is look like this:
>
> table1:
> streetname = "Tadeusza Kosciuszki"
>
> table2:
> streetname = "Kosciuszki Tadeusza"
>
> It's the same street, but in table2 in streetname a surname is first
> (words inversion), and the query that i use compares only identical
> phrases.
>
> I need a query, that compare theese streets :) I make this query, but
> it's not working:
>
> Select * from table1, table2 where table1.streetname Like
> table2.streetname into Selection
>

You've got a classic problem there, Martin, and there's no easy
solution. The way this is usually approached is to first standardize
your street names in both tables. You'll have to come up with ways to
identify when a street name doesn't meet your standards, and then the
second task is to correct it. I'd also recommend that use new columns
for normalizing your streets so you'll always have the original. Also,
you might want to do something non-standard, like sort all the big words
in alphabetical order to catch the "Kosciuszki Tadeusza" situations you
mentioned above.

MapInfo's MapMarker can be used to normalize addresses, and does a very
good job at this, but it'll cost you. If you want save some money and
spend some time instead, there are Open Source solutions. The best one
is SRC's Explorer Geocoder. It can work with MapBaaic too through a
DLL-based API.

I don't have the download info handy, but I'm sure Google knows. The
best search terms I can come up with are "geocoding" and "address
normalization".

I think it's actually less work cleaning your data first than trying to
SQL your way around all the many variations in databases that capture
words and names of people and places.

Good luck!

--
- Bill Thoen
GISnet - www.gisnet.com
303-786-9961