Symfony Profiler

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

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

"COMMIT"

"COMMIT";

"START TRANSACTION"

"START TRANSACTION";

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 = 27690;

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 = 27690 ORDER BY s0_.id 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 = ?
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 = 27690 GROUP BY p0_.osm_user ORDER BY sclr_1 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 = 27690;

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 = '38549' ORDER BY s0_.id ASC LIMIT 1;

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\":\"38549\",\"nom\":\"38549\"}', '2026-07-13 03:19:35', 'https://osm-labo.cipherbliss.com/admin/labourer/38549?deleteMissing=1&disableFollowUpCleanup=1', NULL);

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(((6.027493 45.036662,6.0276 45.0367,6.028068 45.037054,6.0283 45.037677,6.028208 45.03822,6.028377 45.038845,6.028232 45.039525,6.028382 45.03988,6.028669 45.040366,6.028865 45.040449,6.029689 45.040422,6.030251 45.040779,6.030496 45.040713,6.031039 45.040449,6.031621 45.040147,6.032056 45.039994,6.0325 45.039791,6.033146 45.03961,6.033554 45.039531,6.034029 45.03979,6.034527 45.039639,6.035447 45.039158,6.036691 45.038756,6.037723 45.038046,6.038997 45.036969,6.039529 45.036365,6.041675 45.035221,6.042542 45.034878,6.043889 45.034306,6.043948 45.034065,6.044005 45.033973,6.044052 45.033701,6.043847 45.033483,6.043353 45.033076,6.044904 45.032007,6.045945 45.031432,6.046123 45.031246,6.04592 45.031072,6.045975 45.030936,6.046527 45.030647,6.047129 45.030141,6.047123 45.030051,6.048191 45.02988,6.048739 45.029546,6.049347 45.02894,6.050098 45.027835,6.05019 45.027292,6.051017 45.026363,6.051572 45.024994,6.051839 45.02422,6.05224 45.023575,6.053797 45.022173,6.054265 45.021581,6.054441 45.02117,6.053913 45.019917,6.053615 45.018307,6.052959 45.015898,6.052685 45.014645,6.052526 45.014156,6.051756 45.010985,6.050946 45.009301,6.049864 45.008113,6.049798 45.008069,6.049454 45.007676,6.048513 45.006897,6.047425 45.005808,6.047329 45.005316,6.047411 45.004638,6.047073 45.004154,6.046674 45.003853,6.046646 45.003448,6.047174 45.0028,6.047413 45.002566,6.047385 45.002163,6.046952 45.001645,6.046641 45.001432,6.046981 45.000685,6.047039 45.000157,6.046711 44.999844,6.046852 44.997768,6.047 44.997133,6.046982 44.996863,6.045981 44.996123,6.045952 44.995493,6.044653 44.994096,6.043373 44.992968,6.042905 44.992623,6.042812 44.992176,6.043853 44.990475,6.043923 44.989617,6.043517 44.988281,6.043545 44.987739,6.043711 44.987373,6.043748 44.986787,6.043658 44.986385,6.043034 44.985595,6.042151 44.984716,6.041573 44.983654,6.040384 44.982748,6.039424 44.982825,6.038981 44.98284,6.037189 44.982405,6.035073 44.982062,6.032952 44.980692,6.031574 44.980008,6.030877 44.980032,6.030209 44.980459,6.028864 44.981225,6.02673 44.982736,6.023824 44.983448,6.022277 44.983881,6.02029 44.984394,6.018573 44.985043,6.017466 44.985773,6.016765 44.986697,6.015985 44.988343,6.015324 44.991022,6.015384 44.99192,6.015651 44.993082,6.01579 44.995374,6.015019 44.99579,6.013777 44.996371,6.012175 44.997371,6.011387 44.998086,6.010934 44.998912,6.010907 44.999858,6.010812 44.999952,6.012138 45.000808,6.013172 45.002259,6.013787 45.002914,6.014846 45.004545,6.015231 45.005568,6.015281 45.006521,6.014918 45.007748,6.014748 45.00806,6.014661 45.008649,6.015007 45.009096,6.015046 45.00968,6.016286 45.01135,6.016729 45.012281,6.016914 45.013175,6.017624 45.014502,6.01884 45.016578,6.019453 45.017197,6.019315 45.017752,6.019588 45.01796,6.019941 45.018074,6.020194 45.018076,6.020417 45.018189,6.020533 45.018193,6.020904 45.018577,6.021824 45.019279,6.022976 45.020197,6.023687 45.020659,6.024227 45.020885,6.024491 45.021079,6.024818 45.021381,6.025113 45.021729,6.025235 45.021956,6.025411 45.022129,6.025533 45.022365,6.025536 45.022758,6.025562 45.022905,6.025641 45.023798,6.025981 45.024355,6.025459 45.025162,6.025406 45.026018,6.025363 45.027046,6.025566 45.027644,6.025376 45.027904,6.02503 45.028587,6.025025 45.028836,6.024933 45.029348,6.024845 45.029631,6.024546 45.029993,6.024595 45.030283,6.024498 45.030627,6.024349 45.03075,6.02413 45.030858,6.024005 45.031017,6.023942 45.031424,6.023594 45.031733,6.023023 45.031928,6.022849 45.032024,6.022722 45.032162,6.022633 45.032398,6.022628 45.03267,6.022674 45.03302,6.0226 45.033156,6.023165 45.033787,6.024091 45.034341,6.025455 45.03479,6.026228 45.035935,6.026687 45.036326,6.027015 45.036494,6.027493 45.036662)))'') 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
  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'38549'') = ?
  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_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
  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
  COUNT(*)
