Symfony Profiler

SELECT
  t0.id AS id_1,
  t0.name AS name_2,
  t0.measure AS measure_3,
  t0.date AS date_4,
  t0.stats_id AS stats_id_5
FROM
  city_follow_up t0
WHERE
  t0.stats_id = ?

SELECT t0.id AS id_1, t0.name AS name_2, t0.measure AS measure_3, t0.date AS date_4, t0.stats_id AS stats_id_5 FROM city_follow_up t0 WHERE t0.stats_id = 26676;

"COMMIT"

"COMMIT";

SELECT
  s0_.id AS id_0,
  s0_.places_count AS places_count_1,
  s0_.emails_count AS emails_count_2,
  s0_.completion_percent AS completion_percent_3,
  s0_.emails_sent AS emails_sent_4,
  s0_.date AS date_5,
  s0_.names_count AS names_count_6,
  s0_.opening_hours_count AS opening_hours_count_7,
  s0_.website_count AS website_count_8,
  s0_.address_count AS address_count_9,
  s0_.siret_count AS siret_count_10,
  s0_.stats_id AS stats_id_11
FROM
  stats_history s0_
WHERE
  s0_.stats_id = ?
ORDER BY
  s0_.id DESC
LIMIT
  100

SELECT s0_.id AS id_0, s0_.places_count AS places_count_1, s0_.emails_count AS emails_count_2, s0_.completion_percent AS completion_percent_3, s0_.emails_sent AS emails_sent_4, s0_.date AS date_5, s0_.names_count AS names_count_6, s0_.opening_hours_count AS opening_hours_count_7, s0_.website_count AS website_count_8, s0_.address_count AS address_count_9, s0_.siret_count AS siret_count_10, s0_.stats_id AS stats_id_11 FROM stats_history s0_ WHERE s0_.stats_id = 26676 ORDER BY s0_.id DESC LIMIT 100;

SELECT
  t0.id AS id_1,
  t0.disappeared_list AS disappeared_list_2,
  t0.current_list AS current_list_3,
  t0.theme AS theme_4,
  t0.stats_id AS stats_id_5
FROM
  zone_places t0
WHERE
  t0.stats_id = ?

SELECT t0.id AS id_1, t0.disappeared_list AS disappeared_list_2, t0.current_list AS current_list_3, t0.theme AS theme_4, t0.stats_id AS stats_id_5 FROM zone_places t0 WHERE t0.stats_id = 26676;

SELECT
  p0_.osm_user AS osm_user_0,
  COUNT(p0_.id) AS sclr_1,
  AVG(
    (
      CASE WHEN p0_.has_opening_hours = 1 THEN
        1
      ELSE
        0
      END
    ) + (
      CASE WHEN p0_.has_address = 1 THEN
        1
      ELSE
        0
      END
    ) + (
      CASE WHEN p0_.has_website = 1 THEN
        1
      ELSE
        0
      END
    ) + (
      CASE WHEN p0_.has_wheelchair = 1 THEN
        1
      ELSE
        0
      END
    ) + (
      CASE WHEN p0_.has_note = 1 THEN
        1
      ELSE
        0
      END
    )
  ) / 5 * 100 AS sclr_2
FROM
  place p0_
WHERE
  p0_.osm_user IS NOT NULL
  AND p0_.osm_user <> ''
  AND p0_.stats_id = ?
GROUP BY
  p0_.osm_user
ORDER BY
  sclr_1 DESC
LIMIT
  100

SELECT p0_.osm_user AS osm_user_0, COUNT(p0_.id) AS sclr_1, AVG((CASE WHEN p0_.has_opening_hours = 1 THEN 1 ELSE 0 END) + (CASE WHEN p0_.has_address = 1 THEN 1 ELSE 0 END) + (CASE WHEN p0_.has_website = 1 THEN 1 ELSE 0 END) + (CASE WHEN p0_.has_wheelchair = 1 THEN 1 ELSE 0 END) + (CASE WHEN p0_.has_note = 1 THEN 1 ELSE 0 END)) / 5 * 100 AS sclr_2 FROM place p0_ WHERE p0_.osm_user IS NOT NULL AND p0_.osm_user <> '' AND p0_.stats_id = 26676 GROUP BY p0_.osm_user ORDER BY sclr_1 DESC LIMIT 100;

INSERT INTO action_log (
  type, data, created_at, from_url, who
)
VALUES
  (?, ?, ?, ?, ?)

INSERT INTO action_log (type, data, created_at, from_url, who) VALUES ('stats_de_ville', '{\"insee_code\":\"62096\",\"nom\":\"62096\"}', '2026-07-13 00:48:32', NULL, NULL);

SELECT
  s0_.id AS id_0,
  s0_.zone AS zone_1,
  s0_.completion_percent AS completion_percent_2,
  s0_.places_count AS places_count_3,
  s0_.avec_horaires AS avec_horaires_4,
  s0_.avec_adresse AS avec_adresse_5,
  s0_.avec_site AS avec_site_6,
  s0_.avec_accessibilite AS avec_accessibilite_7,
  s0_.avec_note AS avec_note_8,
  s0_.name AS name_9,
  s0_.population AS population_10,
  s0_.siren AS siren_11,
  s0_.code_epci AS code_epci_12,
  s0_.codes_postaux AS codes_postaux_13,
  s0_.date_created AS date_created_14,
  s0_.date_modified AS date_modified_15,
  s0_.avec_siret AS avec_siret_16,
  s0_.avec_name AS avec_name_17,
  s0_.avec_email AS avec_email_18,
  s0_.osm_data_date_min AS osm_data_date_min_19,
  s0_.osm_data_date_avg AS osm_data_date_avg_20,
  s0_.osm_data_date_max AS osm_data_date_max_21,
  s0_.budget_annuel AS budget_annuel_22,
  s0_.lat AS lat_23,
  s0_.lon AS lon_24,
  s0_.date_labourage_requested AS date_labourage_requested_25,
  s0_.date_labourage_done AS date_labourage_done_26,
  s0_.kind AS kind_27
FROM
  stats s0_
WHERE
  s0_.zone = ?
ORDER BY
  s0_.id ASC
LIMIT
  1

SELECT s0_.id AS id_0, s0_.zone AS zone_1, s0_.completion_percent AS completion_percent_2, s0_.places_count AS places_count_3, s0_.avec_horaires AS avec_horaires_4, s0_.avec_adresse AS avec_adresse_5, s0_.avec_site AS avec_site_6, s0_.avec_accessibilite AS avec_accessibilite_7, s0_.avec_note AS avec_note_8, s0_.name AS name_9, s0_.population AS population_10, s0_.siren AS siren_11, s0_.code_epci AS code_epci_12, s0_.codes_postaux AS codes_postaux_13, s0_.date_created AS date_created_14, s0_.date_modified AS date_modified_15, s0_.avec_siret AS avec_siret_16, s0_.avec_name AS avec_name_17, s0_.avec_email AS avec_email_18, s0_.osm_data_date_min AS osm_data_date_min_19, s0_.osm_data_date_avg AS osm_data_date_avg_20, s0_.osm_data_date_max AS osm_data_date_max_21, s0_.budget_annuel AS budget_annuel_22, s0_.lat AS lat_23, s0_.lon AS lon_24, s0_.date_labourage_requested AS date_labourage_requested_25, s0_.date_labourage_done AS date_labourage_done_26, s0_.kind AS kind_27 FROM stats s0_ WHERE s0_.zone = '62096' ORDER BY s0_.id ASC LIMIT 1;

SELECT
  COUNT(*)
FROM
  osm_object t0
WHERE
  t0.zone = ?

SELECT COUNT(*) FROM osm_object t0 WHERE t0.zone = '62096';

"START TRANSACTION"

"START TRANSACTION";

SELECT
  osm_type,
  osm_id :: text AS osm_id,
  ST_Y(
    ST_Centroid(m.geom)
  ) AS lat,
  ST_X(
    ST_Centroid(m.geom)
  ) AS lon,
  tags :: jsonb AS tags_json,
  version,
  osm_timestamp,
  changeset,
  osm_user,
  uid :: bigint AS uid,
  ST_AsGeoJSON(
    ST_Force2D(m.geom),
    6,
    0
  ) AS geom_geojson,
  COALESCE(
    ST_Length(
      ST_Intersection(
        ST_MakeValid(m.geom),
        bufc.buf
      ):: geography
    ),
    0
  ) AS length_m,
  CASE WHEN (tags -> 'highway') = 'proposed'
  AND exist(tags, 'proposed')
  AND (tags -> 'proposed') = 'cycleway' THEN
    'proposed_cycleway'
  WHEN (tags -> 'highway') = 'cycleway' THEN
    'separated'
  WHEN (tags -> 'highway') = 'busway' THEN
    'separated'
  WHEN (tags -> 'cycleway:both') IN (
    'track', 'opposite_track', 'separate'
  )
  AND exist(tags, 'highway')
  AND (tags -> 'highway') IS NOT NULL
  AND (tags -> 'highway') NOT IN ('cycleway', 'busway') THEN
    'separated'
  WHEN EXISTS (
    SELECT
      1
    FROM
      unnest(
        akeys(tags)
      ) k
    WHERE
      k = 'cycleway'
      OR k LIKE 'cycleway:%'
  )
  AND exist(tags, 'highway')
  AND (tags -> 'highway') IS NOT NULL
  AND (tags -> 'highway') NOT IN ('cycleway', 'busway') THEN
    'lane'
  WHEN (
    exist(tags, 'bicycle')
    AND (tags -> 'bicycle') IS NOT NULL
    AND (tags -> 'bicycle') <> 'no'
  )
  AND exist(tags, 'highway')
  AND (tags -> 'highway') IS NOT NULL
  AND (tags -> 'highway') NOT IN ('cycleway', 'busway')
  AND NOT (
    EXISTS (
      SELECT
        1
      FROM
        unnest(
          akeys(tags)
        ) k
      WHERE
        k = 'cycleway'
        OR k LIKE 'cycleway:%'
    )
  ) THEN
    'designated'
  ELSE
    NULL
  END AS cycleway_category
FROM
  osm_object_meta m CROSS
  JOIN (
    SELECT
      ST_MakeValid(
        ST_SetSRID(
          ST_GeomFromText(?),
          4326
        )
      ) AS poly
  ) z CROSS
  JOIN LATERAL (
    SELECT
      ST_Buffer(z.poly :: geography, ?):: geometry AS buf
  ) bufc
WHERE
  m.geom IS NOT NULL
  AND m.osm_type IN ('way', 'relation')
  AND ST_Intersects(m.geom, bufc.buf)
  AND (
    (tags -> 'highway') = 'proposed'
    AND exist(tags, 'proposed')
    AND (tags -> 'proposed') = 'cycleway'
    OR (tags -> 'highway') IN ('cycleway', 'busway')
    OR (
      EXISTS (
        SELECT
          1
        FROM
          unnest(
            akeys(tags)
          ) k
        WHERE
          k = 'cycleway'
          OR k LIKE 'cycleway:%'
      )
      AND exist(tags, 'highway')
      AND (tags -> 'highway') IS NOT NULL
      AND (tags -> 'highway') NOT IN ('cycleway', 'busway')
    )
    OR (
      (
        exist(tags, 'bicycle')
        AND (tags -> 'bicycle') IS NOT NULL
        AND (tags -> 'bicycle') <> 'no'
      )
      AND exist(tags, 'highway')
      AND (tags -> 'highway') IS NOT NULL
      AND (tags -> 'highway') NOT IN ('cycleway', 'busway')
    )
  )

