Extreme newbie to postGres

[siena]

Hello all,
I am without shame an extreme newbie to postGres.
But I have a daunting task and would appreciate any feedbacks, samples, links that would help.

I come from oracle/sql server world but even then, coding a proximity script is still a daunting task.

I need to perform a proximity search and the way it is laid out is that a user clicks on a map and an x/y coordinate pair is created, say x = 1234.2; y=5346.02
Based on the x/y coordinate pair values, the closest address to the x/y coordinate pair is displayed.
Needless to say, I haven't got a clue how to get started on this.
Thanks all in advnce.

 

[ceco]

something like that, but not good enough, because will not use indeces

SELECT * FROM points ORDER BY (point(field_x, field_y) <-> point (userinput_x, userinput_y)) DESC LIMIT 1

a little more elegant way is to use the build in type point for the field
(

http://www.postgresql.org/docs/7.4/interactive/functions-geometry.html)

SELECT * FROM points ORDER BY (field_point <-> point (userinput_x, userinput_y)) DESC LIMIT 1

the problem is that right now, I can't get an idea of how to make it use index

( you have to replace userinput_x and userinput_y with apropriate values, and of course the names ;-)))

 

[ceco]

a thought of a way that use predefined areas around the point (in separate table) and find in which predefined area is the userinput value and join to the original table
then you can make it in a way that use indexes, but you should specify all the rectangles (the easiest way) around the important points

 

[siena]

Thanks all for the response.
Can someone then tell me how to concatenate 2 strings in postgres.

When I do this:

select into my_daccess (str_num||' '||) as address from address,
it doesn't work

but when I do this:

select (str_num||' '||) as address from address, it works.
Is it because of the select into?
If yes, what is the proper of concatenating a select into?

 

[ceco]

i think the problem is in AS address

try
select into my_daccess (str_num||' '||) from address

PS and also it is a little strange concatenation that I'm curious why works at all ;-) because finishes with || with nothing following

 

[siena]

You are right, this code:
select into my_daccess (str_num||' '||) from address
is wrong.
It should be:
select into my_daccess (str_num||' '||name) from address

I do need the as though because this is a function that I am invoking with an asp code.
So with the AS address reference, address becomes an alias for concatenated fields (str_num||' '||name).
Because then I can reference them thus:

<%=rs("address")%> and the result would be the value of the concatenated fields.

 

[ceco]

yes you need the AS, but in the select without INTO
when you have INTO you don't need AS

actually INTO can be used only in function, you can't use it in pg_query('select into ....')

 

[siena]

ceco, I am using the select into in a function.
In fact, I used a select into with the AS in all the functions I have used so far and they work real good.
The reason this is not working is because I am concatenating 2 strings.
Unless select into ..as doesn't work in postgres.
The reason I posted this question is so someone can say it doesn't work in functions, then I know it is not my code.
I almost like to believe it should work.

 

[ceco]

what I meant is that: you do not need AS when using INTO

as far as I was not very careful, and you were not very descriptive I couldn't noticed that it is in a function

your problem is that in function quots ' should be escaped with quotes ''

i.e.

create or replace function test () returns text as
'
declare
test text;
begin
select into test (''a''||''b'') as sss;
return test;
end;
' language 'plpgsql';

notice the double quotes '' around a and b
in your example

select into my_daccess (str_num||'' ''||name) from address

since it is in function it should work

 

[ceco]

yet another problem may exist!!

select into my_daccess (str_num||'' ''||name) from address
if my_daccess is of type text then, it will work only if table address has only one row
you should use where to limit the select to 1 row, may be limit will also work

 

[siena]

ahh, that worked!
Thank you, ceco