Re: GPS positions - Mailing list pgsql-novice

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
needto be. 

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

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
angularvalues 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
coordinatesinto 'map' coordinates through the application of a suitable map projection, eg UTM. This isn't trivial, but
thereare lots of websites with the maths. With this one big hurdle overcome, storing points on a flattened map makes
everythingso 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
prettynear the equator. 

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

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
goodenough 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
thatask 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-treeindex 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
withina rectangular box. 

So to efficiently run a query for all the points within a specified distance, the standard trick is to define the
smallestbox you can that will contain all the points within this distance (this is where the indexes are helpful) and
thentest the distance of each the points that fall within this box. You'd express this in SQL as a bunch of AND'ed
conditionsin 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
distancebetween any two points. 

Nathaniel


On 22 May 2010, at 02:19, Frank Bax <fbax@sympatico.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
notcritical (let's say 0.5km) is accurate enough for my purposes. 

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

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice