Symfony Profiler

Query Metrics

34 Database Queries

28 Different statements

211.46 ms Query time

0 Invalid entities

5205 Managed entities

Grouped Statements

Show all queries

default connection

Time▼

Count

Info

23.11 ms
(10.93%)

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 = ?

Parameters:

[
  26610
]

View formatted query View runnable query Explain query View query backtrace

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

#	File/Call
1	Doctrine\ORM\Persisters\Entity\BasicEntityPersister->getOneToManyStatement (line 1872)
2	Doctrine\ORM\Persisters\Entity\BasicEntityPersister->loadOneToManyCollection (line 1817)
3	Doctrine\ORM\UnitOfWork->loadCollection (line 2722)
4	Doctrine\ORM\PersistentCollection->doInitialize (line 636)
5	Doctrine\ORM\PersistentCollection->initialize (line 186)
6	Doctrine\Common\Collections\AbstractLazyCollection->count (line 39)
7	Doctrine\ORM\PersistentCollection->count (line 391)
8	Doctrine\ORM\PersistentCollection->isEmpty (line 446)
9	App\Controller\AdminController->stats (line 534)
10	Symfony\Component\HttpKernel\HttpKernel->handleRaw (line 183)
11	Symfony\Component\HttpKernel\HttpKernel->handle (line 76)
12	Symfony\Component\HttpKernel\Kernel->handle (line 182)
13	Symfony\Component\Runtime\Runner\Symfony\HttpKernelRunner->run (line 35)
14	require_once (line 29)
15	require_once (line 5)

4.06 ms
(1.92%)

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 = ?

Parameters:

[
  26610
]

View formatted query View runnable query Explain query View query backtrace

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

#	File/Call
1	Doctrine\ORM\Persisters\Entity\BasicEntityPersister->getOneToManyStatement (line 1872)
2	Doctrine\ORM\Persisters\Entity\BasicEntityPersister->loadOneToManyCollection (line 1817)
3	Doctrine\ORM\UnitOfWork->loadCollection (line 2722)
4	Doctrine\ORM\PersistentCollection->doInitialize (line 636)
5	Doctrine\ORM\PersistentCollection->initialize (line 186)
6	Doctrine\Common\Collections\AbstractLazyCollection->getIterator (line 313)
7	App\Controller\AdminController->stats (line 1105)
8	Symfony\Component\HttpKernel\HttpKernel->handleRaw (line 183)
9	Symfony\Component\HttpKernel\HttpKernel->handle (line 76)
10	Symfony\Component\HttpKernel\Kernel->handle (line 182)
11	Symfony\Component\Runtime\Runner\Symfony\HttpKernelRunner->run (line 35)
12	require_once (line 29)
13	require_once (line 5)

3.77 ms
(1.78%)

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

Parameters:

[
  26610
]

View formatted query View runnable query Explain query View query backtrace

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 = 26610 GROUP BY p0_.osm_user ORDER BY sclr_1 DESC LIMIT 100;

#	File/Call
1	Doctrine\ORM\Query\Exec\FinalizedSelectExecutor->execute (line 27)
2	Doctrine\ORM\Query->_doExecute (line 296)
3	Doctrine\ORM\AbstractQuery->executeIgnoreQueryCache (line 930)
4	Doctrine\ORM\AbstractQuery->execute (line 886)
5	Doctrine\ORM\AbstractQuery->getResult (line 688)
6	App\Controller\AdminController->stats (line 955)
7	Symfony\Component\HttpKernel\HttpKernel->handleRaw (line 183)
8	Symfony\Component\HttpKernel\HttpKernel->handle (line 76)
9	Symfony\Component\HttpKernel\Kernel->handle (line 182)
10	Symfony\Component\Runtime\Runner\Symfony\HttpKernelRunner->run (line 35)
11	require_once (line 29)
12	require_once (line 5)

3.40 ms
(1.61%)

"COMMIT"

Parameters:

[]

View formatted query View runnable query Explain query View query backtrace

"COMMIT"

"COMMIT";

#	File/Call
1	Doctrine\ORM\UnitOfWork->commit (line 432)
2	Doctrine\ORM\EntityManager->flush (line 268)
3	App\Service\ActionLogger->log (line 33)
4	App\Controller\AdminController->stats (line 664)
5	Symfony\Component\HttpKernel\HttpKernel->handleRaw (line 183)
6	Symfony\Component\HttpKernel\HttpKernel->handle (line 76)
7	Symfony\Component\HttpKernel\Kernel->handle (line 182)
8	Symfony\Component\Runtime\Runner\Symfony\HttpKernelRunner->run (line 35)
9	require_once (line 29)
10	require_once (line 5)

0.81 ms
(0.38%)

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

Parameters:

[
  "stats_de_ville"
  "{"insee_code":"37161","nom":"37161"}"
  "2026-07-15 04:11:12"
  "https://osm-labo.cipherbliss.com/admin/labourer/37161?deleteMissing=1&disableFollowUpCleanup=1"
  null
]

View formatted query View runnable query Explain query View query backtrace

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

#	File/Call
1	Doctrine\DBAL\Statement->executeStatement (line 133)
2	Doctrine\ORM\Persisters\Entity\BasicEntityPersister->executeInserts (line 253)
3	Doctrine\ORM\UnitOfWork->executeInserts (line 1059)
4	Doctrine\ORM\UnitOfWork->commit (line 402)
5	Doctrine\ORM\EntityManager->flush (line 268)
6	App\Service\ActionLogger->log (line 33)
7	App\Controller\AdminController->stats (line 664)
8	Symfony\Component\HttpKernel\HttpKernel->handleRaw (line 183)
9	Symfony\Component\HttpKernel\HttpKernel->handle (line 76)
10	Symfony\Component\HttpKernel\Kernel->handle (line 182)
11	Symfony\Component\Runtime\Runner\Symfony\HttpKernelRunner->run (line 35)
12	require_once (line 29)
13	require_once (line 5)

0.73 ms
(0.34%)

"START TRANSACTION"

Parameters:

[]

View formatted query View runnable query Explain query View query backtrace

"START TRANSACTION"

"START TRANSACTION";

#	File/Call
1	Doctrine\ORM\UnitOfWork->commit (line 381)
2	Doctrine\ORM\EntityManager->flush (line 268)
3	App\Service\ActionLogger->log (line 33)
4	App\Controller\AdminController->stats (line 664)
5	Symfony\Component\HttpKernel\HttpKernel->handleRaw (line 183)
6	Symfony\Component\HttpKernel\HttpKernel->handle (line 76)
7	Symfony\Component\HttpKernel\Kernel->handle (line 182)
8	Symfony\Component\Runtime\Runner\Symfony\HttpKernelRunner->run (line 35)
9	require_once (line 29)
10	require_once (line 5)

0.44 ms
(0.21%)

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

Parameters:

[
  26610
]

View formatted query View runnable query Explain query View query backtrace

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

#	File/Call
1	Doctrine\ORM\Query\Exec\FinalizedSelectExecutor->execute (line 27)
2	Doctrine\ORM\Query->_doExecute (line 296)
3	Doctrine\ORM\AbstractQuery->executeIgnoreQueryCache (line 930)
4	Doctrine\ORM\AbstractQuery->execute (line 886)
5	Doctrine\ORM\AbstractQuery->getResult (line 688)
6	App\Controller\AdminController->stats (line 757)
7	Symfony\Component\HttpKernel\HttpKernel->handleRaw (line 183)
8	Symfony\Component\HttpKernel\HttpKernel->handle (line 76)
9	Symfony\Component\HttpKernel\Kernel->handle (line 182)
10	Symfony\Component\Runtime\Runner\Symfony\HttpKernelRunner->run (line 35)
11	require_once (line 29)
12	require_once (line 5)

0.44 ms
(0.21%)

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

Parameters:

[
  "37161"
]

View formatted query View runnable query Explain query View query backtrace

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

#	File/Call
1	Doctrine\ORM\Query\Exec\FinalizedSelectExecutor->execute (line 27)
2	Doctrine\ORM\Query->_doExecute (line 296)
3	Doctrine\ORM\AbstractQuery->executeIgnoreQueryCache (line 930)
4	Doctrine\ORM\AbstractQuery->execute (line 886)
5	Doctrine\ORM\AbstractQuery->getOneOrNullResult (line 737)
6	App\Repository\StatsRepository->findOneCanonicalByZone (line 90)
7	App\Controller\AdminController->stats (line 507)
8	Symfony\Component\HttpKernel\HttpKernel->handleRaw (line 183)
9	Symfony\Component\HttpKernel\HttpKernel->handle (line 76)
10	Symfony\Component\HttpKernel\Kernel->handle (line 182)
11	Symfony\Component\Runtime\Runner\Symfony\HttpKernelRunner->run (line 35)
12	require_once (line 29)
13	require_once (line 5)

0.37 ms
(0.17%)

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

Parameters:

[
  "37161"
]

View formatted query View runnable query Explain query View query backtrace

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

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

#	File/Call
1	Doctrine\ORM\Persisters\Entity\BasicEntityPersister->count (line 867)
2	Doctrine\ORM\EntityRepository->count (line 140)
3	Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepositoryProxy->count (line 87)
4	App\Controller\AdminController->stats (line 690)
5	Symfony\Component\HttpKernel\HttpKernel->handleRaw (line 183)
6	Symfony\Component\HttpKernel\HttpKernel->handle (line 76)
7	Symfony\Component\HttpKernel\Kernel->handle (line 182)
8	Symfony\Component\Runtime\Runner\Symfony\HttpKernelRunner->run (line 35)
9	require_once (line 29)
10	require_once (line 5)

osm connection

Time▼

Count

Info

101.24 ms
(47.88%)

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

Parameters:

[
  "MULTIPOLYGON(((1.087111 47.46182,1.08909 47.462246,1.092332 47.462641,1.092727 47.462735,1.097419 47.463505,1.098936 47.463881,1.101506 47.464276,1.101781 47.464271,1.102317 47.464316,1.103111 47.464445,1.104379 47.464635,1.105454 47.464751,1.106391 47.464808,1.107206 47.464936,1.10826 47.465189,1.110461 47.465728,1.112817 47.466109,1.113381 47.464294,1.113513 47.464126,1.113964 47.463055,1.114071 47.462741,1.113794 47.462605,1.114473 47.46203,1.114903 47.46173,1.114985 47.461732,1.115472 47.461593,1.115968 47.46138,1.116321 47.461159,1.116928 47.460452,1.117042 47.4604,1.11704 47.460282,1.116933 47.460044,1.116965 47.459839,1.116925 47.459778,1.117368 47.459246,1.117443 47.459017,1.118027 47.458908,1.118495 47.458767,1.11939 47.458349,1.119656 47.458289,1.120199 47.4582,1.120415 47.458111,1.120719 47.457884,1.120875 47.457655,1.120873 47.45734,1.120651 47.456797,1.121194 47.455606,1.121927 47.454859,1.122425 47.454412,1.122927 47.45403,1.123357 47.453679,1.123952 47.453131,1.124907 47.452293,1.125089 47.452157,1.126006 47.451518,1.126377 47.451269,1.126896 47.451069,1.127978 47.450724,1.128017 47.450683,1.128967 47.449939,1.12957 47.449631,1.130637 47.449417,1.131571 47.449112,1.132834 47.44899,1.133466 47.448979,1.13331 47.448543,1.133039 47.448055,1.132811 47.447101,1.13236 47.446467,1.131789 47.445381,1.131124 47.445418,1.130411 47.444737,1.129368 47.443916,1.129203 47.443688,1.128268 47.442735,1.126571 47.441548,1.125731 47.441028,1.124897 47.440526,1.123783 47.439797,1.123311 47.439447,1.122901 47.439026,1.12218 47.438219,1.121841 47.437749,1.121621 47.43737,1.1215 47.436836,1.121392 47.436568,1.121113 47.435443,1.120921 47.435084,1.120785 47.434901,1.119945 47.433588,1.119338 47.432567,1.119076 47.432165,1.11867 47.431598,1.118387 47.431275,1.118186 47.431098,1.117132 47.430316,1.116901 47.430117,1.116291 47.429483,1.115839 47.429127,1.115521 47.428979,1.115232 47.428888,1.114677 47.428629,1.113787 47.428168,1.11365 47.428024,1.112005 47.42916,1.110264 47.429861,1.10822 47.430323,1.107946 47.43041,1.105716 47.431461,1.105221 47.431668,1.105049 47.431665,1.104811 47.43131,1.10449 47.430874,1.104162 47.430485,1.103967 47.430341,1.103558 47.430251,1.103088 47.43024,1.102223 47.430253,1.097606 47.429535,1.095524 47.429232,1.093631 47.428931,1.093506 47.428922,1.090873 47.429263,1.087938 47.429606,1.086315 47.429787,1.085493 47.429886,1.085249 47.429865,1.084904 47.429694,1.084535 47.429396,1.083925 47.428869,1.082447 47.427648,1.082039 47.427881,1.08165 47.428165,1.080755 47.428852,1.080411 47.429103,1.080311 47.429151,1.079566 47.429379,1.078468 47.429727,1.078376 47.429776,1.076567 47.431017,1.075953 47.431466,1.075591 47.431866,1.075091 47.43223,1.074834 47.432567,1.073985 47.433577,1.073837 47.433703,1.073026 47.434645,1.072682 47.435507,1.072429 47.436065,1.072193 47.436546,1.07203 47.436825,1.071874 47.437291,1.071604 47.437838,1.071569 47.437982,1.071088 47.438199,1.070643 47.439024,1.070472 47.439412,1.070304 47.439734,1.069874 47.440495,1.069744 47.440877,1.069643 47.441083,1.069412 47.441448,1.068678 47.442464,1.068271 47.442968,1.067894 47.443303,1.067609 47.443622,1.067599 47.443697,1.067368 47.443992,1.067125 47.444379,1.067073 47.444497,1.066874 47.444554,1.066175 47.445276,1.06593 47.445481,1.065741 47.445559,1.065297 47.445543,1.064968 47.445835,1.064745 47.445961,1.06432 47.446425,1.064119 47.446776,1.064069 47.447061,1.063974 47.447117,1.06396 47.448416,1.06447 47.449312,1.06497 47.449758,1.065375 47.450039,1.065811 47.450291,1.066453 47.450745,1.066905 47.451022,1.067203 47.451177,1.068428 47.451881,1.068973 47.452164,1.069937 47.452832,1.070654 47.453151,1.071011 47.453193,1.071369 47.453047,1.075437 47.455225,1.07856 47.457054,1.080347 47.458204,1.083111 47.460008,1.083558 47.460205,1.085007 47.461078,1.086781 47.461771,1.087111 47.46182)))"
  2000.0
]

