PostGIS: Find closest geometry to given point

Question

Say I have a number of geometries in my databases. I would like to create a function within PostgreSQL whereby I can pass in a lat/long and have it return the closest geometries by distance (linear, and by road) for a given radius.

I am a newbie to this, so any advice is appreciated.

I am running the following versions:

• PostgreSQL: 9.2
• PostGIS: 2.0

Here is the schema:

-- ----------------------------
--  Table structure for "cities-usa"
-- ----------------------------
DROP TABLE IF EXISTS "cities-usa";
CREATE TABLE "cities-usa" (
"gid" int4 NOT NULL DEFAULT nextval('"cities-usa_gid_seq"'::regclass),
"st_fips" varchar(4),
"sfips" varchar(2),
"county_fip" varchar(4),
"cfips" varchar(4),
"pl_fips" varchar(7),
"id" varchar(20),
"name" varchar(39),
"elevation" varchar(60),
"pop_1990" numeric,
"population" varchar(30),
"st" varchar(6),
"state" varchar(16),
"warngenlev" varchar(16),
"warngentyp" varchar(16),
"watch_warn" varchar(3),
"zwatch_war" float8,
"prog_disc" int4,
"zprog_disc" float8,
"comboflag" float8,
"land_water" varchar(16),
"recnum" float8,
"lon" float8,
"lat" float8,
"geom" "geometry"
)
WITH (OIDS=FALSE);
ALTER TABLE "cities-usa" OWNER TO "postgres";

-- ----------------------------
--  Primary key structure for table "cities-usa"
-- ----------------------------
ALTER TABLE "cities-usa" ADD CONSTRAINT "cities-usa_pkey" PRIMARY KEY ("gid") NOT DEFERRABLE INITIALLY IMMEDIATE;

Answer 1

The logic can be wrapped into a function, but I'd recommend using queries to test out the logic. The difficulties you will encounter are with linear distances (miles) combined with angular coordinates (degrees of latitude and longitude). Also the geometry type specified above does not have an SRID or geometry type. I'm guessing it should be geometry(Point,4326).

There are a few strategies you can try, such as using the geography type, which automagically uses metres for functions like ST_Distance, or ST_DWithin. The example below just uses ST_Distance_Sphere with a conversion from miles to metres to get you going. Or, if you need performance, you can try the <-> function for an indexed nearest neighbour search.

Here is something you can try:

CREATE OR REPLACE FUNCTION WhatAmINear(lat float8, lon float8,
                                       radius_mi float8, num int DEFAULT 10)
    RETURNS SETOF "cities-usa" AS
$body$
SELECT *
FROM "cities-usa"
WHERE ST_Distance_Sphere(geom, ST_MakePoint(lon, lat)) <= radius_mi * 1609.34
LIMIT num;
$body$
LANGUAGE sql VOLATILE;

And then:

SELECT WhatAmINear(44.9, -93.1, 100);