1

Bit confused on how to get it work better than what has been done now. I am new, apologies if I'm asking for too much.

I have trip details which have from and to locations stored in database from Google MAPS API.

One such record could be like this :

From : Bangalore, Karnataka, India To : New Jersey, United States

If someone search for

From  : Bangalore, Karnataka, India

or

From : Karnataka, India

or

From : Bangalore

or

From : Karnataka

or

From : India

or

From : Bangalore, India

Query should be success and same condition applies to TO column also. And if any one of FROM or TO column match is valid result.

How to achieve above solution with ordering better matches on top?

What I tried

  1. Exact match solution.(Either matching FROM or matching TO and MATCHING BOTH, if given) Not a right solution as it doesn't give all matches.

  2. Using LIKE but results are in right order (most or complete matches should be on top is not achieved by me)

  3. FREETEXT and CONTAINS cannot be done as my SQL Server is on SQL Azure which doesn't support.

  4. Create 3 columns for FROM and 3 columns for TO and populate data from FROM to from1,from2,from3 and query for

    (exact match) UNION this with (query for keyword LIKE in from1,from2,from3 columns)

    and do the same for TO column.

None of the solutions as I did now are convincing for me. Any better possible solutions?

  • Have you considered storing the FROM and TO as geography data types? then the user would enter a location and you'd select all points around their location, ordered by distance. Storing locations as free text is just asking for issues if you're trying to search free-text. – Mark Sinkinson Mar 12 '15 at 16:19
  • I didn't know about geography data type. Thanks for advise. Let me read more about it. – PC. Mar 12 '15 at 17:41
  • If you need more help understanding it then come back and update your question with how far you've got – Mark Sinkinson Mar 12 '15 at 17:48
  • I think that geography type is too much for me and considering that system is in running mode, migration will be too difficult. I am looking at alternative solutios now. Thanks anyway @MarkSinkinson – PC. Mar 16 '15 at 17:57
1

As @MarkSinkinson suggested you probably want to bite the bullet and move to the geography datatype. Sometimes a little short-term pain will save you a lifetime of misery.

  • Options 1 and 3 clearly can't work for the reasons you mentioned.
  • Option 2 doesn't work either unless you want a search for India to return results for Indiana too.
  • Option 4 shows the most promise assuming from1, from2, from3 -> city, region, country, and your input search terms follow the same pattern (city, region, country). If they must be a random list search terms then you may run into problems assigning weights for locations like Lima (city), Lima (region/province), Peru (country) because Lima matches two categories.

As I suggested option 4 will probably be the most fruitful, given the caveats I listed for option 4 and the recommendation to abandon text matches in favor of the geography type. The following query isn't sexy but it should be close to your original requirements.

declare @city nvarchar(50) = N'Bangalore', @region nvarchar(50) = N'Karnataka', @country nvarchar(50) = N'India';

;with [City_CTE] AS
(
    select
        <table PK> -- Unique match guaranteed
        , 1 as [present] -- Convenient summing later via the isnull function
    from <tablename>
    where
        [city] = @city
)
,  [Region_CTE] AS
(
    select
        <table PK>
        , 1 as [present]
    from <tablename>
    where
        [region] = @region
)
, [Country_CTE] AS
(
    select
        <table PK>
        , 1 as [present]
    from <tablename>
    where
        [Country] = @country
)
select
    *  -- Select the columns you need
    , isnull([ci].[present], 0) + isnull([re].[present], 0) + isnull([co].[present], 0) as [MatchWeight]
from <tablename> [t]
    left join [City_CTE] [ci] on [ci].<table PK> = [t].<table PK>
    left join [Region_CTE] [re] on [re].<table PK> = [t].<table PK>
    left join [Country_CTE] [co] on [co].<table PK> = [t].<table PK>
order by 
    [MatchWeight] desc

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.