If you want to get X and Y coordinates of a POINT geometry, you can use ST_X(g) and ST_Y(g) built-in functions.

Let’s create a locations_flat database table and populate it with some sample geometry POINT values.

CREATE TABLE IF NOT EXISTS `locations_flat`(

`id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

`name` VARCHAR(100),

`position` POINT NOT NULL SRID 0

); SHOW COLUMNS FROM `locations_flat`;

You can use POINT , POLYGON , LINESTRING and other geometrical types (mentioned here) to explicitly state the data type. MySQL also provides GEOMETRY data type if your column contains variable geometry types. SRID is optional, if omitted, the column can contain geometries of multiple SRIDs. But this is not recommended as this will not utilize the INDEX and query performance on large data set will be slower, as we will see later.

To insert some sample data, use below query

INSERT INTO `locations_flat`(`name`, `position`)

VALUES

( 'point_1', ST_GeomFromText( 'POINT( 1 1 )', 0 ) ),

( 'point_2', ST_GeomFromText( 'POINT( 2 2 )', 0 ) ),

( 'point_3', ST_GeomFromText( 'POINT( 3 3 )', 0 ) );

But since we need a large data set, set take help of JavaScript. Inside your JavaScript console, execute below code which will print above insert statement but with 1000 entry points.

var fn = `ST_GeomFromText`;

var values = new Array( 1000 ).fill(null).map( ( val, index ) => {

var id = index + 1;

return `('point_${ id }', ${fn}('POINT(${ id } ${ id + 1 })'))`;

} ).join(',

\t'); console.log(`

INSERT INTO \`locations_flat\`(\`name\`, \`position\`)

VALUES

${ values };

`);

Let’s see a few data points in our table with a quick SELECT query.

SELECT

*, ST_ASTEXT(`position`) AS `pos_wkt`

FROM

`locations_flat`

LIMIT 10;

It is perfectly legal to pass a column name to ST_ built-in function as long as its input parameters are legal. Also, all ST_ function names are case-insensitive. Hence, you can also use ST_AsText as ST_ASTEXT or st_asText .

Let’s consider these points as places and our user is located at the origin(0,0). If we want to search for places which are within 100 unit distance from him (let’s say the user is male), we need to calculate the distance of each place and compare if it is less than or equal to 100. We have used distance function before, which is ST_Distance . We are going to use this function in WHERE clause in our MySQL query.

SET @user_location = ST_GeomFromText( 'POINT(0 0)' ); SELECT

*,

ST_AsText(`position`) AS `pos_wkt`,

ST_Distance(`position`, @user_location) AS `distance`

FROM

`locations_flat`

WHERE ST_Distance(`position`, @user_location) <= 100;

From the above query, we created a sample origin geometry POINT at (0,0) and used a reference point to calculate the distance of each point. WHERE clause filtered the results based on distance and we got around 70 results.

But we can also achieve this using ST_Buffer(g, d) built-in function (documented here) to create geometry (as stated in the documentation) that represents all points whose distance from the geometry value g is less than or equal to a distance of d . In nutshell, it creates a circular surface area.

⚠️ It should be noted that ST_Buffer can only create geometry for SRID 0 (plane surface). Since, g in our case has SRID = 0, hence this works for us. But a circular area on a spherical surface is rather complex, MySQL just gives up there.

Using this geometry, we can check whether a geometry (point) lies inside it or not. This can be done using ST_Contains(g1, g2) function which returns 1 if the geometry g1 contains g2 , else 0. Otherwise, we can use ST_Within(g1, g2) function which returns 1 if g1 is within g2 , which is quite opposite of ST_Contains function but works for us as well.

SET @user_location = ST_GeomFromText( 'POINT(0 0)' );

SET @area_to_search = ST_Buffer( @user_location, 100 ); SELECT

*,

ST_AsText(`position`) AS `pos_wkt`,

ST_Distance(`position`, @user_location) AS `distance`

FROM

`locations_flat`

WHERE ST_Within( `position`, @area_to_search ) = 1;

Let’s talk about performance now. We all know about MySQL Indexes. If not then there is a lot of literature about it on the internet which you can read. Basically, MySQL maintains an index of a table with rows being ordered according to column values in the B-Tree index. Whenever we try to search something from the table, MySQL looks into the index and uses nodes created by B-Tree to shortened the search path. An index of a table won’t be created unless we tell it (in some case, MySQL does that on its own, like Primary Key).

In the case of Spatial Data, MySQL uses SPATIAL INDEX which is for complex multi-dimensional data values and it maintains this index in R-Tree tree structure. Understanding how it works is not very important at this moment.

Let’s first add SPATIAL INDEX on the table locations_flat . You can do this in three ways. You can add index while creation of the table.

CREATE TABLE IF NOT EXISTS `locations_flat`(

`id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

`name` VARCHAR(100),

`position` POINT NOT NULL SRID 0,

SPATIAL INDEX(`position`)

);

You can also alter the table and add SPATIAL INDEX.

ALTER TABLE `locations_flat` ADD SPATIAL INDEX(`position`);

Or you can add index manually using CREATE SPATIAL INDEX statement.

CREATE SPATIAL INDEX position_index ON locations_flat(`position`);

⚠️ A spatial index can only be created on a column with geometry type. It must be a NOT NULL column and should contain data of only one SRID.

Once, SPATIAL INDEX is created, you can verify indexes on the table using query SHOW INDEXES FROM `locations_flat`;

Let’s see how our earlier location search example is doing without using SPATIAL INDEX (ignore index).

SET @user_location = ST_GeomFromText( 'POINT(0 0)' );

SET @area_to_search = ST_Buffer( @user_location, 100 );

EXPLAIN SELECT

*,

ST_AsText(`position`) AS `pos_wkt`,

ST_Distance(`position`, @user_location) AS `distance`

FROM

`locations_flat`

IGNORE INDEX(`position`)

WHERE ST_Within( `position`, @area_to_search ) = 1;

Seems like MySQL is using full-table scan and results show that 1000 rows are being searched for the match. Also, possible_keys are empty which shows that no index was used to perform the search.

Let’s now use the index to find locations, we are going to use statement USE INDEX but that is optional as position column by-default uses the index.

SET @user_location = ST_GeomFromText( 'POINT(0 0)' );

SET @area_to_search = ST_Buffer( @user_location, 100 );

EXPLAIN SELECT

*,

ST_AsText(`position`) AS `pos_wkt`,

ST_Distance(`position`, @user_location) AS `distance`

FROM

`locations_flat`

USE INDEX(`position`)

WHERE ST_Within( `position`, @area_to_search ) = 1;

Aha! We see no difference. Don’t worry, this is a ⚠️ documented MySQL bug. When we compare the return value of a spatial function in WHERE clause (like we did with ST_Within( `position`, @area_to_search ) = 1; , MySQL ignores the index. Since ST_Within returns 1 or 0, it’s perfectly safe to ignore =1 in the statement and WHERE clause will only select value which returns 1.

Yess! Now we can see that only 53 rows were scanned to return the result and possible_keys column shows that position index was used for the search.