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
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.
Using
LIKE
but results are in right order (most or complete matches should be on top is not achieved by me)FREETEXT
andCONTAINS
cannot be done as my SQL Server is on SQL Azure which doesn't support.Create 3 columns for
FROM
and 3 columns forTO
and populate data fromFROM
tofrom1,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?
FROM
andTO
asgeography
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