SELECT osm_type, osm_id::text AS osm_id,
       ST_Y(ST_Centroid(m.geom)) AS lat, ST_X(ST_Centroid(m.geom)) AS lon,
       tags::jsonb AS tags_json, version, osm_timestamp, changeset, osm_user, uid::bigint AS uid,
       ST_AsGeoJSON(ST_Force2D(m.geom), 6, 0) AS geom_geojson,
       COALESCE(ST_Length(ST_Intersection(ST_MakeValid(m.geom), bufc.buf)::geography), 0) AS length_m,
       CASE
         WHEN (tags->'highway') = 'proposed' AND exist(tags, 'proposed') AND (tags->'proposed') = 'cycleway' THEN 'proposed_cycleway'
         WHEN (tags->'highway') = 'cycleway' THEN 'separated'
         WHEN (tags->'highway') = 'busway' THEN 'separated'
         WHEN (tags->'cycleway'MULTIPOLYGON(((3.010631 50.112806,3.009009 50.11168,3.008709 50.111813,3.007913 50.112331,3.005012 50.110354,3.004198 50.110705,3.003044 50.109735,3.00442 50.109033,3.003134 50.10813,3.005303 50.105976,3.004158 50.104656,3.002759 50.104438,2.997794 50.103613,2.996072 50.10327,2.996104 50.102673,2.995787 50.102735,2.995033 50.103058,2.994221 50.103512,2.993966 50.103616,2.993494 50.103846,2.993446 50.103948,2.993515 50.10432,2.993418 50.104598,2.99307 50.105032,2.992329 50.103897,2.990455 50.103834,2.989818 50.103807,2.990393 50.104921,2.989378 50.104679,2.986886 50.107289,2.985873 50.108839,2.985221 50.108763,2.98179 50.110109,2.982975 50.111382,2.98141 50.111873,2.980594 50.110789,2.979449 50.111278,2.979256 50.111155,2.9783 50.111598,2.978204 50.11192,2.977305 50.112033,2.976242 50.112152,2.975799 50.112247,2.972898 50.113634,2.972832 50.113686,2.972791 50.113888,2.972707 50.11454,2.972571 50.115412,2.972313 50.115907,2.970919 50.117486,2.970264 50.118072,2.970546 50.118351,2.967733 50.120834,2.966067 50.120064,2.963025 50.122951,2.962545 50.122992,2.961993 50.12301,2.961342 50.123011,2.96054 50.122948,2.959924 50.122873,2.958905 50.122728,2.958271 50.122617,2.957864 50.122596,2.957638 50.122605,2.955313 50.12284,2.954388 50.12288,2.954514 50.123623,2.954541 50.123923,2.956717 50.124378,2.962298 50.125535,2.96144 50.126695,2.959702 50.12637,2.959001 50.127353,2.958916 50.127517,2.960898 50.128023,2.961868 50.127501,2.964675 50.12897,2.964517 50.129194,2.964307 50.129432,2.963675 50.129937,2.965355 50.130742,2.967145 50.13144,2.967664 50.131224,2.970712 50.132711,2.971013 50.132492,2.972296 50.131489,2.973796 50.132174,2.972726 50.133212,2.972036 50.132932,2.969256 50.135109,2.971027 50.135953,2.972204 50.135139,2.973892 50.136073,2.978152 50.138487,2.977288 50.139251,2.976737 50.139801,2.976547 50.140037,2.97787 50.140414,2.978077 50.140233,2.981176 50.141471,2.980272 50.141767,2.981196 50.143246,2.98259 50.143531,2.984884 50.144135,2.985186 50.144195,2.98664 50.144559,2.987778 50.144873,2.988042 50.144996,2.987542 50.145168,2.985051 50.146125,2.985152 50.146219,2.985432 50.146353,2.985645 50.146531,2.985926 50.146663,2.98649 50.147065,2.988251 50.148223,2.988462 50.148401,2.990013 50.149469,2.992633 50.151161,2.99457 50.152428,2.99438 50.152597,2.995515 50.153349,2.995849 50.153123,2.996075 50.152922,2.996191 50.15273,2.996289 50.152374,2.996464 50.152225,2.9969 50.152078,2.997105 50.151812,2.997509 50.151698,2.997825 50.152233,3.000802 50.151231,3.003934 50.150193,3.003769 50.149754,3.003589 50.149221,3.003388 50.148841,3.003051 50.148363,3.002531 50.147568,3.001646 50.146374,3.001505 50.146057,3.001384 50.145883,3.00072 50.145086,3.00059 50.144884,3.000362 50.144438,3.000257 50.144295,3.000082 50.144172,2.999518 50.143977,2.99928 50.143877,2.999067 50.143698,2.998946 50.143485,2.998693 50.142953,2.998379 50.142476,2.998218 50.142353,2.997497 50.141859,2.997135 50.141562,2.996263 50.140755,2.995935 50.14039,2.995597 50.139913,2.994482 50.138229,2.994002 50.13755,2.993587 50.137075,2.995937 50.134539,2.996356 50.134581,2.997774 50.132928,2.999515 50.133296,3.001727 50.133753,3.001827 50.133312,3.003041 50.130065,3.000985 50.127653,3.000718 50.127308,3.001478 50.127167,3.001004 50.125661,3.002257 50.125454,3.002101 50.125163,3.002996 50.124985,3.00251 50.124086,3.00162 50.124263,3.001496 50.12405,3.001432 50.123847,3.001083 50.122973,3.000372 50.121286,3.000089 50.120635,2.999451 50.119025,3.000844 50.118792,3.000939 50.118006,3.003545 50.117162,3.006324 50.116205,3.009103 50.115314,3.009631 50.115648,3.010398 50.115231,3.010631 50.112806)))'') IN ('track', 'opposite_track', 'separate')
              AND exist(tags, 'highway')
              AND (tags->'highway') IS NOT NULL AND (tags->'highway') NOT IN ('cycleway', 'busway') THEN 'separated'
         WHEN EXISTS (SELECT 1 FROM unnest(akeys(tags)) k WHERE k = 'cycleway' OR k LIKE 'cycleway:%') AND exist(tags, 'highway')
              AND (tags->'highway') IS NOT NULL AND (tags->'highway') NOT IN ('cycleway', 'busway') THEN 'lane'
         WHEN (exist(tags, 'bicycle') AND (tags->'bicycle') IS NOT NULL AND (tags->'bicycle') <> 'no') AND exist(tags, 'highway')
              AND (tags->'highway') IS NOT NULL AND (tags->'highway') NOT IN ('cycleway', 'busway')
              AND NOT (EXISTS (SELECT 1 FROM unnest(akeys(tags)) k WHERE k = 'cycleway' OR k LIKE 'cycleway:%')) THEN 'designated'
         ELSE NULL
       END AS cycleway_category
FROM osm_object_meta m
CROSS JOIN (SELECT ST_MakeValid(ST_SetSRID(ST_GeomFromText(2000), 4326)) AS poly) z
CROSS JOIN LATERAL (SELECT ST_Buffer(z.poly::geography, ?)::geometry AS buf) bufc
WHERE m.geom IS NOT NULL
  AND m.osm_type IN ('way', 'relation')
  AND ST_Intersects(m.geom, bufc.buf)
  AND (
    (tags->'highway') = 'proposed' AND exist(tags, 'proposed') AND (tags->'proposed') = 'cycleway'
    OR (tags->'highway') IN ('cycleway', 'busway')
    OR (EXISTS (SELECT 1 FROM unnest(akeys(tags)) k WHERE k = 'cycleway' OR k LIKE 'cycleway:%') AND exist(tags, 'highway')
        AND (tags->'highway') IS NOT NULL AND (tags->'highway') NOT IN ('cycleway', 'busway'))
    OR ((exist(tags, 'bicycle') AND (tags->'bicycle') IS NOT NULL AND (tags->'bicycle') <> 'no') AND exist(tags, 'highway')
        AND (tags->'highway') IS NOT NULL AND (tags->'highway') NOT IN ('cycleway', 'busway'))
  )
;

SELECT
  osm_type,
  osm_id :: text AS osm_id,
  ST_Y(
    ST_Centroid(geom)
  ) AS lat,
  ST_X(
    ST_Centroid(geom)
  ) AS lon,
  tags :: jsonb AS tags_json,
  version,
  osm_timestamp,
  changeset,
  osm_user,
  uid :: bigint AS uid
FROM
  osm_object_meta m
WHERE
  geom IS NOT NULL
  AND ST_Intersects(
    m.geom,
    ST_GeomFromText(?, 4326)
  )
  AND (
    (tags -> 'shop') = 'vacant'
    OR EXISTS (
      SELECT
        1
      FROM
        unnest(
          akeys(tags)
        ) k
      WHERE
        k LIKE 'was:%'
    )
  )

