Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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 < 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?

share|improve this question
    
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
1  
It's intended to be on the App store. –  huggie Sep 17 '12 at 2:16
add comment

1 Answer 1

up vote 0 down vote accepted

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.

share|improve this answer
    
Thanks, just what I was looking for! –  huggie Sep 25 '12 at 7:05
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.