Symfony Profiler

Query Metrics

171 Database Queries

26 Different statements

29490.93 ms Query time

0 Invalid entities

0 Managed entities

Queries

Group similar statements

default connection


SELECT t0.id AS id_1, t0.zone AS zone_2, t0.completion_percent AS completion_percent_3, t0.places_count AS places_count_4, t0.avec_horaires AS avec_horaires_5, t0.avec_adresse AS avec_adresse_6, t0.avec_site AS avec_site_7, t0.avec_accessibilite AS avec_accessibilite_8, t0.avec_note AS avec_note_9, t0.name AS name_10, t0.population AS population_11, t0.siren AS siren_12, t0.code_epci AS code_epci_13, t0.codes_postaux AS codes_postaux_14, t0.date_created AS date_created_15, t0.date_modified AS date_modified_16, t0.avec_siret AS avec_siret_17, t0.avec_name AS avec_name_18, t0.avec_email AS avec_email_19, t0.osm_data_date_min AS osm_data_date_min_20, t0.osm_data_date_avg AS osm_data_date_avg_21, t0.osm_data_date_max AS osm_data_date_max_22, t0.budget_annuel AS budget_annuel_23, t0.lat AS lat_24, t0.lon AS lon_25, t0.date_labourage_requested AS date_labourage_requested_26, t0.date_labourage_done AS date_labourage_done_27, t0.kind AS kind_28 FROM stats t0 WHERE t0.zone = ? LIMIT 1
SELECT t0.id AS id_1, t0.uuid_for_url AS uuid_for_url_2, t0.osm_kind AS osm_kind_3, t0.email AS email_4, t0.opted_out AS opted_out_5, t0.dead AS dead_6, t0.note AS note_7, t0.modified_date AS modified_date_8, t0.zip_code AS zip_code_9, t0.asked_humains_support AS asked_humains_support_10, t0.last_contact_attempt_date AS last_contact_attempt_date_11, t0.osm_id AS osm_id_12, t0.name AS name_13, t0.has_opening_hours AS has_opening_hours_14, t0.has_address AS has_address_15, t0.has_website AS has_website_16, t0.has_wheelchair AS has_wheelchair_17, t0.has_note AS has_note_18, t0.main_tag AS main_tag_19, t0.note_content AS note_content_20, t0.displayed_date AS displayed_date_21, t0.lat AS lat_22, t0.lon AS lon_23, t0.street AS street_24, t0.housenumber AS housenumber_25, t0.siret AS siret_26, t0.habitants AS habitants_27, t0.osm_data_date AS osm_data_date_28, t0.osm_version AS osm_version_29, t0.osm_user AS osm_user_30, t0.osm_uid AS osm_uid_31, t0.osm_changeset AS osm_changeset_32, t0.email_content AS email_content_33, t0.place_count AS place_count_34, t0.stats_id AS stats_id_35 FROM place t0 WHERE t0.stats_id = ?
"START TRANSACTION"
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
INSERT INTO city_follow_up (name, measure, date, stats_id) VALUES (?, ?, ?, ?)
"COMMIT"
DELETE c1
FROM city_follow_up c1
INNER JOIN stats s ON c1.stats_id = s.id AND s.zone <> ?
INNER JOIN city_follow_up c2
    ON c1.stats_id = c2.stats_id
   AND c1.name = c2.name
   AND DATE(c1.date) = DATE(c2.date)
   AND c1.id <> c2.id
   AND (
       c2.measure > c1.measure
       OR (c2.measure = c1.measure AND c2.id > c1.id)
   )
WHERE c1.stats_id = ?

osm connection


