2

I have a table named coords with the following columns |name|lat|lon|und| 

import sqlite3
database = sqlite3.connect('geoida.db')
cursor = database.cursor()
cursor.execute("select lat, lon, und  from coords")
results = cursor.fetchall()

every line in table stores coordinates of one point of g r i d and distance from point to point is always in decimal 0.041667, what is equal to 2.5''.
What I would like to achieve is to find 4 nearest adjacent points around given latitude and longitude in decimal. We have to keep in mind that latitude and longitude of these four points have to fill quite simple condition:
excess between lat, lon of adjacent point and lat, lon
of given point must be less/equal 0.041667 on + or -
or we can treat this value as max radius divisive sought neighboring points from the given one.

for example:

for given point 56.02050000 13.02040000
4 nearest adjacent points taken from my coords table are:

56.000000   13.000000
56.000000   13.041667
56.041667   13.000000
56.041667   13.041667

Given points are stored in another database, where C1 is latitude and C2 is longitude

database = sqlite3.connect('F.tsj')
cursor = database.cursor()
cursor.execute("select C1, C2 from tblSoPoints")
results = cursor.fetchall()

How can I put such query using python?
Sorry for code but there's something wrong with formating.

daikini
  • 1,307
  • 6
  • 23
  • 36

1 Answers1

3
def find_adjacent_coords(db, lat, lon, step=0.041667):
    """Find coords that are in a +/- step range of lat, lon."""
    #XXX disregard values near +/- 90 latitude, +/- 180 longitude
    coords_range = lat-step, lat+step, lon-step, lon+step
    return db.execute("""select lat, lon from coords where 
lat > ? and lat < ? and
lon > ? and lon < ?""", coords_range).fetchall()

full example with rtree index

Note: this code doesn't include boundaries.

For very efficient range queries if there are millions of coordinates you might need SQLite R-Tree index.

For 1000000 entries the above approach takes ~0.16 seconds, but the function that uses rtree requires less than 1ms. For 10000 entries it is 800 µs vs. 20 µs for rtree-based solution for the data from the test. DISCLAIMER: The numbers are for the code I've posted that I run on my machine.

jfs
  • 399,953
  • 195
  • 994
  • 1,670
  • @J.F. Sebastian: -1 For a fair comparison, your experiment without the R-tree index should have an index on either latitude or longitude. – John Machin Oct 17 '11 at 20:28
  • @John Machin: All benchmarks are evil. There is no such thing as a fair comparison e.g., read [how difficult it can be to make meaningful comparisons](http://shootout.alioth.debian.org/help.php#why) from computer language benchmarks game. I report what *the* code I've posted produced on my machine. I've not put the explicit disclaimer because it is implied on *any* and *every* comparison that I post. If you have data that prove the above numbers are misleading then post it. – jfs Oct 18 '11 at 19:55
  • @J.F.Sebastian: If all benchmarks are evil, why do you publish them? Comparing an indexed table scan against a full table scan without mentioning it is intrinsically misleading. – John Machin Oct 18 '11 at 20:29
  • @John Machin: thank you. At this point there should be no doubt that the non-rtree query above doesn't use index either on `lat` or `lon` unless sqlite implicitly creates it. – jfs Oct 18 '11 at 21:44