D
To sort this array you need a similarity index that tells you that Guarulhos is the "best response" of the three, something that has to be returned by the server that told you that these are the most similar results. .Using pg_trgmWith PostgreSQL I would use the module https://www.postgresql.org/docs/9.6/static/pgtrgm.html , which returns you such index and already allows you to sort with ORDER BY direct in the query, saving any extra work in PHP. For example, I created a "airports" table with the three records you showed:create table aeroportos (id int not null, iata text not null, full_name text not null);
insert into aeroportos values
(1059, 'BPA', 'Grumman, Bethpager, Bethpage, (BPA)'),
(3419, 'GUU', 'Bakki, Grundarfjorfur, Grundarfjordur, (GUU)'),
(8083, 'GRU', 'Guarulhos, São Paulo, (GRU)');
Here we can use the function word_similarity(), which returns the degree of similarity between a string and the word more similar to it in another string. The higher the value in the column grau (from 0 to 1), greater similarity between strings.
Note that I concatenate the columns iata and full_name to compare with "GRU" as one thing.select *, word_similarity(iata||full_name, 'GRU') as grau
from aeroportos
order by grau desc;
id | iata | full_name | grau
------+------+----------------------------------------------+----------
8083 | GRU | Guarulhos, São Paulo, (GRU) | 0.166667
3419 | GUU | Bakki, Grundarfjordur, Grundarfjordur, (GUU) | 0.125
1059 | BPA | Grumman, Bethpager, Bethpage, (BPA) | 0.047619
(3 rows)
Detailing a little more...As your table certainly has more than three records, it is interesting to also use similarity operators to filter only those who look enough with your search, otherwise every query will return the entire list of airports, mostly with similarity '0.0'. You can simply use LIMIT and cut into the amount of records that are most convenient to you, but you can also use other PostgreSQL features to filter by similarity, something that will give you much more performance for large volumes of data if added to the use of GIST indices.For this we can use the operator (which is also part of the pg_trgm package) <% in the clause WHERE. This operator returns true if the first parameter is a string that contains word similar to the second parameter, provided the word similarity degree is greater than the minimum limit specified by the option pg_trgm.word_similarity_threshold. As we have already noted for the previous query, the general similarity degree of our comparisons is generally low, since the "right response" for GRU has a score of only 0.16. Thus, we must lower the minimum limiter so that our WHERE do not return zero records. The smaller the pg_trgm.word_similarity_threshold, more records will be returned:set pg_trgm.word_similarity_threshold to 0.1;
select *, word_similarity(iata||full_name, 'GRU') as grau
from aeroportos
where (iata||full_name) <% 'GRU'
order by grau desc;
id | iata | full_name | grau
------+------+----------------------------------------------+----------
8083 | GRU | Guarulhos, São Paulo, (GRU) | 0.166667
3419 | GUU | Bakki, Grundarfjordur, Grundarfjordur, (GUU) | 0.125
(2 rows)
Set all this action course, in PHP the thing becomes simple. Just send it SET for the minimum similarity limit along with the query:$query = "set pg_trgm.word_similarity_threshold to 0.1; select *, word_similarity(iata||full_name, 'GRU') as grau from aeroportos where (iata||full_name) <% 'GRU' order by grau desc;";
$res = pg_query($conn, $query);
Or even if you don't want to pass the limiter every time, you can even fix it as the user's property of your application:alter user nunks set pg_trgm.word_similarity_threshold = 0.1;
select usename, useconfig from pg_user where usename = 'nunks';
usename | useconfig
---------+-----------------------------------------
nunks | {pg_trgm.word_similarity_threshold=0.1}
(1 row)
Installing the extension pg_trgmIf you do not have pg_trgm installed, use the command CREATE EXTENSION:> create extension pg_trgm;
CREATE EXTENSION
If the command fails you probably need to install the extensions package https://www.postgresql.org/docs/9.6/static/contrib.html , which can both be compiled and installed from the package manager of your choice. On Windows, the graphical installer gives you the option of which contrib modules to install.In addition to the function word_similarity() we use here, the extension pg_trgm offers other associated features such as https://stackoverflow.com/questions/11249635/finding-similar-strings-with-postgresql-quickly able to greatly streamline the response of similarity searches in large volumes of data, special operators and methods for fine-tuning of the similarity limit values.