SELECT osm_type, osm_id::text AS osm_id, ST_Y(ST_Centroid(geom)) AS lat, ST_X(ST_Centroid(geom)) AS lon,
            tags::jsonb AS tags_json, version, osm_timestamp, changeset, osm_user, uid::bigint AS uid
            FROM osm_object_meta m
            WHERE geom IS NOT NULL AND ST_Intersects(m.geom, ST_GeomFromText('MULTIPOLYGON(((3.010631 50.112806,3.009009 50.11168,3.008709 50.111813,3.007913 50.112331,3.005012 50.110354,3.004198 50.110705,3.003044 50.109735,3.00442 50.109033,3.003134 50.10813,3.005303 50.105976,3.004158 50.104656,3.002759 50.104438,2.997794 50.103613,2.996072 50.10327,2.996104 50.102673,2.995787 50.102735,2.995033 50.103058,2.994221 50.103512,2.993966 50.103616,2.993494 50.103846,2.993446 50.103948,2.993515 50.10432,2.993418 50.104598,2.99307 50.105032,2.992329 50.103897,2.990455 50.103834,2.989818 50.103807,2.990393 50.104921,2.989378 50.104679,2.986886 50.107289,2.985873 50.108839,2.985221 50.108763,2.98179 50.110109,2.982975 50.111382,2.98141 50.111873,2.980594 50.110789,2.979449 50.111278,2.979256 50.111155,2.9783 50.111598,2.978204 50.11192,2.977305 50.112033,2.976242 50.112152,2.975799 50.112247,2.972898 50.113634,2.972832 50.113686,2.972791 50.113888,2.972707 50.11454,2.972571 50.115412,2.972313 50.115907,2.970919 50.117486,2.970264 50.118072,2.970546 50.118351,2.967733 50.120834,2.966067 50.120064,2.963025 50.122951,2.962545 50.122992,2.961993 50.12301,2.961342 50.123011,2.96054 50.122948,2.959924 50.122873,2.958905 50.122728,2.958271 50.122617,2.957864 50.122596,2.957638 50.122605,2.955313 50.12284,2.954388 50.12288,2.954514 50.123623,2.954541 50.123923,2.956717 50.124378,2.962298 50.125535,2.96144 50.126695,2.959702 50.12637,2.959001 50.127353,2.958916 50.127517,2.960898 50.128023,2.961868 50.127501,2.964675 50.12897,2.964517 50.129194,2.964307 50.129432,2.963675 50.129937,2.965355 50.130742,2.967145 50.13144,2.967664 50.131224,2.970712 50.132711,2.971013 50.132492,2.972296 50.131489,2.973796 50.132174,2.972726 50.133212,2.972036 50.132932,2.969256 50.135109,2.971027 50.135953,2.972204 50.135139,2.973892 50.136073,2.978152 50.138487,2.977288 50.139251,2.976737 50.139801,2.976547 50.140037,2.97787 50.140414,2.978077 50.140233,2.981176 50.141471,2.980272 50.141767,2.981196 50.143246,2.98259 50.143531,2.984884 50.144135,2.985186 50.144195,2.98664 50.144559,2.987778 50.144873,2.988042 50.144996,2.987542 50.145168,2.985051 50.146125,2.985152 50.146219,2.985432 50.146353,2.985645 50.146531,2.985926 50.146663,2.98649 50.147065,2.988251 50.148223,2.988462 50.148401,2.990013 50.149469,2.992633 50.151161,2.99457 50.152428,2.99438 50.152597,2.995515 50.153349,2.995849 50.153123,2.996075 50.152922,2.996191 50.15273,2.996289 50.152374,2.996464 50.152225,2.9969 50.152078,2.997105 50.151812,2.997509 50.151698,2.997825 50.152233,3.000802 50.151231,3.003934 50.150193,3.003769 50.149754,3.003589 50.149221,3.003388 50.148841,3.003051 50.148363,3.002531 50.147568,3.001646 50.146374,3.001505 50.146057,3.001384 50.145883,3.00072 50.145086,3.00059 50.144884,3.000362 50.144438,3.000257 50.144295,3.000082 50.144172,2.999518 50.143977,2.99928 50.143877,2.999067 50.143698,2.998946 50.143485,2.998693 50.142953,2.998379 50.142476,2.998218 50.142353,2.997497 50.141859,2.997135 50.141562,2.996263 50.140755,2.995935 50.14039,2.995597 50.139913,2.994482 50.138229,2.994002 50.13755,2.993587 50.137075,2.995937 50.134539,2.996356 50.134581,2.997774 50.132928,2.999515 50.133296,3.001727 50.133753,3.001827 50.133312,3.003041 50.130065,3.000985 50.127653,3.000718 50.127308,3.001478 50.127167,3.001004 50.125661,3.002257 50.125454,3.002101 50.125163,3.002996 50.124985,3.00251 50.124086,3.00162 50.124263,3.001496 50.12405,3.001432 50.123847,3.001083 50.122973,3.000372 50.121286,3.000089 50.120635,2.999451 50.119025,3.000844 50.118792,3.000939 50.118006,3.003545 50.117162,3.006324 50.116205,3.009103 50.115314,3.009631 50.115648,3.010398 50.115231,3.010631 50.112806)))', 4326))
            AND (
              (tags->'shop') = 'vacant'
              OR EXISTS (SELECT 1 FROM unnest(akeys(tags)) k WHERE k LIKE 'was:%')
            );

SELECT
  ST_AsText(
    ST_Multi(geom)
  ) AS wkt
FROM
  osm_object_meta m
WHERE
  osm_type = 'relation'
  AND geom IS NOT NULL
  AND (tags -> 'ref:INSEE') = ?
  AND (tags -> 'admin_level') = '8'
  AND (tags -> 'boundary') = 'administrative'
ORDER BY
  ST_Area(geom :: geography) DESC NULLS LAST
LIMIT
  1

SELECT ST_AsText(ST_Multi(geom)) AS wkt
FROM osm_object_meta m
WHERE osm_type = 'relation'
  AND geom IS NOT NULL
  AND (tags->'ref'62096'') = ?
  AND (tags->'admin_level') = '8'
  AND (tags->'boundary') = 'administrative'
ORDER BY ST_Area(geom::geography) DESC NULLS LAST
LIMIT 1;

SELECT
  COUNT(*)
FROM
  information_schema.tables
WHERE
  table_schema = 'public'
  AND table_name = 'osm_object_meta'

SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'osm_object_meta';

SELECT
  osm_type,
  osm_id :: text AS osm_id,
  ST_Y(
    ST_Centroid(geom)
  ) AS lat,
  ST_X(
    ST_Centroid(geom)
  ) AS lon,
  tags :: jsonb AS tags_json,
  version,
  osm_timestamp,
  changeset,
  osm_user,
  uid :: bigint AS uid
FROM
  osm_object_meta m
WHERE
  geom IS NOT NULL
  AND ST_Intersects(
    m.geom,
    ST_GeomFromText(?, 4326)
  )
  AND (tags -> 'man_made') = 'monitoring_station'
  AND (tags -> 'monitoring:bicycle') = 'yes'

SELECT osm_type, osm_id::text AS osm_id,
       ST_Y(ST_Centroid(geom)) AS lat, ST_X(ST_Centroid(geom)) AS lon,
       tags::jsonb AS tags_json, version, osm_timestamp, changeset, osm_user, uid::bigint AS uid