View formatted query View runnable query Explain query View query backtrace

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(((1.087111 47.46182,1.08909 47.462246,1.092332 47.462641,1.092727 47.462735,1.097419 47.463505,1.098936 47.463881,1.101506 47.464276,1.101781 47.464271,1.102317 47.464316,1.103111 47.464445,1.104379 47.464635,1.105454 47.464751,1.106391 47.464808,1.107206 47.464936,1.10826 47.465189,1.110461 47.465728,1.112817 47.466109,1.113381 47.464294,1.113513 47.464126,1.113964 47.463055,1.114071 47.462741,1.113794 47.462605,1.114473 47.46203,1.114903 47.46173,1.114985 47.461732,1.115472 47.461593,1.115968 47.46138,1.116321 47.461159,1.116928 47.460452,1.117042 47.4604,1.11704 47.460282,1.116933 47.460044,1.116965 47.459839,1.116925 47.459778,1.117368 47.459246,1.117443 47.459017,1.118027 47.458908,1.118495 47.458767,1.11939 47.458349,1.119656 47.458289,1.120199 47.4582,1.120415 47.458111,1.120719 47.457884,1.120875 47.457655,1.120873 47.45734,1.120651 47.456797,1.121194 47.455606,1.121927 47.454859,1.122425 47.454412,1.122927 47.45403,1.123357 47.453679,1.123952 47.453131,1.124907 47.452293,1.125089 47.452157,1.126006 47.451518,1.126377 47.451269,1.126896 47.451069,1.127978 47.450724,1.128017 47.450683,1.128967 47.449939,1.12957 47.449631,1.130637 47.449417,1.131571 47.449112,1.132834 47.44899,1.133466 47.448979,1.13331 47.448543,1.133039 47.448055,1.132811 47.447101,1.13236 47.446467,1.131789 47.445381,1.131124 47.445418,1.130411 47.444737,1.129368 47.443916,1.129203 47.443688,1.128268 47.442735,1.126571 47.441548,1.125731 47.441028,1.124897 47.440526,1.123783 47.439797,1.123311 47.439447,1.122901 47.439026,1.12218 47.438219,1.121841 47.437749,1.121621 47.43737,1.1215 47.436836,1.121392 47.436568,1.121113 47.435443,1.120921 47.435084,1.120785 47.434901,1.119945 47.433588,1.119338 47.432567,1.119076 47.432165,1.11867 47.431598,1.118387 47.431275,1.118186 47.431098,1.117132 47.430316,1.116901 47.430117,1.116291 47.429483,1.115839 47.429127,1.115521 47.428979,1.115232 47.428888,1.114677 47.428629,1.113787 47.428168,1.11365 47.428024,1.112005 47.42916,1.110264 47.429861,1.10822 47.430323,1.107946 47.43041,1.105716 47.431461,1.105221 47.431668,1.105049 47.431665,1.104811 47.43131,1.10449 47.430874,1.104162 47.430485,1.103967 47.430341,1.103558 47.430251,1.103088 47.43024,1.102223 47.430253,1.097606 47.429535,1.095524 47.429232,1.093631 47.428931,1.093506 47.428922,1.090873 47.429263,1.087938 47.429606,1.086315 47.429787,1.085493 47.429886,1.085249 47.429865,1.084904 47.429694,1.084535 47.429396,1.083925 47.428869,1.082447 47.427648,1.082039 47.427881,1.08165 47.428165,1.080755 47.428852,1.080411 47.429103,1.080311 47.429151,1.079566 47.429379,1.078468 47.429727,1.078376 47.429776,1.076567 47.431017,1.075953 47.431466,1.075591 47.431866,1.075091 47.43223,1.074834 47.432567,1.073985 47.433577,1.073837 47.433703,1.073026 47.434645,1.072682 47.435507,1.072429 47.436065,1.072193 47.436546,1.07203 47.436825,1.071874 47.437291,1.071604 47.437838,1.071569 47.437982,1.071088 47.438199,1.070643 47.439024,1.070472 47.439412,1.070304 47.439734,1.069874 47.440495,1.069744 47.440877,1.069643 47.441083,1.069412 47.441448,1.068678 47.442464,1.068271 47.442968,1.067894 47.443303,1.067609 47.443622,1.067599 47.443697,1.067368 47.443992,1.067125 47.444379,1.067073 47.444497,1.066874 47.444554,1.066175 47.445276,1.06593 47.445481,1.065741 47.445559,1.065297 47.445543,1.064968 47.445835,1.064745 47.445961,1.06432 47.446425,1.064119 47.446776,1.064069 47.447061,1.063974 47.447117,1.06396 47.448416,1.06447 47.449312,1.06497 47.449758,1.065375 47.450039,1.065811 47.450291,1.066453 47.450745,1.066905 47.451022,1.067203 47.451177,1.068428 47.451881,1.068973 47.452164,1.069937 47.452832,1.070654 47.453151,1.071011 47.453193,1.071369 47.453047,1.075437 47.455225,1.07856 47.457054,1.080347 47.458204,1.083111 47.460008,1.083558 47.460205,1.085007 47.461078,1.086781 47.461771,1.087111 47.46182)))'') 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'))
  )
;

#	File/Call
1	Doctrine\DBAL\Connection->fetchAllAssociative (line 622)
2	App\Service\OsmDatabaseService->getMobilityCyclewayObjectsInZone (line 1183)
3	App\Service\Motocultrice->getMobilityCyclewayObjects (line 1633)
4	App\Controller\AdminController->stats (line 1278)
5	Symfony\Component\HttpKernel\HttpKernel->handleRaw (line 183)
6	Symfony\Component\HttpKernel\HttpKernel->handle (line 76)
7	Symfony\Component\HttpKernel\Kernel->handle (line 182)
8	Symfony\Component\Runtime\Runner\Symfony\HttpKernelRunner->run (line 35)
9	require_once (line 29)
10	require_once (line 5)

19.76 ms
(9.35%)

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

Parameters:

[
  "MULTIPOLYGON(((1.087111 47.46182,1.08909 47.462246,1.092332 47.462641,1.092727 47.462735,1.097419 47.463505,1.098936 47.463881,1.101506 47.464276,1.101781 47.464271,1.102317 47.464316,1.103111 47.464445,1.104379 47.464635,1.105454 47.464751,1.106391 47.464808,1.107206 47.464936,1.10826 47.465189,1.110461 47.465728,1.112817 47.466109,1.113381 47.464294,1.113513 47.464126,1.113964 47.463055,1.114071 47.462741,1.113794 47.462605,1.114473 47.46203,1.114903 47.46173,1.114985 47.461732,1.115472 47.461593,1.115968 47.46138,1.116321 47.461159,1.116928 47.460452,1.117042 47.4604,1.11704 47.460282,1.116933 47.460044,1.116965 47.459839,1.116925 47.459778,1.117368 47.459246,1.117443 47.459017,1.118027 47.458908,1.118495 47.458767,1.11939 47.458349,1.119656 47.458289,1.120199 47.4582,1.120415 47.458111,1.120719 47.457884,1.120875 47.457655,1.120873 47.45734,1.120651 47.456797,1.121194 47.455606,1.121927 47.454859,1.122425 47.454412,1.122927 47.45403,1.123357 47.453679,1.123952 47.453131,1.124907 47.452293,1.125089 47.452157,1.126006 47.451518,1.126377 47.451269,1.126896 47.451069,1.127978 47.450724,1.128017 47.450683,1.128967 47.449939,1.12957 47.449631,1.130637 47.449417,1.131571 47.449112,1.132834 47.44899,1.133466 47.448979,1.13331 47.448543,1.133039 47.448055,1.132811 47.447101,1.13236 47.446467,1.131789 47.445381,1.131124 47.445418,1.130411 47.444737,1.129368 47.443916,1.129203 47.443688,1.128268 47.442735,1.126571 47.441548,1.125731 47.441028,1.124897 47.440526,1.123783 47.439797,1.123311 47.439447,1.122901 47.439026,1.12218 47.438219,1.121841 47.437749,1.121621 47.43737,1.1215 47.436836,1.121392 47.436568,1.121113 47.435443,1.120921 47.435084,1.120785 47.434901,1.119945 47.433588,1.119338 47.432567,1.119076 47.432165,1.11867 47.431598,1.118387 47.431275,1.118186 47.431098,1.117132 47.430316,1.116901 47.430117,1.116291 47.429483,1.115839 47.429127,1.115521 47.428979,1.115232 47.428888,1.114677 47.428629,1.113787 47.428168,1.11365 47.428024,1.112005 47.42916,1.110264 47.429861,1.10822 47.430323,1.107946 47.43041,1.105716 47.431461,1.105221 47.431668,1.105049 47.431665,1.104811 47.43131,1.10449 47.430874,1.104162 47.430485,1.103967 47.430341,1.103558 47.430251,1.103088 47.43024,1.102223 47.430253,1.097606 47.429535,1.095524 47.429232,1.093631 47.428931,1.093506 47.428922,1.090873 47.429263,1.087938 47.429606,1.086315 47.429787,1.085493 47.429886,1.085249 47.429865,1.084904 47.429694,1.084535 47.429396,1.083925 47.428869,1.082447 47.427648,1.082039 47.427881,1.08165 47.428165,1.080755 47.428852,1.080411 47.429103,1.080311 47.429151,1.079566 47.429379,1.078468 47.429727,1.078376 47.429776,1.076567 47.431017,1.075953 47.431466,1.075591 47.431866,1.075091 47.43223,1.074834 47.432567,1.073985 47.433577,1.073837 47.433703,1.073026 47.434645,1.072682 47.435507,1.072429 47.436065,1.072193 47.436546,1.07203 47.436825,1.071874 47.437291,1.071604 47.437838,1.071569 47.437982,1.071088 47.438199,1.070643 47.439024,1.070472 47.439412,1.070304 47.439734,1.069874 47.440495,1.069744 47.440877,1.069643 47.441083,1.069412 47.441448,1.068678 47.442464,1.068271 47.442968,1.067894 47.443303,1.067609 47.443622,1.067599 47.443697,1.067368 47.443992,1.067125 47.444379,1.067073 47.444497,1.066874 47.444554,1.066175 47.445276,1.06593 47.445481,1.065741 47.445559,1.065297 47.445543,1.064968 47.445835,1.064745 47.445961,1.06432 47.446425,1.064119 47.446776,1.064069 47.447061,1.063974 47.447117,1.06396 47.448416,1.06447 47.449312,1.06497 47.449758,1.065375 47.450039,1.065811 47.450291,1.066453 47.450745,1.066905 47.451022,1.067203 47.451177,1.068428 47.451881,1.068973 47.452164,1.069937 47.452832,1.070654 47.453151,1.071011 47.453193,1.071369 47.453047,1.075437 47.455225,1.07856 47.457054,1.080347 47.458204,1.083111 47.460008,1.083558 47.460205,1.085007 47.461078,1.086781 47.461771,1.087111 47.46182)))"
]

View formatted query View runnable query Explain query View query backtrace

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(((1.087111 47.46182,1.08909 47.462246,1.092332 47.462641,1.092727 47.462735,1.097419 47.463505,1.098936 47.463881,1.101506 47.464276,1.101781 47.464271,1.102317 47.464316,1.103111 47.464445,1.104379 47.464635,1.105454 47.464751,1.106391 47.464808,1.107206 47.464936,1.10826 47.465189,1.110461 47.465728,1.112817 47.466109,1.113381 47.464294,1.113513 47.464126,1.113964 47.463055,1.114071 47.462741,1.113794 47.462605,1.114473 47.46203,1.114903 47.46173,1.114985 47.461732,1.115472 47.461593,1.115968 47.46138,1.116321 47.461159,1.116928 47.460452,1.117042 47.4604,1.11704 47.460282,1.116933 47.460044,1.116965 47.459839,1.116925 47.459778,1.117368 47.459246,1.117443 47.459017,1.118027 47.458908,1.118495 47.458767,1.11939 47.458349,1.119656 47.458289,1.120199 47.4582,1.120415 47.458111,1.120719 47.457884,1.120875 47.457655,1.120873 47.45734,1.120651 47.456797,1.121194 47.455606,1.121927 47.454859,1.122425 47.454412,1.122927 47.45403,1.123357 47.453679,1.123952 47.453131,1.124907 47.452293,1.125089 47.452157,1.126006 47.451518,1.126377 47.451269,1.126896 47.451069,1.127978 47.450724,1.128017 47.450683,1.128967 47.449939,1.12957 47.449631,1.130637 47.449417,1.131571 47.449112,1.132834 47.44899,1.133466 47.448979,1.13331 47.448543,1.133039 47.448055,1.132811 47.447101,1.13236 47.446467,1.131789 47.445381,1.131124 47.445418,1.130411 47.444737,1.129368 47.443916,1.129203 47.443688,1.128268 47.442735,1.126571 47.441548,1.125731 47.441028,1.124897 47.440526,1.123783 47.439797,1.123311 47.439447,1.122901 47.439026,1.12218 47.438219,1.121841 47.437749,1.121621 47.43737,1.1215 47.436836,1.121392 47.436568,1.121113 47.435443,1.120921 47.435084,1.120785 47.434901,1.119945 47.433588,1.119338 47.432567,1.119076 47.432165,1.11867 47.431598,1.118387 47.431275,1.118186 47.431098,1.117132 47.430316,1.116901 47.430117,1.116291 47.429483,1.115839 47.429127,1.115521 47.428979,1.115232 47.428888,1.114677 47.428629,1.113787 47.428168,1.11365 47.428024,1.112005 47.42916,1.110264 47.429861,1.10822 47.430323,1.107946 47.43041,1.105716 47.431461,1.105221 47.431668,1.105049 47.431665,1.104811 47.43131,1.10449 47.430874,1.104162 47.430485,1.103967 47.430341,1.103558 47.430251,1.103088 47.43024,1.102223 47.430253,1.097606 47.429535,1.095524 47.429232,1.093631 47.428931,1.093506 47.428922,1.090873 47.429263,1.087938 47.429606,1.086315 47.429787,1.085493 47.429886,1.085249 47.429865,1.084904 47.429694,1.084535 47.429396,1.083925 47.428869,1.082447 47.427648,1.082039 47.427881,1.08165 47.428165,1.080755 47.428852,1.080411 47.429103,1.080311 47.429151,1.079566 47.429379,1.078468 47.429727,1.078376 47.429776,1.076567 47.431017,1.075953 47.431466,1.075591 47.431866,1.075091 47.43223,1.074834 47.432567,1.073985 47.433577,1.073837 47.433703,1.073026 47.434645,1.072682 47.435507,1.072429 47.436065,1.072193 47.436546,1.07203 47.436825,1.071874 47.437291,1.071604 47.437838,1.071569 47.437982,1.071088 47.438199,1.070643 47.439024,1.070472 47.439412,1.070304 47.439734,1.069874 47.440495,1.069744 47.440877,1.069643 47.441083,1.069412 47.441448,1.068678 47.442464,1.068271 47.442968,1.067894 47.443303,1.067609 47.443622,1.067599 47.443697,1.067368 47.443992,1.067125 47.444379,1.067073 47.444497,1.066874 47.444554,1.066175 47.445276,1.06593 47.445481,1.065741 47.445559,1.065297 47.445543,1.064968 47.445835,1.064745 47.445961,1.06432 47.446425,1.064119 47.446776,1.064069 47.447061,1.063974 47.447117,1.06396 47.448416,1.06447 47.449312,1.06497 47.449758,1.065375 47.450039,1.065811 47.450291,1.066453 47.450745,1.066905 47.451022,1.067203 47.451177,1.068428 47.451881,1.068973 47.452164,1.069937 47.452832,1.070654 47.453151,1.071011 47.453193,1.071369 47.453047,1.075437 47.455225,1.07856 47.457054,1.080347 47.458204,1.083111 47.460008,1.083558 47.460205,1.085007 47.461078,1.086781 47.461771,1.087111 47.46182)))'), 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')
;

