zone-export-31052015.sql

DROP FUNCTION IF EXISTS get_category_zone(integer, text);
CREATE OR REPLACE FUNCTION get_category_zone(cat_id integer, zone_id text) RETURNS numeric AS $$
      SELECT 
            SUM(zone_list.count) 
      FROM (
            SELECT 
                  d.id,
                  d.zone,
                  (
                        SELECT COUNT(*)
                        FROM pins p0
                        JOIN demands d0 ON p0.demand_id = d0.id
                        WHERE ST_Distance(
                              ST_MakePoint(d.lat::numeric, d.long::numeric)::geometry,
                              ST_MakePoint(p0.lat::numeric, p0.long::numeric)::geometry
                        ) < 0.009
                        AND d0.category_id = cat_id
                        AND d0.created_at <= '2015-05-31'::timestamp
                  )
                  FROM districts d
            ) as zone_list
        WHERE zone_list.zone = zone_id
      $$LANGUAGE SQL;
      
SELECT 
      c0.id,
      c0.name,
      (
          SELECT count(*) from demands d1 where d1.category_id = c0.id 
          AND d1.created_at <= '2015-05-31'::timestamp
      ), 
      get_category_zone(c0.id, 'AP1') as AP1,
      get_category_zone(c0.id, 'AP2') as AP2,
      get_category_zone(c0.id, 'AP3') as AP3,
      get_category_zone(c0.id, 'AP4') as AP4,
      get_category_zone(c0.id, 'AP5') as AP5
FROM categories c0
GROUP BY 1