# Finding nearest point in SpatiaLite without GEOS

I want to find the nearest point having latitude and longitude stored as Point in SpatiaLite. I read this and it has this example:

SELECT feature_name, feature_class,
ST_Distance(Geometry, MakePoint(-70.250, 43.802)) AS Distance FROM XYGNIS
WHERE distance &lt; 0.1 AND ROWID IN
(SELECT ROWID FROM SpatialIndex
WHERE f_table_name = 'XYGNIS' AND search_frame =
BuildCircleMbr(-70.250,    43.802, 0.1))
ORDER BY distance LIMIT 10

I cannot use it, however, since ST_Distance requiring compiling SpatiaLite with GEOS, which is in LGPL, which I cannot use. Is there a way I can do arithmetics to calculate distance and set that to a "variable" and then find the one with the minimal distance?

-
any reasons why you cannot use LGPL license? It is more of a commercial friendly license than GPL.. – vinayan Sep 16 '12 at 12:12
It's intended to be on the App store. – huggie Sep 17 '12 at 2:16

If you're working with a smallish dataset and over a small area or not too concerned about accuracy (i.e. distance over oblate spheroids), then you could just use the following:

SELECT
feature_name,
feature_class,
((X(Geometry)-X(loc))*(X(Geometry)-X(loc))) + ((Y(Geometry)-Y(loc))*(Y(Geometry)-Y(loc))) as distance
FROM
XYGNIS,
(SELECT MakePoint(-70.250, 43.802) as loc)
ORDER BY distance
LIMIT 10;

The added advantage here is that you're eliminating the need to square root each result by just ordering the square of the distance (i.e. dx^2 + dy^2). You can square root a set of the results, if you need to display the actual distance, in a sub query.

-
Thanks, just what I was looking for! – huggie Sep 25 '12 at 7:05