#	File/Call
1	Doctrine\DBAL\Connection->fetchAllAssociative (line 622)
2	App\Service\OsmDatabaseService->getMobilityOnewayDscPotentialObjectsInZone (line 1078)
3	App\Service\Motocultrice->getMobilityOnewayDscPotentialObjects (line 1653)
4	App\Controller\AdminController->stats (line 1279)
5	Symfony\Component\HttpKernel\HttpKernel->handleRaw (line 183)
6	Symfony\Component\HttpKernel\HttpKernel->handle (line 76)
7	Symfony\Component\HttpKernel\Kernel->handle (line 182)
8	Symfony\Component\Runtime\Runner\Symfony\HttpKernelRunner->run (line 35)
9	require_once (line 29)
10	require_once (line 5)

11.50 ms
(5.44%)

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

Parameters:

[
  "37161"
]

View formatted query View runnable query Explain query View query backtrace

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'37161'') = ?
  AND (tags->'admin_level') = '8'
  AND (tags->'boundary') = 'administrative'
ORDER BY ST_Area(geom::geography) DESC NULLS LAST
LIMIT 1;

#	File/Call
1	Doctrine\DBAL\Connection->fetchOne (line 332)
2	App\Service\ZonePolygonResolver->fetchBoundaryWkt (line 292)
3	App\Service\ZonePolygonResolver->fetchBoundaryWktFromMeta (line 236)
4	App\Service\ZonePolygonResolver->{closure:App\Service\ZonePolygonResolver::resolvePolygon():100} (line 100)
5	App\Service\ZonePolygonResolver->resolvePolygon (line 106)
6	App\Service\ZonePolygonResolver->polygonWkt (line 62)
7	App\Service\OsmHistoryDatabaseService->requirePolygonWkt (line 711)
8	App\Service\OsmHistoryDatabaseService->getActivityStatsForZone (line 403)
9	App\Controller\AdminController->stats (line 785)
10	Symfony\Component\HttpKernel\HttpKernel->handleRaw (line 183)
11	Symfony\Component\HttpKernel\HttpKernel->handle (line 76)
12	Symfony\Component\HttpKernel\Kernel->handle (line 182)
13	Symfony\Component\Runtime\Runner\Symfony\HttpKernelRunner->run (line 35)
14	require_once (line 29)
15	require_once (line 5)

8.47 ms
(4.01%)

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'

Parameters:

[
  "MULTIPOLYGON(((1.087111 47.46182,1.08909 47.462246,1.092332 47.462641,1.092727 47.462735,1.097419 47.463505,1.098936 47.463881,1.101506 47.464276,1.101781 47.464271,1.102317 47.464316,1.103111 47.464445,1.104379 47.464635,1.105454 47.464751,1.106391 47.464808,1.107206 47.464936,1.10826 47.465189,1.110461 47.465728,1.112817 47.466109,1.113381 47.464294,1.113513 47.464126,1.113964 47.463055,1.114071 47.462741,1.113794 47.462605,1.114473 47.46203,1.114903 47.46173,1.114985 47.461732,1.115472 47.461593,1.115968 47.46138,1.116321 47.461159,1.116928 47.460452,1.117042 47.4604,1.11704 47.460282,1.116933 47.460044,1.116965 47.459839,1.116925 47.459778,1.117368 47.459246,1.117443 47.459017,1.118027 47.458908,1.118495 47.458767,1.11939 47.458349,1.119656 47.458289,1.120199 47.4582,1.120415 47.458111,1.120719 47.457884,1.120875 47.457655,1.120873 47.45734,1.120651 47.456797,1.121194 47.455606,1.121927 47.454859,1.122425 47.454412,1.122927 47.45403,1.123357 47.453679,1.123952 47.453131,1.124907 47.452293,1.125089 47.452157,1.126006 47.451518,1.126377 47.451269,1.126896 47.451069,1.127978 47.450724,1.128017 47.450683,1.128967 47.449939,1.12957 47.449631,1.130637 47.449417,1.131571 47.449112,1.132834 47.44899,1.133466 47.448979,1.13331 47.448543,1.133039 47.448055,1.132811 47.447101,1.13236 47.446467,1.131789 47.445381,1.131124 47.445418,1.130411 47.444737,1.129368 47.443916,1.129203 47.443688,1.128268 47.442735,1.126571 47.441548,1.125731 47.441028,1.124897 47.440526,1.123783 47.439797,1.123311 47.439447,1.122901 47.439026,1.12218 47.438219,1.121841 47.437749,1.121621 47.43737,1.1215 47.436836,1.121392 47.436568,1.121113 47.435443,1.120921 47.435084,1.120785 47.434901,1.119945 47.433588,1.119338 47.432567,1.119076 47.432165,1.11867 47.431598,1.118387 47.431275,1.118186 47.431098,1.117132 47.430316,1.116901 47.430117,1.116291 47.429483,1.115839 47.429127,1.115521 47.428979,1.115232 47.428888,1.114677 47.428629,1.113787 47.428168,1.11365 47.428024,1.112005 47.42916,1.110264 47.429861,1.10822 47.430323,1.107946 47.43041,1.105716 47.431461,1.105221 47.431668,1.105049 47.431665,1.104811 47.43131,1.10449 47.430874,1.104162 47.430485,1.103967 47.430341,1.103558 47.430251,1.103088 47.43024,1.102223 47.430253,1.097606 47.429535,1.095524 47.429232,1.093631 47.428931,1.093506 47.428922,1.090873 47.429263,1.087938 47.429606,1.086315 47.429787,1.085493 47.429886,1.085249 47.429865,1.084904 47.429694,1.084535 47.429396,1.083925 47.428869,1.082447 47.427648,1.082039 47.427881,1.08165 47.428165,1.080755 47.428852,1.080411 47.429103,1.080311 47.429151,1.079566 47.429379,1.078468 47.429727,1.078376 47.429776,1.076567 47.431017,1.075953 47.431466,1.075591 47.431866,1.075091 47.43223,1.074834 47.432567,1.073985 47.433577,1.073837 47.433703,1.073026 47.434645,1.072682 47.435507,1.072429 47.436065,1.072193 47.436546,1.07203 47.436825,1.071874 47.437291,1.071604 47.437838,1.071569 47.437982,1.071088 47.438199,1.070643 47.439024,1.070472 47.439412,1.070304 47.439734,1.069874 47.440495,1.069744 47.440877,1.069643 47.441083,1.069412 47.441448,1.068678 47.442464,1.068271 47.442968,1.067894 47.443303,1.067609 47.443622,1.067599 47.443697,1.067368 47.443992,1.067125 47.444379,1.067073 47.444497,1.066874 47.444554,1.066175 47.445276,1.06593 47.445481,1.065741 47.445559,1.065297 47.445543,1.064968 47.445835,1.064745 47.445961,1.06432 47.446425,1.064119 47.446776,1.064069 47.447061,1.063974 47.447117,1.06396 47.448416,1.06447 47.449312,1.06497 47.449758,1.065375 47.450039,1.065811 47.450291,1.066453 47.450745,1.066905 47.451022,1.067203 47.451177,1.068428 47.451881,1.068973 47.452164,1.069937 47.452832,1.070654 47.453151,1.071011 47.453193,1.071369 47.453047,1.075437 47.455225,1.07856 47.457054,1.080347 47.458204,1.083111 47.460008,1.083558 47.460205,1.085007 47.461078,1.086781 47.461771,1.087111 47.46182)))"
]

View formatted query View runnable query Explain query View query backtrace

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(((1.087111 47.46182,1.08909 47.462246,1.092332 47.462641,1.092727 47.462735,1.097419 47.463505,1.098936 47.463881,1.101506 47.464276,1.101781 47.464271,1.102317 47.464316,1.103111 47.464445,1.104379 47.464635,1.105454 47.464751,1.106391 47.464808,1.107206 47.464936,1.10826 47.465189,1.110461 47.465728,1.112817 47.466109,1.113381 47.464294,1.113513 47.464126,1.113964 47.463055,1.114071 47.462741,1.113794 47.462605,1.114473 47.46203,1.114903 47.46173,1.114985 47.461732,1.115472 47.461593,1.115968 47.46138,1.116321 47.461159,1.116928 47.460452,1.117042 47.4604,1.11704 47.460282,1.116933 47.460044,1.116965 47.459839,1.116925 47.459778,1.117368 47.459246,1.117443 47.459017,1.118027 47.458908,1.118495 47.458767,1.11939 47.458349,1.119656 47.458289,1.120199 47.4582,1.120415 47.458111,1.120719 47.457884,1.120875 47.457655,1.120873 47.45734,1.120651 47.456797,1.121194 47.455606,1.121927 47.454859,1.122425 47.454412,1.122927 47.45403,1.123357 47.453679,1.123952 47.453131,1.124907 47.452293,1.125089 47.452157,1.126006 47.451518,1.126377 47.451269,1.126896 47.451069,1.127978 47.450724,1.128017 47.450683,1.128967 47.449939,1.12957 47.449631,1.130637 47.449417,1.131571 47.449112,1.132834 47.44899,1.133466 47.448979,1.13331 47.448543,1.133039 47.448055,1.132811 47.447101,1.13236 47.446467,1.131789 47.445381,1.131124 47.445418,1.130411 47.444737,1.129368 47.443916,1.129203 47.443688,1.128268 47.442735,1.126571 47.441548,1.125731 47.441028,1.124897 47.440526,1.123783 47.439797,1.123311 47.439447,1.122901 47.439026,1.12218 47.438219,1.121841 47.437749,1.121621 47.43737,1.1215 47.436836,1.121392 47.436568,1.121113 47.435443,1.120921 47.435084,1.120785 47.434901,1.119945 47.433588,1.119338 47.432567,1.119076 47.432165,1.11867 47.431598,1.118387 47.431275,1.118186 47.431098,1.117132 47.430316,1.116901 47.430117,1.116291 47.429483,1.115839 47.429127,1.115521 47.428979,1.115232 47.428888,1.114677 47.428629,1.113787 47.428168,1.11365 47.428024,1.112005 47.42916,1.110264 47.429861,1.10822 47.430323,1.107946 47.43041,1.105716 47.431461,1.105221 47.431668,1.105049 47.431665,1.104811 47.43131,1.10449 47.430874,1.104162 47.430485,1.103967 47.430341,1.103558 47.430251,1.103088 47.43024,1.102223 47.430253,1.097606 47.429535,1.095524 47.429232,1.093631 47.428931,1.093506 47.428922,1.090873 47.429263,1.087938 47.429606,1.086315 47.429787,1.085493 47.429886,1.085249 47.429865,1.084904 47.429694,1.084535 47.429396,1.083925 47.428869,1.082447 47.427648,1.082039 47.427881,1.08165 47.428165,1.080755 47.428852,1.080411 47.429103,1.080311 47.429151,1.079566 47.429379,1.078468 47.429727,1.078376 47.429776,1.076567 47.431017,1.075953 47.431466,1.075591 47.431866,1.075091 47.43223,1.074834 47.432567,1.073985 47.433577,1.073837 47.433703,1.073026 47.434645,1.072682 47.435507,1.072429 47.436065,1.072193 47.436546,1.07203 47.436825,1.071874 47.437291,1.071604 47.437838,1.071569 47.437982,1.071088 47.438199,1.070643 47.439024,1.070472 47.439412,1.070304 47.439734,1.069874 47.440495,1.069744 47.440877,1.069643 47.441083,1.069412 47.441448,1.068678 47.442464,1.068271 47.442968,1.067894 47.443303,1.067609 47.443622,1.067599 47.443697,1.067368 47.443992,1.067125 47.444379,1.067073 47.444497,1.066874 47.444554,1.066175 47.445276,1.06593 47.445481,1.065741 47.445559,1.065297 47.445543,1.064968 47.445835,1.064745 47.445961,1.06432 47.446425,1.064119 47.446776,1.064069 47.447061,1.063974 47.447117,1.06396 47.448416,1.06447 47.449312,1.06497 47.449758,1.065375 47.450039,1.065811 47.450291,1.066453 47.450745,1.066905 47.451022,1.067203 47.451177,1.068428 47.451881,1.068973 47.452164,1.069937 47.452832,1.070654 47.453151,1.071011 47.453193,1.071369 47.453047,1.075437 47.455225,1.07856 47.457054,1.080347 47.458204,1.083111 47.460008,1.083558 47.460205,1.085007 47.461078,1.086781 47.461771,1.087111 47.46182)))', 4326))
  AND (tags->'man_made') = 'monitoring_station'
  AND (tags->'monitoring:bicycle') = 'yes';

