postgis+geoname.sql

CREATE TABLE geoname (
  country TEXT,
  zip TEXT,
  place TEXT,
  state TEXT,
  state_abbr TEXT,
  county TEXT,
  county_code INTEGER,
  foo TEXT,
  bar TEXT,
  latitude FLOAT,
  longitude FLOAT,
  accuracy FLOAT
);

COPY geoname (
  country,
  zip,
  place,
  state,
  state_abbr,
  county,
  county_code,
  foo,
  bar,
  latitude,
  longitude,
  accuracy
) FROM './US/US.txt' NULL AS '';

ALTER TABLE geoname DROP COLUMN foo;
ALTER TABLE geoname DROP COLUMN bar;

CREATE EXTENSION postgis;
ALTER TABLE geoname ADD COLUMN geom GEOMETRY(Point, 4326);
UPDATE geoname SET geom = ST_PointFromText('POINT(' || longitude || ' ' || latitude || ')', 4326);
CREATE INDEX idx_geoname_geom ON public.geoname USING gist(geom);
ALTER TABLE geoname ADD COLUMN geog GEOGRAPHY(Point, 4326);
UPDATE geoname SET geog = Geography(ST_Transform(geom,4326));
CREATE INDEX idx_geoname_geog ON public.geoname USING gist(geog);

SELECT
    *
FROM
    geoname
WHERE
    ST_DWITHIN(
        (SELECT geog FROM geoname WHERE zip = '07436'),
        geog,
        3 * 1609.34 -- 1 miles == 1609.34 meters
    )
;