SELECT 1
SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'osm_object_meta'
SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = ? AND table_name = ?
SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = ? AND table_name = ?
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
INSERT INTO osm_object_meta (
    osm_type, osm_id, geom, tags, version, osm_timestamp, changeset, osm_user, uid
)
SELECT
    'relation',
    latest.osm_id,
    latest.geom,
    latest.tags,
    latest.version,
    to_char(latest.valid_from AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS"Z"'),
    latest.changeset,
    latest.osm_user,
    latest.uid
FROM (
    SELECT DISTINCT ON (h.osm_id)
        h.osm_id,
        h.geom,
        h.tags,
        h.version,
        h.valid_from,
        h.changeset,
        h.osm_user,
        h.uid
    FROM osm_object_history h
    WHERE h.osm_type = 'relation'
      AND h.visible = true
      AND h.geom IS NOT NULL
      AND (h.tags->'boundary') = 'administrative'
      AND (h.tags->'admin_level') = '8'
      AND (h.tags->'ref:INSEE') IS NOT NULL
    ORDER BY h.osm_id, h.version DESC, h.valid_from DESC
) AS latest
WHERE NOT EXISTS (
    SELECT 1 FROM osm_object_meta m
    WHERE m.osm_type = 'relation' AND m.osm_id = latest.osm_id
)
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 COUNT(*) FROM information_schema.tables WHERE table_schema = ? AND table_name = ?
SELECT ST_AsText(ST_Multi(geom)) AS wkt
FROM (
    SELECT DISTINCT ON (h.osm_id)
        h.geom
    FROM osm_object_history h
    WHERE h.osm_type = 'relation'
      AND h.visible = true
      AND h.geom IS NOT NULL
      AND (h.tags->'ref:INSEE') = ?
      AND (h.tags->'admin_level') = '8'
      AND (h.tags->'boundary') = 'administrative'
    ORDER BY h.osm_id, h.version DESC, h.valid_from DESC
) latest
ORDER BY ST_Area(geom::geography) DESC NULLS LAST
LIMIT 1
CREATE TABLE IF NOT EXISTS zone_polygon (
                    insee_code TEXT PRIMARY KEY,
                    geom geometry(MultiPolygon, 4326) NOT NULL,
                    source TEXT NOT NULL,
                    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
                )
CREATE INDEX IF NOT EXISTS zone_polygon_geom_idx ON zone_polygon USING GIST (geom)
SELECT ST_AsText(ST_Multi(geom)) FROM zone_polygon WHERE insee_code = ?
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,
            CASE WHEN osm_type IN ('way','relation') THEN ST_Area(geom::geography) ELSE NULL END AS area_m2
            FROM osm_object_meta m WHERE geom IS NOT NULL AND ST_Intersects(m.geom, ST_GeomFromText(?, 4326))
AND (
  (tags->'emergency') = 'fire_hydrant' OR (tags->'amenity') = 'fire_hydrant'
  OR (tags->'amenity') = 'charging_station'
  OR (tags->'amenity') = 'toilets'
  OR (tags->'highway') = 'bus_stop' OR (tags->'public_transport') = 'platform'
  OR (tags->'emergency') = 'defibrillator'
  OR (tags->'man_made') = 'surveillance'
  OR (tags->'amenity') = 'recycling'
  OR (tags->'power') = 'substation'
  OR exist(tags, 'healthcare')
  OR (tags->'amenity') IN ('doctors','pharmacy','hospital','clinic','social_facility')
  OR (tags->'healthcare') = 'laboratory'
  OR (tags->'amenity') = 'school'
  OR (tags->'amenity') = 'police'
  OR (tags->'amenity') = 'bicycle_parking'
  OR ((tags->'advertising') = 'board' AND (tags->'message') = 'political')
  OR exist(tags, 'building')
  OR exist(tags, 'ref:FR:RNB')
  OR exist(tags, 'email') OR exist(tags, 'contact:email')
  OR (tags->'amenity') = 'bench'
  OR (tags->'amenity') = 'waste_basket'
  OR (tags->'highway') = 'street_lamp'
  OR (tags->'amenity') = 'drinking_water'
  OR (tags->'natural') = 'tree'
  OR (tags->'power') = 'pole'
  OR (tags->'man_made') = 'manhole'
  OR (tags->'amenity') = 'public_bookcase'
  OR (tags->'leisure') = 'playground'
  OR (tags->'amenity') = 'restaurant'
  OR exist(tags, 'air_conditioning')
  OR (tags->'highway') = 'cycleway'
  OR (tags->'highway') = '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, 'highway') AND (tags->'highway') IS NOT NULL AND (tags->'highway') NOT IN ('cycleway', 'busway') AND exist(tags, 'bicycle') AND (tags->'bicycle') IS NOT NULL AND (tags->'bicycle') <> 'no')
  OR (tags->'landuse') = 'construction'
  OR (tags->'railway') IN ('station','halt')
  OR ((tags->'public_transport') = 'station' AND (tags->'train') = 'yes')
  OR (tags->'office') IN ('association','ngo')
  OR exist(tags, 'club')
  OR (tags->'social_facility') IN ('ngo','outreach')
  OR EXISTS (SELECT 1 FROM unnest(akeys(tags)) k WHERE k LIKE 'proposed:%')
  OR EXISTS (SELECT 1 FROM unnest(akeys(tags)) k WHERE k LIKE 'was:%')
  OR EXISTS (SELECT 1 FROM unnest(akeys(tags)) k WHERE k LIKE 'addr:%')
)
SELECT 1
SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'osm_object_meta'
SELECT osm_type, osm_id::text AS osm_id,
       ST_Y(ST_Centroid(geom)) AS lat, ST_X(ST_Centroid(geom)) AS lon,
       tags::jsonb AS tags_json, version, osm_timestamp, changeset, osm_user, uid::bigint AS uid
FROM osm_object_meta m
WHERE geom IS NOT NULL
  AND ST_Intersects(m.geom, ST_GeomFromText(?, 4326))
  AND (
       (tags->'emergency') = 'fire_hydrant'
    OR (tags->'amenity') = 'fire_hydrant'
  )
SELECT 1
SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'osm_object_meta'
SELECT osm_type, osm_id::text AS osm_id,
       ST_Y(ST_Centroid(geom)) AS lat, ST_X(ST_Centroid(geom)) AS lon,
       tags::jsonb AS tags_json, version, osm_timestamp, changeset, osm_user, uid::bigint AS uid,
       CASE WHEN osm_type IN ('way','relation') THEN ST_Area(geom::geography) ELSE NULL END AS area_m2
FROM osm_object_meta m
WHERE geom IS NOT NULL
  AND ST_Intersects(m.geom, ST_GeomFromText(?, 4326))
  AND (tags->'amenity') = 'parking'
SELECT 1
SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'osm_object_meta'
SELECT
  COALESCE(SUM(CASE
    WHEN (tags->'highway') IN ('cycleway', 'busway') THEN COALESCE(ST_Length(ST_Intersection(ST_MakeValid(m.geom), z.poly)::geography), 0)
    WHEN (tags->'cycleway:both') IN ('track', 'opposite_track', 'separate')
     AND NOT ((tags->'highway') = 'proposed' AND exist(tags, 'proposed') AND (tags->'proposed') = 'cycleway')
     AND exist(tags, 'highway')
     AND (tags->'highway') IS NOT NULL
     AND (tags->'highway') NOT IN ('cycleway', 'busway')
    THEN COALESCE(ST_Length(ST_Intersection(ST_MakeValid(m.geom), z.poly)::geography), 0)
    ELSE 0 END), 0) / 1000.0 AS separated_km,
  COALESCE(SUM(CASE
    WHEN EXISTS (SELECT 1 FROM unnest(akeys(tags)) k WHERE k = 'cycleway' OR k LIKE 'cycleway:%')
     AND NOT ((tags->'highway') = 'proposed' AND exist(tags, 'proposed') AND (tags->'proposed') = 'cycleway')
     AND exist(tags, 'highway')
     AND (tags->'highway') IS NOT NULL
     AND (tags->'highway') NOT IN ('cycleway', 'busway')
     AND NOT ((tags->'cycleway:both') IN ('track', 'opposite_track', 'separate'))
    THEN COALESCE(ST_Length(ST_Intersection(ST_MakeValid(m.geom), z.poly)::geography), 0) ELSE 0 END), 0) / 1000.0 AS lane_km,
  COALESCE(SUM(CASE
    WHEN (exist(tags, 'bicycle') AND (tags->'bicycle') IS NOT NULL AND (tags->'bicycle') <> 'no')
     AND NOT ((tags->'highway') = 'proposed' AND exist(tags, 'proposed') AND (tags->'proposed') = 'cycleway')
     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 COALESCE(ST_Length(ST_Intersection(ST_MakeValid(m.geom), z.poly)::geography), 0) ELSE 0 END), 0) / 1000.0 AS designated_km,
  COALESCE(SUM(CASE
    WHEN (tags->'highway') = 'proposed' AND exist(tags, 'proposed') AND (tags->'proposed') = 'cycleway'
    THEN COALESCE(ST_Length(ST_Intersection(ST_MakeValid(m.geom), z.poly)::geography), 0) ELSE 0 END), 0) / 1000.0 AS proposed_cycleway_km
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 (
    (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'))
    OR ((exist(tags, 'bicycle') AND (tags->'bicycle') IS NOT NULL AND (tags->'bicycle') <> 'no') AND exist(tags, 'highway'))
  )
SELECT COALESCE(SUM(COALESCE(ST_Length(ST_Intersection(ST_MakeValid(m.geom), z.poly)::geography), 0)), 0) / 1000.0 AS km
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 1
SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'osm_object_meta'
SELECT osm_type, osm_id::text AS osm_id,
       ST_Y(ST_Centroid(geom)) AS lat, ST_X(ST_Centroid(geom)) AS lon,
       tags::jsonb AS tags_json, version, osm_timestamp, changeset, osm_user, uid::bigint AS uid
FROM osm_object_meta m
WHERE geom IS NOT NULL
  AND ST_Intersects(m.geom, ST_GeomFromText(?, 4326))
  AND (tags->'man_made') = 'monitoring_station'
  AND (tags->'monitoring:bicycle') = 'yes'
SELECT 1
SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'osm_object_meta'
SELECT
  COALESCE(SUM(CASE WHEN exist(tags, 'highway') THEN COALESCE(ST_Length(ST_Intersection(ST_MakeValid(m.geom), z.poly)::geography), 0) ELSE 0 END), 0) / 1000.0 AS total_km,
  COALESCE(SUM(CASE WHEN (tags->'highway') = 'primary' THEN COALESCE(ST_Length(ST_Intersection(ST_MakeValid(m.geom), z.poly)::geography), 0) ELSE 0 END), 0) / 1000.0 AS primary_km,
  COALESCE(SUM(CASE WHEN (tags->'highway') = 'secondary' THEN COALESCE(ST_Length(ST_Intersection(ST_MakeValid(m.geom), z.poly)::geography), 0) ELSE 0 END), 0) / 1000.0 AS secondary_km,
  COALESCE(SUM(CASE WHEN (tags->'highway') = 'tertiary' THEN COALESCE(ST_Length(ST_Intersection(ST_MakeValid(m.geom), z.poly)::geography), 0) ELSE 0 END), 0) / 1000.0 AS tertiary_km,
  COALESCE(SUM(CASE WHEN (tags->'highway') = 'residential' THEN COALESCE(ST_Length(ST_Intersection(ST_MakeValid(m.geom), z.poly)::geography), 0) ELSE 0 END), 0) / 1000.0 AS residential_km,
  COALESCE(SUM(CASE WHEN (tags->'highway') = 'unclassified' THEN COALESCE(ST_Length(ST_Intersection(ST_MakeValid(m.geom), z.poly)::geography), 0) ELSE 0 END), 0) / 1000.0 AS unclassified_km,
  COALESCE(SUM(CASE WHEN (tags->'highway') = 'service' THEN COALESCE(ST_Length(ST_Intersection(ST_MakeValid(m.geom), z.poly)::geography), 0) ELSE 0 END), 0) / 1000.0 AS service_km,
  COALESCE(SUM(CASE WHEN (tags->'highway') = 'cycleway' THEN COALESCE(ST_Length(ST_Intersection(ST_MakeValid(m.geom), z.poly)::geography), 0) ELSE 0 END), 0) / 1000.0 AS cycleway_km,
  COALESCE(SUM(CASE WHEN (tags->'highway') = 'footway' THEN COALESCE(ST_Length(ST_Intersection(ST_MakeValid(m.geom), z.poly)::geography), 0) ELSE 0 END), 0) / 1000.0 AS footway_km,
  COALESCE(SUM(CASE WHEN (tags->'railway') = 'rail' THEN COALESCE(ST_Length(ST_Intersection(ST_MakeValid(m.geom), z.poly)::geography), 0) ELSE 0 END), 0) / 1000.0 AS rail_km,
  COUNT(*) FILTER (WHERE exist(tags, 'highway')) AS highway_count
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') OR (tags->'railway') = 'rail')
SELECT 1
SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'osm_object_meta'
SELECT osm_type, osm_id::text AS osm_id,
       ST_Y(ST_Centroid(geom)) AS lat, ST_X(ST_Centroid(geom)) AS lon,
       tags::jsonb AS tags_json, version, osm_timestamp, changeset, osm_user, uid::bigint AS uid,
       ST_AsGeoJSON(ST_Force2D(geom), 6, 0) AS geom_geojson,
       ST_Length(geom::geography) AS length_m,
       (tags->'route') AS route_value
FROM osm_object_meta m
WHERE geom IS NOT NULL
  AND osm_type = 'relation'
  AND ST_Intersects(m.geom, ST_GeomFromText(?, 4326))
  AND (tags->'type') = 'route'
  AND (tags->'route') IN ('hiking','foot','running','walking','fitness_trail','horse','inline_skates')
SELECT 1
SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'osm_object_meta'
SELECT osm_type, osm_id::text AS osm_id,
       ST_Y(ST_Centroid(geom)) AS lat, ST_X(ST_Centroid(geom)) AS lon,
       tags::jsonb AS tags_json, version, osm_timestamp, changeset, osm_user, uid::bigint AS uid
FROM osm_object_meta m
WHERE geom IS NOT NULL
  AND ST_Intersects(m.geom, ST_GeomFromText(?, 4326))
  AND (
       exist(tags, 'traffic_sign')
    OR (tags->'highway') IN ('traffic_signals','give_way','stop')
    OR (tags->'crossing') = 'traffic_signals'
    OR exist(tags, 'traffic_calming')
    OR (tags->'railway') IN ('level_crossing','crossing')
    OR exist(tags, 'maxheight')
    OR exist(tags, 'maxwidth')
    OR (osm_type IN ('way','relation') AND (
        exist(tags, 'maxspeed')
     OR (tags->'oneway') = 'yes'
    ))
  )
SELECT 1
SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'osm_object_meta'
SELECT osm_type, osm_id::text AS osm_id, ST_Y(ST_Centroid(geom)) AS lat, ST_X(ST_Centroid(geom)) AS lon,
            tags::jsonb AS tags_json, version, osm_timestamp, changeset, osm_user, uid::bigint AS uid
            FROM osm_object_meta m
            WHERE geom IS NOT NULL AND ST_Intersects(m.geom, ST_GeomFromText(?, 4326))
            AND akeys(tags) && ARRAY['amenity','shop','office','craft','leisure','healthcare','emergency','man_made','power','highway','railway','public_transport','landuse','historic','barrier','tourism','sport','place','waterway','natural','geological','route','military','traffic_sign','traffic_calming','seamark','route_master','water','airway','aerialway','building']::text[]

Database Connections

Name	Service
default	doctrine.dbal.default_connection
osm	doctrine.dbal.osm_connection

Entity Managers

Name	Service
default	doctrine.orm.default_entity_manager

Second Level Cache

Second Level Cache is not enabled.

Managed Entities

default entity manager

Class	Amount of managed objects

Entities Mapping

Class	Mapping errors
App\Entity\Stats	No errors.
App\Entity\Place	No errors.
App\Entity\StatsHistory	No errors.
App\Entity\CityFollowUp	No errors.
App\Entity\ZonePlaces	No errors.
App\Entity\SireneEntreprise	No errors.

Symfony Profiler

GET https://osm-commerces.cipherbliss.com/admin/followup/01026

Configuration Settings

Theme

Page Width

Query Metrics

Queries

default connection

osm connection

Database Connections

Entity Managers

Second Level Cache

Managed Entities

default entity manager

Entities Mapping