Investigate search of geonames data for supplied place name

[andystanton]

Google places API doesn't offer search for nearby towns. Will have to implement own version.

There is a free static data provider called geonames that offers raw place data:

http://download.geonames.org/export/dump/

[andystanton]

postgresql + postgis allows for storage of lat/longs and queries based on them

Guide to setting up a postgres table with lat/long: http://twiav-tt.blogspot.co.uk/2012/07/postgis-using-latitude-and-longitude-to.html

installing postgres & postgis

brew install postgresql
brew install postgis

setting up db

-- Database: "opt-loc"
CREATE DATABASE "opt-loc"
  WITH OWNER = stanta01
       ENCODING = 'UTF8'
       TABLESPACE = pg_default
       LC_COLLATE = 'en_GB.UTF-8'
       LC_CTYPE = 'en_GB.UTF-8'
       CONNECTION LIMIT = -1;

-- Enable PostGIS (includes raster)
CREATE EXTENSION postgis;
-- Enable Topology
CREATE EXTENSION postgis_topology;
-- fuzzy matching needed for Tiger
CREATE EXTENSION fuzzystrmatch;
-- Enable US Tiger Geocoder
CREATE EXTENSION postgis_tiger_geocoder;

ALTER DATABASE "opt-loc"
  SET search_path = "$user", public, tiger;

[andystanton]

table for geoname data

CREATE TABLE public.places_gb
(
  geoname_id integer NOT NULL,
  name character varying(200),
  ascii_name character varying(200),
  alternate_names character varying(8000),
  latitude double precision,
  longitutde double precision,
  feature_class character(1),
  feature_code character varying(10),
  country_code character(2),
  country_code_2 character varying(60),
  admin_code character varying(20),
  admin_code_2 character varying(80),
  admin_code_3 character varying(20),
  admin_code_4 character varying(20),
  population bigint,
  dem_raw integer,
  timezone character varying(40),
  modification_date date,
  CONSTRAINT pk_geoname_id PRIMARY KEY (geoname_id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.places_gb
  OWNER TO stanta01;

[andystanton]

importing geoname data

geoname file should be converted to utf 8 and pipe delimited. then can be imported using:

copy places_gb FROM '/path/to/GBpipe.txt' WITH DELIMITER '|' NULL AS ''

[andystanton]

converting data to lat long

ALTER TABLE places_gb ADD COLUMN lat_long geometry(POINT,4326);
UPDATE places_gb SET lat_long = ST_SetSRID(ST_MakePoint(latitude,longitude),4326);
CREATE INDEX idx_places_gb_lat_long ON places_gb USING GIST(lat_long);

[andystanton]

querying data within a given radius

SELECT name 
FROM places_gb
WHERE ST_Distance_Sphere(lat_long, ST_MakePoint(51.401409,-1.3231139)) <= 10000

gives

"Speen"
"Shaw"
"Newtown"
"Newbury"
"Greenham Common"
"Donnington Castle"
"Newbury & Community PCT"
"West Berkshire Community Hospital"
"Hilton Newbury Centre"
"Swallow Chequers"
"Chequers Hotel"
"Donnington Valley Htl And Spa"
"Newbury Railway Station"
"Newbury Racecourse Railway Station"
"Newbury"
"Shaw cum Donnington"
"Newtown"
"Greenham"
"Cabco"
"Bagnor"
"Donnington"
"Newbury Racecourse"