Skip to Main Content

MySQL Database

Improving the "ST_Distance_Sphere" spatial convenience function

plimsterAug 29 2017 — edited Oct 3 2017

Today I have decided to save time by performing spatial convenience functions directly in mysql. In the below example I have used the ST_Distance_Sphere function to calculate distances between Cardiff Airport and some locations around Cardiff (see demo below). Previously I would have output the data and performed the spatial calculations in another application.

Does anyone know if there are any plans to improve the ST_Distance_Sphere function further by having a modifier to change the metres to eg: kilometres or miles? 

It would be a small but great feature or have?

Enter password: ********

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 11

Server version: 5.7.19-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use gprx;

Database changed

mysql> show tables;

+----------------+

| Tables_in_gprx |

+----------------+

| address        |

| bnf            |

| bnfchem        |

| geodb          |

| gpdata         |

| practices      |

+----------------+

6 rows in set (0.00 sec)

mysql> describe address;

+------------+-------------+------+-----+---------+-------+

| Field      | Type        | Null | Key | Default | Extra |

+------------+-------------+------+-----+---------+-------+

| period     | varchar(6)  | YES  |     | NULL    |       |

| practiceid | varchar(60) | YES  |     | NULL    |       |

| locality   | varchar(60) | YES  |     | NULL    |       |

| street     | varchar(60) | YES  |     | NULL    |       |

| area       | varchar(60) | YES  |     | NULL    |       |

| posttown   | varchar(60) | YES  |     | NULL    |       |

| county     | varchar(60) | YES  |     | NULL    |       |

| postcode   | varchar(60) | YES  |     | NULL    |       |

+------------+-------------+------+-----+---------+-------+

8 rows in set (0.06 sec)

mysql> describe geodb;

+----------+------------+------+-----+---------+-------+

| Field    | Type       | Null | Key | Default | Extra |

+----------+------------+------+-----+---------+-------+

| id       | int(11)    | YES  |     | NULL    |       |

| postcode | varchar(8) | YES  |     | NULL    |       |

| lat      | double     | YES  |     | NULL    |       |

| lon      | double     | YES  |     | NULL    |       |

+----------+------------+------+-----+---------+-------+

4 rows in set (0.00 sec)

mysql> -- CWL Cardiff Airport

mysql>

mysql> SET @CWL = POINT(-3.3405464, 51.398687);

Query OK, 0 rows affected (0.00 sec)

mysql>

mysql> -- Distance between practices in cardiff and the airport ranked nearest

mysql>

mysql> select ST_Distance_Sphere(@CWL, point(geodb.lon, geodb.lat))*0.001 as KM_

from_cwl, ST_Distance_Sphere(@CWL, point(geodb.lon, geodb.lat))*0.00062137119223

733 as miles_from_cwl, address.street, address.postcode from geodb,address where

address.postcode=geodb.postcode and address.posttown like '%Cardiff%' order by

miles_from_cwl;

+--------------------+--------------------+------------------------------+------

----+

| KM_from_cwl        | miles_from_cwl     | street                       | postc

ode |

+--------------------+--------------------+------------------------------+------

----+

|  9.768012268142643 | 6.0695614288446595 | WOODLANDS MEDICAL CENTRE     | CF5 4

RG  |

| 10.259034274860998 |  6.374668358574011 | GREENMOUNT SURGERY           | CF5 5

LQ  |

| 10.431578616547728 |  6.481882441881699 | WESTWAY SURGERY              | CF5 4

LJ  |

| 10.573105365990731 |  6.569823086916571 | THE CAERAU LANE SURGERY      | CF5 5

HJ  |

| 11.998867905436633 |  7.455750855899395 | ELY BRIDGE SURGERY           | CF5 4

AD  |

| 12.535532060382186 |  7.789218501688953 | MEDDYGFA LANSDOWNE SURGERY   | CF11

8DG |

| 12.974376309880297 |  8.061903676206091 | BYWAYS                       | CF5 2

NH  |

| 13.319399141494708 |  8.276290924435436 | CARDIFF BAY SURGERY          | CF11

7DJ |

| 13.319399141494708 |  8.276290924435436 | GRANGETOWN HEALTH CENTRE     | CF11

7DJ |

| 13.327869332291968 |  8.281554056989608 | 4 CORPORATION ROAD           | CF11

7AT |

| 13.360435879155501 |  8.301789971041254 | CLARE ROAD MEDICAL CENTRE    | CF11

6RW |

| 13.508271915819423 |  8.393651025398757 | SALTMEAD MEDICAL CENTRE      | CF11

6QQ |

|  13.51120605393531 |  8.395474214298014 | LLANDAFF SURGERY             | CF5 2

DY  |

| 13.545160503434188 |  8.416572531064896 | 6 CLARE STREET               | CF11

6SB |

| 13.567545218654871 |  8.430481748249463 | RIVERSIDE HEALTH CENTRE      | CF11

9SH |

| 13.567545218654871 |  8.430481748249463 | RIVERSIDE HEALTH CENTRE      | CF11

