PostGIS Programming - PostGIS - page 420

5. Trytoquerythenew geonames tableinPostGIStoseeiftheprocesscom-
pleted correctly:
postgis_cookbook=# SELECT
ST_AsText(the_geom), name FROM
chp08.geonames ORDER BY name LIMIT 5;
st_astext | name
--------------------------+----------------------POINT(6.83333
45.03333) | AbbaziaPOINT(12.76667
42.95) | Abbazia di
SassovivoPOINT(14.49298 37.02504) |
AcatePOINT(15.10789 37.59765) | Aci
BonaccorsiPOINT(15.13459 37.55825) | Aci
Castello
(5 rows)
6. Now, create a PL/PostgreSQL function that will return the five closer place
names to the given point and their coordinates (the reverse-geocoding pro-
cess):
CREATE OR REPLACE FUNCTION
chp08.Get_Closest_PlaceNames(in_geom
geometry, 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.geonamesORDER BY the_geom <->
ST_Centroid(in_geom) LIMIT
num_results;END;
$$ LANGUAGE plpgsql;
7. Querythenewfunction.Youcanspecifythenumberofresultsyouwantby
passing the optional num_results input parameter:
postgis_cookbook=# SELECT * FROM
chp08.Get_Closest_PlaceNames(ST_PointFromText('POINT(13.5
42.19)', 4326), 10);