Merge lp:~martin-borho/ubuntu-geonames/wildcard-and-geopoint-searches into lp:ubuntu-geonames

[Martin Borho]

* possibility to search a location by its coordinates
* wildcard searches

Based on MP: https://code.launchpad.net/~twstd-dev/ubuntu-geonames/search-wildcard/+merge/234578

This MP should consist of a possibility to search a location by its coordinates:

http://146.185.188.87/?lat=40.714270&long=-74.005970

and the possibility to do wildcard-searches by name:

http://146.185.188.87/?query=Stuttga*

The asterisk has to be part of the given search term, therefore default behaviour is not changed:

http://146.185.188.87/?query=Stuttga (no wildcard search, no hits)

Unmerged revisions

37. By Martin Borho on 2015-02-28

making wildcard search optional, by adding * to the search term

36. By twstd on 2014-09-13

Align attributes so indexer would not throw error

35. By twstd on 2014-09-13

Add wildcard search

34. By twstd on 2014-09-03

Increase connection timeout

33. By twstd on 2014-09-03

Fix sphinx client isntance being shared between classes

32. By twstd on 2014-08-28

Reduce timeout value

31. By twstd on 2014-08-28

Add search by coordinates

=== modified file 'geoname-modpython.py'
--- geoname-modpython.py	2014-08-14 08:49:43 +0000
+++ geoname-modpython.py	2015-02-28 13:50:19 +0000
@@ -2,10 +2,12 @@
 from mod_python import apache
 import sphinxapi
 import psycopg2
+from math import pi
+from math import radians
 try:
-    from config import authstring
+	from config import authstring
 except ImportError:
