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

"COMMIT"

"COMMIT";

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

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

"START TRANSACTION"

"START TRANSACTION";

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

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

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

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 = 26683 ORDER BY s0_.id DESC LIMIT 100;

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.740777 49.938451,3.742722 49.937754,3.743441 49.937718,3.743909 49.938206,3.743969 49.938417,3.74678 49.938112,3.747316 49.938032,3.748642 49.938164,3.748819 49.938273,3.750185 49.938898,3.750612 49.939235,3.751437 49.93949,3.752464 49.939856,3.752648 49.939901,3.753246 49.939816,3.754742 49.940493,3.755213 49.940787,3.755321 49.940933,3.755541 49.941025,3.756753 49.941117,3.756971 49.941177,3.757352 49.941089,3.758249 49.940811,3.758471 49.940686,3.758674 49.940485,3.75899 49.94056,3.759407 49.940727,3.7598 49.941239,3.759967 49.941285,3.761355 49.941122,3.762021 49.940694,3.76359 49.939617,3.76388 49.939356,3.764388 49.939219,3.764646 49.939121,3.765165 49.939004,3.765619 49.938789,3.766692 49.938166,3.767069 49.937709,3.767677 49.937675,3.768092 49.937908,3.768405 49.937619,3.768806 49.937363,3.76913 49.937075,3.768715 49.936879,3.768249 49.936716,3.768509 49.936403,3.768827 49.936168,3.769021 49.936318,3.769407 49.936067,3.77041 49.935282,3.770928 49.934836,3.771254 49.934965,3.771567 49.934637,3.770879 49.934292,3.770526 49.934097,3.769962 49.933824,3.769415 49.933526,3.769586 49.933408,3.769855 49.933111,3.769775 49.933033,3.769768 49.932868,3.769202 49.932208,3.768956 49.932105,3.768584 49.931831,3.768382 49.931728,3.76817 49.931706,3.767934 49.931009,3.767624 49.930565,3.768093 49.930513,3.767972 49.929935,3.76777 49.929838,3.767331 49.929733,3.766511 49.929599,3.766239 49.929571,3.76593 49.929381,3.764538 49.929307,3.764197 49.929254,3.764131 49.92907,3.764123 49.92877,3.764021 49.928567,3.763827 49.928442,3.763052 49.928441,3.762792 49.928428,3.76248 49.928106,3.761546 49.927085,3.761507 49.926896,3.760831 49.926084,3.760384 49.925599,3.759566 49.924646,3.759383 49.924317,3.758788 49.923851,3.758266 49.92353,3.757999 49.923328,3.757491 49.923166,3.756846 49.9228,3.75516 49.922167,3.753852 49.921409,3.753544 49.921281,3.753516 49.921212,3.752952 49.920899,3.75249 49.920689,3.751903 49.920359,3.751541 49.920137,3.750791 49.919777,3.750508 49.919612,3.750384 49.919492,3.75045 49.919349,3.750377 49.919229,3.750029 49.918998,3.749501 49.918604,3.748671 49.91874,3.749258 49.919309,3.747992 49.920051,3.747399 49.920336,3.747112 49.920329,3.746831 49.920501,3.745439 49.92119,3.745761 49.921423,3.745426 49.921543,3.745614 49.922072,3.74556 49.922326,3.74541 49.922453,3.744947 49.92268,3.744218 49.923088,3.743491 49.923529,3.742815 49.923905,3.741764 49.924548,3.741561 49.924424,3.741434 49.924489,3.741025 49.924825,3.741352 49.924984,3.741573 49.925177,3.741532 49.925261,3.741114 49.925865,3.740654 49.92645,3.740745 49.927533,3.740687 49.927612,3.740343 49.927774,3.74036 49.928074,3.739634 49.928224,3.739489 49.9283,3.739501 49.928405,3.739724 49.928933,3.739887 49.929547,3.740041 49.930035,3.740258 49.930337,3.739994 49.93055,3.740093 49.930571,3.740726 49.930833,3.741012 49.930923,3.740883 49.931251,3.74075 49.931716,3.740485 49.932566,3.74009 49.933628,3.740345 49.933736,3.740119 49.934033,3.740146 49.93439,3.740233 49.935073,3.74036 49.935872,3.74045 49.936301,3.740155 49.936321,3.740189 49.936506,3.740464 49.93707,3.740679 49.937562,3.740699 49.937709,3.74065 49.93788,3.740777 49.938451)))'') 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(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.740777 49.938451,3.742722 49.937754,3.743441 49.937718,3.743909 49.938206,3.743969 49.938417,3.74678 49.938112,3.747316 49.938032,3.748642 49.938164,3.748819 49.938273,3.750185 49.938898,3.750612 49.939235,3.751437 49.93949,3.752464 49.939856,3.752648 49.939901,3.753246 49.939816,3.754742 49.940493,3.755213 49.940787,3.755321 49.940933,3.755541 49.941025,3.756753 49.941117,3.756971 49.941177,3.757352 49.941089,3.758249 49.940811,3.758471 49.940686,3.758674 49.940485,3.75899 49.94056,3.759407 49.940727,3.7598 49.941239,3.759967 49.941285,3.761355 49.941122,3.762021 49.940694,3.76359 49.939617,3.76388 49.939356,3.764388 49.939219,3.764646 49.939121,3.765165 49.939004,3.765619 49.938789,3.766692 49.938166,3.767069 49.937709,3.767677 49.937675,3.768092 49.937908,3.768405 49.937619,3.768806 49.937363,3.76913 49.937075,3.768715 49.936879,3.768249 49.936716,3.768509 49.936403,3.768827 49.936168,3.769021 49.936318,3.769407 49.936067,3.77041 49.935282,3.770928 49.934836,3.771254 49.934965,3.771567 49.934637,3.770879 49.934292,3.770526 49.934097,3.769962 49.933824,3.769415 49.933526,3.769586 49.933408,3.769855 49.933111,3.769775 49.933033,3.769768 49.932868,3.769202 49.932208,3.768956 49.932105,3.768584 49.931831,3.768382 49.931728,3.76817 49.931706,3.767934 49.931009,3.767624 49.930565,3.768093 49.930513,3.767972 49.929935,3.76777 49.929838,3.767331 49.929733,3.766511 49.929599,3.766239 49.929571,3.76593 49.929381,3.764538 49.929307,3.764197 49.929254,3.764131 49.92907,3.764123 49.92877,3.764021 49.928567,3.763827 49.928442,3.763052 49.928441,3.762792 49.928428,3.76248 49.928106,3.761546 49.927085,3.761507 49.926896,3.760831 49.926084,3.760384 49.925599,3.759566 49.924646,3.759383 49.924317,3.758788 49.923851,3.758266 49.92353,3.757999 49.923328,3.757491 49.923166,3.756846 49.9228,3.75516 49.922167,3.753852 49.921409,3.753544 49.921281,3.753516 49.921212,3.752952 49.920899,3.75249 49.920689,3.751903 49.920359,3.751541 49.920137,3.750791 49.919777,3.750508 49.919612,3.750384 49.919492,3.75045 49.919349,3.750377 49.919229,3.750029 49.918998,3.749501 49.918604,3.748671 49.91874,3.749258 49.919309,3.747992 49.920051,3.747399 49.920336,3.747112 49.920329,3.746831 49.920501,3.745439 49.92119,3.745761 49.921423,3.745426 49.921543,3.745614 49.922072,3.74556 49.922326,3.74541 49.922453,3.744947 49.92268,3.744218 49.923088,3.743491 49.923529,3.742815 49.923905,3.741764 49.924548,3.741561 49.924424,3.741434 49.924489,3.741025 49.924825,3.741352 49.924984,3.741573 49.925177,3.741532 49.925261,3.741114 49.925865,3.740654 49.92645,3.740745 49.927533,3.740687 49.927612,3.740343 49.927774,3.74036 49.928074,3.739634 49.928224,3.739489 49.9283,3.739501 49.928405,3.739724 49.928933,3.739887 49.929547,3.740041 49.930035,3.740258 49.930337,3.739994 49.93055,3.740093 49.930571,3.740726 49.930833,3.741012 49.930923,3.740883 49.931251,3.74075 49.931716,3.740485 49.932566,3.74009 49.933628,3.740345 49.933736,3.740119 49.934033,3.740146 49.93439,3.740233 49.935073,3.74036 49.935872,3.74045 49.936301,3.740155 49.936321,3.740189 49.936506,3.740464 49.93707,3.740679 49.937562,3.740699 49.937709,3.74065 49.93788,3.740777 49.938451)))'), 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
  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'02244'') = ?
  AND (tags->'admin_level') = '8'
  AND (tags->'boundary') = 'administrative'
