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
Create a table
- Let us create a table named
coordinatesof data type
CREATE TABLE locations ( id INT(11) NOT NULL AUTO_INCREMENT, coordinates POINT, PRIMARY KEY (id) );
Insertion of coordinates
- To insert/update the field
coordinates, we need to prepare a string like this
- Then we will use the in-built function called
ST_GeomFromTextto create a geometry in given SRID from WKT specification.
- Pass the prepared string of points into
INSERT INTO locations (coordinates) VALUES (ST_GeomFromText('POINT(21.67890 91.54789)');
Table will store and display the data in the following way
id coordinates 1 POINT(21.67890 91.54789)
Originally published on blog.bibekkakati.me
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)