CODEWITHSUNDEEP
sqlpostgis
merlin371
merlin371

Reputation: 514

Use the value of a where variable in other parts of a query

Is there anyway that I can use the value of a where option somewhere else in the query? what I'm trying to do is get the points in a map with postgis and return the distance as well in a view so I had this working 

SELECT name, 
       ST_Distance(ST_GeographyFromText('SRID=4326;POINT(-110 29)')::geography, location:: geography) 
FROM global_points 
WHERE ST_DWithin(location, ST_GeographyFromText('SRID=4326;POINT(-110 29)'), 1000000);

while that's good for a single query, I wanted to use a view to both join some other informations from other tables and so that I can just do 

SELECT * FROM view WHERE currLoc = POINT(50 50)

So that then I would be able to use currLoc in the rest of the view what I'm trying is something like this

CREATE VIEW testDis 
AS
SELECT name, ST_Distance(currPos::geography, latlong::geography) 
FROM global_points;

it's probably fully wrong but I'm not sure what else I can do.

Thanks

Upvotes: 0

Views: 98

Answers (1)

user330315
user330315

Reputation:

A view can't have parameters, but you can create a set returning function that can do this:

Something along the lines:

create function testDis(currPos geography, latlong geography)
   returns table(text, float)
as $$
  SELECT name, ST_Distance($1, $2) 
  FROM global_points;
$$
language sql;

(Sorry I don't know PostGIS so I probably got the data types wrong)

With the above definition you can do something like:

select * from testDis(foo, bar);

where foo and bar are the approriatly typed values.

Upvotes: 1

Related Questions