From: | hmidi slim <hmidi(dot)slim2(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | query performance |
Date: | 2018-02-17 23:35:18 |
Message-ID: | CAMsqVxvaucQ6p7f5RSE34uMwY0NQ=u4AaoabYTWeU57MWKxyzQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
I have two tables: establishment which contains these columns: id, name,
longitude, latitude, geom (Geometric column)
Product contains: id, name, establishment_id
First of all I want to select the establishment within a radius.
I run this query:
select e.name, e1.name
from establishment as e, establishment as e1
where e.id <> e1.id
and e1.id = 1
and ST_DWithin(geom, ST_MakePoint(e1.longitude, e1.latitude)::geography,
1000)
The result of this query returns all the establishment within a radius 1KM
from from a given establishment which has an id = 1.
After that I want to get the product's name of each establishment from the
query's result.
Is there an other optimized solution to make a query such this:
select * from (
select e.name, e1.name, e.id
from establishment as e, establishment as e1
where e.id <> e1.id
and e1.id = 1
and ST_DWithin(geom, ST_MakePoint(e1.longitude, e1.latitude)::geography,
1000)) as tmp inner join product as p on p.establishment_id = tmp.id
From | Date | Subject | |
---|---|---|---|
Next Message | Rich Shepard | 2018-02-17 23:59:38 | Re: Need to fix one more glitch in upgrade to -10.2 |
Previous Message | Adrian Klaver | 2018-02-17 23:07:29 | Re: Need to fix one more glitch in upgrade to -10.2 |