0

I have a table "ways" containing coordinates (lat/lon) values. Suppose I have a coordinate (x,y) and I want to check the closest match from the table ways. I have seen some similar questions like this: Is there a postgres CLOSEST operator?

However in my case I have 2 columns instead of 1. Is there a query to do something like this this ?

lyng
  • 3
  • 1
  • When you say you have two columns, do you mean you have a "latitude" column and a "longitude" column? – Avocado May 18 '20 at 16:21
  • Yes, basically I have loaded .osm data to the db using osm2pgrouting. The table "ways" contains all the edges (start end points given by coordinates). I have calculated "middle coordinates" for each edge. Now I want to analyze actual routes provided by GPS data and check which edges were taken. My idea is to match the route coordinates with the "middle coordinates" I have calculated to determine which edges were taken. – lyng May 19 '20 at 22:59
  • Did you find a solution? Were you able to spin up PostGIS to help? – Thomas Portwood Jun 15 '20 at 01:03

1 Answers1

0

You could store the data as PostGIS 'point' type instead of coordinate values:

This would empower you with all the PostGIS functionality such as:

Then you could create a GiST index and use the PostGIS <-> operator to take advantage of index assisted nearest neighbor result sets. The 'nearest neighbor' functionality is pretty common. Here is a great explanation:

“KNN” stands for “K nearest neighbours”, where “K” is the number of neighbours you are looking for.

KNN is a pure index based nearest neighbour search. By walking up and down the index, the search can find the nearest candidate geometries without using any magical search radius numbers, so the technique is suitable and high performance even for very large tables with highly variable data densities.

Community
  • 1
  • 1
Thomas Portwood
  • 1,031
  • 8
  • 13