9SH |

| 13.567545218654871 |  8.430481748249463 | TAFF RIVERSIDE PRACTICE      | CF11

9SH |

|  13.60301842315694 |  8.452523775623392 | DANESCOURT SURGERY           | CF5 2

SH  |

|  13.60509822979375 |   8.45381610755293 | MEDDYGFA CANNA SURGERY       | CF11

9EE |

| 13.782442514135644 |  8.564012736950929 | 180 KINGS ROAD               | CF11

9DG |

| 14.019633531676053 |  8.711396402307999 | PRIMECARE                    | CF11

9LJ |

| 14.019633531676053 |  8.711396402307999 | PRIMECARE                    | CF11

9LJ |

| 14.019633531676053 |  8.711396402307999 | PRIMECARE                    | CF11

9LJ |

| 14.019633531676053 |  8.711396402307999 | PRIMECARE                    | CF11

9LJ |

|  14.13956842565319 |   8.78592049036943 | RADYR MEDICAL CENTRE         | CF15

8DZ |

| 14.215973508152349 |  8.833396407574924 | LLANDAFF NORTH MED CENTRE    | CF14

2FD |

|  14.87923032644187 |  9.245525087515022 | CHURCHILL HOUSE              | CF10

2TW |

|  14.87923032644187 |  9.245525087515022 | CHURCHILL HOUSE              | CF10

2TW |

| 15.067220172638297 |  9.362336562374608 | BISHOPS ROAD MEDICAL CENTRE  | CF14

1LT |

| 15.140928328333572 |  9.408136686956595 | NORTH ROAD MEDICAL PRACTICE  | CF14

3XQ |

| 15.324883129788851 |  9.522440901254644 | CATHAYS SURGERY              | CF24

4HU |

| 15.370078468411949 |  9.550523982698447 | MEDDYGFA LLWYNCELYN PRACTICE | CF14

7EZ |

| 15.370078468411949 |  9.550523982698447 | WHITCHURCH VILLAGE PRACTICE  | CF14

7EZ |

|  15.57191230205559 |  9.675937712543428 | WHITCHURCH ROAD SURGERY      | CF14

3NB |

| 15.606268447436015 |  9.697285631559142 | THE CITY SURGERY             | CF24

3WD |

| 15.637766661700777 |  9.716857714510184 | MEDDYGFA ALBANY SURGERY      | CF24

3JD |

|  15.66308654382136 |  9.732590759850758 | CARDIFF ROYAL INFIRMARY      | CF24

0SZ |

| 15.998860394875347 |  9.941230958002294 | CLIFTON SURGERY              | CF24

1AG |

|  16.02409618283855 |  9.956911749656038 | CRWYS MEDICAL CENTRE         | CF14

3QX |

| 16.049412974864836 |  9.972642874901037 | ROATHWELL SURGERY            | CF24

1YT |

| 16.310503033113125 | 10.134876715676091 | BIRCHGROVE SURGERY           | CF14

4QJ |

|   16.3663897869258 | 10.169603134522946 | THE PENYLAN SURGERY          | CF23

5SY |

| 16.542982718651345 | 10.279332895049931 | ROATH HOUSE SURGERY          | CF23

5RH |

|   16.7979832901986 | 10.437782904213453 | ST. ISAN ROAD SURGERY        | CF14

4UU |

| 17.885070487664724 | 11.113267572168914 | LLANISHEN COURT SURGERY      | CF14

5YU |

| 18.269820319276942 | 11.352340033750911 | LLANEDEYRN HEALTH CENTRE     | CF23

9PN |

| 18.473789419816907 | 11.479080556933004 | NORTH CARDIFF MEDICAL CENTRE | CF14

9BB |

|  18.85707833618618 | 11.717245247868734 | DAINTREE SURGERY             | CF3 3

XE  |

|  19.45813814205661 | 12.090726496048381 | ST. DAVIDS MEDICAL CENTRE    | CF23

7SD |

|  19.70344960767732 | 12.243155973910609 | CYNCOED MEDICAL PRACTICE     | CF23

8SQ |

| 20.557998864562677 | 12.774148264486987 | LLANRUMNEY MEDICAL GROUP     | CF3 5

NP  |

|  21.73183659869933 | 13.503537216840646 | BRYNDERWEN SURGERY           | CF3 0

EF  |

| 22.227596834095884 | 13.811588345372861 | WILLOWBROOK SURGERY          | CF3 0

SH  |

|  32.75130313803725 | 20.350716278208413 | BARGOED HALL                 | CF81

8NY |

+--------------------+--------------------+------------------------------+------

----+

54 rows in set (30.97 sec)

mysql>

/* The End */

Note:  I decided not to use the Distance() function that was added in MySQL 5.7.5 because it is deprecated as of MySQL 5.7.6.

This post has been answered by Gaz in Oz on Sep 30 2017
Jump to Answer
Comments
Post Details
Added on Aug 29 2017
2 comments
299 views