5

I'm working with data in Postgresql+PostGIS and I've stumped myself (probably from overthinking it). For each lat/long in a site table, I want to calculate an average of a field for all points within a certain distance of that lat/long. For example:

SELECT
       site.id,
       ring1.avg as r1_avg,
       ring5.avg as r5_avg
FROM
       (SELECT id, lon, lat FROM sites) as site,
       (SELECT avg(sales) FROM revenue WHERE ST_DWithin(geom::geography, ST_SetSRID(ST_MakePoint(site.lon, site.lat)::geography, 4326), (1.0*1609.34))) as ring1,
       (SELECT avg(sales) FROM revenue WHERE ST_DWithin(geom::geography, ST_SetSRID(ST_MakePoint(site.lon, site.lat)::geography, 4326), (5.0*1609.34))) as ring5;

Yep, the implicit/explicit syntax is bad form and wrong. That's what I'm struggling with: how to get the subqueries to pickup the lat/long from the site table and use it for the WHERE calculation?

5

If you are using a recent version of PostgreSQL, you can use LATERAL Joins to fix the dependency problem in your JOIN.

Without LATERAL, each sub-query in your FROM clause is evaluated independently and cannot cross-reference any other items in the FROM clause. The LATERAL keyword can precede a sub-query to allow reference to columns of FROM items that appear before (to the left of) it.

In your case, you can add the LATERAL keyword before the last two subqueries.

SELECT
       site.id,
       ring1.avg as r1_avg,
       ring5.avg as r5_avg
FROM
       (SELECT id, lon, lat FROM sites) as site,
       LATERAL (SELECT avg(sales) FROM revenue WHERE ST_DWithin(geom::geography, ST_SetSRID(ST_MakePoint(site.lon, site.lat)::geography, 4326), (1.0*1609.34))) as ring1,
       LATERAL (SELECT avg(sales) FROM revenue WHERE ST_DWithin(geom::geography, ST_SetSRID(ST_MakePoint(site.lon, site.lat)::geography, 4326), (5.0*1609.34))) as ring5;
  • 2
    Nice, did not know about LATERAL JOIN. You can also move the ST_SetSRID(ST_MakePoint(site.lon, site.lat)::geography, 4326) to the site CTE so it's only done once (I assume that isn't being optimised): (SELECT id, ST_SetSRID(ST_MakePoint(site.lon, site.lat)::geography, 4326) AS geog FROM sites) as site. – Richard Law May 23 '17 at 23:49
  • @RichardLaw You are right. That saves computation indeed. – tinlyx May 23 '17 at 23:51
  • This is exactly the solution I needed. Moving the St_MakePoint call also decreased runtime by a few minutes. Thanks tinylx and @RichardLaw for the help. – drzookeeper May 24 '17 at 14:39
0

Joining two times the same tables might be a bit slow.

You could just join once and make use of the Filter function for aggregations (older Versions use CASE WHEN inside the Aggregate function).

PostgreSQL > 9.4:

SELECT
   site.id,
   avg(revenue.sales) FILTER (WHERE ST_DWithin(revenue.geog, site.geog, (1.0*1609.34))) as r1_avg,
   avg(revenue.sales) as r5_avg
FROM
   ( select id, ST_SetSRID(ST_MakePoint(site.lon, site.lat)::geography as geog from site) as site
JOIN
   ( select sales, geom::geography as geog from revenue) as revenue
ON ST_DWithin(revenue.geog, site.geog, (5.0*1609.34));

PostgreSQL < 9.4:

exchange the line for r1_avg with the following:

avg(case when ST_DWithin(revenue.geog, site.geog, (1.0*1609.34)) then revenue.sales end) as r1_avg,

For performance improvement consider to create an index on revenue.geom::geography

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.