FROM osm_object_meta m
WHERE geom IS NOT NULL
  AND ST_Intersects(m.geom, ST_GeomFromText('MULTIPOLYGON(((3.010631 50.112806,3.009009 50.11168,3.008709 50.111813,3.007913 50.112331,3.005012 50.110354,3.004198 50.110705,3.003044 50.109735,3.00442 50.109033,3.003134 50.10813,3.005303 50.105976,3.004158 50.104656,3.002759 50.104438,2.997794 50.103613,2.996072 50.10327,2.996104 50.102673,2.995787 50.102735,2.995033 50.103058,2.994221 50.103512,2.993966 50.103616,2.993494 50.103846,2.993446 50.103948,2.993515 50.10432,2.993418 50.104598,2.99307 50.105032,2.992329 50.103897,2.990455 50.103834,2.989818 50.103807,2.990393 50.104921,2.989378 50.104679,2.986886 50.107289,2.985873 50.108839,2.985221 50.108763,2.98179 50.110109,2.982975 50.111382,2.98141 50.111873,2.980594 50.110789,2.979449 50.111278,2.979256 50.111155,2.9783 50.111598,2.978204 50.11192,2.977305 50.112033,2.976242 50.112152,2.975799 50.112247,2.972898 50.113634,2.972832 50.113686,2.972791 50.113888,2.972707 50.11454,2.972571 50.115412,2.972313 50.115907,2.970919 50.117486,2.970264 50.118072,2.970546 50.118351,2.967733 50.120834,2.966067 50.120064,2.963025 50.122951,2.962545 50.122992,2.961993 50.12301,2.961342 50.123011,2.96054 50.122948,2.959924 50.122873,2.958905 50.122728,2.958271 50.122617,2.957864 50.122596,2.957638 50.122605,2.955313 50.12284,2.954388 50.12288,2.954514 50.123623,2.954541 50.123923,2.956717 50.124378,2.962298 50.125535,2.96144 50.126695,2.959702 50.12637,2.959001 50.127353,2.958916 50.127517,2.960898 50.128023,2.961868 50.127501,2.964675 50.12897,2.964517 50.129194,2.964307 50.129432,2.963675 50.129937,2.965355 50.130742,2.967145 50.13144,2.967664 50.131224,2.970712 50.132711,2.971013 50.132492,2.972296 50.131489,2.973796 50.132174,2.972726 50.133212,2.972036 50.132932,2.969256 50.135109,2.971027 50.135953,2.972204 50.135139,2.973892 50.136073,2.978152 50.138487,2.977288 50.139251,2.976737 50.139801,2.976547 50.140037,2.97787 50.140414,2.978077 50.140233,2.981176 50.141471,2.980272 50.141767,2.981196 50.143246,2.98259 50.143531,2.984884 50.144135,2.985186 50.144195,2.98664 50.144559,2.987778 50.144873,2.988042 50.144996,2.987542 50.145168,2.985051 50.146125,2.985152 50.146219,2.985432 50.146353,2.985645 50.146531,2.985926 50.146663,2.98649 50.147065,2.988251 50.148223,2.988462 50.148401,2.990013 50.149469,2.992633 50.151161,2.99457 50.152428,2.99438 50.152597,2.995515 50.153349,2.995849 50.153123,2.996075 50.152922,2.996191 50.15273,2.996289 50.152374,2.996464 50.152225,2.9969 50.152078,2.997105 50.151812,2.997509 50.151698,2.997825 50.152233,3.000802 50.151231,3.003934 50.150193,3.003769 50.149754,3.003589 50.149221,3.003388 50.148841,3.003051 50.148363,3.002531 50.147568,3.001646 50.146374,3.001505 50.146057,3.001384 50.145883,3.00072 50.145086,3.00059 50.144884,3.000362 50.144438,3.000257 50.144295,3.000082 50.144172,2.999518 50.143977,2.99928 50.143877,2.999067 50.143698,2.998946 50.143485,2.998693 50.142953,2.998379 50.142476,2.998218 50.142353,2.997497 50.141859,2.997135 50.141562,2.996263 50.140755,2.995935 50.14039,2.995597 50.139913,2.994482 50.138229,2.994002 50.13755,2.993587 50.137075,2.995937 50.134539,2.996356 50.134581,2.997774 50.132928,2.999515 50.133296,3.001727 50.133753,3.001827 50.133312,3.003041 50.130065,3.000985 50.127653,3.000718 50.127308,3.001478 50.127167,3.001004 50.125661,3.002257 50.125454,3.002101 50.125163,3.002996 50.124985,3.00251 50.124086,3.00162 50.124263,3.001496 50.12405,3.001432 50.123847,3.001083 50.122973,3.000372 50.121286,3.000089 50.120635,2.999451 50.119025,3.000844 50.118792,3.000939 50.118006,3.003545 50.117162,3.006324 50.116205,3.009103 50.115314,3.009631 50.115648,3.010398 50.115231,3.010631 50.112806)))', 4326))
  AND (tags->'man_made') = 'monitoring_station'
  AND (tags->'monitoring:bicycle') = 'yes';

SELECT
  osm_type,
  osm_id :: text AS osm_id,
  ST_Y(
    ST_Centroid(m.geom)
  ) AS lat,
  ST_X(
    ST_Centroid(m.geom)
  ) AS lon,
  tags :: jsonb AS tags_json,
  version,
  osm_timestamp,
  changeset,
  osm_user,
  uid :: bigint AS uid,
  ST_AsGeoJSON(
    ST_Force2D(m.geom),
    6,
    0
  ) AS geom_geojson,
  COALESCE(
    ST_Length(
      ST_Intersection(
        ST_MakeValid(m.geom),
        z.poly
      ):: geography
    ),
    0
  ) AS length_m
FROM
  osm_object_meta m CROSS
  JOIN (
    SELECT
      ST_MakeValid(
        ST_SetSRID(
          ST_GeomFromText(?),
          4326
        )
      ) AS poly
  ) z
WHERE
  m.geom IS NOT NULL
  AND m.osm_type IN ('way', 'relation')
  AND ST_Intersects(m.geom, z.poly)
  AND exist(tags, 'highway')
  AND (tags -> 'highway') NOT IN (
    'motorway', 'motorway_link', 'trunk',
    'trunk_link', 'footway', 'steps',
    'pedestrian', 'bridleway', 'raceway',
    'cycleway', 'busway', 'construction',
    'proposed'
  )
  AND (tags -> 'oneway') IN ('yes', '-1', '1')
  AND NOT (
    (tags -> 'highway') IN ('cycleway', 'busway')
    OR (
      EXISTS (
        SELECT
          1
        FROM
          unnest(
            akeys(tags)
          ) k
        WHERE
          k = 'cycleway'
          OR k LIKE 'cycleway:%'
      )
      AND exist(tags, 'highway')
      AND (tags -> 'highway') IS NOT NULL
      AND (tags -> 'highway') NOT IN ('cycleway', 'busway')
    )
    OR (
      (
        exist(tags, 'bicycle')
        AND (tags -> 'bicycle') IS NOT NULL
        AND (tags -> 'bicycle') <> 'no'
      )
      AND exist(tags, 'highway')
      AND (tags -> 'highway') IS NOT NULL
      AND (tags -> 'highway') NOT IN ('cycleway', 'busway')
      AND NOT (
        EXISTS (
          SELECT
            1
          FROM
            unnest(
              akeys(tags)
            ) k
          WHERE
            k = 'cycleway'
            OR k LIKE 'cycleway:%'
        )
      )
    )
  )
  AND NOT (
    (tags -> 'oneway:bicycle') = 'no'
  )

