Re: GPS positions

Hi Frank,

It all depends on how much data you'll be storing, what kind of distances you'll be querying for, and how accurate you need to be.

If the example you give in your post is typical, namely searching for points within 5km, and inaccuracies up to 0.5km is okay, and you're dealing with thousands, rather than millions of points, then postgres (without postgis) should be perfectly servicable.

Storage representation:

You can store Lat/Lon coords as float8 columns. This is sufficient accuracy for all but super-duper precise surveying.

You'll need to covert your hexagesimal string representation into decimal degrees (or radians--don't discount storing angular values in radians since it makes the trigonometric SQL calls that follow marginally clearer)

If your db of points are all roughly in the same place in the world, you may want to consider converting your coordinates into 'map' coordinates through the application of a suitable map projection, eg UTM. This isn't trivial, but there are lots of websites with the maths. With this one big hurdle overcome, storing points on a flattened map makes everything so much easier (planar Euclidean geometry applies--eg Pythagoras)

But, for now, let's assume you are storing positions as Lat/Lon.

Computing distance:

As has been said, you can't really use Pythagoras to compute the distance between two points in Lat/Lon, unless you're pretty near the equator.

However, if your requirements aren't rigorous, you can get an approximation of the distance using Pythagoras using an equation like:

D = R*sqrt((lat1-lat0)^2+(cos(0.5*(lat0+lat1))*(lon1-lon0))^2)

Where:
R is the approximate radius of the earth (eg 6370000m)
And lat0, lon0, lat1, and lon1 are in radians.

This will be fine for short distances, or where the accuracy you need is low (preferably both).

For better accuracy, there are formulas available on the web that assume a spherical earth, and they're likely to be good enough for most people.

For best accuracy, you assume an ellipsoidal earth, but the the sums become harder still.

Efficient querying:

The best thing that postgis gives you is the R-tree index. This is an efficient form of index for helping with queries that ask for all the points in some rectangular area.

But if your not dealing with loads and loads of points, you'll find that you get acceptable performance if you use a B-tree index on both the Lat and Lon columns.

These indices (whether an R or 2 B trees) will allow the db to efficiently retun all the points whose Lat/Lon are within a rectangular box.

So to efficiently run a query for all the points within a specified distance, the standard trick is to define the smallest box you can that will contain all the points within this distance (this is where the indexes are helpful) and then test the distance of each the points that fall within this box. You'd express this in SQL as a bunch of AND'ed conditions in the where clause, eg

SELECT * FROM mytable
WHERE
lat >= boxlatmin
AND lat <= boxlatmax
AND lon >= boxlonmin
AND lon <= boxlonmax
AND mydistancefunc(lat, lon, centrelat, centrelon) < threshold

setting box* and threshold to appropriate values, and where mydistancefunc is a function you've created to compute the distance between any two points.

Nathaniel

On 22 May 2010, at 02:19, Frank Bax <fbax(at)sympatico(dot)ca> wrote:

I'd like to create a small database for GPS positions that are currently in the format "N 42° 57.750 W 081° 37.200".

What's an easy way to store these points and how would I query data, if if the only question I need answered is:

Which positions are within 5km of a given position; where "within" can be a square or circle or whatever; accuracy is not critical (let's say 0.5km) is accurate enough for my purposes.

Are there datatypes within base install of pgsql to do this?