Revisions to Does MySQL or MariaDB support GIS KNN Index Queries (k-nearest neighbors)?

added 19 characters in body

Source Link

edited Apr 12, 2019 at 14:07

64.7k
49
251
496

Can you do k-nearest neighbors algorithm (k-NN) queries on MySQL for example, to find the nearest point in PostgreSQL with PostGIS, I can run a kNN query on the spatial index with <->

SELECT ST_AsText(geom), city
FROM person.address
ORDER BY geom <-> 'POINT(-121.626 47.8315)'
FETCH FIRST 7 ROWS ONLY;

SQL Server has a similar method,

DECLARE @g geography = 'POINT(-121.626 47.8315)';

SELECT TOP(7) SpatialLocation.ToString(), City
FROM Person.Address  
WHERE SpatialLocation.STDistance(@g) IS NOT NULL  
ORDER BY SpatialLocation.STDistance(@g);

Both PostgreSQL and SQL Server have KNN. What is the MySQL equivalent of <-> to do KNN on their spatial index?

If you don't know what KNN is, you can see a description of it here description of it here

The KNN system works by evaluating distances between bounding boxes inside the PostGIS R-Tree index.

Because the index is built using the bounding boxes of geometries, the distances between any geometries that are not points will be inexact: they will be the distances between the bounding boxes of geometries.

The kicker here is that the KNN approach,

Does not require you to confine the query to a distance with a WHERE clause
Does not calculate all of the distances between all of the points
Can find the nearest point on the index.

This question is specifically about the method in which the job gets done. I realize I can get the same result, with substantially more work by calculating the distance from each point to every other point, then ORDERing, and LIMITing.*

Can you do k-nearest neighbors algorithm (k-NN) queries on MySQL for example, to find the nearest point in PostgreSQL with PostGIS, I can run a kNN query on the spatial index with <->

SELECT ST_AsText(geom), city
FROM person.address
ORDER BY geom <-> 'POINT(-121.626 47.8315)'
FETCH FIRST 7 ROWS ONLY;

SQL Server has a similar method,

DECLARE @g geography = 'POINT(-121.626 47.8315)';

SELECT TOP(7) SpatialLocation.ToString(), City
FROM Person.Address  
WHERE SpatialLocation.STDistance(@g) IS NOT NULL  
ORDER BY SpatialLocation.STDistance(@g);

Both PostgreSQL and SQL Server have KNN. What is the MySQL equivalent of <-> to do KNN on their spatial index?

If you don't know what KNN is, you can see a description of it here

The KNN system works by evaluating distances between bounding boxes inside the PostGIS R-Tree index.

Because the index is built using the bounding boxes of geometries, the distances between any geometries that are not points will be inexact: they will be the distances between the bounding boxes of geometries.

The kicker here is that the KNN approach,

Does not require you to confine the query to a distance with a WHERE clause
Does not calculate all of the distances between all of the points
Can find the nearest point on the index.

This question is specifically about the method in which the job gets done. I realize I can get the same result, with substantially more work by calculating the distance from each point to every other point, then ORDERing, and LIMITing.*

Can you do k-nearest neighbors algorithm (k-NN) queries on MySQL for example, to find the nearest point in PostgreSQL with PostGIS, I can run a kNN query on the spatial index with <->

SELECT ST_AsText(geom), city
FROM person.address
ORDER BY geom <-> 'POINT(-121.626 47.8315)'
FETCH FIRST 7 ROWS ONLY;

SQL Server has a similar method,

DECLARE @g geography = 'POINT(-121.626 47.8315)';

SELECT TOP(7) SpatialLocation.ToString(), City
FROM Person.Address  
WHERE SpatialLocation.STDistance(@g) IS NOT NULL  
ORDER BY SpatialLocation.STDistance(@g);

Both PostgreSQL and SQL Server have KNN. What is the MySQL equivalent of <-> to do KNN on their spatial index?

If you don't know what KNN is, you can see a description of it here

The KNN system works by evaluating distances between bounding boxes inside the PostGIS R-Tree index.

Because the index is built using the bounding boxes of geometries, the distances between any geometries that are not points will be inexact: they will be the distances between the bounding boxes of geometries.

The kicker here is that the KNN approach,

Does not require you to confine the query to a distance with a WHERE clause
Does not calculate all of the distances between all of the points
Can find the nearest point on the index.

This question is specifically about the method in which the job gets done. I realize I can get the same result, with substantially more work by calculating the distance from each point to every other point, then ORDERing, and LIMITing.*

Tweeted twitter.com/StackDBAs/status/1036630613997088768

occurred Sep 3, 2018 at 15:02

added 163 characters in body

Source Link

edited Aug 8, 2018 at 7:43

Evan Carroll

64.7k
49
251
496

Can you do k-nearest neighbors algorithm (k-NN) queries on MySQL for example, to find the nearest point in PostgreSQL with PostGIS, I can run a kNN query on the spatial index with <->

SELECT *
FROM t1
CROSS JOINST_AsText(geom), t2city
WHEREFROM t1person.id = 42address
ORDER BY t1.geom <-> t2'POINT(-121.geom626 47.8315)'
LIMITFETCH 1;FIRST 7 ROWS ONLY;

SQL Server has a similar method,

DECLARE @g geography = 'POINT(-121.626 47.8315)';

SELECT TOP(7) SpatialLocation.ToString(), City
FROM Person.Address  
WHERE SpatialLocation.STDistance(@g) IS NOT NULL  
ORDER BY SpatialLocation.STDistance(@g);

Both PostgreSQL and SQL Server have KNN. What is the MySQL equivalent of <-> to do KNN on their spatial index?

If you don't know what KNN is, you can see a description of it here

The KNN system works by evaluating distances between bounding boxes inside the PostGIS R-Tree index.

Because the index is built using the bounding boxes of geometries, the distances between any geometries that are not points will be inexact: they will be the distances between the bounding boxes of geometries.

The kicker here is that the KNN approach,

Does not require you to confine the query to a distance with a WHERE clause
Does not calculate all of the distances between all of the points
Can find the nearest point on the index.

This question is specifically about the method in which the job gets done. I realize I can get the same result, with substantially more work by calculating the distance from each point to every other point, then ORDERing, and LIMITing.*

Can you do k-nearest neighbors algorithm (k-NN) queries on MySQL for example, to find the nearest point in PostgreSQL with PostGIS, I can run a kNN query on the spatial index with <->

SELECT *
FROM t1
CROSS JOIN t2
WHERE t1.id = 42
ORDER BY t1.geom <-> t2.geom
LIMIT 1;

SQL Server has a similar method,

DECLARE @g geography = 'POINT(-121.626 47.8315)';

SELECT TOP(7) SpatialLocation.ToString(), City
FROM Person.Address  
WHERE SpatialLocation.STDistance(@g) IS NOT NULL  
ORDER BY SpatialLocation.STDistance(@g);

Both PostgreSQL and SQL Server have KNN. What is the MySQL equivalent of <-> to do KNN on their spatial index?

If you don't know what KNN is, you can see a description of it here

The KNN system works by evaluating distances between bounding boxes inside the PostGIS R-Tree index.

Because the index is built using the bounding boxes of geometries, the distances between any geometries that are not points will be inexact: they will be the distances between the bounding boxes of geometries.

The kicker here is that the KNN approach,

Does not require you to confine the query to a distance with a WHERE clause
Does not calculate all of the distances between all of the points
Can find the nearest point on the index.

This question is specifically about the method in which the job gets done. I realize I can get the same result, with substantially more work by calculating the distance from each point to every other point, then ORDERing, and LIMITing.*

Can you do k-nearest neighbors algorithm (k-NN) queries on MySQL for example, to find the nearest point in PostgreSQL with PostGIS, I can run a kNN query on the spatial index with <->

SELECT ST_AsText(geom), city
FROM person.address
ORDER BY geom <-> 'POINT(-121.626 47.8315)'
FETCH FIRST 7 ROWS ONLY;

SQL Server has a similar method,

DECLARE @g geography = 'POINT(-121.626 47.8315)';

SELECT TOP(7) SpatialLocation.ToString(), City
FROM Person.Address  
WHERE SpatialLocation.STDistance(@g) IS NOT NULL  
ORDER BY SpatialLocation.STDistance(@g);

Both PostgreSQL and SQL Server have KNN. What is the MySQL equivalent of <-> to do KNN on their spatial index?

If you don't know what KNN is, you can see a description of it here

The KNN system works by evaluating distances between bounding boxes inside the PostGIS R-Tree index.

Because the index is built using the bounding boxes of geometries, the distances between any geometries that are not points will be inexact: they will be the distances between the bounding boxes of geometries.

The kicker here is that the KNN approach,

Does not require you to confine the query to a distance with a WHERE clause
Does not calculate all of the distances between all of the points
Can find the nearest point on the index.

This question is specifically about the method in which the job gets done. I realize I can get the same result, with substantially more work by calculating the distance from each point to every other point, then ORDERing, and LIMITing.*

added 163 characters in body

Source Link

edited Aug 8, 2018 at 7:34

Evan Carroll

64.7k
49
251
496

Can you do KNNk-nearest neighbors algorithm (k-NN) queries on MySQL for example, to find the nearest point in PostgreSQL with PostGIS, I can dorun a kNN query on the spatial index with <->

