LINQ query to find similar addresses

Question

I have a table that store address. This table has multiple fields for address components such as address number, street name, direction, suffix, prefix, city, state and zip. (Edit: this address table having addresses that previously added by users. I expect they are from same town, city, state, and country. So I did keep city, state, country and zip from them but not used for query.)

My application is to find an exact match of address from user entered address in database. If there is no exact match, then return similar addresses.

All addresses entered by user or stored in database normalized by Google Map API to avoid mismatching such as 1234 N Johnson St, 1234 North Johnson St or 1234 North John Street.

This is a query of exact match that I am using. Since both stored and entered address are normalized by Google Address API, I get an exact match result as I wanted.

var exactMatch = (from address in db.Addresses
                             where address.PrimaryAddressNumber == userInput.Number && address.Directional == userInput.Direction && address.Suffix == userInput.Suffix && address.StreetName  == userInput.StreetName
                             select new IncidentSite
                             {
                                 FullAddress = 'address components goes here'
                             });

However, if there is no exact match, then I'd like to give an option to users. As far as I thinking is that build multiple queries and then combine together. It works as I expected but the time takes too long.

I am doing like

    private IQueryable<IncidentSite> GetSimilarAddress(UserInput userInput)
            {
            var numberDirectionStreetname = (from address in db.Addresses
                                     where address.PrimaryAddressNumber == userInput.Number && address.Directional == userInput.Direction && address.StreetName  == userInput.StreetName
                                     select new IncidentSite
                                     {
                                         FullAddress = 'address components goes here'
                                     });

        var numberStreetname = (from address in db.Addresses
                                     where address.PrimaryAddressNumber == userInput.Number && address.StreetName  == userInput.StreetName
                                     select new IncidentSite
                                     {
                                         FullAddress = 'address components goes here'
                                     });

        var streetname = (from address in db.Addresses
                                     where address.StreetName  == userInput.StreetName
                                     select new IncidentSite
                                     {
                                         FullAddress = 'address components goes here'
                                     });

        var similarAddress = numberDirectionStreetname.Union(numberStreetname).Union(streetname);

return similarAddress;
    }

As you seeing at similarAdddress, it will run three queries from dbo.Addresses table but with different where statement, and then union all three results to build a one result.

I believe what I am doing is not a smarter way to find a similar addresses. Is there any good method that I could build a query that is much more simple and efficient?

EDIT: I think I was not clear enough why I had to have three different query, not one. The reason is to provide all possible results to user. To make it more detailed explanation, please see below.

If user search '1234 North Johnson St' and if there isn't exact match returned, following steps performed.

First, numberDirectionStreetname, select all address matches '1234 North Johnson'. So the result can be 1234 North Johnson + Boulevard/Street/Court/Way/Parkway/etc. I want it displayed top of the list since there are more matching address component exist than following ones.

Second, numberStreetname, select all address that matches '1234 Johnson'. So the result can be 1234 + South/North/East/West/etc + Johnson + Boulevard/Street/Court/Way/Parkway/etc

Third, streetname, select all address that matches 'Johnson'. So the result can be 9999 + South/North/East/West/etc + Johnson + Boulevard/Street/Court/Way/Parkway/etc

I'd like to do it with in one query if possible. This is part of my question also, not only make it perform faster but also make it simple. However, it it has to be three separate queries, how would you order them? And if my logic is not ideal, then how would you suggest?

Answer 1

Just a solution, not the exact code to resolve your problem. Get All the Address into a list by applying or condition of User inputs. Then from the filtered list find out the address which has maximum of count.

For Example:

List<Address> listOfAddress = new List<Address>{
            new Address(){Street="street 1", FlatNum="15", City="Auckland"},
            new Address(){Street="street 2", FlatNum="20", City="Napier"},
            new Address(){Street="street 1", FlatNum="15", City="Hamilton"}
        };



        string userInputStree = "street 1";
        string userInputFlatnum = "15";
        string userInputCity = "Whangrey";

        var addressList = (from address in listOfAddress

                             where address.Street  == userInputStree || address.City==userInputCity || address.FlatNum == userInputFlatnum
                             select address.FlatNum + ", " + address.Street + ", " + address.City

                           ).ToList();

        //from address List find the address which has maximum count

Answer 2

What do you mean by similar address? I presume that by similar address you mean similar addresses in the same state and country? In this case, you need to filter out your dataset using the country, state probably in the order of country first, state second, city third, and so on. You need to narrow down in this order to reduce the rows you are working with. After this is done, you can use your logic of finding out the similar address by street, number,etc.. Even here I would suggest using the top-down approach.

Your queries are taking time probably due to the amount of data the query has to work with. So filtering out rows is the way to go.

Also you can avoid sending muliptle queries and doing a union. Can you not doing everything at once using appropriate AND OR conditions in one single query.

