Thread: Geometric types in SELECT?
First off, thanks for the answer to my earlier question -- I've become less misguided since (I hope!) One of my tables uses the point data type. I wanted to use this to find points close to one another. Unfortunately, when I do something like: =>select * from address order by length ( '((0,0), coord)'::lseg ); or =>select length( '((0,0),coord)'::lseg ) from address; I receive this error: Bad lseg external representation '((0,0), coord)' If I replace coord with an actual value, I don't get the error. Am I somehow mangling the syntax? I've tried a number of different formats for the query, with the same error each time. Is it possible to do this? Thanks, -mike -- And though the holes were rather small They had to count them all Now they know how many holes it takes to fill the Albert Hall *I'd love to turn you on* -- Beatles, "A Day in the Life" GnuPG key available at http://devel.duluoz.net/pubkey.asc Key ID = 1024D/9A256AE5 1999-11-13 Mike Glover <mpg4@duluoz.net> Key fingerprint = EF6E 8BCB 4810 E98C F0FD 4596 367A 32B7 9A25 6AE5
mpg4@duluoz.net writes: > =>select length( '((0,0),coord)'::lseg ) from address; > I receive this error: > Bad lseg external representation '((0,0), coord)' > If I replace coord with an actual value, I don't get the error. Am I > somehow mangling the syntax? Yup. '((0,0),coord)'::lseg is a literal constant --- in general, 'anything'::typename is a literal constant in Postgres. And it's not a valid literal constant for lseg. I suppose coord is the name of a point column in your table? To do it correctly, you'd need something like select length( makelseg( '(0,0)'::point, coord )) from table; I'm not sure if there is a function that makes an lseg from two points, and if there is it probably isn't called "makelseg", but hopefully you get the idea. I am pretty sure there is a function that computes the distance between two points, so what you are likely to end up really writing is select distance( '(0,0)'::point, coord ) from table; (after you dig through the documentation to find the actual name of that function...) regards, tom lane