Thread: GPS positions

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?

If you don't want to work with postgis, I think you could just use a bit of sqrt
using double precision - for a circle anyway.

I did something similar for a simple target archery scoring example I
set up awhile ago.
http://www.younicycle.com/web/younicycle_com/xml-hr/ex_12_a.html

Many of the other links off this page require login/privileges - but I
believe this specific page works.
round(GREATEST(10.5 - sqrt((("xyarchery"."xx" - 200) ^ 2) +
(("xyarchery"."yy" - 200) ^ 2)) / 20, 0))
where pixels are being recorded & scored.

It's been awhile since I made this, but from recall xx - yy are not
much the same as long and lat, but recorded to account for a browser
click.

Anyway -- think about it for a minute - and the lightbulb will turn
on.  Believe me - I'm no math wiz.

On Fri, May 21, 2010 at 9:22 PM, Sean Davis <sdavis2@mail.nih.gov> wrote:
>
>
> On Fri, May 21, 2010 at 9:19 PM, 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 not critical (let's say
>> 0.5km) is accurate enough for my purposes.
>>
>> Are there datatypes within base install of pgsql to do this?
>>
>
> http://postgis.refractions.net/
>
> Sean
>
>



--
Mike Ellsworth

On 2010-05-22, Mike Ellsworth <younicycle@gmail.com> wrote:
> If you don't want to work with postgis, I think you could just use a bit of sqrt
> using double precision - for a circle anyway.
>
> I did something similar for a simple target archery scoring example I
> set up awhile ago.
> http://www.younicycle.com/web/younicycle_com/xml-hr/ex_12_a.html
>
> Many of the other links off this page require login/privileges - but I
> believe this specific page works.
> round(GREATEST(10.5 - sqrt((("xyarchery"."xx" - 200) ^ 2) +
> (("xyarchery"."yy" - 200) ^ 2)) / 20, 0))
> where pixels are being recorded & scored.

you can't apply pythagoras's theorem to latt0tude,longitude
coordinates and get a useful result unless the points are near the
equator.

By far the easiest solution is to us a package, like postgis, that was
prepared by persons who know what they are doing.

the same goes time arithmetic, where leap-years are just one of the
pitfalls.

Jasen Betts wrote:
> On 2010-05-22, Mike Ellsworth <younicycle@gmail.com> wrote:
>> If you don't want to work with postgis, I think you could just use a bit of sqrt
>> using double precision - for a circle anyway.
>>
>> I did something similar for a simple target archery scoring example I
>> set up awhile ago.
>> http://www.younicycle.com/web/younicycle_com/xml-hr/ex_12_a.html
>>
>> Many of the other links off this page require login/privileges - but I
>> believe this specific page works.
>> round(GREATEST(10.5 - sqrt((("xyarchery"."xx" - 200) ^ 2) +
>> (("xyarchery"."yy" - 200) ^ 2)) / 20, 0))
>> where pixels are being recorded & scored.
>
> you can't apply pythagoras's theorem to latt0tude,longitude
> coordinates and get a useful result unless the points are near the
> equator.
>
> By far the easiest solution is to us a package, like postgis, that was
> prepared by persons who know what they are doing.
>
> the same goes time arithmetic, where leap-years are just one of the
> pitfalls.


Thanks Sean Mike & Jasen.

Installing PostGIS is not possible at this time.

I just found this page:
http://en.wikipedia.org/wiki/Geographical_distance
which mentions Pythagorean formula has an error of at least 30m for
distance of 20km in my area; this is close enough for my purposes.

If/when more accuracy is needed, I'll look into PostGIS.

Frank

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

Nathaniel Trellice wrote:
> 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.


Thanks Nathaniel for the in-depth explanations.  When I started working
on calculations; I soon realized why storing points in radians makes
more sense!

Database tables will start with hundreds of rows; might grow to thousands.

Frank