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.