#	File/Call
1	Doctrine\DBAL\Connection->fetchAllAssociative (line 622)
2	App\Service\OsmDatabaseService->getBicycleCountersInZone (line 1239)
3	App\Service\Motocultrice->getBicycleCounters (line 1674)
4	App\Controller\AdminController->stats (line 1280)
5	Symfony\Component\HttpKernel\HttpKernel->handleRaw (line 183)
6	Symfony\Component\HttpKernel\HttpKernel->handle (line 76)
7	Symfony\Component\HttpKernel\Kernel->handle (line 182)
8	Symfony\Component\Runtime\Runner\Symfony\HttpKernelRunner->run (line 35)
9	require_once (line 29)
10	require_once (line 5)

8.41 ms
(3.98%)

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 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

Parameters:

[
  "MULTIPOLYGON(((1.087111 47.46182,1.08909 47.462246,1.092332 47.462641,1.092727 47.462735,1.097419 47.463505,1.098936 47.463881,1.101506 47.464276,1.101781 47.464271,1.102317 47.464316,1.103111 47.464445,1.104379 47.464635,1.105454 47.464751,1.106391 47.464808,1.107206 47.464936,1.10826 47.465189,1.110461 47.465728,1.112817 47.466109,1.113381 47.464294,1.113513 47.464126,1.113964 47.463055,1.114071 47.462741,1.113794 47.462605,1.114473 47.46203,1.114903 47.46173,1.114985 47.461732,1.115472 47.461593,1.115968 47.46138,1.116321 47.461159,1.116928 47.460452,1.117042 47.4604,1.11704 47.460282,1.116933 47.460044,1.116965 47.459839,1.116925 47.459778,1.117368 47.459246,1.117443 47.459017,1.118027 47.458908,1.118495 47.458767,1.11939 47.458349,1.119656 47.458289,1.120199 47.4582,1.120415 47.458111,1.120719 47.457884,1.120875 47.457655,1.120873 47.45734,1.120651 47.456797,1.121194 47.455606,1.121927 47.454859,1.122425 47.454412,1.122927 47.45403,1.123357 47.453679,1.123952 47.453131,1.124907 47.452293,1.125089 47.452157,1.126006 47.451518,1.126377 47.451269,1.126896 47.451069,1.127978 47.450724,1.128017 47.450683,1.128967 47.449939,1.12957 47.449631,1.130637 47.449417,1.131571 47.449112,1.132834 47.44899,1.133466 47.448979,1.13331 47.448543,1.133039 47.448055,1.132811 47.447101,1.13236 47.446467,1.131789 47.445381,1.131124 47.445418,1.130411 47.444737,1.129368 47.443916,1.129203 47.443688,1.128268 47.442735,1.126571 47.441548,1.125731 47.441028,1.124897 47.440526,1.123783 47.439797,1.123311 47.439447,1.122901 47.439026,1.12218 47.438219,1.121841 47.437749,1.121621 47.43737,1.1215 47.436836,1.121392 47.436568,1.121113 47.435443,1.120921 47.435084,1.120785 47.434901,1.119945 47.433588,1.119338 47.432567,1.119076 47.432165,1.11867 47.431598,1.118387 47.431275,1.118186 47.431098,1.117132 47.430316,1.116901 47.430117,1.116291 47.429483,1.115839 47.429127,1.115521 47.428979,1.115232 47.428888,1.114677 47.428629,1.113787 47.428168,1.11365 47.428024,1.112005 47.42916,1.110264 47.429861,1.10822 47.430323,1.107946 47.43041,1.105716 47.431461,1.105221 47.431668,1.105049 47.431665,1.104811 47.43131,1.10449 47.430874,1.104162 47.430485,1.103967 47.430341,1.103558 47.430251,1.103088 47.43024,1.102223 47.430253,1.097606 47.429535,1.095524 47.429232,1.093631 47.428931,1.093506 47.428922,1.090873 47.429263,1.087938 47.429606,1.086315 47.429787,1.085493 47.429886,1.085249 47.429865,1.084904 47.429694,1.084535 47.429396,1.083925 47.428869,1.082447 47.427648,1.082039 47.427881,1.08165 47.428165,1.080755 47.428852,1.080411 47.429103,1.080311 47.429151,1.079566 47.429379,1.078468 47.429727,1.078376 47.429776,1.076567 47.431017,1.075953 47.431466,1.075591 47.431866,1.075091 47.43223,1.074834 47.432567,1.073985 47.433577,1.073837 47.433703,1.073026 47.434645,1.072682 47.435507,1.072429 47.436065,1.072193 47.436546,1.07203 47.436825,1.071874 47.437291,1.071604 47.437838,1.071569 47.437982,1.071088 47.438199,1.070643 47.439024,1.070472 47.439412,1.070304 47.439734,1.069874 47.440495,1.069744 47.440877,1.069643 47.441083,1.069412 47.441448,1.068678 47.442464,1.068271 47.442968,1.067894 47.443303,1.067609 47.443622,1.067599 47.443697,1.067368 47.443992,1.067125 47.444379,1.067073 47.444497,1.066874 47.444554,1.066175 47.445276,1.06593 47.445481,1.065741 47.445559,1.065297 47.445543,1.064968 47.445835,1.064745 47.445961,1.06432 47.446425,1.064119 47.446776,1.064069 47.447061,1.063974 47.447117,1.06396 47.448416,1.06447 47.449312,1.06497 47.449758,1.065375 47.450039,1.065811 47.450291,1.066453 47.450745,1.066905 47.451022,1.067203 47.451177,1.068428 47.451881,1.068973 47.452164,1.069937 47.452832,1.070654 47.453151,1.071011 47.453193,1.071369 47.453047,1.075437 47.455225,1.07856 47.457054,1.080347 47.458204,1.083111 47.460008,1.083558 47.460205,1.085007 47.461078,1.086781 47.461771,1.087111 47.46182)))"
  "motorway"
  "trunk"
  "primary"
  "secondary"
  "tertiary"
  "unclassified"
  "residential"
  "living_street"
  "motorway_link"
  "trunk_link"
  "primary_link"
  "secondary_link"
  "tertiary_link"
  "service"
  "road"
]

View formatted query View runnable query Explain query View query backtrace

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(((1.087111 47.46182,1.08909 47.462246,1.092332 47.462641,1.092727 47.462735,1.097419 47.463505,1.098936 47.463881,1.101506 47.464276,1.101781 47.464271,1.102317 47.464316,1.103111 47.464445,1.104379 47.464635,1.105454 47.464751,1.106391 47.464808,1.107206 47.464936,1.10826 47.465189,1.110461 47.465728,1.112817 47.466109,1.113381 47.464294,1.113513 47.464126,1.113964 47.463055,1.114071 47.462741,1.113794 47.462605,1.114473 47.46203,1.114903 47.46173,1.114985 47.461732,1.115472 47.461593,1.115968 47.46138,1.116321 47.461159,1.116928 47.460452,1.117042 47.4604,1.11704 47.460282,1.116933 47.460044,1.116965 47.459839,1.116925 47.459778,1.117368 47.459246,1.117443 47.459017,1.118027 47.458908,1.118495 47.458767,1.11939 47.458349,1.119656 47.458289,1.120199 47.4582,1.120415 47.458111,1.120719 47.457884,1.120875 47.457655,1.120873 47.45734,1.120651 47.456797,1.121194 47.455606,1.121927 47.454859,1.122425 47.454412,1.122927 47.45403,1.123357 47.453679,1.123952 47.453131,1.124907 47.452293,1.125089 47.452157,1.126006 47.451518,1.126377 47.451269,1.126896 47.451069,1.127978 47.450724,1.128017 47.450683,1.128967 47.449939,1.12957 47.449631,1.130637 47.449417,1.131571 47.449112,1.132834 47.44899,1.133466 47.448979,1.13331 47.448543,1.133039 47.448055,1.132811 47.447101,1.13236 47.446467,1.131789 47.445381,1.131124 47.445418,1.130411 47.444737,1.129368 47.443916,1.129203 47.443688,1.128268 47.442735,1.126571 47.441548,1.125731 47.441028,1.124897 47.440526,1.123783 47.439797,1.123311 47.439447,1.122901 47.439026,1.12218 47.438219,1.121841 47.437749,1.121621 47.43737,1.1215 47.436836,1.121392 47.436568,1.121113 47.435443,1.120921 47.435084,1.120785 47.434901,1.119945 47.433588,1.119338 47.432567,1.119076 47.432165,1.11867 47.431598,1.118387 47.431275,1.118186 47.431098,1.117132 47.430316,1.116901 47.430117,1.116291 47.429483,1.115839 47.429127,1.115521 47.428979,1.115232 47.428888,1.114677 47.428629,1.113787 47.428168,1.11365 47.428024,1.112005 47.42916,1.110264 47.429861,1.10822 47.430323,1.107946 47.43041,1.105716 47.431461,1.105221 47.431668,1.105049 47.431665,1.104811 47.43131,1.10449 47.430874,1.104162 47.430485,1.103967 47.430341,1.103558 47.430251,1.103088 47.43024,1.102223 47.430253,1.097606 47.429535,1.095524 47.429232,1.093631 47.428931,1.093506 47.428922,1.090873 47.429263,1.087938 47.429606,1.086315 47.429787,1.085493 47.429886,1.085249 47.429865,1.084904 47.429694,1.084535 47.429396,1.083925 47.428869,1.082447 47.427648,1.082039 47.427881,1.08165 47.428165,1.080755 47.428852,1.080411 47.429103,1.080311 47.429151,1.079566 47.429379,1.078468 47.429727,1.078376 47.429776,1.076567 47.431017,1.075953 47.431466,1.075591 47.431866,1.075091 47.43223,1.074834 47.432567,1.073985 47.433577,1.073837 47.433703,1.073026 47.434645,1.072682 47.435507,1.072429 47.436065,1.072193 47.436546,1.07203 47.436825,1.071874 47.437291,1.071604 47.437838,1.071569 47.437982,1.071088 47.438199,1.070643 47.439024,1.070472 47.439412,1.070304 47.439734,1.069874 47.440495,1.069744 47.440877,1.069643 47.441083,1.069412 47.441448,1.068678 47.442464,1.068271 47.442968,1.067894 47.443303,1.067609 47.443622,1.067599 47.443697,1.067368 47.443992,1.067125 47.444379,1.067073 47.444497,1.066874 47.444554,1.066175 47.445276,1.06593 47.445481,1.065741 47.445559,1.065297 47.445543,1.064968 47.445835,1.064745 47.445961,1.06432 47.446425,1.064119 47.446776,1.064069 47.447061,1.063974 47.447117,1.06396 47.448416,1.06447 47.449312,1.06497 47.449758,1.065375 47.450039,1.065811 47.450291,1.066453 47.450745,1.066905 47.451022,1.067203 47.451177,1.068428 47.451881,1.068973 47.452164,1.069937 47.452832,1.070654 47.453151,1.071011 47.453193,1.071369 47.453047,1.075437 47.455225,1.07856 47.457054,1.080347 47.458204,1.083111 47.460008,1.083558 47.460205,1.085007 47.461078,1.086781 47.461771,1.087111 47.46182)))', 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');

#	File/Call
1	Doctrine\DBAL\Connection->fetchAllAssociative (line 622)
2	App\Service\OsmDatabaseService->getStreetsInZone (line 838)
3	App\Service\OsmDatabaseService->findCarParkingsNearHighwayJunctions (line 1715)
4	App\Controller\AdminController->stats (line 1286)
5	Symfony\Component\HttpKernel\HttpKernel->handleRaw (line 183)
6	Symfony\Component\HttpKernel\HttpKernel->handle (line 76)
7	Symfony\Component\HttpKernel\Kernel->handle (line 182)
8	Symfony\Component\Runtime\Runner\Symfony\HttpKernelRunner->run (line 35)
9	require_once (line 29)
10	require_once (line 5)

4.30 ms
(2.03%)

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

Parameters:

[]

View formatted query View runnable query Explain query View query backtrace

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

#	File/Call
1	Doctrine\DBAL\Connection->fetchOne (line 332)
2	App\Service\OsmDatabaseService->hasOsmObjectMetaTable (line 2375)
3	App\Service\Motocultrice->canQueryPostgisZone (line 255)
4	App\Service\Motocultrice->getMobilityCyclewayObjects (line 1629)
5	App\Controller\AdminController->stats (line 1278)
6	Symfony\Component\HttpKernel\HttpKernel->handleRaw (line 183)
7	Symfony\Component\HttpKernel\HttpKernel->handle (line 76)
8	Symfony\Component\HttpKernel\Kernel->handle (line 182)
9	Symfony\Component\Runtime\Runner\Symfony\HttpKernelRunner->run (line 35)
10	require_once (line 29)
11	require_once (line 5)

3.43 ms
(1.62%)

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

Parameters:

[]

View formatted query View runnable query Explain query View query backtrace

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

#	File/Call
1	Doctrine\DBAL\Connection->fetchOne (line 332)
2	App\Service\OsmHistoryDatabaseService->hasHistoryTable (line 60)
3	App\Service\OsmHistoryDatabaseService->getActivityStatsForZone (line 400)
4	App\Controller\AdminController->stats (line 785)
5	Symfony\Component\HttpKernel\HttpKernel->handleRaw (line 183)
6	Symfony\Component\HttpKernel\HttpKernel->handle (line 76)
7	Symfony\Component\HttpKernel\Kernel->handle (line 182)
8	Symfony\Component\Runtime\Runner\Symfony\HttpKernelRunner->run (line 35)
9	require_once (line 29)
10	require_once (line 5)

3.27 ms
(1.55%)

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

Parameters:

[
  "public"
  "osm_object_meta"
]

View formatted query View runnable query Explain query View query backtrace

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

#	File/Call
1	Doctrine\DBAL\Connection->fetchOne (line 332)
2	App\Service\ZonePolygonResolver->postgresTableExists (line 563)
3	App\Service\ZonePolygonResolver->syncAdminLevel8RelationsFromHistory (line 495)
4	App\Service\ZonePolygonResolver->resolvePolygon (line 97)
5	App\Service\ZonePolygonResolver->polygonWkt (line 62)
6	App\Service\OsmHistoryDatabaseService->requirePolygonWkt (line 711)
7	App\Service\OsmHistoryDatabaseService->getActivityStatsForZone (line 403)
8	App\Controller\AdminController->stats (line 785)
9	Symfony\Component\HttpKernel\HttpKernel->handleRaw (line 183)
10	Symfony\Component\HttpKernel\HttpKernel->handle (line 76)
11	Symfony\Component\HttpKernel\Kernel->handle (line 182)
12	Symfony\Component\Runtime\Runner\Symfony\HttpKernelRunner->run (line 35)
13	require_once (line 29)
14	require_once (line 5)

