1

I have created the following query which is used to calculate a total from the results of a spatial query:

SELECT
  SUM(med_price)
FROM (SELECT
  ST_INTERSECTION(a.geom, (SELECT
    ST_SETSRID(geom, 4326)
  FROM catalogue_table
  WHERE id_number = '2211654')
  ) AS geom,
  small_price,
  med_price,
  large_price
FROM pricing_table AS a
WHERE ST_INTERSECTS(ST_SETSRID(a.geom, 4326), (SELECT
  ST_SETSRID(geom, 4326)
FROM catalogue_table
WHERE id_number = '2211654')
)) AS total_price

My tables look like this:

CREATE TABLE pricing_table
(
  gid serial NOT NULL,
  tile_id numeric,
  small_price numeric,
  med_price numeric,
  large_price numeric,
  geom geometry(MultiPolygon,4326)
)

And:

CREATE TABLE catalogue_table
(
  ogc_fid serial NOT NULL,
  geom geometry(Geometry,4326),
  id_number character varying,
  scale integer,
  scale_code character varying
)

I am trying to modify this query so that it calculates the total price for all rows, instead of just a single row with a specific id_number. However, whenever I try and modify my query to do this, I get:

ERROR: more than one row returned by a subquery used as an expression. SQL state: 21000

I would like to create a 'price' column on the catalogue table, and populate this with calculated prices, which, depending on the 'scale_code' from the catalogue_table (either small, medium or large) will calculate a total from the relevant column in the 'pricing_table'.

  • 1
    There is no point in calling ST_SetSRID() when both of the types, by the table are constrianed to SRID 4326. That said, using geometry srid:4326, seems a bit silly for most applications just use geography. – Evan Carroll Sep 26 '17 at 19:56
1

The trick here is to not to get overwhelmed by the fact that there is a spatial datatype involved. Then it becomes a fairly simple aggregate query over a join between the pricing table and the catalog table where the join just happens to be a spatial intersection.

SELECT c.id_number, SUM(med_price) 
FROM pricing_table p 
    INNER JOIN catalog_table c ON ST_INTERSECTS(ST_SETSRID(p.geom, 4326), ST_SETSRID(c.geom, 4326)) 
GROUP BY c.id_number

Then to incorporate the scaling factor into the sum you can use a case statement in a subquery, similar to the following

SELECT p.id_number, SUM(price)
FROM (
SELECT c.id_number, 
    CASE WHEN scaling_code = 'small' THEN small_price
        WHEN scale_code = 'medium' THEN med_price
        ELSE large_price
    END price
FROM pricing_table p 
    INNER JOIN catalog_table c ON ST_INTERSECTS(ST_SETSRID(p.geom, 4326), ST_SETSRID(c.geom, 4326)) 
    ) p
GROUP BY p.id_number

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.