Forum Stats

  • 3,839,935 Users
  • 2,262,549 Discussions
  • 7,901,101 Comments

Discussions

Improving the "ST_Distance_Sphere" spatial convenience function

plimster
plimster Member Posts: 3
edited Oct 3, 2017 2:00AM in MySQL Community Space

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.

plimster

Best Answer

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,785 Bronze Crown
    edited Sep 30, 2017 7:23AM Answer ✓

    @Dave Stokes-Oracle

    Depending on where in the UK, the person wanting a measure, the age of the person, the way the wind is blowing,...

    The UK still uses imperial system measures, along with metric.

    @plimster

    As this thread is still "open" it appears you are waiting for more?

    You yourself could enhance ST_Distance_Sphere by creating a wrapper for it, with a unit of measure parameter, and use the wrapper to do the conversion to what ever UOM was passed in.

    plimsterplimster

Answers

  • Dave Stokes-MySQL Community Team-Oracle
    Dave Stokes-MySQL Community Team-Oracle MySQL Community Manager TexasMember Posts: 374 Employee
    edited Aug 30, 2017 10:58AM

    Since only the US uses Imperial measures and multiplying to convert to miles is fairly simple, I doubt there will be any changes in that function.  And 5.7.5/6 is fairly old.  

    Dave Stokes

    MySQL Community Manager

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,785 Bronze Crown
    edited Sep 30, 2017 7:23AM Answer ✓

    @Dave Stokes-Oracle

    Depending on where in the UK, the person wanting a measure, the age of the person, the way the wind is blowing,...

    The UK still uses imperial system measures, along with metric.

    @plimster

    As this thread is still "open" it appears you are waiting for more?

    You yourself could enhance ST_Distance_Sphere by creating a wrapper for it, with a unit of measure parameter, and use the wrapper to do the conversion to what ever UOM was passed in.

    plimsterplimster