-2

right now I store long and lat as two decimal, indexed fields in the DB.
I am wondering (without installing any bizzare engine) if there is an efficient way to do this, so the index will also help me to calculate distance. A sample query would be

get me all the location in 10M radios from long X lat Y

Itay Moav -Malimovka
  • 52,579
  • 61
  • 190
  • 278
  • NB: related question - http://stackoverflow.com/questions/6409035/quicker-way-to-calculate-geographic-distance-between-two-points/6409253#6409253 – Alnitak Jul 12 '11 at 20:21

2 Answers2

0

Use the float datatype for latitude and longitude. Anything of higher precision is most likely over-engineering.

Unless your results need to be accurate to less than a meter or so, the float datatype has PLENTY of precision for what you're trying to do. If you are working at resolutions of less than a meter, you're going to need to find out about projections (sphere-to-plane) like Universal Transverse Mercator and Lambert.

When you start doing the computations, keep in mind that one minute (one-sixtieth of a degree) of change in latitude (north-to-south) is one nautical mile.

Here's a nice presentation from a mySql person on doing this search.

http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL

The performance optimization is to make an index on the latitudes, and maybe also longitudes, then do a search like this (positive radius)

where loctable.lat >=  (mylat-radius)
  and loctable.lat <=  (mylat+radius)
  and loctable.long >= (mylong-radius)
  and loctable.long <= (mylong+radius)
  and haversine_distance(mylat, mylong, loctable.lat, loctable.long) <= radius

This searches for a bounding box. That bounding box is the right size in latitude, and probably too big in longitude (unless you're near the equator). But it's OK if the box is too big, because the last line gets rid of any extra matches.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • If the float type is 32 bit, then it will not have enough significant digits for accurate lat/lon positions for sub-foot accuracy. You need at least 10 sig figs (consider longitude -179.1234567) which you can get with a double. To increase the accuracy of the position, simply add more decimal places. UTM and other projections will not gain you any accuracy in describing a position. – TreyA Aug 07 '11 at 12:25
  • Sorry, that's incorrect. The haversine great circle distance formula assumes a spherical earth. The earth is not quite spherical, and the surveyors' projections correct for that. The haversine formula is fine for store finders and airplane fuel computations. But if you use it to lay out your parking lot, you'll get puddles. – O. Jones Aug 07 '11 at 15:48
  • You are confusing several issues. Haversine is about distance not precision. Surveyors use an instrument (Theodolite) to determine lat/lon position precisely. You can describe a position precisely with lat/lon down to the nano meter however your measuring instrument may not be that accurate. – TreyA Aug 07 '11 at 21:13
  • Try this: With IEEE 754 float, you get 7 significant digits (http://en.wikipedia.org/wiki/Floating_point). Now go to google earth and set a marker at 39.0/-123.4567. Now set another marker at 39.0/-123.4568 and use the ruler tool to measure the distance. I get about 8.5 meters. That means if you use a float type for your CA lat/lon database, it can only describe positions with 8.5 meters. Therefore, use a double if you need more accuracy. – TreyA Aug 08 '11 at 12:32
0

You want to look for a spatial index or a space-filling-curve. A si reduces the 2d complexity to a 1d complexity. It's looks like a quadtree and a bit like a fractal. If you don't mind the shape and an exact search you want to delete the harvesine formule because you can just search for a quadtree tile. Of course you need the mercantor projection. This is by far the fastest method. I uses it a lot with a hilbert curve. You want to look for Nick's hilbert curve spatial index quadtree blog.

Micromega
  • 12,486
  • 7
  • 35
  • 72