Calculate distance between two point on earth in PostgreSQL

Come across nice function by Merlin Moncure posted on grokbase

CREATE OR REPLACE FUNCTION calculate_distance(lat1 float8, lon1 float8, lat2 float8, lon2 float8)
RETURNS float8 AS
$$
SELECT ACOS(SIN(RADIANS($1)) * SIN(RADIANS($3)) + COS(RADIANS($1)) * COS(RADIANS($3)) * COS(RADIANS($4) - RADIANS($2))) * 6371;
$$ LANGUAGE 'sql' IMMUTABLE;

More details

http://grokbase.com/t/postgresql/pgsql-general/1069rn3ca0/calculating-distance-between-longitude-and-latitude
http://www.movable-type.co.uk/scripts/latlong.html

Advertisements

Migrating from MySQL to PostgreSQL

Encounter some issue along the way

1.MySQL DATE_SUB & DATE_ADD

(DATE_ADD(NOW(), INTERVAL 30 MINUTES)

(DATE_SUB(NOW(), INTERVAL 30 MINUTES)

(NOW() – INTERVAL ’30’ MINUTE)

(NOW() – INTERVAL ’30’ MINUTE) or

(NOW() – INTERVAL ’30 MINUTES’) or

(NOW() – ’30 MINUTES’::INTERVAL)

2. MySQL RADIANS

Need to cast to real

SELECT RADIANS(lat::real);

3. DISTINCT on json field (v9.4.5)


SELECT DISTINCT id, json_field FROM driver;

Throw could not identify an equality operator for type json error.
Converting the json field to jsonb solve the problem

Install mysql 5.6 in debian 8 jessie

The replication performance leap from MySQL 5.5 to MySQL 5.6 is more than 4x.

https://blogs.oracle.com/MySQL/entry/mysql_5_6_replication_performance

#wget http://dev.mysql.com/get/mysql-apt-config_0.3.5-1debian8_all.deb
#dpkg -i mysql-apt-config_0.3.5-1debian7_all.deb
#apt-get update
#apt-get install mysql-community-server

More info

Firefox 40+ slow right click in Debian Linux

Just installed latest firefox on debian linux streetch.
Right click on link popup very slow.

SOLVED: [blfs-support] Firefox – slow menu and right-click response

The right click menu in Firefox is now fast again. I disabled Pulseaudio which
tried to load ConsoleKit. This did not work sine I have unstalled ConsoleKit.

=== details ===

I found this:

/var/log/sys.log
Jan 25 09:13:47 lfs pulseaudio[2565]: [pulseaudio] module-console-kit.c:
GetSessionsForUnixUser() call failed:
org.freedesktop.DBus.Error.ServiceUnknown: The name org.freedesktop.ConsoleKit
was not provided by any .service files
Jan 25 09:13:47 lfs pulseaudio[2565]: [pulseaudio] module.c: Failed to load
module “module-console-kit” (argument: “”): initialization failed.
Jan 25 09:13:47 lfs pulseaudio[2565]: [pulseaudio] main.c: Module load failed.
Jan 25 09:13:47 lfs pulseaudio[2565]: [pulseaudio] main.c: Failed to
initialize daemon.
Jan 25 09:13:47 lfs pulseaudio[2562]: [pulseaudio] main.c: Daemon startup
failed.

I commented out the text below in /etc/pulse/default.pa
#.ifexists module-console-kit.so
#load-module module-console-kit
#.endif

I restarted and then the problem with the Firefox menus disappeared.

The root cause is probably that I uninstalled ConsoleKit. This was
intentional, but I failed to recognize the side effects.

Magnus