2.87 ms
(1.36%)

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

Parameters:

[]

View formatted query View runnable query Explain query View query backtrace

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;

#	File/Call
1	Doctrine\DBAL\Connection->fetchOne (line 332)
2	App\Service\ZonePolygonResolver->syncAdminLevel8RelationsFromHistory (line 500)
3	App\Service\ZonePolygonResolver->resolvePolygon (line 97)
4	App\Service\ZonePolygonResolver->polygonWkt (line 62)
5	App\Service\OsmHistoryDatabaseService->requirePolygonWkt (line 711)
6	App\Service\OsmHistoryDatabaseService->getActivityStatsForZone (line 403)
7	App\Controller\AdminController->stats (line 785)
8	Symfony\Component\HttpKernel\HttpKernel->handleRaw (line 183)
9	Symfony\Component\HttpKernel\HttpKernel->handle (line 76)
10	Symfony\Component\HttpKernel\Kernel->handle (line 182)
11	Symfony\Component\Runtime\Runner\Symfony\HttpKernelRunner->run (line 35)
12	require_once (line 29)
13	require_once (line 5)

2.84 ms
(1.34%)

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
)

Parameters:

[]

View formatted query View runnable query Explain query View query backtrace

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
  )

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

#	File/Call
1	App\Service\ZonePolygonResolver->syncAdminLevel8RelationsFromHistory (line 554)
2	App\Service\ZonePolygonResolver->resolvePolygon (line 97)
3	App\Service\ZonePolygonResolver->polygonWkt (line 62)
4	App\Service\OsmHistoryDatabaseService->requirePolygonWkt (line 711)
5	App\Service\OsmHistoryDatabaseService->getActivityStatsForZone (line 403)
6	App\Controller\AdminController->stats (line 785)
7	Symfony\Component\HttpKernel\HttpKernel->handleRaw (line 183)
8	Symfony\Component\HttpKernel\HttpKernel->handle (line 76)
9	Symfony\Component\HttpKernel\Kernel->handle (line 182)
10	Symfony\Component\Runtime\Runner\Symfony\HttpKernelRunner->run (line 35)
11	require_once (line 29)
12	require_once (line 5)

2.35 ms
(1.11%)

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

Parameters:

[
  "MULTIPOLYGON(((1.087111 47.46182,1.08909 47.462246,1.092332 47.462641,1.092727 47.462735,1.097419 47.463505,1.098936 47.463881,1.101506 47.464276,1.101781 47.464271,1.102317 47.464316,1.103111 47.464445,1.104379 47.464635,1.105454 47.464751,1.106391 47.464808,1.107206 47.464936,1.10826 47.465189,1.110461 47.465728,1.112817 47.466109,1.113381 47.464294,1.113513 47.464126,1.113964 47.463055,1.114071 47.462741,1.113794 47.462605,1.114473 47.46203,1.114903 47.46173,1.114985 47.461732,1.115472 47.461593,1.115968 47.46138,1.116321 47.461159,1.116928 47.460452,1.117042 47.4604,1.11704 47.460282,1.116933 47.460044,1.116965 47.459839,1.116925 47.459778,1.117368 47.459246,1.117443 47.459017,1.118027 47.458908,1.118495 47.458767,1.11939 47.458349,1.119656 47.458289,1.120199 47.4582,1.120415 47.458111,1.120719 47.457884,1.120875 47.457655,1.120873 47.45734,1.120651 47.456797,1.121194 47.455606,1.121927 47.454859,1.122425 47.454412,1.122927 47.45403,1.123357 47.453679,1.123952 47.453131,1.124907 47.452293,1.125089 47.452157,1.126006 47.451518,1.126377 47.451269,1.126896 47.451069,1.127978 47.450724,1.128017 47.450683,1.128967 47.449939,1.12957 47.449631,1.130637 47.449417,1.131571 47.449112,1.132834 47.44899,1.133466 47.448979,1.13331 47.448543,1.133039 47.448055,1.132811 47.447101,1.13236 47.446467,1.131789 47.445381,1.131124 47.445418,1.130411 47.444737,1.129368 47.443916,1.129203 47.443688,1.128268 47.442735,1.126571 47.441548,1.125731 47.441028,1.124897 47.440526,1.123783 47.439797,1.123311 47.439447,1.122901 47.439026,1.12218 47.438219,1.121841 47.437749,1.121621 47.43737,1.1215 47.436836,1.121392 47.436568,1.121113 47.435443,1.120921 47.435084,1.120785 47.434901,1.119945 47.433588,1.119338 47.432567,1.119076 47.432165,1.11867 47.431598,1.118387 47.431275,1.118186 47.431098,1.117132 47.430316,1.116901 47.430117,1.116291 47.429483,1.115839 47.429127,1.115521 47.428979,1.115232 47.428888,1.114677 47.428629,1.113787 47.428168,1.11365 47.428024,1.112005 47.42916,1.110264 47.429861,1.10822 47.430323,1.107946 47.43041,1.105716 47.431461,1.105221 47.431668,1.105049 47.431665,1.104811 47.43131,1.10449 47.430874,1.104162 47.430485,1.103967 47.430341,1.103558 47.430251,1.103088 47.43024,1.102223 47.430253,1.097606 47.429535,1.095524 47.429232,1.093631 47.428931,1.093506 47.428922,1.090873 47.429263,1.087938 47.429606,1.086315 47.429787,1.085493 47.429886,1.085249 47.429865,1.084904 47.429694,1.084535 47.429396,1.083925 47.428869,1.082447 47.427648,1.082039 47.427881,1.08165 47.428165,1.080755 47.428852,1.080411 47.429103,1.080311 47.429151,1.079566 47.429379,1.078468 47.429727,1.078376 47.429776,1.076567 47.431017,1.075953 47.431466,1.075591 47.431866,1.075091 47.43223,1.074834 47.432567,1.073985 47.433577,1.073837 47.433703,1.073026 47.434645,1.072682 47.435507,1.072429 47.436065,1.072193 47.436546,1.07203 47.436825,1.071874 47.437291,1.071604 47.437838,1.071569 47.437982,1.071088 47.438199,1.070643 47.439024,1.070472 47.439412,1.070304 47.439734,1.069874 47.440495,1.069744 47.440877,1.069643 47.441083,1.069412 47.441448,1.068678 47.442464,1.068271 47.442968,1.067894 47.443303,1.067609 47.443622,1.067599 47.443697,1.067368 47.443992,1.067125 47.444379,1.067073 47.444497,1.066874 47.444554,1.066175 47.445276,1.06593 47.445481,1.065741 47.445559,1.065297 47.445543,1.064968 47.445835,1.064745 47.445961,1.06432 47.446425,1.064119 47.446776,1.064069 47.447061,1.063974 47.447117,1.06396 47.448416,1.06447 47.449312,1.06497 47.449758,1.065375 47.450039,1.065811 47.450291,1.066453 47.450745,1.066905 47.451022,1.067203 47.451177,1.068428 47.451881,1.068973 47.452164,1.069937 47.452832,1.070654 47.453151,1.071011 47.453193,1.071369 47.453047,1.075437 47.455225,1.07856 47.457054,1.080347 47.458204,1.083111 47.460008,1.083558 47.460205,1.085007 47.461078,1.086781 47.461771,1.087111 47.46182)))"
  "30"
  "30"
]

View formatted query View runnable query Explain query View query backtrace

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(((1.087111 47.46182,1.08909 47.462246,1.092332 47.462641,1.092727 47.462735,1.097419 47.463505,1.098936 47.463881,1.101506 47.464276,1.101781 47.464271,1.102317 47.464316,1.103111 47.464445,1.104379 47.464635,1.105454 47.464751,1.106391 47.464808,1.107206 47.464936,1.10826 47.465189,1.110461 47.465728,1.112817 47.466109,1.113381 47.464294,1.113513 47.464126,1.113964 47.463055,1.114071 47.462741,1.113794 47.462605,1.114473 47.46203,1.114903 47.46173,1.114985 47.461732,1.115472 47.461593,1.115968 47.46138,1.116321 47.461159,1.116928 47.460452,1.117042 47.4604,1.11704 47.460282,1.116933 47.460044,1.116965 47.459839,1.116925 47.459778,1.117368 47.459246,1.117443 47.459017,1.118027 47.458908,1.118495 47.458767,1.11939 47.458349,1.119656 47.458289,1.120199 47.4582,1.120415 47.458111,1.120719 47.457884,1.120875 47.457655,1.120873 47.45734,1.120651 47.456797,1.121194 47.455606,1.121927 47.454859,1.122425 47.454412,1.122927 47.45403,1.123357 47.453679,1.123952 47.453131,1.124907 47.452293,1.125089 47.452157,1.126006 47.451518,1.126377 47.451269,1.126896 47.451069,1.127978 47.450724,1.128017 47.450683,1.128967 47.449939,1.12957 47.449631,1.130637 47.449417,1.131571 47.449112,1.132834 47.44899,1.133466 47.448979,1.13331 47.448543,1.133039 47.448055,1.132811 47.447101,1.13236 47.446467,1.131789 47.445381,1.131124 47.445418,1.130411 47.444737,1.129368 47.443916,1.129203 47.443688,1.128268 47.442735,1.126571 47.441548,1.125731 47.441028,1.124897 47.440526,1.123783 47.439797,1.123311 47.439447,1.122901 47.439026,1.12218 47.438219,1.121841 47.437749,1.121621 47.43737,1.1215 47.436836,1.121392 47.436568,1.121113 47.435443,1.120921 47.435084,1.120785 47.434901,1.119945 47.433588,1.119338 47.432567,1.119076 47.432165,1.11867 47.431598,1.118387 47.431275,1.118186 47.431098,1.117132 47.430316,1.116901 47.430117,1.116291 47.429483,1.115839 47.429127,1.115521 47.428979,1.115232 47.428888,1.114677 47.428629,1.113787 47.428168,1.11365 47.428024,1.112005 47.42916,1.110264 47.429861,1.10822 47.430323,1.107946 47.43041,1.105716 47.431461,1.105221 47.431668,1.105049 47.431665,1.104811 47.43131,1.10449 47.430874,1.104162 47.430485,1.103967 47.430341,1.103558 47.430251,1.103088 47.43024,1.102223 47.430253,1.097606 47.429535,1.095524 47.429232,1.093631 47.428931,1.093506 47.428922,1.090873 47.429263,1.087938 47.429606,1.086315 47.429787,1.085493 47.429886,1.085249 47.429865,1.084904 47.429694,1.084535 47.429396,1.083925 47.428869,1.082447 47.427648,1.082039 47.427881,1.08165 47.428165,1.080755 47.428852,1.080411 47.429103,1.080311 47.429151,1.079566 47.429379,1.078468 47.429727,1.078376 47.429776,1.076567 47.431017,1.075953 47.431466,1.075591 47.431866,1.075091 47.43223,1.074834 47.432567,1.073985 47.433577,1.073837 47.433703,1.073026 47.434645,1.072682 47.435507,1.072429 47.436065,1.072193 47.436546,1.07203 47.436825,1.071874 47.437291,1.071604 47.437838,1.071569 47.437982,1.071088 47.438199,1.070643 47.439024,1.070472 47.439412,1.070304 47.439734,1.069874 47.440495,1.069744 47.440877,1.069643 47.441083,1.069412 47.441448,1.068678 47.442464,1.068271 47.442968,1.067894 47.443303,1.067609 47.443622,1.067599 47.443697,1.067368 47.443992,1.067125 47.444379,1.067073 47.444497,1.066874 47.444554,1.066175 47.445276,1.06593 47.445481,1.065741 47.445559,1.065297 47.445543,1.064968 47.445835,1.064745 47.445961,1.06432 47.446425,1.064119 47.446776,1.064069 47.447061,1.063974 47.447117,1.06396 47.448416,1.06447 47.449312,1.06497 47.449758,1.065375 47.450039,1.065811 47.450291,1.066453 47.450745,1.066905 47.451022,1.067203 47.451177,1.068428 47.451881,1.068973 47.452164,1.069937 47.452832,1.070654 47.453151,1.071011 47.453193,1.071369 47.453047,1.075437 47.455225,1.07856 47.457054,1.080347 47.458204,1.083111 47.460008,1.083558 47.460205,1.085007 47.461078,1.086781 47.461771,1.087111 47.46182)))', 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;

#	File/Call
1	Doctrine\DBAL\Connection->fetchAllAssociative (line 622)
2	App\Service\OsmHistoryDatabaseService->getActivityStatsForZone (line 447)
3	App\Controller\AdminController->stats (line 785)
4	Symfony\Component\HttpKernel\HttpKernel->handleRaw (line 183)
5	Symfony\Component\HttpKernel\HttpKernel->handle (line 76)
6	Symfony\Component\HttpKernel\Kernel->handle (line 182)
7	Symfony\Component\Runtime\Runner\Symfony\HttpKernelRunner->run (line 35)
8	require_once (line 29)
9	require_once (line 5)

1.32 ms
(0.63%)

SELECT 1

Parameters:

[]

View formatted query View runnable query Explain query View query backtrace

SELECT
  1

SELECT 1;

#	File/Call
1	Doctrine\DBAL\Connection->fetchOne (line 332)
2	App\Service\OsmDatabaseService->isAvailable (line 2364)
3	App\Service\Motocultrice->canQueryPostgisZone (line 254)
4	App\Service\Motocultrice->getMobilityCyclewayObjects (line 1629)
5	App\Controller\AdminController->stats (line 1278)
6	Symfony\Component\HttpKernel\HttpKernel->handleRaw (line 183)
7	Symfony\Component\HttpKernel\HttpKernel->handle (line 76)
8	Symfony\Component\HttpKernel\Kernel->handle (line 182)
9	Symfony\Component\Runtime\Runner\Symfony\HttpKernelRunner->run (line 35)
10	require_once (line 29)
11	require_once (line 5)

0.77 ms
(0.37%)

WITH zone AS (SELECT ST_GeomFromText(?, 4326) AS g),
in_zone AS (
  SELECT h.osm_type, h.osm_id, h.version, h.visible
  FROM osm_object_history h, zone z
  WHERE h.geom IS NOT NULL AND ST_Intersects(h.geom, z.g)
),
deletions AS (
  SELECT 1 AS one
  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
)
SELECT
  (SELECT COUNT(*) FROM in_zone WHERE version = 1 AND visible = true) AS creations_total,
  (SELECT COUNT(*) FROM deletions)                                    AS deletions_total,
  (SELECT COUNT(*) FROM in_zone)                                      AS versions_total

