DEV Community

Cover image for How to store coordinates in MySQL

Posted on • Updated on • Originally published at


How to store coordinates in MySQL

Many times we capture the geo-location of users and store in the database for different use cases.
I have seen that most of the developers use multiple fields to store the latitude and longitude separately like
Table_name(field1, field2, ..., latitude, longitude).

In this short article we will see an alternative way of storing coordinates in MySQL database using the spatial data types like POINT.

Create a table

  • Let us create a table named locations.
  • Field coordinates of data type POINT.
CREATE TABLE locations (
      coordinates POINT,
      PRIMARY KEY (id)
Enter fullscreen mode Exit fullscreen mode

Insertion of coordinates

  • To insert/update the field coordinates, we need to prepare a string like this 'POINT(latitude longitude)'.
  • Then we will use the in-built function called ST_GeomFromText to create a geometry in given SRID from WKT specification.
  • Pass the prepared string of points into ST_GeomFromText function.
     locations (coordinates) 
     (ST_GeomFromText('POINT(21.67890 91.54789)');
Enter fullscreen mode Exit fullscreen mode

Table will store and display the data in the following way

id        coordinates
1         POINT(21.67890 91.54789)
Enter fullscreen mode Exit fullscreen mode

Originally published on

Also, check this out to know how to capture user's geo-location in web browser.

Thank you for reading πŸ™

If you enjoyed this article or found it helpful, give it a thumbs-up πŸ‘

Feel free to connect πŸ‘‹

Twitter | Instagram | LinkedIn

If you like my work and want to support it, you can do it here. I will really appreciate it.

Top comments (0)

An Animated Guide to Node.js Event Loop

Node.js doesn’t stop from running other operations because of Libuv, a C++ library responsible for the event loop and asynchronously handling tasks such as network requests, DNS resolution, file system operations, data encryption, etc.

What happens under the hood when Node.js works on tasks such as database queries? We will explore it by following this piece of code step by step.