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

From PostgreSQL to MSSQL

I love postgresql but my client request specifically for MSSQL.

1. PGSQL boolean column return ‘t’ and ‘f’ for true and false
MSSQL bit column return 1 and 0 for truen and false

2. PGSQL text column is special it can be use for any size of text
MSSQL varchar column to be able to use operator in where clause and varchar(max) for long string

3. PGSQL use pg_* functions
MSSQL use sqlsrv_* driver supplied by Microsoft and this driver can only be use in windows.

4. PGSQL pg_fetch_object return string for datetime column value
MSSQL sqlsrv_fetch_object return DateTime object for datetime column value.
I have to pass ‘ReturnDatesAsString’ option in the connection string to turn if off.

5. Paging in PGSQL is straight forward using LIMIT .. OFFSET
In MSSQL i have to use CTE function to enable to use my existing paging library.

6. PGSQL pg_affected_rows()
MSSQL sqlsrv_rows_affected()