I was meaning something like this. Use a combination of Inersect and Union to rewrite your logic.

   using System;
using System.Linq;
using System.Collections.Generic;

namespace mns
{
                    
public class Program
{
     private static readonly IEnumerable<Address> Addresses = new List<Address>
    {
           new Address{ Number = "1234", Direction = "South", Street = "Main" },
         new Address{ Number = "1234", Direction = "North", Street = "Broadway" },
         new Address{ Number = "1234", Direction = "North", Street = "Grand" },
      
      
        new Address{ Number = "1234", Direction = "South", Street = "Broadway" },
        new Address{ Number = "34", Direction = "East", Street = "Broadway" },
    };

    public static void Main()
    {
        const string streetToMatch = "Broadway";
        const string numberToMatch = "1234";
        const string directionToMatch = "South";
        var combinedAdrress = numberToMatch +" "+ streetToMatch + " "+ directionToMatch;

                    var rankedAddresses = from address in Addresses.Where(s=>numberToMatch== s.Number).Intersect(Addresses.Where(s=>directionToMatch==s.Direction)).Intersect(Addresses.Where(s=>streetToMatch == s.Street))
                        .Union(Addresses.Where(s=>numberToMatch== s.Number).Intersect(Addresses.Where(s=>streetToMatch == s.Street)))
                        .Union(Addresses.Where(s=>streetToMatch == s.Street))
              
                              select new
                              {
                                  Address = address.Number + " " + address.Street+ " "+ address.Direction
                                  
                              };
         Console.WriteLine("You are searching for: "+combinedAdrress);;

        foreach (var rankedAddress in rankedAddresses)
        {
            
            var address = rankedAddress.Address;
            Console.WriteLine(address);
        }
    }
}

public class Address
{
    public string Street { get; set; }
    public string Number { get; set; }
    public string Direction { get; set; }
}
}

You can change the input values to test .WHat I got was

You are searching for: 1234 Broadway South

1234 Broadway South 1234 Broadway North 34 Broadway East

fiddle : https://dotnetfiddle.net/Qpb5J1

Answer 3

Don't worry about making a direct comparison. Since you are wanting a list of close matches you just need to rank the results based on how many components match.

Here is a working example program that ranks if each element of an address matches, calculates an overall rank and orders based on the rank (the higher the rank, the better the match).

public class Program
{
    private static readonly IEnumerable<Address> Addresses = new List<Address>
    {
        new Address{ Number = "1000", Direction = "North", Street = "Grand" },
        new Address{ Number = "2000", Direction = "North", Street = "Broadway" },
        new Address{ Number = "1000", Direction = "South", Street = "Main" },
        new Address{ Number = "3000", Direction = "South", Street = "Grand" },
        new Address{ Number = "2000", Direction = "East", Street = "Broadway" },
    };

    static void Main()
    {
        const string streetToMatch = "Broadway";
        const string numberToMatch = "2000";
        const string directionToMatch = "South";

        var rankedAddresses = from address in Addresses
                              let streetRank = address.Street == streetToMatch ? 1 : 0
                              let numberRank = address.Number == numberToMatch ? 1 : 0
                              let directionRank = address.Direction == directionToMatch ? 1 : 0
                              let rank = streetRank + numberRank + directionRank
                              orderby rank descending
                              select new
                              {
                                  Address = address,
                                  Rank = rank
                              };

        foreach (var rankedAddress in rankedAddresses)
        {
            var rank = rankedAddress.Rank;
            var address = rankedAddress.Address;
            Console.WriteLine($"Rank: {rank} | Address: {address.Number} {address.Direction} {address.Street}");
        }
    }
}

public class Address
{
    public string Street { get; set; }
    public string Number { get; set; }
    public string Direction { get; set; }
}

Result

Rank: 2 | Address: 2000 North Broadway
Rank: 2 | Address: 2000 East Broadway
Rank: 1 | Address: 1000 South Main
Rank: 1 | Address: 3000 South Grand
Rank: 0 | Address: 1000 North Grand

Answer 4

Why don't you go with getting all the streetNames first, then using that as your main list to filter down from there?

var streetname = (from address in db.Addresses
                         where address.StreetName  == userInput.StreetName
                         select new IncidentSite
                         {
                             FullAddress = 'address components goes here'
                         });

var numberStreetname = (from address in streetname
                         where address.PrimaryAddressNumber == userInput.Number && address.StreetName  == userInput.StreetName
                         select new IncidentSite
                         {
                             FullAddress = 'address components goes here'
                         });

var numberDirectionStreetname = (from address in numberStreetname
                         where address.PrimaryAddressNumber == userInput.Number && address.Directional == userInput.Direction && address.StreetName  == userInput.StreetName
                         select new IncidentSite
                         {
                             FullAddress = 'address components goes here'
                         });