SELECT osm_type, osm_id::text AS osm_id,
       ST_Y(ST_Centroid(m.geom)) AS lat, ST_X(ST_Centroid(m.geom)) AS lon,
       tags::jsonb AS tags_json, version, osm_timestamp, changeset, osm_user, uid::bigint AS uid,
       ST_AsGeoJSON(ST_Force2D(m.geom), 6, 0) AS geom_geojson,
       COALESCE(ST_Length(ST_Intersection(ST_MakeValid(m.geom), z.poly)::geography), 0) AS length_m
FROM osm_object_meta m
CROSS JOIN (SELECT ST_MakeValid(ST_SetSRID(ST_GeomFromText('MULTIPOLYGON(((3.010631 50.112806,3.009009 50.11168,3.008709 50.111813,3.007913 50.112331,3.005012 50.110354,3.004198 50.110705,3.003044 50.109735,3.00442 50.109033,3.003134 50.10813,3.005303 50.105976,3.004158 50.104656,3.002759 50.104438,2.997794 50.103613,2.996072 50.10327,2.996104 50.102673,2.995787 50.102735,2.995033 50.103058,2.994221 50.103512,2.993966 50.103616,2.993494 50.103846,2.993446 50.103948,2.993515 50.10432,2.993418 50.104598,2.99307 50.105032,2.992329 50.103897,2.990455 50.103834,2.989818 50.103807,2.990393 50.104921,2.989378 50.104679,2.986886 50.107289,2.985873 50.108839,2.985221 50.108763,2.98179 50.110109,2.982975 50.111382,2.98141 50.111873,2.980594 50.110789,2.979449 50.111278,2.979256 50.111155,2.9783 50.111598,2.978204 50.11192,2.977305 50.112033,2.976242 50.112152,2.975799 50.112247,2.972898 50.113634,2.972832 50.113686,2.972791 50.113888,2.972707 50.11454,2.972571 50.115412,2.972313 50.115907,2.970919 50.117486,2.970264 50.118072,2.970546 50.118351,2.967733 50.120834,2.966067 50.120064,2.963025 50.122951,2.962545 50.122992,2.961993 50.12301,2.961342 50.123011,2.96054 50.122948,2.959924 50.122873,2.958905 50.122728,2.958271 50.122617,2.957864 50.122596,2.957638 50.122605,2.955313 50.12284,2.954388 50.12288,2.954514 50.123623,2.954541 50.123923,2.956717 50.124378,2.962298 50.125535,2.96144 50.126695,2.959702 50.12637,2.959001 50.127353,2.958916 50.127517,2.960898 50.128023,2.961868 50.127501,2.964675 50.12897,2.964517 50.129194,2.964307 50.129432,2.963675 50.129937,2.965355 50.130742,2.967145 50.13144,2.967664 50.131224,2.970712 50.132711,2.971013 50.132492,2.972296 50.131489,2.973796 50.132174,2.972726 50.133212,2.972036 50.132932,2.969256 50.135109,2.971027 50.135953,2.972204 50.135139,2.973892 50.136073,2.978152 50.138487,2.977288 50.139251,2.976737 50.139801,2.976547 50.140037,2.97787 50.140414,2.978077 50.140233,2.981176 50.141471,2.980272 50.141767,2.981196 50.143246,2.98259 50.143531,2.984884 50.144135,2.985186 50.144195,2.98664 50.144559,2.987778 50.144873,2.988042 50.144996,2.987542 50.145168,2.985051 50.146125,2.985152 50.146219,2.985432 50.146353,2.985645 50.146531,2.985926 50.146663,2.98649 50.147065,2.988251 50.148223,2.988462 50.148401,2.990013 50.149469,2.992633 50.151161,2.99457 50.152428,2.99438 50.152597,2.995515 50.153349,2.995849 50.153123,2.996075 50.152922,2.996191 50.15273,2.996289 50.152374,2.996464 50.152225,2.9969 50.152078,2.997105 50.151812,2.997509 50.151698,2.997825 50.152233,3.000802 50.151231,3.003934 50.150193,3.003769 50.149754,3.003589 50.149221,3.003388 50.148841,3.003051 50.148363,3.002531 50.147568,3.001646 50.146374,3.001505 50.146057,3.001384 50.145883,3.00072 50.145086,3.00059 50.144884,3.000362 50.144438,3.000257 50.144295,3.000082 50.144172,2.999518 50.143977,2.99928 50.143877,2.999067 50.143698,2.998946 50.143485,2.998693 50.142953,2.998379 50.142476,2.998218 50.142353,2.997497 50.141859,2.997135 50.141562,2.996263 50.140755,2.995935 50.14039,2.995597 50.139913,2.994482 50.138229,2.994002 50.13755,2.993587 50.137075,2.995937 50.134539,2.996356 50.134581,2.997774 50.132928,2.999515 50.133296,3.001727 50.133753,3.001827 50.133312,3.003041 50.130065,3.000985 50.127653,3.000718 50.127308,3.001478 50.127167,3.001004 50.125661,3.002257 50.125454,3.002101 50.125163,3.002996 50.124985,3.00251 50.124086,3.00162 50.124263,3.001496 50.12405,3.001432 50.123847,3.001083 50.122973,3.000372 50.121286,3.000089 50.120635,2.999451 50.119025,3.000844 50.118792,3.000939 50.118006,3.003545 50.117162,3.006324 50.116205,3.009103 50.115314,3.009631 50.115648,3.010398 50.115231,3.010631 50.112806)))'), 4326)) AS poly) z
WHERE m.geom IS NOT NULL
  AND m.osm_type IN ('way', 'relation')
  AND ST_Intersects(m.geom, z.poly)
  AND exist(tags, 'highway')
  AND (tags->'highway') NOT IN (
    'motorway', 'motorway_link', 'trunk', 'trunk_link',
    'footway', 'steps', 'pedestrian', 'bridleway', 'raceway',
    'cycleway', 'busway', 'construction', 'proposed'
  )
  AND (tags->'oneway') IN ('yes', '-1', '1')
  AND NOT ( (tags->'highway') IN ('cycleway', 'busway')
    OR (EXISTS (SELECT 1 FROM unnest(akeys(tags)) k WHERE k = 'cycleway' OR k LIKE 'cycleway:%') AND exist(tags, 'highway') AND (tags->'highway') IS NOT NULL AND (tags->'highway') NOT IN ('cycleway', 'busway'))
    OR ((exist(tags, 'bicycle') AND (tags->'bicycle') IS NOT NULL AND (tags->'bicycle') <> 'no') AND exist(tags, 'highway') AND (tags->'highway') IS NOT NULL AND (tags->'highway') NOT IN ('cycleway', 'busway') AND NOT (EXISTS (SELECT 1 FROM unnest(akeys(tags)) k WHERE k = 'cycleway' OR k LIKE 'cycleway:%'))))
  AND NOT ((tags->'oneway:bicycle') = 'no')
;

SELECT
  COUNT(*)
FROM
  information_schema.tables
WHERE
  table_schema = 'public'
  AND table_name = 'osm_object_history'

SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'osm_object_history';

SELECT
  COUNT(*)
FROM
  information_schema.tables
WHERE
  table_schema = ?
  AND table_name = ?

SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'osm_object_meta';

SELECT
  osm_type,
  osm_id :: text AS osm_id,
  ST_Y(
    ST_Centroid(geom)
  ) AS lat,
  ST_X(
    ST_Centroid(geom)
  ) AS lon,
  tags :: jsonb AS tags_json,
  version,
  osm_timestamp,
  changeset,
  osm_user,
  uid :: bigint AS uid,
  ST_AsGeoJSON(
    ST_Force2D(geom),
    6,
    0
  ) AS geom_geojson
FROM
  osm_object_meta m
WHERE
  geom IS NOT NULL
  AND ST_Intersects(
    m.geom,
    ST_GeomFromText(?, 4326)
  )
  AND osm_type IN ('way', 'relation')
  AND (tags -> 'highway') IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

SELECT osm_type, osm_id::text AS osm_id,
                ST_Y(ST_Centroid(geom)) AS lat, ST_X(ST_Centroid(geom)) AS lon,
                tags::jsonb AS tags_json, version, osm_timestamp, changeset, osm_user, uid::bigint AS uid,
                ST_AsGeoJSON(ST_Force2D(geom), 6, 0) AS geom_geojson
            FROM osm_object_meta m
            WHERE geom IS NOT NULL AND ST_Intersects(m.geom, ST_GeomFromText('MULTIPOLYGON(((3.010631 50.112806,3.009009 50.11168,3.008709 50.111813,3.007913 50.112331,3.005012 50.110354,3.004198 50.110705,3.003044 50.109735,3.00442 50.109033,3.003134 50.10813,3.005303 50.105976,3.004158 50.104656,3.002759 50.104438,2.997794 50.103613,2.996072 50.10327,2.996104 50.102673,2.995787 50.102735,2.995033 50.103058,2.994221 50.103512,2.993966 50.103616,2.993494 50.103846,2.993446 50.103948,2.993515 50.10432,2.993418 50.104598,2.99307 50.105032,2.992329 50.103897,2.990455 50.103834,2.989818 50.103807,2.990393 50.104921,2.989378 50.104679,2.986886 50.107289,2.985873 50.108839,2.985221 50.108763,2.98179 50.110109,2.982975 50.111382,2.98141 50.111873,2.980594 50.110789,2.979449 50.111278,2.979256 50.111155,2.9783 50.111598,2.978204 50.11192,2.977305 50.112033,2.976242 50.112152,2.975799 50.112247,2.972898 50.113634,2.972832 50.113686,2.972791 50.113888,2.972707 50.11454,2.972571 50.115412,2.972313 50.115907,2.970919 50.117486,2.970264 50.118072,2.970546 50.118351,2.967733 50.120834,2.966067 50.120064,2.963025 50.122951,2.962545 50.122992,2.961993 50.12301,2.961342 50.123011,2.96054 50.122948,2.959924 50.122873,2.958905 50.122728,2.958271 50.122617,2.957864 50.122596,2.957638 50.122605,2.955313 50.12284,2.954388 50.12288,2.954514 50.123623,2.954541 50.123923,2.956717 50.124378,2.962298 50.125535,2.96144 50.126695,2.959702 50.12637,2.959001 50.127353,2.958916 50.127517,2.960898 50.128023,2.961868 50.127501,2.964675 50.12897,2.964517 50.129194,2.964307 50.129432,2.963675 50.129937,2.965355 50.130742,2.967145 50.13144,2.967664 50.131224,2.970712 50.132711,2.971013 50.132492,2.972296 50.131489,2.973796 50.132174,2.972726 50.133212,2.972036 50.132932,2.969256 50.135109,2.971027 50.135953,2.972204 50.135139,2.973892 50.136073,2.978152 50.138487,2.977288 50.139251,2.976737 50.139801,2.976547 50.140037,2.97787 50.140414,2.978077 50.140233,2.981176 50.141471,2.980272 50.141767,2.981196 50.143246,2.98259 50.143531,2.984884 50.144135,2.985186 50.144195,2.98664 50.144559,2.987778 50.144873,2.988042 50.144996,2.987542 50.145168,2.985051 50.146125,2.985152 50.146219,2.985432 50.146353,2.985645 50.146531,2.985926 50.146663,2.98649 50.147065,2.988251 50.148223,2.988462 50.148401,2.990013 50.149469,2.992633 50.151161,2.99457 50.152428,2.99438 50.152597,2.995515 50.153349,2.995849 50.153123,2.996075 50.152922,2.996191 50.15273,2.996289 50.152374,2.996464 50.152225,2.9969 50.152078,2.997105 50.151812,2.997509 50.151698,2.997825 50.152233,3.000802 50.151231,3.003934 50.150193,3.003769 50.149754,3.003589 50.149221,3.003388 50.148841,3.003051 50.148363,3.002531 50.147568,3.001646 50.146374,3.001505 50.146057,3.001384 50.145883,3.00072 50.145086,3.00059 50.144884,3.000362 50.144438,3.000257 50.144295,3.000082 50.144172,2.999518 50.143977,2.99928 50.143877,2.999067 50.143698,2.998946 50.143485,2.998693 50.142953,2.998379 50.142476,2.998218 50.142353,2.997497 50.141859,2.997135 50.141562,2.996263 50.140755,2.995935 50.14039,2.995597 50.139913,2.994482 50.138229,2.994002 50.13755,2.993587 50.137075,2.995937 50.134539,2.996356 50.134581,2.997774 50.132928,2.999515 50.133296,3.001727 50.133753,3.001827 50.133312,3.003041 50.130065,3.000985 50.127653,3.000718 50.127308,3.001478 50.127167,3.001004 50.125661,3.002257 50.125454,3.002101 50.125163,3.002996 50.124985,3.00251 50.124086,3.00162 50.124263,3.001496 50.12405,3.001432 50.123847,3.001083 50.122973,3.000372 50.121286,3.000089 50.120635,2.999451 50.119025,3.000844 50.118792,3.000939 50.118006,3.003545 50.117162,3.006324 50.116205,3.009103 50.115314,3.009631 50.115648,3.010398 50.115231,3.010631 50.112806)))', 4326))
              AND osm_type IN ('way', 'relation')
              AND (tags->'highway') IN ('motorway','trunk','primary','secondary','tertiary','unclassified','residential','living_street','motorway_link','trunk_link','primary_link','secondary_link','tertiary_link','service','road');

WITH
  zone AS (
    SELECT
      ST_GeomFromText(?, 4326) AS g
  ),
  in_zone AS (
    SELECT
      h.osm_type,
      h.osm_id,
      h.version,
      h.valid_from,
      h.visible
    FROM
      osm_object_history h,
      zone z
    WHERE
      h.geom IS NOT NULL
      AND ST_Intersects(h.geom, z.g)
  ),
  creations AS (
    SELECT
      (valid_from AT TIME ZONE 'UTC'):: date AS d
    FROM
      in_zone
    WHERE
      version = 1
      AND visible = true
      AND valid_from >= NOW() - (? || ' days'):: interval
  ),
  deletions AS (
    SELECT
      (del.valid_from AT TIME ZONE 'UTC'):: date AS d
    FROM
      osm_object_history del
      JOIN in_zone prev ON prev.osm_type = del.osm_type
      AND prev.osm_id = del.osm_id
      AND prev.version = del.version - 1
    WHERE
      del.visible = false
      AND del.valid_from >= NOW() - (? || ' days'):: interval
  )
