Storing latitude and longitude data in relational databases poses some challenges due to the spatial nature of the data, as traditional numeric data types don’t lend themselves well to their related calculations. In this article, we’ll explore some options for storing geographical coordinates (latitude/longitude) in MySQL including DECIMAL and POINT. We’ll also say why you shouldn’t use FLOAT.
Also find me here:
Baeldung Editor
Josh Cummings
Josh has been a software engineer for over 15 years building enterprise applications across multiple industries. He has long been passionate about application security and loves opportunities to mentor and to learn from others. When Josh isn't hacking away at code, he is either running, playing basketball, camping, or reading a Brandon Sanderson novel.
Here's what I've reviewed (so far):
Baeldung on SQL
- All
- SQL Tables (3)
- DML (2)
- SQL Queries (1)
- SQL Constraints (1)
- SQL Basics (1)
- Databases (1)
- DDL (1)
How to Interpret and Fix the MySQL Error 1093 – Can’t specify target table for update in FROM clause
Filed under SQL Queries, SQL Tables
When working with MySQL, we might run into “ERROR 1093 (HY000): You can’t specify target table ‘x’ for update in FROM clause”. This error typically occurs when trying to access the outer query table within a subquery in an UPDATE or a DELETE statement. We can’t do this, but there are a few ways that we can rework our query to still get what we need.
How to Fix Error 1064 When Using ALTER TABLE ADD CONSTRAINT in MySQL
Filed under DML, SQL Constraints
MySQL’s Error 1064 (42000) when using the ALTER TABLE … ADD CONSTRAINT statement could be tricky to fix. It provides very sketchy information regarding the error itself, only directing the user to check the syntax without telling what the syntax issue is.
How to Lock a Single Row in MySQL
Filed under DML
When using InnoDB in MySQL, we need to consider the rows that are locked by our queries. We may end up locking more rows than we need to, which unnecessarily blocks other queries trying to access the same rows. In this tutorial, we’ll look at how we can run SQL statements to lock only a single row.
How to Duplicate a Row in a Table Containing an Auto-Increment Field in MySQL
Filed under SQL Tables
The MySQL relational database uses its auto-increment feature to automatically generate a unique, ascending sequence of integers for a column when we add a new row of data. In this tutorial, we’ll learn how to handle duplicating rows and how MySQL prevents collisions in various arrangements.
How to Optimize All the Tables in MySQL
Filed under Databases
MySQL supports different types of storage engines, like InnoDB and MyISAM, each with their own propensity for fragmentation or other deterioration that can reduce storage allocation and performance. We can address these by optimizing these takes using a straighforward command or MySQL utility.
Getting the Size of a Table in MySQL
Filed under SQL Tables
MySQL supports different types of storage engines and these can take differing amounts of disk space to use when storing tables. Whether it is MyISAM or InnoDB, we can query MySQL to find out how much space each of our tables is taking up to better help us do capacity planning.