Parameters:

[
  "MULTIPOLYGON(((1.087111 47.46182,1.08909 47.462246,1.092332 47.462641,1.092727 47.462735,1.097419 47.463505,1.098936 47.463881,1.101506 47.464276,1.101781 47.464271,1.102317 47.464316,1.103111 47.464445,1.104379 47.464635,1.105454 47.464751,1.106391 47.464808,1.107206 47.464936,1.10826 47.465189,1.110461 47.465728,1.112817 47.466109,1.113381 47.464294,1.113513 47.464126,1.113964 47.463055,1.114071 47.462741,1.113794 47.462605,1.114473 47.46203,1.114903 47.46173,1.114985 47.461732,1.115472 47.461593,1.115968 47.46138,1.116321 47.461159,1.116928 47.460452,1.117042 47.4604,1.11704 47.460282,1.116933 47.460044,1.116965 47.459839,1.116925 47.459778,1.117368 47.459246,1.117443 47.459017,1.118027 47.458908,1.118495 47.458767,1.11939 47.458349,1.119656 47.458289,1.120199 47.4582,1.120415 47.458111,1.120719 47.457884,1.120875 47.457655,1.120873 47.45734,1.120651 47.456797,1.121194 47.455606,1.121927 47.454859,1.122425 47.454412,1.122927 47.45403,1.123357 47.453679,1.123952 47.453131,1.124907 47.452293,1.125089 47.452157,1.126006 47.451518,1.126377 47.451269,1.126896 47.451069,1.127978 47.450724,1.128017 47.450683,1.128967 47.449939,1.12957 47.449631,1.130637 47.449417,1.131571 47.449112,1.132834 47.44899,1.133466 47.448979,1.13331 47.448543,1.133039 47.448055,1.132811 47.447101,1.13236 47.446467,1.131789 47.445381,1.131124 47.445418,1.130411 47.444737,1.129368 47.443916,1.129203 47.443688,1.128268 47.442735,1.126571 47.441548,1.125731 47.441028,1.124897 47.440526,1.123783 47.439797,1.123311 47.439447,1.122901 47.439026,1.12218 47.438219,1.121841 47.437749,1.121621 47.43737,1.1215 47.436836,1.121392 47.436568,1.121113 47.435443,1.120921 47.435084,1.120785 47.434901,1.119945 47.433588,1.119338 47.432567,1.119076 47.432165,1.11867 47.431598,1.118387 47.431275,1.118186 47.431098,1.117132 47.430316,1.116901 47.430117,1.116291 47.429483,1.115839 47.429127,1.115521 47.428979,1.115232 47.428888,1.114677 47.428629,1.113787 47.428168,1.11365 47.428024,1.112005 47.42916,1.110264 47.429861,1.10822 47.430323,1.107946 47.43041,1.105716 47.431461,1.105221 47.431668,1.105049 47.431665,1.104811 47.43131,1.10449 47.430874,1.104162 47.430485,1.103967 47.430341,1.103558 47.430251,1.103088 47.43024,1.102223 47.430253,1.097606 47.429535,1.095524 47.429232,1.093631 47.428931,1.093506 47.428922,1.090873 47.429263,1.087938 47.429606,1.086315 47.429787,1.085493 47.429886,1.085249 47.429865,1.084904 47.429694,1.084535 47.429396,1.083925 47.428869,1.082447 47.427648,1.082039 47.427881,1.08165 47.428165,1.080755 47.428852,1.080411 47.429103,1.080311 47.429151,1.079566 47.429379,1.078468 47.429727,1.078376 47.429776,1.076567 47.431017,1.075953 47.431466,1.075591 47.431866,1.075091 47.43223,1.074834 47.432567,1.073985 47.433577,1.073837 47.433703,1.073026 47.434645,1.072682 47.435507,1.072429 47.436065,1.072193 47.436546,1.07203 47.436825,1.071874 47.437291,1.071604 47.437838,1.071569 47.437982,1.071088 47.438199,1.070643 47.439024,1.070472 47.439412,1.070304 47.439734,1.069874 47.440495,1.069744 47.440877,1.069643 47.441083,1.069412 47.441448,1.068678 47.442464,1.068271 47.442968,1.067894 47.443303,1.067609 47.443622,1.067599 47.443697,1.067368 47.443992,1.067125 47.444379,1.067073 47.444497,1.066874 47.444554,1.066175 47.445276,1.06593 47.445481,1.065741 47.445559,1.065297 47.445543,1.064968 47.445835,1.064745 47.445961,1.06432 47.446425,1.064119 47.446776,1.064069 47.447061,1.063974 47.447117,1.06396 47.448416,1.06447 47.449312,1.06497 47.449758,1.065375 47.450039,1.065811 47.450291,1.066453 47.450745,1.066905 47.451022,1.067203 47.451177,1.068428 47.451881,1.068973 47.452164,1.069937 47.452832,1.070654 47.453151,1.071011 47.453193,1.071369 47.453047,1.075437 47.455225,1.07856 47.457054,1.080347 47.458204,1.083111 47.460008,1.083558 47.460205,1.085007 47.461078,1.086781 47.461771,1.087111 47.46182)))"
]

View formatted query View runnable query Explain query View query backtrace

WITH
  zone AS (
    SELECT
      ST_GeomFromText(?, 4326) AS g
  ),
  in_zone AS (
    SELECT
      h.osm_type,
      h.osm_id,
      h.version,
      h.visible
    FROM
      osm_object_history h,
      zone z
    WHERE
      h.geom IS NOT NULL
      AND ST_Intersects(h.geom, z.g)
  ),
  deletions AS (
    SELECT
      1 AS one
    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
  )
SELECT
  (
    SELECT
      COUNT(*)
    FROM
      in_zone
    WHERE
      version = 1
      AND visible = true
  ) AS creations_total,
  (
    SELECT
      COUNT(*)
    FROM
      deletions
  ) AS deletions_total,
  (
    SELECT
      COUNT(*)
    FROM
      in_zone
  ) AS versions_total

WITH zone AS (SELECT ST_GeomFromText('MULTIPOLYGON(((1.087111 47.46182,1.08909 47.462246,1.092332 47.462641,1.092727 47.462735,1.097419 47.463505,1.098936 47.463881,1.101506 47.464276,1.101781 47.464271,1.102317 47.464316,1.103111 47.464445,1.104379 47.464635,1.105454 47.464751,1.106391 47.464808,1.107206 47.464936,1.10826 47.465189,1.110461 47.465728,1.112817 47.466109,1.113381 47.464294,1.113513 47.464126,1.113964 47.463055,1.114071 47.462741,1.113794 47.462605,1.114473 47.46203,1.114903 47.46173,1.114985 47.461732,1.115472 47.461593,1.115968 47.46138,1.116321 47.461159,1.116928 47.460452,1.117042 47.4604,1.11704 47.460282,1.116933 47.460044,1.116965 47.459839,1.116925 47.459778,1.117368 47.459246,1.117443 47.459017,1.118027 47.458908,1.118495 47.458767,1.11939 47.458349,1.119656 47.458289,1.120199 47.4582,1.120415 47.458111,1.120719 47.457884,1.120875 47.457655,1.120873 47.45734,1.120651 47.456797,1.121194 47.455606,1.121927 47.454859,1.122425 47.454412,1.122927 47.45403,1.123357 47.453679,1.123952 47.453131,1.124907 47.452293,1.125089 47.452157,1.126006 47.451518,1.126377 47.451269,1.126896 47.451069,1.127978 47.450724,1.128017 47.450683,1.128967 47.449939,1.12957 47.449631,1.130637 47.449417,1.131571 47.449112,1.132834 47.44899,1.133466 47.448979,1.13331 47.448543,1.133039 47.448055,1.132811 47.447101,1.13236 47.446467,1.131789 47.445381,1.131124 47.445418,1.130411 47.444737,1.129368 47.443916,1.129203 47.443688,1.128268 47.442735,1.126571 47.441548,1.125731 47.441028,1.124897 47.440526,1.123783 47.439797,1.123311 47.439447,1.122901 47.439026,1.12218 47.438219,1.121841 47.437749,1.121621 47.43737,1.1215 47.436836,1.121392 47.436568,1.121113 47.435443,1.120921 47.435084,1.120785 47.434901,1.119945 47.433588,1.119338 47.432567,1.119076 47.432165,1.11867 47.431598,1.118387 47.431275,1.118186 47.431098,1.117132 47.430316,1.116901 47.430117,1.116291 47.429483,1.115839 47.429127,1.115521 47.428979,1.115232 47.428888,1.114677 47.428629,1.113787 47.428168,1.11365 47.428024,1.112005 47.42916,1.110264 47.429861,1.10822 47.430323,1.107946 47.43041,1.105716 47.431461,1.105221 47.431668,1.105049 47.431665,1.104811 47.43131,1.10449 47.430874,1.104162 47.430485,1.103967 47.430341,1.103558 47.430251,1.103088 47.43024,1.102223 47.430253,1.097606 47.429535,1.095524 47.429232,1.093631 47.428931,1.093506 47.428922,1.090873 47.429263,1.087938 47.429606,1.086315 47.429787,1.085493 47.429886,1.085249 47.429865,1.084904 47.429694,1.084535 47.429396,1.083925 47.428869,1.082447 47.427648,1.082039 47.427881,1.08165 47.428165,1.080755 47.428852,1.080411 47.429103,1.080311 47.429151,1.079566 47.429379,1.078468 47.429727,1.078376 47.429776,1.076567 47.431017,1.075953 47.431466,1.075591 47.431866,1.075091 47.43223,1.074834 47.432567,1.073985 47.433577,1.073837 47.433703,1.073026 47.434645,1.072682 47.435507,1.072429 47.436065,1.072193 47.436546,1.07203 47.436825,1.071874 47.437291,1.071604 47.437838,1.071569 47.437982,1.071088 47.438199,1.070643 47.439024,1.070472 47.439412,1.070304 47.439734,1.069874 47.440495,1.069744 47.440877,1.069643 47.441083,1.069412 47.441448,1.068678 47.442464,1.068271 47.442968,1.067894 47.443303,1.067609 47.443622,1.067599 47.443697,1.067368 47.443992,1.067125 47.444379,1.067073 47.444497,1.066874 47.444554,1.066175 47.445276,1.06593 47.445481,1.065741 47.445559,1.065297 47.445543,1.064968 47.445835,1.064745 47.445961,1.06432 47.446425,1.064119 47.446776,1.064069 47.447061,1.063974 47.447117,1.06396 47.448416,1.06447 47.449312,1.06497 47.449758,1.065375 47.450039,1.065811 47.450291,1.066453 47.450745,1.066905 47.451022,1.067203 47.451177,1.068428 47.451881,1.068973 47.452164,1.069937 47.452832,1.070654 47.453151,1.071011 47.453193,1.071369 47.453047,1.075437 47.455225,1.07856 47.457054,1.080347 47.458204,1.083111 47.460008,1.083558 47.460205,1.085007 47.461078,1.086781 47.461771,1.087111 47.46182)))', 4326) AS g),
in_zone AS (
  SELECT h.osm_type, h.osm_id, h.version, h.visible
  FROM osm_object_history h, zone z
  WHERE h.geom IS NOT NULL AND ST_Intersects(h.geom, z.g)
),
deletions AS (
  SELECT 1 AS one
  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
)
SELECT
  (SELECT COUNT(*) FROM in_zone WHERE version = 1 AND visible = true) AS creations_total,
  (SELECT COUNT(*) FROM deletions)                                    AS deletions_total,
  (SELECT COUNT(*) FROM in_zone)                                      AS versions_total;

#	File/Call
1	Doctrine\DBAL\Connection->fetchAssociative (line 300)
2	App\Service\OsmHistoryDatabaseService->getActivityStatsForZone (line 548)
3	App\Controller\AdminController->stats (line 785)
4	Symfony\Component\HttpKernel\HttpKernel->handleRaw (line 183)
5	Symfony\Component\HttpKernel\HttpKernel->handle (line 76)
6	Symfony\Component\HttpKernel\Kernel->handle (line 182)
7	Symfony\Component\Runtime\Runner\Symfony\HttpKernelRunner->run (line 35)
8	require_once (line 29)
9	require_once (line 5)

0.74 ms
(0.35%)

SELECT to_char(qstart, 'YYYY') || '-Q' || EXTRACT(QUARTER FROM qstart)::int AS k,
       n
FROM (
  SELECT date_trunc('quarter', valid_from) AS qstart, COUNT(*) AS n
  FROM osm_object_history h
  WHERE h.geom IS NOT NULL AND ST_Intersects(h.geom, ST_GeomFromText(?, 4326))
  GROUP BY 1
) t
ORDER BY qstart

Parameters:

