pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Casting pairs of floating point variables as a point in plpgsql
2+ messages / 2 participants
[nested] [flat]

* Casting pairs of floating point variables as a point in plpgsql
@ 2002-02-18 19:02  Irv Shapiro <ishapiro@cogitations.com>
  0 siblings, 1 reply; 2+ messages in thread

From: ishapiro@cogitations.com @ 2002-02-18 19:02 UTC (permalink / raw)
  To: pgsql-sql

I have a zip code database which includes pairs of floting point
numbers representing latitude and longitude.  I can retieve this data
with the following query:


Declare
  
m_zip_info1 char(5);
m_zip_info2 char(5);
m_miles integer;
m_zipcode char(5) := ''60076'';

Begin

select logitude, latitude into m_zip_info1, m_zip_info2
     from zipstate where zipcode = m_zipcode;

How do I cast the logitude and latitude floatig point numbers, stored
in my plpgsql variables into a point datatype so I can use it with the
earthdistance function.

I tried the following and it returns a syntax error on the "as":

select cast((m_zip_info1, m_zip_info2) as point) <@>
       cast((m_merchant_zip1, m_merchant_zip2) as point) into m_miles;

I also tried the following, and Postgresql complained about the "::"

select (m_zip_info1,m_zip_info2)::point <@>
       (m_merchant_zip1,m_merchant_zip2)::point into m_miles;

Any help would be greatly appreciated.

Irv
ishapiro@cogitations.com



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Casting pairs of floating point variables as a point in plpgsql
@ 2002-02-22 00:18  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Irv Shapiro <ishapiro@cogitations.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2002-02-22 00:18 UTC (permalink / raw)
  To: ishapiro@cogitations.com; +Cc: pgsql-sql

ishapiro@cogitations.com (Irv Shapiro) writes:
> How do I cast the logitude and latitude floatig point numbers, stored
> in my plpgsql variables into a point datatype so I can use it with the
> earthdistance function.

> I tried the following and it returns a syntax error on the "as":

> select cast((m_zip_info1, m_zip_info2) as point) <@>
>        cast((m_merchant_zip1, m_merchant_zip2) as point) into m_miles;

You cannot "cast" two floats into a point (a cast always operates on
a single data value).  However, a moment's perusal of pg_proc shows
that there is a function point(float8,float8), so this should work:

select point(m_zip_info1, m_zip_info2) <@>
       point(m_merchant_zip1, m_merchant_zip2) into m_miles;

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2002-02-22 00:18 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-02-18 19:02 Casting pairs of floating point variables as a point in plpgsql Irv Shapiro <ishapiro@cogitations.com>
2002-02-22 00:18 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox