Обсуждение: Re: Earth distance
On Wed, Mar 26, 2003 at 18:29:42 -0600, Tad Marko <tad@tadland.net> wrote: > Bruno, > > I aplogize for bothering you direclty, but I'm having some difficulty with > some syntax using the earth distance functions. Every single example I can > find is using hard coded constants for the latitude and longitude, but I'm > trying to use a join to get these values. Basically, what I'm trying to do > is: > > select up.first_name, up.last_name, up.city, zip_code from user_primary as > up, ziplocs as zl where zl.zip=up.zip_code::integer and '(33.0, 97.1)'::point > <@> '(zl.lat, zl.lon)'::point < 50; > > But this fails with a > > ERROR: Bad point external representation '(zl.lat, zl.lon)' > > message. I simply cannot figure out how to specify the lat and lon values > from the joined table. Can you offer any suggestions? The best thing would be to store the lat and lon as point in the table instead of in separate float columns. You can extra the lat and lon using subscripts if you need them broken out. You really want to ask questions like this on one of the postgres lists. Just because I have touched the earthdistance code doesn't make me the foremost expert on it. Also there may be periods of time when I am not able to respond to questions, when someone on the lists might have been able to provide you with an answer promptly.
On Thu, 27 Mar 2003, Bruno Wolff III wrote: > On Wed, Mar 26, 2003 at 18:29:42 -0600, > Tad Marko <tad@tadland.net> wrote: > > Bruno, > > > > I aplogize for bothering you direclty, but I'm having some difficulty with > > some syntax using the earth distance functions. Every single example I can > > find is using hard coded constants for the latitude and longitude, but I'm > > trying to use a join to get these values. Basically, what I'm trying to do > > is: > > > > select up.first_name, up.last_name, up.city, zip_code from user_primary as > > up, ziplocs as zl where zl.zip=up.zip_code::integer and '(33.0, 97.1)'::point > > <@> '(zl.lat, zl.lon)'::point < 50; > > > > But this fails with a > > > > ERROR: Bad point external representation '(zl.lat, zl.lon)' > > > > message. I simply cannot figure out how to specify the lat and lon values > > from the joined table. Can you offer any suggestions? That'd attempt to make a point from the string literal given, not what you want. I think point(zl.lat, zl.lon) might work (there's a point function that takes two doubles and returns a point).
On Thu, 2003-03-27 at 08:40, Stephan Szabo wrote: > On Thu, 27 Mar 2003, Bruno Wolff III wrote: > > > On Wed, Mar 26, 2003 at 18:29:42 -0600, > > Tad Marko <tad@tadland.net> wrote: > > > Bruno, > > > > > > I aplogize for bothering you direclty, but I'm having some difficulty with > > > some syntax using the earth distance functions. Every single example I can > > > find is using hard coded constants for the latitude and longitude, but I'm > > > trying to use a join to get these values. Basically, what I'm trying to do > > > is: > > > > > > select up.first_name, up.last_name, up.city, zip_code from user_primary as > > > up, ziplocs as zl where zl.zip=up.zip_code::integer and '(33.0, 97.1)'::point > > > <@> '(zl.lat, zl.lon)'::point < 50; > > > > > > But this fails with a > > > > > > ERROR: Bad point external representation '(zl.lat, zl.lon)' > > > > > > message. I simply cannot figure out how to specify the lat and lon values > > > from the joined table. Can you offer any suggestions? > > That'd attempt to make a point from the string literal given, not what you > want. I think point(zl.lat, zl.lon) might work (there's a point function > that takes two doubles and returns a point). Excellent! That's all I needed. I just hadn't come across the point function in the documentation (searching on point yields quite a pile of responses at the Postgres site), and I'm starting to doubt the value the O'Reilly book on Postgres. Now all I have to do is figure out how to deal with the issue of canadian zip codes that we have mixed in. Thanks! Tad -- Tad Marko <tad@tadland.net>
So does Postgres do 'Great Circle' calculations? Like distance between two points? What dataset does it use? Tad Marko wrote: > On Thu, 2003-03-27 at 08:40, Stephan Szabo wrote: > >>On Thu, 27 Mar 2003, Bruno Wolff III wrote: >> >> >>>On Wed, Mar 26, 2003 at 18:29:42 -0600, >>> Tad Marko <tad@tadland.net> wrote: >>> >>>>Bruno, >>>> >>>>I aplogize for bothering you direclty, but I'm having some difficulty with >>>>some syntax using the earth distance functions. Every single example I can >>>>find is using hard coded constants for the latitude and longitude, but I'm >>>>trying to use a join to get these values. Basically, what I'm trying to do >>>>is: >>>> >>>>select up.first_name, up.last_name, up.city, zip_code from user_primary as >>>>up, ziplocs as zl where zl.zip=up.zip_code::integer and '(33.0, 97.1)'::point >>>><@> '(zl.lat, zl.lon)'::point < 50; >>>> >>>>But this fails with a >>>> >>>>ERROR: Bad point external representation '(zl.lat, zl.lon)' >>>> >>>>message. I simply cannot figure out how to specify the lat and lon values >>>>from the joined table. Can you offer any suggestions? >> >>That'd attempt to make a point from the string literal given, not what you >>want. I think point(zl.lat, zl.lon) might work (there's a point function >>that takes two doubles and returns a point). > > > Excellent! That's all I needed. I just hadn't come across the point > function in the documentation (searching on point yields quite a pile of > responses at the Postgres site), and I'm starting to doubt the value the > O'Reilly book on Postgres. > > Now all I have to do is figure out how to deal with the issue of > canadian zip codes that we have mixed in. > > Thanks! > > Tad >
On Tue, Apr 01, 2003 at 13:09:33 -0800, Dennis Gearon <gearond@cvc.net> wrote: > So does Postgres do 'Great Circle' calculations? Like distance between two > points? What dataset does it use? Yes it does Great Circle distance. It uses a spherical model, so the only parameter is the Earth's radius.
How easy would it be to add other datums, or the flattened spheroid model to be an option for calculations? Bruno Wolff III wrote: > On Tue, Apr 01, 2003 at 13:09:33 -0800, > Dennis Gearon <gearond@cvc.net> wrote: > >>So does Postgres do 'Great Circle' calculations? Like distance between two >>points? What dataset does it use? > > > Yes it does Great Circle distance. It uses a spherical model, so the only > parameter is the Earth's radius. >
On Wed, Apr 02, 2003 at 08:21:53 -0800, Dennis Gearon <gearond@cvc.net> wrote: > How easy would it be to add other datums, or the flattened spheroid model > to be an option for calculations? You could do it using the current earthdistance as a model for your functions. Calculating the distance along a geodesic on those surfaces is much more complicated. But if you have the formulas (which normally won't be exact), you can do it.