[
  "MULTIPOLYGON(((1.087111 47.46182,1.08909 47.462246,1.092332 47.462641,1.092727 47.462735,1.097419 47.463505,1.098936 47.463881,1.101506 47.464276,1.101781 47.464271,1.102317 47.464316,1.103111 47.464445,1.104379 47.464635,1.105454 47.464751,1.106391 47.464808,1.107206 47.464936,1.10826 47.465189,1.110461 47.465728,1.112817 47.466109,1.113381 47.464294,1.113513 47.464126,1.113964 47.463055,1.114071 47.462741,1.113794 47.462605,1.114473 47.46203,1.114903 47.46173,1.114985 47.461732,1.115472 47.461593,1.115968 47.46138,1.116321 47.461159,1.116928 47.460452,1.117042 47.4604,1.11704 47.460282,1.116933 47.460044,1.116965 47.459839,1.116925 47.459778,1.117368 47.459246,1.117443 47.459017,1.118027 47.458908,1.118495 47.458767,1.11939 47.458349,1.119656 47.458289,1.120199 47.4582,1.120415 47.458111,1.120719 47.457884,1.120875 47.457655,1.120873 47.45734,1.120651 47.456797,1.121194 47.455606,1.121927 47.454859,1.122425 47.454412,1.122927 47.45403,1.123357 47.453679,1.123952 47.453131,1.124907 47.452293,1.125089 47.452157,1.126006 47.451518,1.126377 47.451269,1.126896 47.451069,1.127978 47.450724,1.128017 47.450683,1.128967 47.449939,1.12957 47.449631,1.130637 47.449417,1.131571 47.449112,1.132834 47.44899,1.133466 47.448979,1.13331 47.448543,1.133039 47.448055,1.132811 47.447101,1.13236 47.446467,1.131789 47.445381,1.131124 47.445418,1.130411 47.444737,1.129368 47.443916,1.129203 47.443688,1.128268 47.442735,1.126571 47.441548,1.125731 47.441028,1.124897 47.440526,1.123783 47.439797,1.123311 47.439447,1.122901 47.439026,1.12218 47.438219,1.121841 47.437749,1.121621 47.43737,1.1215 47.436836,1.121392 47.436568,1.121113 47.435443,1.120921 47.435084,1.120785 47.434901,1.119945 47.433588,1.119338 47.432567,1.119076 47.432165,1.11867 47.431598,1.118387 47.431275,1.118186 47.431098,1.117132 47.430316,1.116901 47.430117,1.116291 47.429483,1.115839 47.429127,1.115521 47.428979,1.115232 47.428888,1.114677 47.428629,1.113787 47.428168,1.11365 47.428024,1.112005 47.42916,1.110264 47.429861,1.10822 47.430323,1.107946 47.43041,1.105716 47.431461,1.105221 47.431668,1.105049 47.431665,1.104811 47.43131,1.10449 47.430874,1.104162 47.430485,1.103967 47.430341,1.103558 47.430251,1.103088 47.43024,1.102223 47.430253,1.097606 47.429535,1.095524 47.429232,1.093631 47.428931,1.093506 47.428922,1.090873 47.429263,1.087938 47.429606,1.086315 47.429787,1.085493 47.429886,1.085249 47.429865,1.084904 47.429694,1.084535 47.429396,1.083925 47.428869,1.082447 47.427648,1.082039 47.427881,1.08165 47.428165,1.080755 47.428852,1.080411 47.429103,1.080311 47.429151,1.079566 47.429379,1.078468 47.429727,1.078376 47.429776,1.076567 47.431017,1.075953 47.431466,1.075591 47.431866,1.075091 47.43223,1.074834 47.432567,1.073985 47.433577,1.073837 47.433703,1.073026 47.434645,1.072682 47.435507,1.072429 47.436065,1.072193 47.436546,1.07203 47.436825,1.071874 47.437291,1.071604 47.437838,1.071569 47.437982,1.071088 47.438199,1.070643 47.439024,1.070472 47.439412,1.070304 47.439734,1.069874 47.440495,1.069744 47.440877,1.069643 47.441083,1.069412 47.441448,1.068678 47.442464,1.068271 47.442968,1.067894 47.443303,1.067609 47.443622,1.067599 47.443697,1.067368 47.443992,1.067125 47.444379,1.067073 47.444497,1.066874 47.444554,1.066175 47.445276,1.06593 47.445481,1.065741 47.445559,1.065297 47.445543,1.064968 47.445835,1.064745 47.445961,1.06432 47.446425,1.064119 47.446776,1.064069 47.447061,1.063974 47.447117,1.06396 47.448416,1.06447 47.449312,1.06497 47.449758,1.065375 47.450039,1.065811 47.450291,1.066453 47.450745,1.066905 47.451022,1.067203 47.451177,1.068428 47.451881,1.068973 47.452164,1.069937 47.452832,1.070654 47.453151,1.071011 47.453193,1.071369 47.453047,1.075437 47.455225,1.07856 47.457054,1.080347 47.458204,1.083111 47.460008,1.083558 47.460205,1.085007 47.461078,1.086781 47.461771,1.087111 47.46182)))"
]

View formatted query View runnable query Explain query View query backtrace

SELECT
  to_char(qstart, 'YYYY') || '-Q' || EXTRACT(
    QUARTER
    FROM
      qstart
  ):: int AS k,
  n
FROM
  (
    SELECT
      date_trunc('quarter', valid_from) AS qstart,
      COUNT(*) AS n
    FROM
      osm_object_history h
    WHERE
      h.geom IS NOT NULL
      AND ST_Intersects(
        h.geom,
        ST_GeomFromText(?, 4326)
      )
    GROUP BY
      1
  ) t
ORDER BY
  qstart

SELECT to_char(qstart, 'YYYY') || '-Q' || EXTRACT(QUARTER FROM qstart)::int AS k,
       n
FROM (
  SELECT date_trunc('quarter', valid_from) AS qstart, COUNT(*) AS n
  FROM osm_object_history h
  WHERE h.geom IS NOT NULL AND ST_Intersects(h.geom, ST_GeomFromText('MULTIPOLYGON(((1.087111 47.46182,1.08909 47.462246,1.092332 47.462641,1.092727 47.462735,1.097419 47.463505,1.098936 47.463881,1.101506 47.464276,1.101781 47.464271,1.102317 47.464316,1.103111 47.464445,1.104379 47.464635,1.105454 47.464751,1.106391 47.464808,1.107206 47.464936,1.10826 47.465189,1.110461 47.465728,1.112817 47.466109,1.113381 47.464294,1.113513 47.464126,1.113964 47.463055,1.114071 47.462741,1.113794 47.462605,1.114473 47.46203,1.114903 47.46173,1.114985 47.461732,1.115472 47.461593,1.115968 47.46138,1.116321 47.461159,1.116928 47.460452,1.117042 47.4604,1.11704 47.460282,1.116933 47.460044,1.116965 47.459839,1.116925 47.459778,1.117368 47.459246,1.117443 47.459017,1.118027 47.458908,1.118495 47.458767,1.11939 47.458349,1.119656 47.458289,1.120199 47.4582,1.120415 47.458111,1.120719 47.457884,1.120875 47.457655,1.120873 47.45734,1.120651 47.456797,1.121194 47.455606,1.121927 47.454859,1.122425 47.454412,1.122927 47.45403,1.123357 47.453679,1.123952 47.453131,1.124907 47.452293,1.125089 47.452157,1.126006 47.451518,1.126377 47.451269,1.126896 47.451069,1.127978 47.450724,1.128017 47.450683,1.128967 47.449939,1.12957 47.449631,1.130637 47.449417,1.131571 47.449112,1.132834 47.44899,1.133466 47.448979,1.13331 47.448543,1.133039 47.448055,1.132811 47.447101,1.13236 47.446467,1.131789 47.445381,1.131124 47.445418,1.130411 47.444737,1.129368 47.443916,1.129203 47.443688,1.128268 47.442735,1.126571 47.441548,1.125731 47.441028,1.124897 47.440526,1.123783 47.439797,1.123311 47.439447,1.122901 47.439026,1.12218 47.438219,1.121841 47.437749,1.121621 47.43737,1.1215 47.436836,1.121392 47.436568,1.121113 47.435443,1.120921 47.435084,1.120785 47.434901,1.119945 47.433588,1.119338 47.432567,1.119076 47.432165,1.11867 47.431598,1.118387 47.431275,1.118186 47.431098,1.117132 47.430316,1.116901 47.430117,1.116291 47.429483,1.115839 47.429127,1.115521 47.428979,1.115232 47.428888,1.114677 47.428629,1.113787 47.428168,1.11365 47.428024,1.112005 47.42916,1.110264 47.429861,1.10822 47.430323,1.107946 47.43041,1.105716 47.431461,1.105221 47.431668,1.105049 47.431665,1.104811 47.43131,1.10449 47.430874,1.104162 47.430485,1.103967 47.430341,1.103558 47.430251,1.103088 47.43024,1.102223 47.430253,1.097606 47.429535,1.095524 47.429232,1.093631 47.428931,1.093506 47.428922,1.090873 47.429263,1.087938 47.429606,1.086315 47.429787,1.085493 47.429886,1.085249 47.429865,1.084904 47.429694,1.084535 47.429396,1.083925 47.428869,1.082447 47.427648,1.082039 47.427881,1.08165 47.428165,1.080755 47.428852,1.080411 47.429103,1.080311 47.429151,1.079566 47.429379,1.078468 47.429727,1.078376 47.429776,1.076567 47.431017,1.075953 47.431466,1.075591 47.431866,1.075091 47.43223,1.074834 47.432567,1.073985 47.433577,1.073837 47.433703,1.073026 47.434645,1.072682 47.435507,1.072429 47.436065,1.072193 47.436546,1.07203 47.436825,1.071874 47.437291,1.071604 47.437838,1.071569 47.437982,1.071088 47.438199,1.070643 47.439024,1.070472 47.439412,1.070304 47.439734,1.069874 47.440495,1.069744 47.440877,1.069643 47.441083,1.069412 47.441448,1.068678 47.442464,1.068271 47.442968,1.067894 47.443303,1.067609 47.443622,1.067599 47.443697,1.067368 47.443992,1.067125 47.444379,1.067073 47.444497,1.066874 47.444554,1.066175 47.445276,1.06593 47.445481,1.065741 47.445559,1.065297 47.445543,1.064968 47.445835,1.064745 47.445961,1.06432 47.446425,1.064119 47.446776,1.064069 47.447061,1.063974 47.447117,1.06396 47.448416,1.06447 47.449312,1.06497 47.449758,1.065375 47.450039,1.065811 47.450291,1.066453 47.450745,1.066905 47.451022,1.067203 47.451177,1.068428 47.451881,1.068973 47.452164,1.069937 47.452832,1.070654 47.453151,1.071011 47.453193,1.071369 47.453047,1.075437 47.455225,1.07856 47.457054,1.080347 47.458204,1.083111 47.460008,1.083558 47.460205,1.085007 47.461078,1.086781 47.461771,1.087111 47.46182)))', 4326))
  GROUP BY 1
) t
ORDER BY qstart;

#	File/Call
1	Doctrine\DBAL\Connection->fetchAllAssociative (line 622)
2	App\Service\OsmHistoryDatabaseService->getActivityStatsForZone (line 482)
3	App\Controller\AdminController->stats (line 785)
4	Symfony\Component\HttpKernel\HttpKernel->handleRaw (line 183)
5	Symfony\Component\HttpKernel\HttpKernel->handle (line 76)
6	Symfony\Component\HttpKernel\Kernel->handle (line 182)
7	Symfony\Component\Runtime\Runner\Symfony\HttpKernelRunner->run (line 35)
8	require_once (line 29)
9	require_once (line 5)

0.72 ms
(0.34%)

CREATE INDEX IF NOT EXISTS zone_polygon_geom_idx ON zone_polygon USING GIST (geom)

Parameters:

[]

View formatted query View runnable query Explain query View query backtrace

CREATE INDEX IF NOT EXISTS zone_polygon_geom_idx ON zone_polygon USING GIST (geom)

CREATE INDEX IF NOT EXISTS zone_polygon_geom_idx ON zone_polygon USING GIST (geom);

#	File/Call
1	App\Service\ZonePolygonResolver->ensureZonePolygonTable (line 477)
2	App\Service\ZonePolygonResolver->fetchPolygonFromStore (line 417)
3	App\Service\ZonePolygonResolver->{closure:App\Service\ZonePolygonResolver::resolvePolygon():102} (line 102)
4	App\Service\ZonePolygonResolver->resolvePolygon (line 106)
5	App\Service\ZonePolygonResolver->polygonWkt (line 62)
6	App\Service\OsmHistoryDatabaseService->requirePolygonWkt (line 711)
7	App\Service\OsmHistoryDatabaseService->getActivityStatsForZone (line 403)
8	App\Controller\AdminController->stats (line 785)
9	Symfony\Component\HttpKernel\HttpKernel->handleRaw (line 183)
10	Symfony\Component\HttpKernel\HttpKernel->handle (line 76)
11	Symfony\Component\HttpKernel\Kernel->handle (line 182)
12	Symfony\Component\Runtime\Runner\Symfony\HttpKernelRunner->run (line 35)
13	require_once (line 29)
14	require_once (line 5)

0.69 ms
(0.33%)

WITH last_versions AS (
  SELECT DISTINCT ON (osm_type, osm_id) osm_type, osm_id, valid_from, visible, geom
  FROM osm_object_history h
  WHERE h.geom IS NOT NULL
    AND ST_Intersects(h.geom, ST_GeomFromText(?, 4326))
  ORDER BY osm_type, osm_id, valid_from DESC
)
SELECT TO_TIMESTAMP(AVG(EXTRACT(EPOCH FROM valid_from))) AS avg_ts,
       COUNT(*) AS n
FROM last_versions
WHERE visible = true

Parameters:

