Database Reference
In-Depth Information
8. If you don't specify the
num_results
optional parameter, it will default to
five results:
postgis_cookbook=# SELECT * FROM
chp08.Get_Closest_PlaceNames(ST_PointFromText('POINT(13.5
42.19)', 4326));
9. Now create a PL/pgSQL function that will return a list of place names and
geometries containing a text search in their name field (geocoding process):
CREATE OR REPLACE FUNCTION
chp08.Find_PlaceNames(search_string
text,num_results int DEFAULT 5,OUT geom
geometry,OUT place_name character
varying)RETURNS SETOF RECORDAS
$$BEGINRETURN QUERYSELECT the_geom as
geom, name as place_nameFROM
chp08.geonamesWHERE name @@
to_tsquery(search_string)LIMIT
num_results;END;
$$ LANGUAGE plpgsql;
10. Query this second function to check if it is working properly:
postgis_cookbook=# SELECT * FROM
chp08.Find_PlaceNames('Rocca', 10);
How it works...
In this recipe, you wrote two PostgreSQL functions to perform geocoding and
reverse-geocoding. For both the functions, you defined a set of input and output
parameters;aftersomePL/PostgreSQLprocessing,youreturnedasetofrecordsto
the function client, given by executing a query.
As the input parameters, the
Get_Closest_PlaceNames
function accepts a
PostGISgeometryandanoptional
num_results
parameterthatissettoadefault
of five in case the function caller does not provide it. The output of this function is
SETOF RECORD
,whichisreturnedafterrunningaqueryinthefunctionbody(defined
