db_fetch = fsphinx.DBFetch(db,
sql='''
select
g.id,
"geonames" as source_db,
g.name as place,
g.ascii_name as place_ascii,
g.alternate_names as place_alternate,
a2.name as county,
a2.name_ascii as county_ascii,
g.admin1_code as state_code,
a1.name as state,
g.country_code,
c.country,
g.population,
g.latitude*PI()/180 as lat,
g.longitude*PI()/180 as lon,
g.elevation,
f.kind,
f.feature_class,
f.feature_code
from geoname as g
left join admin1 as a1 on
g.country_code=a1.country_code and g.admin1_code=a1.admin1_code
left join admin2 as a2 on
g.country_code=a2.country_code and g.admin1_code=a2.admin1_code and g.admin2_code=a2.admin2_code
left join country_info as c on
g.country_code=c.iso
left join features as f on
g.feature_class=f.feature_class and g.feature_code=f.feature_code
where id in ($id)
order by field(id, $id)''')
# connect to searchd
cl.SetServer('localhost', 9312)
# matching mode (faceted client must be SPH_MATCH_EXTENDED2)
cl.SetMatchMode(sphinxapi.SPH_MATCH_EXTENDED2)
# sorting and possible custom sorting function
cl.SetSortMode(sphinxapi.SPH_SORT_EXTENDED, '@relevance desc')
## sql query to fetch the hits
db_fetch = fsphinx.DBFetch(db,
sql='''
select
id,
"zipcodesdotcom" as source_db,
ZipCode as zip,
City as place,
State as state,
State as state_code,
'United States' as country,
'us' as country_code,
Latitude*PI()/180 as lat,
Longitude*PI()/180 as lon
from zipcodesdotcom.zipcodes
where id in ($id)
order by field(id, $id)''')
cl.AttachDBFetch(db_fetch)
# by default only this index will be queried
cl.SetDefaultIndex('zipcodes_US')
# sorting and possible custom sorting function
cl.SetSortMode(sphinxapi.SPH_SORT_EXTENDED, '@relevance desc')
# some fields could matter more than others
cl.SetFieldWeights(dict(postal_code=40))
# sql query to fetch the hits
db_fetch = fsphinx.DBFetch(db,
sql='''
select
id,
"geonames.postals" as source_db,
postal_code,
place_name as place,
admin2 as county,
admin1 as state_code,
admin1_name as state,
p.iso as country_code,
latitude*PI()/180 as lat,
longitude*PI()/180 as lon,
c.country
from postals as p
left join country_info as c on
p.iso=c.iso
where id in ($id)
order by field(id, $id)''')
cl.AttachDBFetch(db_fetch)
# by default only this index will be queried
cl.SetDefaultIndex('geonames_postals')
import fsphinx
import sphinxapi
# creating a sphinx client to handle full text search
cl = simsearch.SimClient(fsphinx.FSphinxClient(), handler, max_terms=5)
# assuming searchd is running on 10001
cl.SetServer('localhost', 10001)
# telling fsphinx how to fetch the results
db = fsphinx.utils.database(dbn='mysql', **db_params)
cl.AttachDBFetch(fsphinx.DBFetch(db, sql='''
select imdb_id as id, title
from titles
where imdb_id in ($id)
order by field(imdb_id, $id)'''
))
# order the results solely by similarity using the log_score_attr
cl.SetSortMode(sphinxapi.SPH_SORT_EXPR, 'log_score_attr')
# enable us to search within fields
cl.SetMatchMode(sphinxapi.SPH_MATCH_EXTENDED2)
# searching for all animation movies re-ranked by similarity to "The Shawshank Redemption"
results = cl.Query('@genres animation @similar 111161')
# looking at the results with similarity search
print results