[
  "MULTIPOLYGON(((1.087111 47.46182,1.08909 47.462246,1.092332 47.462641,1.092727 47.462735,1.097419 47.463505,1.098936 47.463881,1.101506 47.464276,1.101781 47.464271,1.102317 47.464316,1.103111 47.464445,1.104379 47.464635,1.105454 47.464751,1.106391 47.464808,1.107206 47.464936,1.10826 47.465189,1.110461 47.465728,1.112817 47.466109,1.113381 47.464294,1.113513 47.464126,1.113964 47.463055,1.114071 47.462741,1.113794 47.462605,1.114473 47.46203,1.114903 47.46173,1.114985 47.461732,1.115472 47.461593,1.115968 47.46138,1.116321 47.461159,1.116928 47.460452,1.117042 47.4604,1.11704 47.460282,1.116933 47.460044,1.116965 47.459839,1.116925 47.459778,1.117368 47.459246,1.117443 47.459017,1.118027 47.458908,1.118495 47.458767,1.11939 47.458349,1.119656 47.458289,1.120199 47.4582,1.120415 47.458111,1.120719 47.457884,1.120875 47.457655,1.120873 47.45734,1.120651 47.456797,1.121194 47.455606,1.121927 47.454859,1.122425 47.454412,1.122927 47.45403,1.123357 47.453679,1.123952 47.453131,1.124907 47.452293,1.125089 47.452157,1.126006 47.451518,1.126377 47.451269,1.126896 47.451069,1.127978 47.450724,1.128017 47.450683,1.128967 47.449939,1.12957 47.449631,1.130637 47.449417,1.131571 47.449112,1.132834 47.44899,1.133466 47.448979,1.13331 47.448543,1.133039 47.448055,1.132811 47.447101,1.13236 47.446467,1.131789 47.445381,1.131124 47.445418,1.130411 47.444737,1.129368 47.443916,1.129203 47.443688,1.128268 47.442735,1.126571 47.441548,1.125731 47.441028,1.124897 47.440526,1.123783 47.439797,1.123311 47.439447,1.122901 47.439026,1.12218 47.438219,1.121841 47.437749,1.121621 47.43737,1.1215 47.436836,1.121392 47.436568,1.121113 47.435443,1.120921 47.435084,1.120785 47.434901,1.119945 47.433588,1.119338 47.432567,1.119076 47.432165,1.11867 47.431598,1.118387 47.431275,1.118186 47.431098,1.117132 47.430316,1.116901 47.430117,1.116291 47.429483,1.115839 47.429127,1.115521 47.428979,1.115232 47.428888,1.114677 47.428629,1.113787 47.428168,1.11365 47.428024,1.112005 47.42916,1.110264 47.429861,1.10822 47.430323,1.107946 47.43041,1.105716 47.431461,1.105221 47.431668,1.105049 47.431665,1.104811 47.43131,1.10449 47.430874,1.104162 47.430485,1.103967 47.430341,1.103558 47.430251,1.103088 47.43024,1.102223 47.430253,1.097606 47.429535,1.095524 47.429232,1.093631 47.428931,1.093506 47.428922,1.090873 47.429263,1.087938 47.429606,1.086315 47.429787,1.085493 47.429886,1.085249 47.429865,1.084904 47.429694,1.084535 47.429396,1.083925 47.428869,1.082447 47.427648,1.082039 47.427881,1.08165 47.428165,1.080755 47.428852,1.080411 47.429103,1.080311 47.429151,1.079566 47.429379,1.078468 47.429727,1.078376 47.429776,1.076567 47.431017,1.075953 47.431466,1.075591 47.431866,1.075091 47.43223,1.074834 47.432567,1.073985 47.433577,1.073837 47.433703,1.073026 47.434645,1.072682 47.435507,1.072429 47.436065,1.072193 47.436546,1.07203 47.436825,1.071874 47.437291,1.071604 47.437838,1.071569 47.437982,1.071088 47.438199,1.070643 47.439024,1.070472 47.439412,1.070304 47.439734,1.069874 47.440495,1.069744 47.440877,1.069643 47.441083,1.069412 47.441448,1.068678 47.442464,1.068271 47.442968,1.067894 47.443303,1.067609 47.443622,1.067599 47.443697,1.067368 47.443992,1.067125 47.444379,1.067073 47.444497,1.066874 47.444554,1.066175 47.445276,1.06593 47.445481,1.065741 47.445559,1.065297 47.445543,1.064968 47.445835,1.064745 47.445961,1.06432 47.446425,1.064119 47.446776,1.064069 47.447061,1.063974 47.447117,1.06396 47.448416,1.06447 47.449312,1.06497 47.449758,1.065375 47.450039,1.065811 47.450291,1.066453 47.450745,1.066905 47.451022,1.067203 47.451177,1.068428 47.451881,1.068973 47.452164,1.069937 47.452832,1.070654 47.453151,1.071011 47.453193,1.071369 47.453047,1.075437 47.455225,1.07856 47.457054,1.080347 47.458204,1.083111 47.460008,1.083558 47.460205,1.085007 47.461078,1.086781 47.461771,1.087111 47.46182)))"
]

View formatted query View runnable query Explain query View query backtrace

WITH
  last_versions AS (
    SELECT
      DISTINCT ON (osm_type, osm_id) osm_type,
      osm_id,
      valid_from,
      visible,
      geom
    FROM
      osm_object_history h
    WHERE
      h.geom IS NOT NULL
      AND ST_Intersects(
        h.geom,
        ST_GeomFromText(?, 4326)
      )
    ORDER BY
      osm_type,
      osm_id,
      valid_from DESC
  )
SELECT
  TO_TIMESTAMP(
    AVG(
      EXTRACT(
        EPOCH
        FROM
          valid_from
      )
    )
  ) AS avg_ts,
  COUNT(*) AS n
FROM
  last_versions
WHERE
  visible = true

WITH last_versions AS (
  SELECT DISTINCT ON (osm_type, osm_id) osm_type, osm_id, valid_from, visible, geom
  FROM osm_object_history h
  WHERE h.geom IS NOT NULL
    AND ST_Intersects(h.geom, ST_GeomFromText('MULTIPOLYGON(((1.087111 47.46182,1.08909 47.462246,1.092332 47.462641,1.092727 47.462735,1.097419 47.463505,1.098936 47.463881,1.101506 47.464276,1.101781 47.464271,1.102317 47.464316,1.103111 47.464445,1.104379 47.464635,1.105454 47.464751,1.106391 47.464808,1.107206 47.464936,1.10826 47.465189,1.110461 47.465728,1.112817 47.466109,1.113381 47.464294,1.113513 47.464126,1.113964 47.463055,1.114071 47.462741,1.113794 47.462605,1.114473 47.46203,1.114903 47.46173,1.114985 47.461732,1.115472 47.461593,1.115968 47.46138,1.116321 47.461159,1.116928 47.460452,1.117042 47.4604,1.11704 47.460282,1.116933 47.460044,1.116965 47.459839,1.116925 47.459778,1.117368 47.459246,1.117443 47.459017,1.118027 47.458908,1.118495 47.458767,1.11939 47.458349,1.119656 47.458289,1.120199 47.4582,1.120415 47.458111,1.120719 47.457884,1.120875 47.457655,1.120873 47.45734,1.120651 47.456797,1.121194 47.455606,1.121927 47.454859,1.122425 47.454412,1.122927 47.45403,1.123357 47.453679,1.123952 47.453131,1.124907 47.452293,1.125089 47.452157,1.126006 47.451518,1.126377 47.451269,1.126896 47.451069,1.127978 47.450724,1.128017 47.450683,1.128967 47.449939,1.12957 47.449631,1.130637 47.449417,1.131571 47.449112,1.132834 47.44899,1.133466 47.448979,1.13331 47.448543,1.133039 47.448055,1.132811 47.447101,1.13236 47.446467,1.131789 47.445381,1.131124 47.445418,1.130411 47.444737,1.129368 47.443916,1.129203 47.443688,1.128268 47.442735,1.126571 47.441548,1.125731 47.441028,1.124897 47.440526,1.123783 47.439797,1.123311 47.439447,1.122901 47.439026,1.12218 47.438219,1.121841 47.437749,1.121621 47.43737,1.1215 47.436836,1.121392 47.436568,1.121113 47.435443,1.120921 47.435084,1.120785 47.434901,1.119945 47.433588,1.119338 47.432567,1.119076 47.432165,1.11867 47.431598,1.118387 47.431275,1.118186 47.431098,1.117132 47.430316,1.116901 47.430117,1.116291 47.429483,1.115839 47.429127,1.115521 47.428979,1.115232 47.428888,1.114677 47.428629,1.113787 47.428168,1.11365 47.428024,1.112005 47.42916,1.110264 47.429861,1.10822 47.430323,1.107946 47.43041,1.105716 47.431461,1.105221 47.431668,1.105049 47.431665,1.104811 47.43131,1.10449 47.430874,1.104162 47.430485,1.103967 47.430341,1.103558 47.430251,1.103088 47.43024,1.102223 47.430253,1.097606 47.429535,1.095524 47.429232,1.093631 47.428931,1.093506 47.428922,1.090873 47.429263,1.087938 47.429606,1.086315 47.429787,1.085493 47.429886,1.085249 47.429865,1.084904 47.429694,1.084535 47.429396,1.083925 47.428869,1.082447 47.427648,1.082039 47.427881,1.08165 47.428165,1.080755 47.428852,1.080411 47.429103,1.080311 47.429151,1.079566 47.429379,1.078468 47.429727,1.078376 47.429776,1.076567 47.431017,1.075953 47.431466,1.075591 47.431866,1.075091 47.43223,1.074834 47.432567,1.073985 47.433577,1.073837 47.433703,1.073026 47.434645,1.072682 47.435507,1.072429 47.436065,1.072193 47.436546,1.07203 47.436825,1.071874 47.437291,1.071604 47.437838,1.071569 47.437982,1.071088 47.438199,1.070643 47.439024,1.070472 47.439412,1.070304 47.439734,1.069874 47.440495,1.069744 47.440877,1.069643 47.441083,1.069412 47.441448,1.068678 47.442464,1.068271 47.442968,1.067894 47.443303,1.067609 47.443622,1.067599 47.443697,1.067368 47.443992,1.067125 47.444379,1.067073 47.444497,1.066874 47.444554,1.066175 47.445276,1.06593 47.445481,1.065741 47.445559,1.065297 47.445543,1.064968 47.445835,1.064745 47.445961,1.06432 47.446425,1.064119 47.446776,1.064069 47.447061,1.063974 47.447117,1.06396 47.448416,1.06447 47.449312,1.06497 47.449758,1.065375 47.450039,1.065811 47.450291,1.066453 47.450745,1.066905 47.451022,1.067203 47.451177,1.068428 47.451881,1.068973 47.452164,1.069937 47.452832,1.070654 47.453151,1.071011 47.453193,1.071369 47.453047,1.075437 47.455225,1.07856 47.457054,1.080347 47.458204,1.083111 47.460008,1.083558 47.460205,1.085007 47.461078,1.086781 47.461771,1.087111 47.46182)))', 4326))
  ORDER BY osm_type, osm_id, valid_from DESC
)
SELECT TO_TIMESTAMP(AVG(EXTRACT(EPOCH FROM valid_from))) AS avg_ts,
       COUNT(*) AS n
FROM last_versions
WHERE visible = true;

#	File/Call
1	Doctrine\DBAL\Connection->fetchAssociative (line 300)
2	App\Service\OsmHistoryDatabaseService->getActivityStatsForZone (line 512)
3	App\Controller\AdminController->stats (line 785)
4	Symfony\Component\HttpKernel\HttpKernel->handleRaw (line 183)
5	Symfony\Component\HttpKernel\HttpKernel->handle (line 76)
6	Symfony\Component\HttpKernel\Kernel->handle (line 182)
7	Symfony\Component\Runtime\Runner\Symfony\HttpKernelRunner->run (line 35)
8	require_once (line 29)
9	require_once (line 5)

0.68 ms
(0.32%)

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

Parameters:

[
  "37161"
]

View formatted query View runnable query Explain query View query backtrace

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

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'37161'') = ?
      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;

#	File/Call
1	Doctrine\DBAL\Connection->fetchOne (line 332)
2	App\Service\ZonePolygonResolver->fetchBoundaryWkt (line 292)
3	App\Service\ZonePolygonResolver->fetchBoundaryWktFromHistory (line 259)
4	App\Service\ZonePolygonResolver->{closure:App\Service\ZonePolygonResolver::resolvePolygon():101} (line 101)
5	App\Service\ZonePolygonResolver->resolvePolygon (line 106)
6	App\Service\ZonePolygonResolver->polygonWkt (line 62)
7	App\Service\OsmHistoryDatabaseService->requirePolygonWkt (line 711)
8	App\Service\OsmHistoryDatabaseService->getActivityStatsForZone (line 403)
9	App\Controller\AdminController->stats (line 785)
10	Symfony\Component\HttpKernel\HttpKernel->handleRaw (line 183)
11	Symfony\Component\HttpKernel\HttpKernel->handle (line 76)
12	Symfony\Component\HttpKernel\Kernel->handle (line 182)
13	Symfony\Component\Runtime\Runner\Symfony\HttpKernelRunner->run (line 35)
14	require_once (line 29)
15	require_once (line 5)

0.67 ms
(0.32%)

SELECT ST_AsText(ST_Multi(geom)) FROM zone_polygon WHERE insee_code = ?

Parameters:

[
  "37161"
]

View formatted query View runnable query Explain query View query backtrace

SELECT
  ST_AsText(
    ST_Multi(geom)
  )
FROM
  zone_polygon
WHERE
  insee_code = ?

SELECT ST_AsText(ST_Multi(geom)) FROM zone_polygon WHERE insee_code = '37161';

#	File/Call
1	Doctrine\DBAL\Connection->fetchOne (line 332)
2	App\Service\ZonePolygonResolver->fetchPolygonFromStore (line 422)
3	App\Service\ZonePolygonResolver->{closure:App\Service\ZonePolygonResolver::resolvePolygon():102} (line 102)
4	App\Service\ZonePolygonResolver->resolvePolygon (line 106)
5	App\Service\ZonePolygonResolver->polygonWkt (line 62)
6	App\Service\OsmHistoryDatabaseService->requirePolygonWkt (line 711)
7	App\Service\OsmHistoryDatabaseService->getActivityStatsForZone (line 403)
8	App\Controller\AdminController->stats (line 785)
9	Symfony\Component\HttpKernel\HttpKernel->handleRaw (line 183)
10	Symfony\Component\HttpKernel\HttpKernel->handle (line 76)
11	Symfony\Component\HttpKernel\Kernel->handle (line 182)
12	Symfony\Component\Runtime\Runner\Symfony\HttpKernelRunner->run (line 35)
13	require_once (line 29)
14	require_once (line 5)

0.31 ms
(0.14%)

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()
                )

Parameters:

[]

View formatted query View runnable query Explain query View query backtrace

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 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()
                );

#	File/Call
1	App\Service\ZonePolygonResolver->ensureZonePolygonTable (line 469)
2	App\Service\ZonePolygonResolver->fetchPolygonFromStore (line 417)
3	App\Service\ZonePolygonResolver->{closure:App\Service\ZonePolygonResolver::resolvePolygon():102} (line 102)
4	App\Service\ZonePolygonResolver->resolvePolygon (line 106)
5	App\Service\ZonePolygonResolver->polygonWkt (line 62)
6	App\Service\OsmHistoryDatabaseService->requirePolygonWkt (line 711)
7	App\Service\OsmHistoryDatabaseService->getActivityStatsForZone (line 403)
8	App\Controller\AdminController->stats (line 785)
9	Symfony\Component\HttpKernel\HttpKernel->handleRaw (line 183)
10	Symfony\Component\HttpKernel\HttpKernel->handle (line 76)
11	Symfony\Component\HttpKernel\Kernel->handle (line 182)
12	Symfony\Component\Runtime\Runner\Symfony\HttpKernelRunner->run (line 35)
13	require_once (line 29)
14	require_once (line 5)

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
App\Entity\CityFollowUp	5165
App\Entity\ZonePlaces	38
App\Entity\Stats	1
App\Entity\ActionLog	1

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.
App\Entity\ActionLog	No errors.
App\Entity\OsmObject	No errors.

Symfony Profiler

GET https://osm-labo.cipherbliss.com/admin/stats/37161

Configuration Settings

Theme

Page Width

Query Metrics

Grouped Statements

default connection

osm connection

Database Connections

Entity Managers

Second Level Cache

Managed Entities

default entity manager

Entities Mapping