ORDER BY ST_Area(geom::geography) DESC NULLS LAST
LIMIT 1;

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.740777 49.938451,3.742722 49.937754,3.743441 49.937718,3.743909 49.938206,3.743969 49.938417,3.74678 49.938112,3.747316 49.938032,3.748642 49.938164,3.748819 49.938273,3.750185 49.938898,3.750612 49.939235,3.751437 49.93949,3.752464 49.939856,3.752648 49.939901,3.753246 49.939816,3.754742 49.940493,3.755213 49.940787,3.755321 49.940933,3.755541 49.941025,3.756753 49.941117,3.756971 49.941177,3.757352 49.941089,3.758249 49.940811,3.758471 49.940686,3.758674 49.940485,3.75899 49.94056,3.759407 49.940727,3.7598 49.941239,3.759967 49.941285,3.761355 49.941122,3.762021 49.940694,3.76359 49.939617,3.76388 49.939356,3.764388 49.939219,3.764646 49.939121,3.765165 49.939004,3.765619 49.938789,3.766692 49.938166,3.767069 49.937709,3.767677 49.937675,3.768092 49.937908,3.768405 49.937619,3.768806 49.937363,3.76913 49.937075,3.768715 49.936879,3.768249 49.936716,3.768509 49.936403,3.768827 49.936168,3.769021 49.936318,3.769407 49.936067,3.77041 49.935282,3.770928 49.934836,3.771254 49.934965,3.771567 49.934637,3.770879 49.934292,3.770526 49.934097,3.769962 49.933824,3.769415 49.933526,3.769586 49.933408,3.769855 49.933111,3.769775 49.933033,3.769768 49.932868,3.769202 49.932208,3.768956 49.932105,3.768584 49.931831,3.768382 49.931728,3.76817 49.931706,3.767934 49.931009,3.767624 49.930565,3.768093 49.930513,3.767972 49.929935,3.76777 49.929838,3.767331 49.929733,3.766511 49.929599,3.766239 49.929571,3.76593 49.929381,3.764538 49.929307,3.764197 49.929254,3.764131 49.92907,3.764123 49.92877,3.764021 49.928567,3.763827 49.928442,3.763052 49.928441,3.762792 49.928428,3.76248 49.928106,3.761546 49.927085,3.761507 49.926896,3.760831 49.926084,3.760384 49.925599,3.759566 49.924646,3.759383 49.924317,3.758788 49.923851,3.758266 49.92353,3.757999 49.923328,3.757491 49.923166,3.756846 49.9228,3.75516 49.922167,3.753852 49.921409,3.753544 49.921281,3.753516 49.921212,3.752952 49.920899,3.75249 49.920689,3.751903 49.920359,3.751541 49.920137,3.750791 49.919777,3.750508 49.919612,3.750384 49.919492,3.75045 49.919349,3.750377 49.919229,3.750029 49.918998,3.749501 49.918604,3.748671 49.91874,3.749258 49.919309,3.747992 49.920051,3.747399 49.920336,3.747112 49.920329,3.746831 49.920501,3.745439 49.92119,3.745761 49.921423,3.745426 49.921543,3.745614 49.922072,3.74556 49.922326,3.74541 49.922453,3.744947 49.92268,3.744218 49.923088,3.743491 49.923529,3.742815 49.923905,3.741764 49.924548,3.741561 49.924424,3.741434 49.924489,3.741025 49.924825,3.741352 49.924984,3.741573 49.925177,3.741532 49.925261,3.741114 49.925865,3.740654 49.92645,3.740745 49.927533,3.740687 49.927612,3.740343 49.927774,3.74036 49.928074,3.739634 49.928224,3.739489 49.9283,3.739501 49.928405,3.739724 49.928933,3.739887 49.929547,3.740041 49.930035,3.740258 49.930337,3.739994 49.93055,3.740093 49.930571,3.740726 49.930833,3.741012 49.930923,3.740883 49.931251,3.74075 49.931716,3.740485 49.932566,3.74009 49.933628,3.740345 49.933736,3.740119 49.934033,3.740146 49.93439,3.740233 49.935073,3.74036 49.935872,3.74045 49.936301,3.740155 49.936321,3.740189 49.936506,3.740464 49.93707,3.740679 49.937562,3.740699 49.937709,3.74065 49.93788,3.740777 49.938451)))', 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(((3.740777 49.938451,3.742722 49.937754,3.743441 49.937718,3.743909 49.938206,3.743969 49.938417,3.74678 49.938112,3.747316 49.938032,3.748642 49.938164,3.748819 49.938273,3.750185 49.938898,3.750612 49.939235,3.751437 49.93949,3.752464 49.939856,3.752648 49.939901,3.753246 49.939816,3.754742 49.940493,3.755213 49.940787,3.755321 49.940933,3.755541 49.941025,3.756753 49.941117,3.756971 49.941177,3.757352 49.941089,3.758249 49.940811,3.758471 49.940686,3.758674 49.940485,3.75899 49.94056,3.759407 49.940727,3.7598 49.941239,3.759967 49.941285,3.761355 49.941122,3.762021 49.940694,3.76359 49.939617,3.76388 49.939356,3.764388 49.939219,3.764646 49.939121,3.765165 49.939004,3.765619 49.938789,3.766692 49.938166,3.767069 49.937709,3.767677 49.937675,3.768092 49.937908,3.768405 49.937619,3.768806 49.937363,3.76913 49.937075,3.768715 49.936879,3.768249 49.936716,3.768509 49.936403,3.768827 49.936168,3.769021 49.936318,3.769407 49.936067,3.77041 49.935282,3.770928 49.934836,3.771254 49.934965,3.771567 49.934637,3.770879 49.934292,3.770526 49.934097,3.769962 49.933824,3.769415 49.933526,3.769586 49.933408,3.769855 49.933111,3.769775 49.933033,3.769768 49.932868,3.769202 49.932208,3.768956 49.932105,3.768584 49.931831,3.768382 49.931728,3.76817 49.931706,3.767934 49.931009,3.767624 49.930565,3.768093 49.930513,3.767972 49.929935,3.76777 49.929838,3.767331 49.929733,3.766511 49.929599,3.766239 49.929571,3.76593 49.929381,3.764538 49.929307,3.764197 49.929254,3.764131 49.92907,3.764123 49.92877,3.764021 49.928567,3.763827 49.928442,3.763052 49.928441,3.762792 49.928428,3.76248 49.928106,3.761546 49.927085,3.761507 49.926896,3.760831 49.926084,3.760384 49.925599,3.759566 49.924646,3.759383 49.924317,3.758788 49.923851,3.758266 49.92353,3.757999 49.923328,3.757491 49.923166,3.756846 49.9228,3.75516 49.922167,3.753852 49.921409,3.753544 49.921281,3.753516 49.921212,3.752952 49.920899,3.75249 49.920689,3.751903 49.920359,3.751541 49.920137,3.750791 49.919777,3.750508 49.919612,3.750384 49.919492,3.75045 49.919349,3.750377 49.919229,3.750029 49.918998,3.749501 49.918604,3.748671 49.91874,3.749258 49.919309,3.747992 49.920051,3.747399 49.920336,3.747112 49.920329,3.746831 49.920501,3.745439 49.92119,3.745761 49.921423,3.745426 49.921543,3.745614 49.922072,3.74556 49.922326,3.74541 49.922453,3.744947 49.92268,3.744218 49.923088,3.743491 49.923529,3.742815 49.923905,3.741764 49.924548,3.741561 49.924424,3.741434 49.924489,3.741025 49.924825,3.741352 49.924984,3.741573 49.925177,3.741532 49.925261,3.741114 49.925865,3.740654 49.92645,3.740745 49.927533,3.740687 49.927612,3.740343 49.927774,3.74036 49.928074,3.739634 49.928224,3.739489 49.9283,3.739501 49.928405,3.739724 49.928933,3.739887 49.929547,3.740041 49.930035,3.740258 49.930337,3.739994 49.93055,3.740093 49.930571,3.740726 49.930833,3.741012 49.930923,3.740883 49.931251,3.74075 49.931716,3.740485 49.932566,3.74009 49.933628,3.740345 49.933736,3.740119 49.934033,3.740146 49.93439,3.740233 49.935073,3.74036 49.935872,3.74045 49.936301,3.740155 49.936321,3.740189 49.936506,3.740464 49.93707,3.740679 49.937562,3.740699 49.937709,3.74065 49.93788,3.740777 49.938451)))', 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');

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
  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
  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';

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.740777 49.938451,3.742722 49.937754,3.743441 49.937718,3.743909 49.938206,3.743969 49.938417,3.74678 49.938112,3.747316 49.938032,3.748642 49.938164,3.748819 49.938273,3.750185 49.938898,3.750612 49.939235,3.751437 49.93949,3.752464 49.939856,3.752648 49.939901,3.753246 49.939816,3.754742 49.940493,3.755213 49.940787,3.755321 49.940933,3.755541 49.941025,3.756753 49.941117,3.756971 49.941177,3.757352 49.941089,3.758249 49.940811,3.758471 49.940686,3.758674 49.940485,3.75899 49.94056,3.759407 49.940727,3.7598 49.941239,3.759967 49.941285,3.761355 49.941122,3.762021 49.940694,3.76359 49.939617,3.76388 49.939356,3.764388 49.939219,3.764646 49.939121,3.765165 49.939004,3.765619 49.938789,3.766692 49.938166,3.767069 49.937709,3.767677 49.937675,3.768092 49.937908,3.768405 49.937619,3.768806 49.937363,3.76913 49.937075,3.768715 49.936879,3.768249 49.936716,3.768509 49.936403,3.768827 49.936168,3.769021 49.936318,3.769407 49.936067,3.77041 49.935282,3.770928 49.934836,3.771254 49.934965,3.771567 49.934637,3.770879 49.934292,3.770526 49.934097,3.769962 49.933824,3.769415 49.933526,3.769586 49.933408,3.769855 49.933111,3.769775 49.933033,3.769768 49.932868,3.769202 49.932208,3.768956 49.932105,3.768584 49.931831,3.768382 49.931728,3.76817 49.931706,3.767934 49.931009,3.767624 49.930565,3.768093 49.930513,3.767972 49.929935,3.76777 49.929838,3.767331 49.929733,3.766511 49.929599,3.766239 49.929571,3.76593 49.929381,3.764538 49.929307,3.764197 49.929254,3.764131 49.92907,3.764123 49.92877,3.764021 49.928567,3.763827 49.928442,3.763052 49.928441,3.762792 49.928428,3.76248 49.928106,3.761546 49.927085,3.761507 49.926896,3.760831 49.926084,3.760384 49.925599,3.759566 49.924646,3.759383 49.924317,3.758788 49.923851,3.758266 49.92353,3.757999 49.923328,3.757491 49.923166,3.756846 49.9228,3.75516 49.922167,3.753852 49.921409,3.753544 49.921281,3.753516 49.921212,3.752952 49.920899,3.75249 49.920689,3.751903 49.920359,3.751541 49.920137,3.750791 49.919777,3.750508 49.919612,3.750384 49.919492,3.75045 49.919349,3.750377 49.919229,3.750029 49.918998,3.749501 49.918604,3.748671 49.91874,3.749258 49.919309,3.747992 49.920051,3.747399 49.920336,3.747112 49.920329,3.746831 49.920501,3.745439 49.92119,3.745761 49.921423,3.745426 49.921543,3.745614 49.922072,3.74556 49.922326,3.74541 49.922453,3.744947 49.92268,3.744218 49.923088,3.743491 49.923529,3.742815 49.923905,3.741764 49.924548,3.741561 49.924424,3.741434 49.924489,3.741025 49.924825,3.741352 49.924984,3.741573 49.925177,3.741532 49.925261,3.741114 49.925865,3.740654 49.92645,3.740745 49.927533,3.740687 49.927612,3.740343 49.927774,3.74036 49.928074,3.739634 49.928224,3.739489 49.9283,3.739501 49.928405,3.739724 49.928933,3.739887 49.929547,3.740041 49.930035,3.740258 49.930337,3.739994 49.93055,3.740093 49.930571,3.740726 49.930833,3.741012 49.930923,3.740883 49.931251,3.74075 49.931716,3.740485 49.932566,3.74009 49.933628,3.740345 49.933736,3.740119 49.934033,3.740146 49.93439,3.740233 49.935073,3.74036 49.935872,3.74045 49.936301,3.740155 49.936321,3.740189 49.936506,3.740464 49.93707,3.740679 49.937562,3.740699 49.937709,3.74065 49.93788,3.740777 49.938451)))', 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;