Thread: Casting pairs of floating point variables as a point in plpgsql
Casting pairs of floating point variables as a point in plpgsql
From
ishapiro@cogitations.com (Irv Shapiro)
Date:
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
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