SqlArgs sqlArgs = new SqlArgs();
sqlArgs.append(" select ");
sqlArgs.append(" org.id, ");
sqlArgs.append(" org.unique_id as org_unique_id, ");
sqlArgs.append(" org.name, ");
sqlArgs.append(" org.address, ");
sqlArgs.append(" org.longitude, ");
sqlArgs.append(" org.latitude, ");
Double latitude = apiOrganizationSearchDTO.getLatitude();
Double longitude = apiOrganizationSearchDTO.getLongitude();
String str = "ROUND(6378.138*2*ASIN(SQRT(POW(SIN((%s*PI()/180-org.latitude*PI()/180)/2),2)" +
"+COS(%s*PI()/180)*COS(org.latitude*PI()/180)*POW(SIN((%s*PI()/180-org.longitude*PI()/180)/2),2)))*1000) AS distance ";
sqlArgs.append(String.format(str, latitude, latitude, longitude));
sqlArgs.append(" from ");
sqlArgs.append(" dict_organization org ");
sqlArgs.append(" order by distance asc ");
SELECT
id,
NAME,
unique_id,
longitude,
latitude,
ROUND(
6378.138 * 2 * ASIN(
SQRT(
POW( SIN( ( 39.916527 * PI( ) / 180- latitude * PI( ) / 180 ) / 2 ), 2 ) + COS( 39.916527 * PI( ) / 180 ) * COS( latitude * PI( ) / 180 ) * POW( SIN( ( 116.397128 * PI( ) / 180- longitude * PI( ) / 180 ) / 2 ), 2 )
)
) * 1000
) AS distance
FROM
(
SELECT
org.id,
org.NAME,
org.unique_id,
org.longitude,
org.latitude
FROM
dict_organization org
RIGHT JOIN tbl_activity_organization aorg ON org.id = aorg.org_id
) o