I have been trying to figure out the query I need to calculate the potential total revenue value for a specific location. It's a training exercise.

I have created 3 tables:

• p_locations (with and id for each location and geometry),
• p_consumers (with id, geometry and consumer_type) and
• p_consumer_type (with consumer_type and a potential_value).

In total I have created 6 consumer_types with their own potential_value. As example I have the consumer types "healthy" with a potential_value of 350 euro and the consumer type "fast and easy" with a value of 600 euro.

In p_locations I have added 3 potential locations for a new business. For each of the locations I want to do a spatial query with a 1km search radius of consumers in the area.

In p_consumers I have added 3000 consumers with their geographical location and their customer type.

I have been able to create a query that provides me with the total number of consumers within a radius of 200 meters from the location with fid 3757.

select count(a.fid)
from p_consumers a, p_locations b  
Where b.fid = 3757 and (st_distance (a.geometry, b.geometry) <= 200);

I however want to know the total based on the following: total for the total number for each consumer_type in the radius times the potential_value.

Who is able to help me and provide some explaination. I want to write the result to a new table. I am using PGadmin with a PostgeSQL database.

As a result I want to know which consumers fall within the search parameters for each of the three locations. And I want the total potential_value based on the number of the consumers in each class times the potential_value for that class in the p_consumer_type table.

I have been trying to figure out the query I need to calculate the potential total revenue value for a specific location. It's a training exercise.

I have created 3 tables:

• p_locations (with and id for each location and geometry),
• p_consumers (with id, geometry and consumer_type) and
• p_consumer_type (with consumer_type and a potential_value).

In total I have created 6 consumer_types with their own potential_value. As example I have the consumer types "healthy" with a potential_value of 350 euro and the consumer type "fast and easy" with a value of 600 euro.

In p_locations I have added 3 potential locations for a new business. For each of the locations I want to do a spatial query with a 1km search radius of consumers in the area.

In p_consumers I have added 3000 consumers with their geographical location and their customer type.

I have been able to create a query that provides me with the total number of consumers within a radius of 200 meters from the location with fid 3757.

select count(a.fid)
from p_consumers a, p_locations b  
Where b.fid = 3757 and (st_distance (a.geometry, b.geometry) <= 200);

I however want to know the total based on the following: total for the total number for each consumer_type in the radius times the potential_value.

Who is able to help me and provide some explaination. I want to write the result to a new table. I am using PGadmin with a PostgeSQL database.

As a result I want to know which consumers fall within the search parameters for each of the three locations. And I want the total potential_value based on the number of the consumers in each class times the potential_value for that class in the p_consumer_type table.

• sql
• postgresql
• postgis

• Please add sample data amd expected result – Jens Commented Feb 16 at 11:49
• Sample data and desired results is much clearer when added in tabular format (rather than a text description). – Dale K Commented Feb 16 at 18:10
• 1 An st_dwithin(g1,g2,200) is faster than st_distance(g1,g2)<=200 if you did create index on p_consumers using gist(geometry); (same is recommended for the other geom column in the other table) – Zegarek Commented Feb 16 at 18:54
• 1 on a side note, never ever compute any distance in 3857, this projection highly distorts them as you move away from the equator. – JGH Commented Feb 17 at 13:01
• @JGH I think we share an allergy to that srid. It took me a moment to realise I'm looking at a random id of 3757, not necessarily a transformation to 3857. – Zegarek Commented Feb 17 at 13:21

2 Answers 2

You basically want to group by b.fid, like

select b.fid, count(a.fid)
from p_consumers a, p_locations b  
Where (st_distance (a.geometry, b.geometry) <= 200)
group by b.fid;

and if you want to insert this to a new table, you can do it like this:

insert into theothertable(b_fid, cnt)
select b.fid, count(a.fid)
from p_consumers a, p_locations b  
Where (st_distance (a.geometry, b.geometry) <= 200)
group by b.fid;

EDIT

As Charlieface correctly pointed out we can also use explicit joins, which would be my choice as well in general, here I complied to the style used in the question. A join-based solution would look like this:

insert into theothertable(b_fid, cnt)
select b.fid, count(a.fid)
from p_consumers a join p_locations b  
on (st_distance (a.geometry, b.geometry) <= 200)
group by b.fid;

You can work out the value of the customers in range of each location, with revenue totals per consumer_type as follows:

/*
Get all consumers in range of 1000m, and their value
“WITH…” creates a named sort of temp table called a Common Table Expression 
(“CTE”). You can then call it as if it was a real table. After the query 
has run it no longer exists. 
*/
WITH in_range AS (
    SELECT
        a.fid as fid_a,
        b.fid as fid_b,
        a.consumer_type,
        c.potential_value,
        ST_Distance(a.geometry, b.geometry) AS distance
    FROM
        p_consumers AS a
    CROSS JOIN
        p_locations AS b
    INNER JOIN
        p_consumer_type AS c
        ON a.consumer_type = c.consumer_type
    WHERE
        ST_Distance(a.geometry, b.geometry) <= 1000
    AND a.consumer_type IN ('healthy','fast and easy')
)
/*
Get the number of consumers in range per location 
and sum the value per consumer_type per location, using GROUP BY on 
both location and consumer_type
*/
SELECT
    fid_b AS location_fid,
    consumer_type,
    COUNT(fid_a) AS customers_in_range,
    SUM(potential_value) AS potential_customer_value
FROM    
    in_range
GROUP BY
    fid_b,
    consumer_type;