SELECT
  d :: text AS date,
  SUM(n_creations):: int AS creations,
  SUM(n_deletions):: int AS deletions
FROM
  (
    SELECT
      d,
      COUNT(*) AS n_creations,
      0 AS n_deletions
    FROM
      creations
    GROUP BY
      d
    UNION ALL
    SELECT
      d,
      0,
      COUNT(*)
    FROM
      deletions
    GROUP BY
      d
  ) u
GROUP BY
  d
ORDER BY
  d

WITH zone AS (SELECT ST_GeomFromText('MULTIPOLYGON(((3.010631 50.112806,3.009009 50.11168,3.008709 50.111813,3.007913 50.112331,3.005012 50.110354,3.004198 50.110705,3.003044 50.109735,3.00442 50.109033,3.003134 50.10813,3.005303 50.105976,3.004158 50.104656,3.002759 50.104438,2.997794 50.103613,2.996072 50.10327,2.996104 50.102673,2.995787 50.102735,2.995033 50.103058,2.994221 50.103512,2.993966 50.103616,2.993494 50.103846,2.993446 50.103948,2.993515 50.10432,2.993418 50.104598,2.99307 50.105032,2.992329 50.103897,2.990455 50.103834,2.989818 50.103807,2.990393 50.104921,2.989378 50.104679,2.986886 50.107289,2.985873 50.108839,2.985221 50.108763,2.98179 50.110109,2.982975 50.111382,2.98141 50.111873,2.980594 50.110789,2.979449 50.111278,2.979256 50.111155,2.9783 50.111598,2.978204 50.11192,2.977305 50.112033,2.976242 50.112152,2.975799 50.112247,2.972898 50.113634,2.972832 50.113686,2.972791 50.113888,2.972707 50.11454,2.972571 50.115412,2.972313 50.115907,2.970919 50.117486,2.970264 50.118072,2.970546 50.118351,2.967733 50.120834,2.966067 50.120064,2.963025 50.122951,2.962545 50.122992,2.961993 50.12301,2.961342 50.123011,2.96054 50.122948,2.959924 50.122873,2.958905 50.122728,2.958271 50.122617,2.957864 50.122596,2.957638 50.122605,2.955313 50.12284,2.954388 50.12288,2.954514 50.123623,2.954541 50.123923,2.956717 50.124378,2.962298 50.125535,2.96144 50.126695,2.959702 50.12637,2.959001 50.127353,2.958916 50.127517,2.960898 50.128023,2.961868 50.127501,2.964675 50.12897,2.964517 50.129194,2.964307 50.129432,2.963675 50.129937,2.965355 50.130742,2.967145 50.13144,2.967664 50.131224,2.970712 50.132711,2.971013 50.132492,2.972296 50.131489,2.973796 50.132174,2.972726 50.133212,2.972036 50.132932,2.969256 50.135109,2.971027 50.135953,2.972204 50.135139,2.973892 50.136073,2.978152 50.138487,2.977288 50.139251,2.976737 50.139801,2.976547 50.140037,2.97787 50.140414,2.978077 50.140233,2.981176 50.141471,2.980272 50.141767,2.981196 50.143246,2.98259 50.143531,2.984884 50.144135,2.985186 50.144195,2.98664 50.144559,2.987778 50.144873,2.988042 50.144996,2.987542 50.145168,2.985051 50.146125,2.985152 50.146219,2.985432 50.146353,2.985645 50.146531,2.985926 50.146663,2.98649 50.147065,2.988251 50.148223,2.988462 50.148401,2.990013 50.149469,2.992633 50.151161,2.99457 50.152428,2.99438 50.152597,2.995515 50.153349,2.995849 50.153123,2.996075 50.152922,2.996191 50.15273,2.996289 50.152374,2.996464 50.152225,2.9969 50.152078,2.997105 50.151812,2.997509 50.151698,2.997825 50.152233,3.000802 50.151231,3.003934 50.150193,3.003769 50.149754,3.003589 50.149221,3.003388 50.148841,3.003051 50.148363,3.002531 50.147568,3.001646 50.146374,3.001505 50.146057,3.001384 50.145883,3.00072 50.145086,3.00059 50.144884,3.000362 50.144438,3.000257 50.144295,3.000082 50.144172,2.999518 50.143977,2.99928 50.143877,2.999067 50.143698,2.998946 50.143485,2.998693 50.142953,2.998379 50.142476,2.998218 50.142353,2.997497 50.141859,2.997135 50.141562,2.996263 50.140755,2.995935 50.14039,2.995597 50.139913,2.994482 50.138229,2.994002 50.13755,2.993587 50.137075,2.995937 50.134539,2.996356 50.134581,2.997774 50.132928,2.999515 50.133296,3.001727 50.133753,3.001827 50.133312,3.003041 50.130065,3.000985 50.127653,3.000718 50.127308,3.001478 50.127167,3.001004 50.125661,3.002257 50.125454,3.002101 50.125163,3.002996 50.124985,3.00251 50.124086,3.00162 50.124263,3.001496 50.12405,3.001432 50.123847,3.001083 50.122973,3.000372 50.121286,3.000089 50.120635,2.999451 50.119025,3.000844 50.118792,3.000939 50.118006,3.003545 50.117162,3.006324 50.116205,3.009103 50.115314,3.009631 50.115648,3.010398 50.115231,3.010631 50.112806)))', 4326) AS g),
in_zone AS (
  SELECT h.osm_type, h.osm_id, h.version, h.valid_from, h.visible
  FROM osm_object_history h, zone z
  WHERE h.geom IS NOT NULL AND ST_Intersects(h.geom, z.g)
),
creations AS (
  SELECT (valid_from AT TIME ZONE 'UTC')::date AS d
  FROM in_zone
  WHERE version = 1 AND visible = true AND valid_from >= NOW() - ('30' || ' days')::interval
),
deletions AS (
  SELECT (del.valid_from AT TIME ZONE 'UTC')::date AS d
  FROM osm_object_history del
  JOIN in_zone prev
    ON prev.osm_type = del.osm_type
   AND prev.osm_id   = del.osm_id
   AND prev.version  = del.version - 1
  WHERE del.visible = false
    AND del.valid_from >= NOW() - ('30' || ' days')::interval
)
SELECT d::text AS date,
       SUM(n_creations)::int AS creations,
       SUM(n_deletions)::int AS deletions
FROM (
  SELECT d, COUNT(*) AS n_creations, 0 AS n_deletions FROM creations GROUP BY d
  UNION ALL
  SELECT d, 0, COUNT(*) FROM deletions GROUP BY d
) u
GROUP BY d
ORDER BY d;