CODEWITHSUNDEEP
sqlitecoordinatesdistance
sharkyenergy
sharkyenergy

Reputation: 4173

get nearest coordinate from sqlite database

I have a point coordinate based on two double values x0,y0 both in this format: xx.x (point as decimal separator)

In a database I have a list of lines that are defined by the coordinates x1,y1 as startpoint and x2,y2 as endpoint. Among other columns, (such as line thickness and so on) in the database there are these columns:

id | x1 | y1 | x2 | y2

What I would need is a query that returns whatever line has either the starpoint(x1,y1) or the endpoint(x2,y2) nearest to my basepoint (x0,y0). So the line that starts or ends nearest to my current position.

Thanks

Upvotes: 0

Views: 548

Answers (1)

CL.
CL.

Reputation: 180192

SQLite has no square root function, but for comparing distances, we can just as well use the square of the distance:

SELECT *
FROM MyTable
ORDER BY min((x1-x0)*(x1-x0) + (y1-y0)*(y1-y0),
             (x2-x0)*(x2-x0) + (y2-y0)*(y2-y0))
LIMIT 1

Upvotes: 1

Related Questions