Symfony Profiler

"COMMIT"

"COMMIT";

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

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

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

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

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

"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(((5.011563 45.934686,5.0109 45.934745,5.010423 45.934765,5.009173 45.934644,5.008964 45.934528,5.008774 45.934008,5.008392 45.932421,5.008057 45.932105,5.007709 45.931921,5.00749 45.931544,5.007111 45.930955,5.006509 45.930206,5.005411 45.928198,5.001036 45.927798,5.000761 45.927827,5.000476 45.927907,4.99939 45.928244,4.998635 45.928647,4.998316 45.928791,4.997991 45.928885,4.996954 45.929349,4.996636 45.929472,4.995882 45.92973,4.995213 45.92991,4.994773 45.930072,4.993995 45.930396,4.993406 45.930673,4.992982 45.930832,4.992744 45.931017,4.991999 45.931505,4.991745 45.931622,4.991053 45.931787,4.990623 45.93186,4.989906 45.932003,4.988497 45.93244,4.987958 45.932532,4.986855 45.93279,4.986305 45.932882,4.985762 45.932996,4.98568 45.93303,4.985093 45.934455,4.983605 45.93438,4.982995 45.93433,4.98258 45.93431,4.982127 45.934358,4.981946 45.934435,4.980878 45.935092,4.980223 45.935483,4.979404 45.935801,4.97866 45.936073,4.977517 45.936455,4.976438 45.936826,4.975496 45.937228,4.975199 45.937405,4.974553 45.937689,4.973963 45.937992,4.973846 45.938008,4.973426 45.937925,4.9732 45.937925,4.972756 45.938107,4.972632 45.938198,4.971956 45.938779,4.971647 45.939026,4.971297 45.939233,4.970361 45.939655,4.970075 45.939822,4.96948 45.940097,4.968186 45.940843,4.968193 45.941179,4.968152 45.941352,4.967922 45.941532,4.967527 45.941783,4.967167 45.942054,4.966187 45.942668,4.964693 45.943524,4.964441 45.943696,4.963808 45.944248,4.963205 45.944954,4.962876 45.945229,4.962409 45.945422,4.961903 45.945717,4.961272 45.946104,4.960623 45.946875,4.960969 45.947076,4.961329 45.947465,4.961439 45.947476,4.961432 45.947597,4.961778 45.94812,4.961833 45.948253,4.961904 45.948603,4.961843 45.948734,4.965777 45.95045,4.96652 45.950985,4.968362 45.954035,4.970433 45.957981,4.97053 45.958025,4.970976 45.958086,4.971701 45.958226,4.972708 45.958466,4.97312 45.958538,4.973401 45.958559,4.973988 45.958546,4.974499 45.958572,4.97484 45.95864,4.974774 45.961533,4.974898 45.962029,4.975136 45.962397,4.975111 45.962743,4.975167 45.962983,4.9754 45.963503,4.975611 45.964066,4.974175 45.965643,4.97418 45.965958,4.974496 45.966328,4.973805 45.969089,4.974995 45.96913,4.975433 45.968624,4.976421 45.968543,4.977138 45.968685,4.978116 45.968756,4.982812 45.968801,4.993338 45.965606,4.994035 45.96518,4.995077 45.964813,4.995369 45.964643,4.9966 45.964032,5.0012 45.962186,5.00149 45.962056,5.001714 45.962044,5.003066 45.962041,5.003299 45.962005,5.004841 45.962327,5.004794 45.961671,5.005413 45.961622,5.006347 45.96159,5.00667 45.961611,5.007946 45.961758,5.008202 45.961773,5.008799 45.961765,5.008019 45.958314,5.00938 45.957354,5.009517 45.956224,5.008818 45.955768,5.008729 45.95496,5.00928 45.954664,5.010291 45.95367,5.011136 45.9533,5.010761 45.951718,5.011152 45.950577,5.011198 45.950406,5.010722 45.946826,5.009828 45.943932,5.011223 45.938952,5.011782 45.9369,5.011628 45.935518,5.011563 45.934686)))'') 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(((5.011563 45.934686,5.0109 45.934745,5.010423 45.934765,5.009173 45.934644,5.008964 45.934528,5.008774 45.934008,5.008392 45.932421,5.008057 45.932105,5.007709 45.931921,5.00749 45.931544,5.007111 45.930955,5.006509 45.930206,5.005411 45.928198,5.001036 45.927798,5.000761 45.927827,5.000476 45.927907,4.99939 45.928244,4.998635 45.928647,4.998316 45.928791,4.997991 45.928885,4.996954 45.929349,4.996636 45.929472,4.995882 45.92973,4.995213 45.92991,4.994773 45.930072,4.993995 45.930396,4.993406 45.930673,4.992982 45.930832,4.992744 45.931017,4.991999 45.931505,4.991745 45.931622,4.991053 45.931787,4.990623 45.93186,4.989906 45.932003,4.988497 45.93244,4.987958 45.932532,4.986855 45.93279,4.986305 45.932882,4.985762 45.932996,4.98568 45.93303,4.985093 45.934455,4.983605 45.93438,4.982995 45.93433,4.98258 45.93431,4.982127 45.934358,4.981946 45.934435,4.980878 45.935092,4.980223 45.935483,4.979404 45.935801,4.97866 45.936073,4.977517 45.936455,4.976438 45.936826,4.975496 45.937228,4.975199 45.937405,4.974553 45.937689,4.973963 45.937992,4.973846 45.938008,4.973426 45.937925,4.9732 45.937925,4.972756 45.938107,4.972632 45.938198,4.971956 45.938779,4.971647 45.939026,4.971297 45.939233,4.970361 45.939655,4.970075 45.939822,4.96948 45.940097,4.968186 45.940843,4.968193 45.941179,4.968152 45.941352,4.967922 45.941532,4.967527 45.941783,4.967167 45.942054,4.966187 45.942668,4.964693 45.943524,4.964441 45.943696,4.963808 45.944248,4.963205 45.944954,4.962876 45.945229,4.962409 45.945422,4.961903 45.945717,4.961272 45.946104,4.960623 45.946875,4.960969 45.947076,4.961329 45.947465,4.961439 45.947476,4.961432 45.947597,4.961778 45.94812,4.961833 45.948253,4.961904 45.948603,4.961843 45.948734,4.965777 45.95045,4.96652 45.950985,4.968362 45.954035,4.970433 45.957981,4.97053 45.958025,4.970976 45.958086,4.971701 45.958226,4.972708 45.958466,4.97312 45.958538,4.973401 45.958559,4.973988 45.958546,4.974499 45.958572,4.97484 45.95864,4.974774 45.961533,4.974898 45.962029,4.975136 45.962397,4.975111 45.962743,4.975167 45.962983,4.9754 45.963503,4.975611 45.964066,4.974175 45.965643,4.97418 45.965958,4.974496 45.966328,4.973805 45.969089,4.974995 45.96913,4.975433 45.968624,4.976421 45.968543,4.977138 45.968685,4.978116 45.968756,4.982812 45.968801,4.993338 45.965606,4.994035 45.96518,4.995077 45.964813,4.995369 45.964643,4.9966 45.964032,5.0012 45.962186,5.00149 45.962056,5.001714 45.962044,5.003066 45.962041,5.003299 45.962005,5.004841 45.962327,5.004794 45.961671,5.005413 45.961622,5.006347 45.96159,5.00667 45.961611,5.007946 45.961758,5.008202 45.961773,5.008799 45.961765,5.008019 45.958314,5.00938 45.957354,5.009517 45.956224,5.008818 45.955768,5.008729 45.95496,5.00928 45.954664,5.010291 45.95367,5.011136 45.9533,5.010761 45.951718,5.011152 45.950577,5.011198 45.950406,5.010722 45.946826,5.009828 45.943932,5.011223 45.938952,5.011782 45.9369,5.011628 45.935518,5.011563 45.934686)))'), 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'01371'') = ?
  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,
  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(((5.011563 45.934686,5.0109 45.934745,5.010423 45.934765,5.009173 45.934644,5.008964 45.934528,5.008774 45.934008,5.008392 45.932421,5.008057 45.932105,5.007709 45.931921,5.00749 45.931544,5.007111 45.930955,5.006509 45.930206,5.005411 45.928198,5.001036 45.927798,5.000761 45.927827,5.000476 45.927907,4.99939 45.928244,4.998635 45.928647,4.998316 45.928791,4.997991 45.928885,4.996954 45.929349,4.996636 45.929472,4.995882 45.92973,4.995213 45.92991,4.994773 45.930072,4.993995 45.930396,4.993406 45.930673,4.992982 45.930832,4.992744 45.931017,4.991999 45.931505,4.991745 45.931622,4.991053 45.931787,4.990623 45.93186,4.989906 45.932003,4.988497 45.93244,4.987958 45.932532,4.986855 45.93279,4.986305 45.932882,4.985762 45.932996,4.98568 45.93303,4.985093 45.934455,4.983605 45.93438,4.982995 45.93433,4.98258 45.93431,4.982127 45.934358,4.981946 45.934435,4.980878 45.935092,4.980223 45.935483,4.979404 45.935801,4.97866 45.936073,4.977517 45.936455,4.976438 45.936826,4.975496 45.937228,4.975199 45.937405,4.974553 45.937689,4.973963 45.937992,4.973846 45.938008,4.973426 45.937925,4.9732 45.937925,4.972756 45.938107,4.972632 45.938198,4.971956 45.938779,4.971647 45.939026,4.971297 45.939233,4.970361 45.939655,4.970075 45.939822,4.96948 45.940097,4.968186 45.940843,4.968193 45.941179,4.968152 45.941352,4.967922 45.941532,4.967527 45.941783,4.967167 45.942054,4.966187 45.942668,4.964693 45.943524,4.964441 45.943696,4.963808 45.944248,4.963205 45.944954,4.962876 45.945229,4.962409 45.945422,4.961903 45.945717,4.961272 45.946104,4.960623 45.946875,4.960969 45.947076,4.961329 45.947465,4.961439 45.947476,4.961432 45.947597,4.961778 45.94812,4.961833 45.948253,4.961904 45.948603,4.961843 45.948734,4.965777 45.95045,4.96652 45.950985,4.968362 45.954035,4.970433 45.957981,4.97053 45.958025,4.970976 45.958086,4.971701 45.958226,4.972708 45.958466,4.97312 45.958538,4.973401 45.958559,4.973988 45.958546,4.974499 45.958572,4.97484 45.95864,4.974774 45.961533,4.974898 45.962029,4.975136 45.962397,4.975111 45.962743,4.975167 45.962983,4.9754 45.963503,4.975611 45.964066,4.974175 45.965643,4.97418 45.965958,4.974496 45.966328,4.973805 45.969089,4.974995 45.96913,4.975433 45.968624,4.976421 45.968543,4.977138 45.968685,4.978116 45.968756,4.982812 45.968801,4.993338 45.965606,4.994035 45.96518,4.995077 45.964813,4.995369 45.964643,4.9966 45.964032,5.0012 45.962186,5.00149 45.962056,5.001714 45.962044,5.003066 45.962041,5.003299 45.962005,5.004841 45.962327,5.004794 45.961671,5.005413 45.961622,5.006347 45.96159,5.00667 45.961611,5.007946 45.961758,5.008202 45.961773,5.008799 45.961765,5.008019 45.958314,5.00938 45.957354,5.009517 45.956224,5.008818 45.955768,5.008729 45.95496,5.00928 45.954664,5.010291 45.95367,5.011136 45.9533,5.010761 45.951718,5.011152 45.950577,5.011198 45.950406,5.010722 45.946826,5.009828 45.943932,5.011223 45.938952,5.011782 45.9369,5.011628 45.935518,5.011563 45.934686)))', 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
  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(((5.011563 45.934686,5.0109 45.934745,5.010423 45.934765,5.009173 45.934644,5.008964 45.934528,5.008774 45.934008,5.008392 45.932421,5.008057 45.932105,5.007709 45.931921,5.00749 45.931544,5.007111 45.930955,5.006509 45.930206,5.005411 45.928198,5.001036 45.927798,5.000761 45.927827,5.000476 45.927907,4.99939 45.928244,4.998635 45.928647,4.998316 45.928791,4.997991 45.928885,4.996954 45.929349,4.996636 45.929472,4.995882 45.92973,4.995213 45.92991,4.994773 45.930072,4.993995 45.930396,4.993406 45.930673,4.992982 45.930832,4.992744 45.931017,4.991999 45.931505,4.991745 45.931622,4.991053 45.931787,4.990623 45.93186,4.989906 45.932003,4.988497 45.93244,4.987958 45.932532,4.986855 45.93279,4.986305 45.932882,4.985762 45.932996,4.98568 45.93303,4.985093 45.934455,4.983605 45.93438,4.982995 45.93433,4.98258 45.93431,4.982127 45.934358,4.981946 45.934435,4.980878 45.935092,4.980223 45.935483,4.979404 45.935801,4.97866 45.936073,4.977517 45.936455,4.976438 45.936826,4.975496 45.937228,4.975199 45.937405,4.974553 45.937689,4.973963 45.937992,4.973846 45.938008,4.973426 45.937925,4.9732 45.937925,4.972756 45.938107,4.972632 45.938198,4.971956 45.938779,4.971647 45.939026,4.971297 45.939233,4.970361 45.939655,4.970075 45.939822,4.96948 45.940097,4.968186 45.940843,4.968193 45.941179,4.968152 45.941352,4.967922 45.941532,4.967527 45.941783,4.967167 45.942054,4.966187 45.942668,4.964693 45.943524,4.964441 45.943696,4.963808 45.944248,4.963205 45.944954,4.962876 45.945229,4.962409 45.945422,4.961903 45.945717,4.961272 45.946104,4.960623 45.946875,4.960969 45.947076,4.961329 45.947465,4.961439 45.947476,4.961432 45.947597,4.961778 45.94812,4.961833 45.948253,4.961904 45.948603,4.961843 45.948734,4.965777 45.95045,4.96652 45.950985,4.968362 45.954035,4.970433 45.957981,4.97053 45.958025,4.970976 45.958086,4.971701 45.958226,4.972708 45.958466,4.97312 45.958538,4.973401 45.958559,4.973988 45.958546,4.974499 45.958572,4.97484 45.95864,4.974774 45.961533,4.974898 45.962029,4.975136 45.962397,4.975111 45.962743,4.975167 45.962983,4.9754 45.963503,4.975611 45.964066,4.974175 45.965643,4.97418 45.965958,4.974496 45.966328,4.973805 45.969089,4.974995 45.96913,4.975433 45.968624,4.976421 45.968543,4.977138 45.968685,4.978116 45.968756,4.982812 45.968801,4.993338 45.965606,4.994035 45.96518,4.995077 45.964813,4.995369 45.964643,4.9966 45.964032,5.0012 45.962186,5.00149 45.962056,5.001714 45.962044,5.003066 45.962041,5.003299 45.962005,5.004841 45.962327,5.004794 45.961671,5.005413 45.961622,5.006347 45.96159,5.00667 45.961611,5.007946 45.961758,5.008202 45.961773,5.008799 45.961765,5.008019 45.958314,5.00938 45.957354,5.009517 45.956224,5.008818 45.955768,5.008729 45.95496,5.00928 45.954664,5.010291 45.95367,5.011136 45.9533,5.010761 45.951718,5.011152 45.950577,5.011198 45.950406,5.010722 45.946826,5.009828 45.943932,5.011223 45.938952,5.011782 45.9369,5.011628 45.935518,5.011563 45.934686)))', 4326))
  AND (tags->'man_made') = 'monitoring_station'
  AND (tags->'monitoring:bicycle') = 'yes';

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

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(((5.011563 45.934686,5.0109 45.934745,5.010423 45.934765,5.009173 45.934644,5.008964 45.934528,5.008774 45.934008,5.008392 45.932421,5.008057 45.932105,5.007709 45.931921,5.00749 45.931544,5.007111 45.930955,5.006509 45.930206,5.005411 45.928198,5.001036 45.927798,5.000761 45.927827,5.000476 45.927907,4.99939 45.928244,4.998635 45.928647,4.998316 45.928791,4.997991 45.928885,4.996954 45.929349,4.996636 45.929472,4.995882 45.92973,4.995213 45.92991,4.994773 45.930072,4.993995 45.930396,4.993406 45.930673,4.992982 45.930832,4.992744 45.931017,4.991999 45.931505,4.991745 45.931622,4.991053 45.931787,4.990623 45.93186,4.989906 45.932003,4.988497 45.93244,4.987958 45.932532,4.986855 45.93279,4.986305 45.932882,4.985762 45.932996,4.98568 45.93303,4.985093 45.934455,4.983605 45.93438,4.982995 45.93433,4.98258 45.93431,4.982127 45.934358,4.981946 45.934435,4.980878 45.935092,4.980223 45.935483,4.979404 45.935801,4.97866 45.936073,4.977517 45.936455,4.976438 45.936826,4.975496 45.937228,4.975199 45.937405,4.974553 45.937689,4.973963 45.937992,4.973846 45.938008,4.973426 45.937925,4.9732 45.937925,4.972756 45.938107,4.972632 45.938198,4.971956 45.938779,4.971647 45.939026,4.971297 45.939233,4.970361 45.939655,4.970075 45.939822,4.96948 45.940097,4.968186 45.940843,4.968193 45.941179,4.968152 45.941352,4.967922 45.941532,4.967527 45.941783,4.967167 45.942054,4.966187 45.942668,4.964693 45.943524,4.964441 45.943696,4.963808 45.944248,4.963205 45.944954,4.962876 45.945229,4.962409 45.945422,4.961903 45.945717,4.961272 45.946104,4.960623 45.946875,4.960969 45.947076,4.961329 45.947465,4.961439 45.947476,4.961432 45.947597,4.961778 45.94812,4.961833 45.948253,4.961904 45.948603,4.961843 45.948734,4.965777 45.95045,4.96652 45.950985,4.968362 45.954035,4.970433 45.957981,4.97053 45.958025,4.970976 45.958086,4.971701 45.958226,4.972708 45.958466,4.97312 45.958538,4.973401 45.958559,4.973988 45.958546,4.974499 45.958572,4.97484 45.95864,4.974774 45.961533,4.974898 45.962029,4.975136 45.962397,4.975111 45.962743,4.975167 45.962983,4.9754 45.963503,4.975611 45.964066,4.974175 45.965643,4.97418 45.965958,4.974496 45.966328,4.973805 45.969089,4.974995 45.96913,4.975433 45.968624,4.976421 45.968543,4.977138 45.968685,4.978116 45.968756,4.982812 45.968801,4.993338 45.965606,4.994035 45.96518,4.995077 45.964813,4.995369 45.964643,4.9966 45.964032,5.0012 45.962186,5.00149 45.962056,5.001714 45.962044,5.003066 45.962041,5.003299 45.962005,5.004841 45.962327,5.004794 45.961671,5.005413 45.961622,5.006347 45.96159,5.00667 45.961611,5.007946 45.961758,5.008202 45.961773,5.008799 45.961765,5.008019 45.958314,5.00938 45.957354,5.009517 45.956224,5.008818 45.955768,5.008729 45.95496,5.00928 45.954664,5.010291 45.95367,5.011136 45.9533,5.010761 45.951718,5.011152 45.950577,5.011198 45.950406,5.010722 45.946826,5.009828 45.943932,5.011223 45.938952,5.011782 45.9369,5.011628 45.935518,5.011563 45.934686)))', 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;

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