PostgreSQL PostGIS – Finding Nearby Locations by Zip Code or City

postgispostgresql

I am using PostgreSQL and PostGIS for this. I have downloaded dumps from Geoname, and built my tables from allCountries, alternateNames, and countryInfo. I've created my PostGIS geometry column, and populated it.

Now, what I'd like to do is given a zip code, I want to find all zip codes within 20 miles of the radius. Here's the structure of my geoname table:

CREATE TABLE public.geoname 
(
  geoname_id integer NOT NULL,
  name text,
  ascii_name text,
  alternate_names text,
  latitude double precision,
  longitude double precision,
  fclass text,
  fcode text,
  country text,
  cc2 text,
  admin1 text,
  admin2 text,
  admin3 text,
  admin4 text,
  population bigint,
  elevation integer,
  gtopo30 integer,
  timezone text,
  mod_date date,
  the_geom geometry(Point,4326),
  CONSTRAINT geoname_id_pk PRIMARY KEY (geoname_id)
)

CREATE INDEX idx_geoname_admin1
  ON public.geoname
  USING btree
  (admin1 COLLATE pg_catalog."default");

CREATE INDEX idx_geoname_ascii_name
  ON public.geoname
  USING btree
  (ascii_name COLLATE pg_catalog."default");

CREATE INDEX idx_geoname_the_geom
  ON public.geoname
  USING gist
  (the_geom);

Here's the query I am using (it never completes, takes forever) and the EXPLAIN output:

SELECT ascii_name
FROM geoname
WHERE
ST_distance(
ST_PointFromText('POINT(-122.67621 45.52345)', 4326),
the_geom) < 16093

"Seq Scan on geoname  (cost=0.00..2729028.01 rows=3179247 width=14)"
"  Filter:   (st_distance('0101000020E61000009ACE4E0647AB5EC0AC8BDB6800C34640'::geometry, the_geom) < 16093::double precision)"

I was thinking about using ST_DWithin, but from what I can tell it requires two locations.

This is more-or-less what I am trying to accomplish.

Best Answer

What you want is ST_DWithin. That will use an index.

SELECT ascii_name
FROM geoname
WHERE
ST_DWithin(
  ST_SetSRID(ST_MakePoint(-122.67621 45.52345), 4326),
  the_geom,
  16093
);

You probably want to use geography rather than geometry.

Related Question