FROM
  osm_object_meta
WHERE
  osm_type = 'relation'
  AND geom IS NOT NULL
  AND (tags -> 'admin_level') = '8'
  AND (tags -> 'boundary') = 'administrative'
  AND (tags -> 'ref:INSEE') IS NOT NULL

SELECT COUNT(*) FROM osm_object_meta WHERE osm_type = 'relation'
                  AND geom IS NOT NULL
                  AND (tags->'admin_level') = '8'
                  AND (tags->'boundary') = 'administrative'
                  AND (tags->'ref:INSEE') IS NOT NULL;

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(((6.027493 45.036662,6.0276 45.0367,6.028068 45.037054,6.0283 45.037677,6.028208 45.03822,6.028377 45.038845,6.028232 45.039525,6.028382 45.03988,6.028669 45.040366,6.028865 45.040449,6.029689 45.040422,6.030251 45.040779,6.030496 45.040713,6.031039 45.040449,6.031621 45.040147,6.032056 45.039994,6.0325 45.039791,6.033146 45.03961,6.033554 45.039531,6.034029 45.03979,6.034527 45.039639,6.035447 45.039158,6.036691 45.038756,6.037723 45.038046,6.038997 45.036969,6.039529 45.036365,6.041675 45.035221,6.042542 45.034878,6.043889 45.034306,6.043948 45.034065,6.044005 45.033973,6.044052 45.033701,6.043847 45.033483,6.043353 45.033076,6.044904 45.032007,6.045945 45.031432,6.046123 45.031246,6.04592 45.031072,6.045975 45.030936,6.046527 45.030647,6.047129 45.030141,6.047123 45.030051,6.048191 45.02988,6.048739 45.029546,6.049347 45.02894,6.050098 45.027835,6.05019 45.027292,6.051017 45.026363,6.051572 45.024994,6.051839 45.02422,6.05224 45.023575,6.053797 45.022173,6.054265 45.021581,6.054441 45.02117,6.053913 45.019917,6.053615 45.018307,6.052959 45.015898,6.052685 45.014645,6.052526 45.014156,6.051756 45.010985,6.050946 45.009301,6.049864 45.008113,6.049798 45.008069,6.049454 45.007676,6.048513 45.006897,6.047425 45.005808,6.047329 45.005316,6.047411 45.004638,6.047073 45.004154,6.046674 45.003853,6.046646 45.003448,6.047174 45.0028,6.047413 45.002566,6.047385 45.002163,6.046952 45.001645,6.046641 45.001432,6.046981 45.000685,6.047039 45.000157,6.046711 44.999844,6.046852 44.997768,6.047 44.997133,6.046982 44.996863,6.045981 44.996123,6.045952 44.995493,6.044653 44.994096,6.043373 44.992968,6.042905 44.992623,6.042812 44.992176,6.043853 44.990475,6.043923 44.989617,6.043517 44.988281,6.043545 44.987739,6.043711 44.987373,6.043748 44.986787,6.043658 44.986385,6.043034 44.985595,6.042151 44.984716,6.041573 44.983654,6.040384 44.982748,6.039424 44.982825,6.038981 44.98284,6.037189 44.982405,6.035073 44.982062,6.032952 44.980692,6.031574 44.980008,6.030877 44.980032,6.030209 44.980459,6.028864 44.981225,6.02673 44.982736,6.023824 44.983448,6.022277 44.983881,6.02029 44.984394,6.018573 44.985043,6.017466 44.985773,6.016765 44.986697,6.015985 44.988343,6.015324 44.991022,6.015384 44.99192,6.015651 44.993082,6.01579 44.995374,6.015019 44.99579,6.013777 44.996371,6.012175 44.997371,6.011387 44.998086,6.010934 44.998912,6.010907 44.999858,6.010812 44.999952,6.012138 45.000808,6.013172 45.002259,6.013787 45.002914,6.014846 45.004545,6.015231 45.005568,6.015281 45.006521,6.014918 45.007748,6.014748 45.00806,6.014661 45.008649,6.015007 45.009096,6.015046 45.00968,6.016286 45.01135,6.016729 45.012281,6.016914 45.013175,6.017624 45.014502,6.01884 45.016578,6.019453 45.017197,6.019315 45.017752,6.019588 45.01796,6.019941 45.018074,6.020194 45.018076,6.020417 45.018189,6.020533 45.018193,6.020904 45.018577,6.021824 45.019279,6.022976 45.020197,6.023687 45.020659,6.024227 45.020885,6.024491 45.021079,6.024818 45.021381,6.025113 45.021729,6.025235 45.021956,6.025411 45.022129,6.025533 45.022365,6.025536 45.022758,6.025562 45.022905,6.025641 45.023798,6.025981 45.024355,6.025459 45.025162,6.025406 45.026018,6.025363 45.027046,6.025566 45.027644,6.025376 45.027904,6.02503 45.028587,6.025025 45.028836,6.024933 45.029348,6.024845 45.029631,6.024546 45.029993,6.024595 45.030283,6.024498 45.030627,6.024349 45.03075,6.02413 45.030858,6.024005 45.031017,6.023942 45.031424,6.023594 45.031733,6.023023 45.031928,6.022849 45.032024,6.022722 45.032162,6.022633 45.032398,6.022628 45.03267,6.022674 45.03302,6.0226 45.033156,6.023165 45.033787,6.024091 45.034341,6.025455 45.03479,6.026228 45.035935,6.026687 45.036326,6.027015 45.036494,6.027493 45.036662)))'), 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(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(((6.027493 45.036662,6.0276 45.0367,6.028068 45.037054,6.0283 45.037677,6.028208 45.03822,6.028377 45.038845,6.028232 45.039525,6.028382 45.03988,6.028669 45.040366,6.028865 45.040449,6.029689 45.040422,6.030251 45.040779,6.030496 45.040713,6.031039 45.040449,6.031621 45.040147,6.032056 45.039994,6.0325 45.039791,6.033146 45.03961,6.033554 45.039531,6.034029 45.03979,6.034527 45.039639,6.035447 45.039158,6.036691 45.038756,6.037723 45.038046,6.038997 45.036969,6.039529 45.036365,6.041675 45.035221,6.042542 45.034878,6.043889 45.034306,6.043948 45.034065,6.044005 45.033973,6.044052 45.033701,6.043847 45.033483,6.043353 45.033076,6.044904 45.032007,6.045945 45.031432,6.046123 45.031246,6.04592 45.031072,6.045975 45.030936,6.046527 45.030647,6.047129 45.030141,6.047123 45.030051,6.048191 45.02988,6.048739 45.029546,6.049347 45.02894,6.050098 45.027835,6.05019 45.027292,6.051017 45.026363,6.051572 45.024994,6.051839 45.02422,6.05224 45.023575,6.053797 45.022173,6.054265 45.021581,6.054441 45.02117,6.053913 45.019917,6.053615 45.018307,6.052959 45.015898,6.052685 45.014645,6.052526 45.014156,6.051756 45.010985,6.050946 45.009301,6.049864 45.008113,6.049798 45.008069,6.049454 45.007676,6.048513 45.006897,6.047425 45.005808,6.047329 45.005316,6.047411 45.004638,6.047073 45.004154,6.046674 45.003853,6.046646 45.003448,6.047174 45.0028,6.047413 45.002566,6.047385 45.002163,6.046952 45.001645,6.046641 45.001432,6.046981 45.000685,6.047039 45.000157,6.046711 44.999844,6.046852 44.997768,6.047 44.997133,6.046982 44.996863,6.045981 44.996123,6.045952 44.995493,6.044653 44.994096,6.043373 44.992968,6.042905 44.992623,6.042812 44.992176,6.043853 44.990475,6.043923 44.989617,6.043517 44.988281,6.043545 44.987739,6.043711 44.987373,6.043748 44.986787,6.043658 44.986385,6.043034 44.985595,6.042151 44.984716,6.041573 44.983654,6.040384 44.982748,6.039424 44.982825,6.038981 44.98284,6.037189 44.982405,6.035073 44.982062,6.032952 44.980692,6.031574 44.980008,6.030877 44.980032,6.030209 44.980459,6.028864 44.981225,6.02673 44.982736,6.023824 44.983448,6.022277 44.983881,6.02029 44.984394,6.018573 44.985043,6.017466 44.985773,6.016765 44.986697,6.015985 44.988343,6.015324 44.991022,6.015384 44.99192,6.015651 44.993082,6.01579 44.995374,6.015019 44.99579,6.013777 44.996371,6.012175 44.997371,6.011387 44.998086,6.010934 44.998912,6.010907 44.999858,6.010812 44.999952,6.012138 45.000808,6.013172 45.002259,6.013787 45.002914,6.014846 45.004545,6.015231 45.005568,6.015281 45.006521,6.014918 45.007748,6.014748 45.00806,6.014661 45.008649,6.015007 45.009096,6.015046 45.00968,6.016286 45.01135,6.016729 45.012281,6.016914 45.013175,6.017624 45.014502,6.01884 45.016578,6.019453 45.017197,6.019315 45.017752,6.019588 45.01796,6.019941 45.018074,6.020194 45.018076,6.020417 45.018189,6.020533 45.018193,6.020904 45.018577,6.021824 45.019279,6.022976 45.020197,6.023687 45.020659,6.024227 45.020885,6.024491 45.021079,6.024818 45.021381,6.025113 45.021729,6.025235 45.021956,6.025411 45.022129,6.025533 45.022365,6.025536 45.022758,6.025562 45.022905,6.025641 45.023798,6.025981 45.024355,6.025459 45.025162,6.025406 45.026018,6.025363 45.027046,6.025566 45.027644,6.025376 45.027904,6.02503 45.028587,6.025025 45.028836,6.024933 45.029348,6.024845 45.029631,6.024546 45.029993,6.024595 45.030283,6.024498 45.030627,6.024349 45.03075,6.02413 45.030858,6.024005 45.031017,6.023942 45.031424,6.023594 45.031733,6.023023 45.031928,6.022849 45.032024,6.022722 45.032162,6.022633 45.032398,6.022628 45.03267,6.022674 45.03302,6.0226 45.033156,6.023165 45.033787,6.024091 45.034341,6.025455 45.03479,6.026228 45.035935,6.026687 45.036326,6.027015 45.036494,6.027493 45.036662)))', 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,
  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(((6.027493 45.036662,6.0276 45.0367,6.028068 45.037054,6.0283 45.037677,6.028208 45.03822,6.028377 45.038845,6.028232 45.039525,6.028382 45.03988,6.028669 45.040366,6.028865 45.040449,6.029689 45.040422,6.030251 45.040779,6.030496 45.040713,6.031039 45.040449,6.031621 45.040147,6.032056 45.039994,6.0325 45.039791,6.033146 45.03961,6.033554 45.039531,6.034029 45.03979,6.034527 45.039639,6.035447 45.039158,6.036691 45.038756,6.037723 45.038046,6.038997 45.036969,6.039529 45.036365,6.041675 45.035221,6.042542 45.034878,6.043889 45.034306,6.043948 45.034065,6.044005 45.033973,6.044052 45.033701,6.043847 45.033483,6.043353 45.033076,6.044904 45.032007,6.045945 45.031432,6.046123 45.031246,6.04592 45.031072,6.045975 45.030936,6.046527 45.030647,6.047129 45.030141,6.047123 45.030051,6.048191 45.02988,6.048739 45.029546,6.049347 45.02894,6.050098 45.027835,6.05019 45.027292,6.051017 45.026363,6.051572 45.024994,6.051839 45.02422,6.05224 45.023575,6.053797 45.022173,6.054265 45.021581,6.054441 45.02117,6.053913 45.019917,6.053615 45.018307,6.052959 45.015898,6.052685 45.014645,6.052526 45.014156,6.051756 45.010985,6.050946 45.009301,6.049864 45.008113,6.049798 45.008069,6.049454 45.007676,6.048513 45.006897,6.047425 45.005808,6.047329 45.005316,6.047411 45.004638,6.047073 45.004154,6.046674 45.003853,6.046646 45.003448,6.047174 45.0028,6.047413 45.002566,6.047385 45.002163,6.046952 45.001645,6.046641 45.001432,6.046981 45.000685,6.047039 45.000157,6.046711 44.999844,6.046852 44.997768,6.047 44.997133,6.046982 44.996863,6.045981 44.996123,6.045952 44.995493,6.044653 44.994096,6.043373 44.992968,6.042905 44.992623,6.042812 44.992176,6.043853 44.990475,6.043923 44.989617,6.043517 44.988281,6.043545 44.987739,6.043711 44.987373,6.043748 44.986787,6.043658 44.986385,6.043034 44.985595,6.042151 44.984716,6.041573 44.983654,6.040384 44.982748,6.039424 44.982825,6.038981 44.98284,6.037189 44.982405,6.035073 44.982062,6.032952 44.980692,6.031574 44.980008,6.030877 44.980032,6.030209 44.980459,6.028864 44.981225,6.02673 44.982736,6.023824 44.983448,6.022277 44.983881,6.02029 44.984394,6.018573 44.985043,6.017466 44.985773,6.016765 44.986697,6.015985 44.988343,6.015324 44.991022,6.015384 44.99192,6.015651 44.993082,6.01579 44.995374,6.015019 44.99579,6.013777 44.996371,6.012175 44.997371,6.011387 44.998086,6.010934 44.998912,6.010907 44.999858,6.010812 44.999952,6.012138 45.000808,6.013172 45.002259,6.013787 45.002914,6.014846 45.004545,6.015231 45.005568,6.015281 45.006521,6.014918 45.007748,6.014748 45.00806,6.014661 45.008649,6.015007 45.009096,6.015046 45.00968,6.016286 45.01135,6.016729 45.012281,6.016914 45.013175,6.017624 45.014502,6.01884 45.016578,6.019453 45.017197,6.019315 45.017752,6.019588 45.01796,6.019941 45.018074,6.020194 45.018076,6.020417 45.018189,6.020533 45.018193,6.020904 45.018577,6.021824 45.019279,6.022976 45.020197,6.023687 45.020659,6.024227 45.020885,6.024491 45.021079,6.024818 45.021381,6.025113 45.021729,6.025235 45.021956,6.025411 45.022129,6.025533 45.022365,6.025536 45.022758,6.025562 45.022905,6.025641 45.023798,6.025981 45.024355,6.025459 45.025162,6.025406 45.026018,6.025363 45.027046,6.025566 45.027644,6.025376 45.027904,6.02503 45.028587,6.025025 45.028836,6.024933 45.029348,6.024845 45.029631,6.024546 45.029993,6.024595 45.030283,6.024498 45.030627,6.024349 45.03075,6.02413 45.030858,6.024005 45.031017,6.023942 45.031424,6.023594 45.031733,6.023023 45.031928,6.022849 45.032024,6.022722 45.032162,6.022633 45.032398,6.022628 45.03267,6.022674 45.03302,6.0226 45.033156,6.023165 45.033787,6.024091 45.034341,6.025455 45.03479,6.026228 45.035935,6.026687 45.036326,6.027015 45.036494,6.027493 45.036662)))', 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(((6.027493 45.036662,6.0276 45.0367,6.028068 45.037054,6.0283 45.037677,6.028208 45.03822,6.028377 45.038845,6.028232 45.039525,6.028382 45.03988,6.028669 45.040366,6.028865 45.040449,6.029689 45.040422,6.030251 45.040779,6.030496 45.040713,6.031039 45.040449,6.031621 45.040147,6.032056 45.039994,6.0325 45.039791,6.033146 45.03961,6.033554 45.039531,6.034029 45.03979,6.034527 45.039639,6.035447 45.039158,6.036691 45.038756,6.037723 45.038046,6.038997 45.036969,6.039529 45.036365,6.041675 45.035221,6.042542 45.034878,6.043889 45.034306,6.043948 45.034065,6.044005 45.033973,6.044052 45.033701,6.043847 45.033483,6.043353 45.033076,6.044904 45.032007,6.045945 45.031432,6.046123 45.031246,6.04592 45.031072,6.045975 45.030936,6.046527 45.030647,6.047129 45.030141,6.047123 45.030051,6.048191 45.02988,6.048739 45.029546,6.049347 45.02894,6.050098 45.027835,6.05019 45.027292,6.051017 45.026363,6.051572 45.024994,6.051839 45.02422,6.05224 45.023575,6.053797 45.022173,6.054265 45.021581,6.054441 45.02117,6.053913 45.019917,6.053615 45.018307,6.052959 45.015898,6.052685 45.014645,6.052526 45.014156,6.051756 45.010985,6.050946 45.009301,6.049864 45.008113,6.049798 45.008069,6.049454 45.007676,6.048513 45.006897,6.047425 45.005808,6.047329 45.005316,6.047411 45.004638,6.047073 45.004154,6.046674 45.003853,6.046646 45.003448,6.047174 45.0028,6.047413 45.002566,6.047385 45.002163,6.046952 45.001645,6.046641 45.001432,6.046981 45.000685,6.047039 45.000157,6.046711 44.999844,6.046852 44.997768,6.047 44.997133,6.046982 44.996863,6.045981 44.996123,6.045952 44.995493,6.044653 44.994096,6.043373 44.992968,6.042905 44.992623,6.042812 44.992176,6.043853 44.990475,6.043923 44.989617,6.043517 44.988281,6.043545 44.987739,6.043711 44.987373,6.043748 44.986787,6.043658 44.986385,6.043034 44.985595,6.042151 44.984716,6.041573 44.983654,6.040384 44.982748,6.039424 44.982825,6.038981 44.98284,6.037189 44.982405,6.035073 44.982062,6.032952 44.980692,6.031574 44.980008,6.030877 44.980032,6.030209 44.980459,6.028864 44.981225,6.02673 44.982736,6.023824 44.983448,6.022277 44.983881,6.02029 44.984394,6.018573 44.985043,6.017466 44.985773,6.016765 44.986697,6.015985 44.988343,6.015324 44.991022,6.015384 44.99192,6.015651 44.993082,6.01579 44.995374,6.015019 44.99579,6.013777 44.996371,6.012175 44.997371,6.011387 44.998086,6.010934 44.998912,6.010907 44.999858,6.010812 44.999952,6.012138 45.000808,6.013172 45.002259,6.013787 45.002914,6.014846 45.004545,6.015231 45.005568,6.015281 45.006521,6.014918 45.007748,6.014748 45.00806,6.014661 45.008649,6.015007 45.009096,6.015046 45.00968,6.016286 45.01135,6.016729 45.012281,6.016914 45.013175,6.017624 45.014502,6.01884 45.016578,6.019453 45.017197,6.019315 45.017752,6.019588 45.01796,6.019941 45.018074,6.020194 45.018076,6.020417 45.018189,6.020533 45.018193,6.020904 45.018577,6.021824 45.019279,6.022976 45.020197,6.023687 45.020659,6.024227 45.020885,6.024491 45.021079,6.024818 45.021381,6.025113 45.021729,6.025235 45.021956,6.025411 45.022129,6.025533 45.022365,6.025536 45.022758,6.025562 45.022905,6.025641 45.023798,6.025981 45.024355,6.025459 45.025162,6.025406 45.026018,6.025363 45.027046,6.025566 45.027644,6.025376 45.027904,6.02503 45.028587,6.025025 45.028836,6.024933 45.029348,6.024845 45.029631,6.024546 45.029993,6.024595 45.030283,6.024498 45.030627,6.024349 45.03075,6.02413 45.030858,6.024005 45.031017,6.023942 45.031424,6.023594 45.031733,6.023023 45.031928,6.022849 45.032024,6.022722 45.032162,6.022633 45.032398,6.022628 45.03267,6.022674 45.03302,6.0226 45.033156,6.023165 45.033787,6.024091 45.034341,6.025455 45.03479,6.026228 45.035935,6.026687 45.036326,6.027015 45.036494,6.027493 45.036662)))', 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;