SELECT *
FROM t1
CROSS JOIN t2
WHERE t1.id = 42
ORDER BY t1.geom <-> t2.geom
LIMIT 1;

SQL Server has a similar method,

DECLARE @g geography = 'POINT(-121.626 47.8315)';

SELECT TOP(7) SpatialLocation.ToString(), City
FROM Person.Address  
WHERE SpatialLocation.STDistance(@g) IS NOT NULL  
ORDER BY SpatialLocation.STDistance(@g);

Both PostgreSQL and SQL Server use the k-nearest neighbors algorithm (k-NN) on a spatial index with <->have KNN. What is the MySQL equivalent of <-> to do KNN on their spatial index?

If you don't know what KNN is, you can see a description of it here

The KNN system works by evaluating distances between bounding boxes inside the PostGIS R-Tree index.

Because the index is built using the bounding boxes of geometries, the distances between any geometries that are not points will be inexact: they will be the distances between the bounding boxes of geometries.

The kicker here is that the KNN approach,

Does not require you to confine the query to a distance with a WHERE clause
Does not calculate all of the distances between all of the points
Can find the nearest point on the index.

This question is specifically about the method in which the job gets done. I realize I can get the same result, with substantially more work by calculating the distance from each point to every other point, then ORDERing, and LIMITing.*

Can you do KNN queries on MySQL for example, to find the nearest point in PostgreSQL with PostGIS, I can do

SELECT *
FROM t1
CROSS JOIN t2
WHERE t1.id = 42
ORDER BY t1.geom <-> t2.geom
LIMIT 1;

SQL Server has a similar method,

DECLARE @g geography = 'POINT(-121.626 47.8315)';

SELECT TOP(7) SpatialLocation.ToString(), City
FROM Person.Address  
WHERE SpatialLocation.STDistance(@g) IS NOT NULL  
ORDER BY SpatialLocation.STDistance(@g);

Both PostgreSQL and SQL Server use the k-nearest neighbors algorithm (k-NN) on a spatial index with <->. What is the MySQL equivalent of <-> to do KNN on their spatial index?

If you don't know what KNN is, you can see a description of it here

The KNN system works by evaluating distances between bounding boxes inside the PostGIS R-Tree index.

Because the index is built using the bounding boxes of geometries, the distances between any geometries that are not points will be inexact: they will be the distances between the bounding boxes of geometries.

The kicker here is that the KNN approach,

Does not require you to confine the query to a distance with a WHERE clause
Does not calculate all of the distances between all of the points
Can find the nearest point on the index.

This question is specifically about the method in which the job gets done. I realize I can get the same result, with substantially more work by calculating the distance from each point to every other point, then ORDERing, and LIMITing.*

Can you do k-nearest neighbors algorithm (k-NN) queries on MySQL for example, to find the nearest point in PostgreSQL with PostGIS, I can run a kNN query on the spatial index with <->

SELECT *
FROM t1
CROSS JOIN t2
WHERE t1.id = 42
ORDER BY t1.geom <-> t2.geom
LIMIT 1;

SQL Server has a similar method,

DECLARE @g geography = 'POINT(-121.626 47.8315)';

SELECT TOP(7) SpatialLocation.ToString(), City
FROM Person.Address  
WHERE SpatialLocation.STDistance(@g) IS NOT NULL  
ORDER BY SpatialLocation.STDistance(@g);

Both PostgreSQL and SQL Server have KNN. What is the MySQL equivalent of <-> to do KNN on their spatial index?

If you don't know what KNN is, you can see a description of it here

The KNN system works by evaluating distances between bounding boxes inside the PostGIS R-Tree index.

Because the index is built using the bounding boxes of geometries, the distances between any geometries that are not points will be inexact: they will be the distances between the bounding boxes of geometries.

The kicker here is that the KNN approach,

Does not require you to confine the query to a distance with a WHERE clause
Does not calculate all of the distances between all of the points
Can find the nearest point on the index.

This question is specifically about the method in which the job gets done. I realize I can get the same result, with substantially more work by calculating the distance from each point to every other point, then ORDERing, and LIMITing.*

added 163 characters in body

Source Link

edited Aug 8, 2018 at 7:27

Evan Carroll

64.7k
49
251
496

Loading

added 250 characters in body

Source Link

edited Aug 8, 2018 at 5:56

Evan Carroll

64.7k
49
251
496

Loading

edited title

Source Link

edited Aug 8, 2018 at 5:41

Evan Carroll

64.7k
49
251
496

Loading

added 39 characters in body

Source Link

edited Aug 8, 2018 at 5:08

Evan Carroll

64.7k
49
251
496

Loading

Source Link

asked Aug 8, 2018 at 5:02

Evan Carroll

64.7k
49
251
496

Loading

Stack Exchange Network

Return to Question