1

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.

3
  • 1
    You should know that your query will not use INDEX as it's inside a function, and your index is on a plain column value. Oct 7 '14 at 11:43
  • 1
    how many are there? try to run it again using ... SET enable_seqscan to OFF; an repost. let us see if the index is usable at all. which version of PG is it? Oct 28 '14 at 8:13
  • 1
    This was fairly common at an old job (I sent you junk mail, love me) We tied a postal code to the centerpoint and used that for our lat and long. Then used great circle distance to find all the postal codes within whatever distance our client specified. I'm guessing your final link there is doing the same thing
    – billinkc
    Oct 28 '14 at 14:32
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.