Setting up and mastering radius search in PostgreSQL (9.1)

Radius search in PostgreSQL may come in employing a light and/or a much more sophisticated version. This article discusses the light one, namely the cube and the earth distance extensions, most probably sufficient for the web user’s getting here and there requirements. earth distance, depending on cube, assumes the earth to be perfectly spherical, anyone demanding a higher accuracy level, especially for the mountainous parts, may take a look at the PostGIS project.

Although radius search, the light variety, will be up fast and performing well, there may be some mantrap around, for the ones who prefer to read documentation the easy way too. First of all, PostgreSQL: Documentation: 9.1: earthdistance indicates that the point-based earth distance calculation is hard-wired to statute miles in units. You may use this circumstance to your advantage, like datachomp did in Radius Queries in Postgres, as long as you know what you’re doing. Second to that, taking on the alternate cube-based earth distance calculation, the earth_box function, accepting a lat/long and a radius on input, may return locations farther than the actual radius given (documented alike). This is because earth_box, as the name implies, still handles a box geometry on the idealized sphere (and not some higher order circle surface). But more on that below.

Upgrading Postgresql from/to 9.1/9.3 along with Ubuntu 14.04

This is about getting Postgresql from versions 9.1 to 9.3 initiated by the upgrade of Ubuntu to 14.04 (Lts). Actually, successfully upgrading Ubuntu to trusty finds you starting with the old 9.1 still running as usual and the new 9.3 already installed and initialized (initdb). Since file locations for Postgresql on Debianlikes always feature the version with the pathnames, this not much of a problem. Notably at least, the old 9.1 keeps to the known default port of 5432 whereas the new 9.3 has been configured to the also known increment of 5433. To anticipate the results below, the databases (clusters) upgrade finished without any problems, a simple hurdle being the correct callout of pg_upgrade against pg_ctl, which is not officially installed on Debianlikes. Beware to install the postgres-xc-package as recommended when trying to tentatively lift off pg_ctl on the command line – you’re not going to need a bit of it. All you’ll need is to pass some dedicated pg_ctl-options within the option list of pg_upgrade, just an exception for Debianlikes, deviating from the otherwise still excellent Postgresql documentation (hats off). Docs that helped along the way read as follows: