CODEWITHSUNDEEP
sqlpostgresql
Hugh_Kelley
Hugh_Kelley

Reputation: 1040

Joined Filtered Table

I need to count a small subset of points in a table that are within a polygon and add the count as an attribute of the geometries.

Is there a way to add a where clause to the LEFT JOIN below to avoid having to use the temporary table? I'd prefer the temp table solution below to a CTE i think for the sake of clarity. Performance isn't really and issue for this.

DROP TABLE IF EXISTS libraries;
CREATE TEMP TABLE libraries AS(SELECT ID, osm_id, geom FROM received.osm_pois_p opp WHERE fclass = 'library');

CREATE INDEX sindx_libraries ON libraries USING gist(geom);
ANALYZE libraries;

UPDATE automated.trip_potential_hex 
SET libraries = total_count 
FROM  (
    SELECT 
        tph.ID,
        COUNT(libraries.geom) AS total_count
    FROM automated.trip_potential_hex tph 
    LEFT JOIN
        libraries 
    ON st_contains(tph.geom, libraries.geom)
    GROUP BY tph.ID) count_source
WHERE trip_potential_hex.ID = count_source.ID;

Upvotes: 0

Views: 32

Answers (2)

JustMe
JustMe

Reputation: 2373

Why just don't move view query to a left join sub-query?

UPDATE automated.trip_potential_hex 
SET libraries = total_count 
FROM  (
    SELECT 
        tph.ID,
        COUNT(libraries.geom) AS total_count
    FROM automated.trip_potential_hex tph 
    LEFT JOIN
        (SELECT ID, osm_id, geom FROM received.osm_pois_p opp WHERE fclass = 'library') libraries 
    ON st_contains(tph.geom, libraries.geom)
    GROUP BY tph.ID) count_source
WHERE trip_potential_hex.ID = count_source.ID;

Second way is to rewrite your join clause like:

UPDATE automated.trip_potential_hex 
SET libraries = total_count 
FROM  (
    SELECT 
        tph.ID,
        COUNT(libraries.geom) AS total_count
    FROM automated.trip_potential_hex tph 
    LEFT JOIN
        received.osm_pois_p libraries 
    ON st_contains(tph.geom, libraries.geom) AND libraries.fclass = 'library'
    GROUP BY tph.ID) count_source
WHERE trip_potential_hex.ID = count_source.ID;

Upvotes: 1

wildplasser
wildplasser

Reputation: 44240

First step: create a temp VIEW instead of a table. (you won't need the index, and the view will be inlined):

CREATE TEMP VIEW libraries AS
 SELECT ID, osm_id, geom
 FROM received.osm_pois_p opp 
 WHERE fclass = 'library'
 ;

Upvotes: 1

Related Questions