-    authstring = 'dbname=geonames user=geouser password=geopw host=localhost'
+	authstring = 'dbname=geonames user=geouser password=geopw host=localhost'
 
 statement = """
 SELECT
@@ -44,48 +46,205 @@
 jsonheader = '['
 jsonfooter = ']'
 jsonentry = '{"name" : "%s", "admin1" : "%s", "admin2" : "%s", "country" : "%s", ' \
-            '"longitude" : "%F", "latitude" : "%F" , '"timezone" : "%s" }'
+		'"longitude" : "%F", "latitude" : "%F" , "timezone" : "%s" }'
+
+class RequestVariables:
+	"""
+	A very simple wrapper to handle incoming GET parameters.
+	"""
+	def __init__( self, request ):
+		self.__fields = util.FieldStorage( request )
+
+	def contains( self, variable_name ):
+		"""
+		Checks if parsed field list contains given parameter.
+		"""
+		return variable_name in self.__fields
+
+	def get( self, variable_name ):
+		"""
+		Returns given variable value.
+		"""
+		return self.__fields[ variable_name ] if self.contains( variable_name ) else ""
+
+class SphinxQuery:
+	"""
+	Implements a parent class to hold a query to a sphinx engine.
+	"""
+	__hostname = "localhost"
+	__port = 3312
+	query_keyword = ""
+	client = None
+	search_index = "geonames"
+
+	def __init__( self ):
+		self.client = sphinxapi.SphinxClient()
+		self.initialize_client()
+
+	def initialize_client( self ):
+		"""
+		Prepares sphinx client.
+		"""
+		self.client.SetServer( self.__hostname, self.__port )
+
+	def execute( self ):
+		try:
+			result = self.client.Query( self.query_keyword, self.search_index )
+			return result[ "matches" ] if  "matches" in result else []
+		except:
+			return []
+
+class TextQuery( SphinxQuery ):
+	"""
+	Implements a simple text based query.
+	"""
+	def __init__( self, query ):
+		SphinxQuery.__init__( self )
+		self.client.SetSortMode( sphinxapi.SPH_SORT_ATTR_DESC, 'population' )
+		self.query_keyword = "%s" % query
+
+
+	def execute( self ):
+		return ArrayQueryResult( SphinxQuery.execute( self ) )
+
+class CoordinatesBasedQuery( SphinxQuery ):
+	# default is not enough to search by coordinates
+	__connection_timeout = 10.00
+	# set to 5km
+	__radius = 5000.00
+
+	"""
+	Implements a search by latitude and longitude coordinates.
+	"""
+	def __init__( self, latitude, longitude ):
+		SphinxQuery.__init__( self )
+		self.search_index = "geonamescoordinates"
+		self.client.SetConnectTimeout( self.__connection_timeout )
+		self.client.SetLimits( 0, 1 )
+		self.client.SetGeoAnchor(
+			"latitude",
+			"longitude",
+			convert_to_radians( latitude ),
+			convert_to_radians( longitude ) )
+		self.client.SetFilterFloatRange( "@geodist", 0.0, self.__radius )
+		self.client.SetSortMode( sphinxapi.SPH_SORT_EXTENDED, "@geodist ASC" )
+
+
+	def execute( self ):
+		return SingleQueryResult( SphinxQuery.execute( self ) )
+
+class QueryResult:
+	__result = []
+
+	"""
+	Implements a structure for query results.
+	"""
+	def __init__( self, result ):
+		self.__result = result
+
+	def get( self ):
+		return self.__result
+
+class SingleQueryResult( QueryResult ):
+	"""
+	Represents result containing only one item.
+	"""
+	pass
+
+class ArrayQueryResult( QueryResult ):
+	"""
+	Represents result containing a list of items.
+	"""
+	pass
+
+
+def prepare_request( request ):
+	"""
+	Sets required headers to the given request.
+	"""
+	request.content_type = "application/json"
+	return request
+
+def convert_to_radians( degrees ):
+	parsed_value = 0.0
+
+	try:
+		parsed_value = float( degrees )
+	except:
+		pass
+
+	return radians( parsed_value )
+
+def parse_query_result( query_result ):
+	"""
+	Returns json ready string formed from the given result.
+	"""
+	result = query_result.get()
+
+	ret = []
+	if result:
+		connection = psycopg2.connect(authstring)
+		cursor = connection.cursor()
+		try:
+			# We need at least one value for the sql in operator
+			# and there are no locations with id 0
+			statement_ids = ['0']
+			altstatement_ids = ['0']
+			for x in result:
+				rawid = x['id']
+				idval = rawid / 10
+				idtype = rawid % 10
+				if idtype == 1:
+					statement_ids.append(str(idval))
+				else:
+					altstatement_ids.append(str(idval))
+
+			statement_ids_str = '(' + ','.join(statement_ids) + ')'
+			altstatement_ids_str = '(' + ','.join(altstatement_ids) + ')'
+			fullstatement = statement % (statement_ids_str,
+					altstatement_ids_str)
+			cursor.execute(fullstatement)
+			records = cursor.fetchall()
+			for record in records:
+				record = tuple([f or '' for f in record])
+				# Do not expose population column
+				ret.append(jsonentry % record[:-1])
+		finally:
+			cursor.close()
+			connection.close()
+
+
+	if isinstance( query_result, SingleQueryResult ):
+		return "".join( ret )
+
+	return ( jsonheader + ",".join( ret ) + jsonfooter )
+
+
+
+class QueryFactory:
+	"""
+	Returns a query object if known values were found.
+	"""
+	@staticmethod
+	def create( request ):
+		variable_list = RequestVariables( request )
+
+		if variable_list.contains( "query" ):
+			return TextQuery( variable_list.get( "query" ) )
+		elif variable_list.contains( "lat" ) and variable_list.contains( "long" ):
+			return CoordinatesBasedQuery( variable_list.get( "lat" ), variable_list.get( "long" ) )
+		else:
+			return None
+
 
 def handler(req):
-    fs = util.FieldStorage(req)
-    req.content_type = 'application/json'
-    if 'query' in fs:
-        client = sphinxapi.SphinxClient()
-        client.SetServer('localhost', 3312)
-        client.SetSortMode(sphinxapi.SPH_SORT_ATTR_DESC, 'population')
-        result = client.Query(fs['query'])
-        if result:
-            result = result['matches']
-        ret = []
-        if result:
-            connection = psycopg2.connect(authstring)
-            cursor = connection.cursor()
-            try:
-                # We need at least one value for the sql in operator
-                # and there are no locations with id 0
-                statement_ids = ['0']
-                altstatement_ids = ['0']
-                for x in result:
-                    rawid = x['id']
-                    idval = rawid / 10
-                    idtype = rawid % 10
-                    if idtype == 1:
-                        statement_ids.append(str(idval))
-                    else:
-                        altstatement_ids.append(str(idval))
-
-                statement_ids_str = '(' + ','.join(statement_ids) + ')'
-                altstatement_ids_str = '(' + ','.join(altstatement_ids) + ')'
-                fullstatement = statement % (statement_ids_str,
-                                             altstatement_ids_str)
-                cursor.execute(fullstatement)
-                records = cursor.fetchall()
-                for record in records:
-                    record = tuple([f or '' for f in record])
-                    # Do not expose population column
-                    ret.append(jsonentry % record[:-1])
-            finally:
-                cursor.close()
-                connection.close()
-        req.write(jsonheader + ', '.join(ret) + jsonfooter)
-    return apache.OK
+	query = QueryFactory.create( req )
+
+	if query:
+		result = query.execute()
+
+		req = prepare_request( req )
+		#req.write( str( result.get() ) )
+		req.write( parse_query_result( result ) )
+
+	return apache.OK

=== modified file 'sphinx.conf'
--- sphinx.conf	2012-11-21 12:35:46 +0000
+++ sphinx.conf	2015-02-28 13:50:19 +0000
@@ -5,8 +5,10 @@
 sql_user = geouser
 sql_pass = geopw
 sql_db = geonames
-sql_query = SELECT geoname.geonameid*10+1 AS id, geoname.name AS name, geoname.population AS population, geoname.geonameid AS geonameid FROM geoname WHERE geoname.fclass='P'
+sql_query = SELECT geoname.geonameid*10+1 AS id, geoname.name AS name, geoname.population AS population, geoname.geonameid AS geonameid, radians(geoname.latitude) AS latitude, radians(geoname.longitude) AS longitude FROM geoname WHERE geoname.fclass='P'
 sql_attr_uint = population
+sql_attr_float = latitude
+sql_attr_float = longitude
 }
 
 source altnamessrc
@@ -16,12 +18,15 @@
 sql_user = geouser
 sql_pass = geopw
 sql_db = geonames
-sql_query = SELECT alternatename.alternatenameId*10+2 AS id, alternatename.alternateName AS name, geoname.population AS population, geoname.geonameid AS geonameid FROM alternatename JOIN geoname on (geoname.geonameid = alternatename.geonameid AND geoname.name != alternatename.alternateName) WHERE (geoname.fclass='P' AND (LENGTH(alternatename.isoLanguage) < 4 OR alternatename.isoLanguage is null))
+sql_query = SELECT alternatename.alternatenameId*10+2 AS id, alternatename.alternateName AS name, geoname.population AS population, geoname.geonameid AS geonameid, radians(geoname.latitude) AS latitude, radians(geoname.longitude) AS longitude FROM alternatename JOIN geoname on (geoname.geonameid = alternatename.geonameid AND geoname.name != alternatename.alternateName) WHERE (geoname.fclass='P' AND (LENGTH(alternatename.isoLanguage) < 4 OR alternatename.isoLanguage is null))
 sql_attr_uint = population
+sql_attr_float = latitude
+sql_attr_float = longitude
 }
 
 index geonames
 {
+	enable_star		= 1
 	source			= geonamessrc
 	source			= altnamessrc
 	path			= /var/lib/sphinxsearch/data/geonames
@@ -29,6 +34,19 @@
 	morphology			= none
 	stopwords			=
 	min_word_len		= 2
+	min_prefix_len		= 3
+	min_infix_len		= 0
+        charset_type = utf-8
+	charset_table = U+FF10..U+FF19->0..9, 0..9, U+FF41..U+FF5A->a..z, U+FF21..U+FF3A->a..z,A..Z->a..z, a..z, U+0149, U+017F, U+0138, U+00DF, U+00FF, U+00C0..U+00D6->U+00E0..U+00F6,U+00E0..U+00F6, U+00D8..U+00DE->U+00F8..U+00FE, U+00F8..U+00FE, U+0100->U+0101, U+0101, U+0102->U+0103, U+0103, U+0104->U+0105, U+0105, U+0106->U+0107, U+0107, U+0108->U+0109, U+0109, U+010A->U+010B, U+010B, U+010C->U+010D, U+010D, U+010E->U+010F, U+010F, U+0110->U+0111, U+0111, U+0112->U+0113, U+0113, U+0114->U+0115, U+0115, U+0116->U+0117, U+0117, U+0118->U+0119, U+0119, U+011A->U+011B, U+011B, U+011C->U+011D, U+011D, U+011E->U+011F, U+011F, U+0130->U+0131, U+0131, U+0132->U+0133, U+0133, U+0134->U+0135, U+0135, U+0136->U+0137, U+0137, U+0139->U+013A, U+013A, U+013B->U+013C, U+013C, U+013D->U+013E, U+013E, U+013F->U+0140, U+0140, U+0141->U+0142, U+0142, U+0143->U+0144, U+0144, U+0145->U+0146, U+0146, U+0147->U+0148, U+0148, U+014A->U+014B, U+014B, U+014C->U+014D, U+014D, U+014E->U+014F, U+014F, U+0150->U+0151, U+0151, U+0152->U+0153, U+0153, U+0154->U+0155, U+0155, U+0156->U+0157, U+0157, U+0158->U+0159, U+0159, U+015A->U+015B, U+015B, U+015C->U+015D, U+015D, U+015E->U+015F, U+015F, U+0160->U+0161, U+0161, U+0162->U+0163, U+0163, U+0164->U+0165, U+0165, U+0166->U+0167, U+0167, U+0168->U+0169, U+0169, U+016A->U+016B, U+016B, U+016C->U+016D, U+016D, U+016E->U+016F, U+016F, U+0170->U+0171, U+0171, U+0172->U+0173, U+0173, U+0174->U+0175, U+0175, U+0176->U+0177, U+0177, U+0178->U+00FF, U+00FF, U+0179->U+017A, U+017A, U+017B->U+017C, U+017C, U+017D->U+017E, U+017E, U+4E00..U+9FFF, U+3000..U+30FF
+}
+index geonamescoordinates
+{
+	source			= geonamessrc
+	path			= /var/lib/sphinxsearch/data/geonamescoordinates
+	docinfo			= extern
+	morphology			= none
+	stopwords			=
+	min_word_len		= 2
 	min_prefix_len		= 0
 	min_infix_len		= 0
         charset_type = utf-8
@@ -39,7 +57,7 @@
 port = 3312
 log					= /var/log/sphinxsearch/searchd.log
 query_log			= /var/log/sphinxsearch/query.log
-read_timeout		= 5
+read_timeout		= 10
 max_children		= 30
 pid_file			= /var/run/searchd.pid
 max_matches			= 1000