Symfony Profiler

Query Metrics

35 Database Queries

29 Different statements

510.18 ms Query time

0 Invalid entities

5583 Managed entities

Grouped Statements

Show all queries

default connection

Time▼

Count

Info

284.69 ms
(55.80%)

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

92.17 ms
(18.07%)

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:

[
  26511
]

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

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

42.06 ms
(8.24%)

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

Parameters:

[
  "stats_de_ville"
  "{"insee_code":"14118","nom":"14118"}"
  "2026-07-13 00:49:05"
  null
  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\":\"14118\",\"nom\":\"14118\"}', '2026-07-13 00:49:05', NULL, 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)

4.88 ms
(0.96%)

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:

[
  26511
]

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 = 26511 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)

1.73 ms
(0.34%)

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

Parameters:

[
  "14118"
]

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

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

1.66 ms
(0.33%)

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:

[
  26511
]

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

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

1.35 ms
(0.26%)

SELECT t0.id AS id_1, t0.uuid_for_url AS uuid_for_url_2, t0.osm_kind AS osm_kind_3, t0.email AS email_4, t0.opted_out AS opted_out_5, t0.dead AS dead_6, t0.note AS note_7, t0.modified_date AS modified_date_8, t0.zip_code AS zip_code_9, t0.asked_humains_support AS asked_humains_support_10, t0.last_contact_attempt_date AS last_contact_attempt_date_11, t0.osm_id AS osm_id_12, t0.name AS name_13, t0.has_opening_hours AS has_opening_hours_14, t0.has_address AS has_address_15, t0.has_website AS has_website_16, t0.has_wheelchair AS has_wheelchair_17, t0.has_note AS has_note_18, t0.main_tag AS main_tag_19, t0.note_content AS note_content_20, t0.displayed_date AS displayed_date_21, t0.lat AS lat_22, t0.lon AS lon_23, t0.street AS street_24, t0.housenumber AS housenumber_25, t0.siret AS siret_26, t0.habitants AS habitants_27, t0.osm_data_date AS osm_data_date_28, t0.osm_version AS osm_version_29, t0.osm_user AS osm_user_30, t0.osm_uid AS osm_uid_31, t0.osm_changeset AS osm_changeset_32, t0.email_content AS email_content_33, t0.place_count AS place_count_34, t0.stats_id AS stats_id_35 FROM place t0 WHERE t0.stats_id = ?

Parameters:

[
  26511
]

View formatted query View runnable query Explain query View query backtrace

SELECT
  t0.id AS id_1,
  t0.uuid_for_url AS uuid_for_url_2,
  t0.osm_kind AS osm_kind_3,
  t0.email AS email_4,
  t0.opted_out AS opted_out_5,
  t0.dead AS dead_6,
  t0.note AS note_7,
  t0.modified_date AS modified_date_8,
  t0.zip_code AS zip_code_9,
  t0.asked_humains_support AS asked_humains_support_10,
  t0.last_contact_attempt_date AS last_contact_attempt_date_11,
  t0.osm_id AS osm_id_12,
  t0.name AS name_13,
  t0.has_opening_hours AS has_opening_hours_14,
  t0.has_address AS has_address_15,
  t0.has_website AS has_website_16,
  t0.has_wheelchair AS has_wheelchair_17,
  t0.has_note AS has_note_18,
  t0.main_tag AS main_tag_19,
  t0.note_content AS note_content_20,
  t0.displayed_date AS displayed_date_21,
  t0.lat AS lat_22,
  t0.lon AS lon_23,
  t0.street AS street_24,
  t0.housenumber AS housenumber_25,
  t0.siret AS siret_26,
  t0.habitants AS habitants_27,
  t0.osm_data_date AS osm_data_date_28,
  t0.osm_version AS osm_version_29,
  t0.osm_user AS osm_user_30,
  t0.osm_uid AS osm_uid_31,
  t0.osm_changeset AS osm_changeset_32,
  t0.email_content AS email_content_33,
  t0.place_count AS place_count_34,
  t0.stats_id AS stats_id_35
FROM
  place t0
WHERE
  t0.stats_id = ?

SELECT t0.id AS id_1, t0.uuid_for_url AS uuid_for_url_2, t0.osm_kind AS osm_kind_3, t0.email AS email_4, t0.opted_out AS opted_out_5, t0.dead AS dead_6, t0.note AS note_7, t0.modified_date AS modified_date_8, t0.zip_code AS zip_code_9, t0.asked_humains_support AS asked_humains_support_10, t0.last_contact_attempt_date AS last_contact_attempt_date_11, t0.osm_id AS osm_id_12, t0.name AS name_13, t0.has_opening_hours AS has_opening_hours_14, t0.has_address AS has_address_15, t0.has_website AS has_website_16, t0.has_wheelchair AS has_wheelchair_17, t0.has_note AS has_note_18, t0.main_tag AS main_tag_19, t0.note_content AS note_content_20, t0.displayed_date AS displayed_date_21, t0.lat AS lat_22, t0.lon AS lon_23, t0.street AS street_24, t0.housenumber AS housenumber_25, t0.siret AS siret_26, t0.habitants AS habitants_27, t0.osm_data_date AS osm_data_date_28, t0.osm_version AS osm_version_29, t0.osm_user AS osm_user_30, t0.osm_uid AS osm_uid_31, t0.osm_changeset AS osm_changeset_32, t0.email_content AS email_content_33, t0.place_count AS place_count_34, t0.stats_id AS stats_id_35 FROM place t0 WHERE t0.stats_id = 26511;

#	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 1201)
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.44 ms
(0.09%)

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:

[
  "14118"
]

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 = '14118' 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.32 ms
(0.06%)

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:

[
  26511
]

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 = 26511 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.26 ms
(0.05%)

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

osm connection

Time▼

Count

Info

44.87 ms
(8.79%)

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(((-0.343146 49.153021,-0.344011 49.154516,-0.344181 49.154826,-0.344692 49.155692,-0.347301 49.160279,-0.34893 49.163043,-0.349032 49.163011,-0.351206 49.162423,-0.351183 49.162382,-0.352075 49.162259,-0.351638 49.161252,-0.351978 49.161109,-0.353842 49.160371,-0.354227 49.160278,-0.354771 49.160204,-0.355388 49.160075,-0.355862 49.160012,-0.35592 49.159931,-0.357276 49.159673,-0.358832 49.15942,-0.3614 49.158753,-0.361512 49.158932,-0.361559 49.159144,-0.361737 49.159545,-0.363491 49.159539,-0.365058 49.159653,-0.366113 49.159805,-0.36606 49.159913,-0.367161 49.160029,-0.367039 49.160245,-0.367126 49.160338,-0.367542 49.16042,-0.367154 49.161279,-0.366696 49.16238,-0.366369 49.163237,-0.365393 49.165588,-0.366551 49.165757,-0.367419 49.166573,-0.367734 49.166546,-0.368691 49.166488,-0.369021 49.166643,-0.369394 49.166596,-0.369707 49.166138,-0.369761 49.165831,-0.369993 49.165534,-0.36999 49.165111,-0.370525 49.164931,-0.370799 49.165029,-0.371088 49.165019,-0.371073 49.164813,-0.371237 49.164687,-0.371635 49.164569,-0.372195 49.16437,-0.37207 49.164245,-0.37222 49.163839,-0.372673 49.16391,-0.372823 49.163605,-0.373602 49.16323,-0.374657 49.163297,-0.375154 49.163278,-0.375636 49.163391,-0.37587 49.163396,-0.376201 49.163468,-0.376853 49.163244,-0.377097 49.163145,-0.377393 49.16296,-0.377597 49.162759,-0.377924 49.162747,-0.378455 49.162595,-0.378652 49.162572,-0.378968 49.162597,-0.379375 49.162736,-0.379864 49.162721,-0.380041 49.162913,-0.380275 49.162926,-0.380525 49.1628,-0.381006 49.162718,-0.381154 49.16272,-0.381607 49.162803,-0.38192 49.162749,-0.382432 49.162812,-0.382874 49.162905,-0.383183 49.16292,-0.38365 49.162896,-0.384223 49.162986,-0.384519 49.162983,-0.385044 49.162871,-0.385204 49.162856,-0.385761 49.162861,-0.386686 49.162893,-0.386863 49.162874,-0.386989 49.162917,-0.38719 49.163165,-0.387348 49.163199,-0.38757 49.163161,-0.387746 49.163265,-0.388392 49.163499,-0.388508 49.163568,-0.388629 49.16384,-0.388737 49.163873,-0.389417 49.163893,-0.39016 49.163853,-0.39037 49.163885,-0.390521 49.163865,-0.39071 49.163755,-0.391104 49.163965,-0.391283 49.163985,-0.391479 49.163846,-0.391637 49.163871,-0.391718 49.164039,-0.391825 49.164133,-0.392344 49.164219,-0.392396 49.164333,-0.392202 49.164534,-0.39224 49.164675,-0.392467 49.164717,-0.393401 49.164738,-0.393628 49.164797,-0.393711 49.164874,-0.393734 49.16506,-0.393817 49.165238,-0.394168 49.165488,-0.394195 49.16558,-0.394095 49.165813,-0.394206 49.16592,-0.394488 49.165935,-0.394589 49.166029,-0.394382 49.166159,-0.394341 49.166304,-0.394475 49.166484,-0.393989 49.166479,-0.39469 49.167315,-0.3949 49.167642,-0.394757 49.168203,-0.395434 49.168118,-0.395943 49.168145,-0.396734 49.16824,-0.397261 49.168362,-0.397744 49.168449,-0.398263 49.168501,-0.398764 49.168427,-0.398954 49.168427,-0.399842 49.168505,-0.400501 49.168588,-0.40165 49.168642,-0.401843 49.168619,-0.401886 49.168808,-0.401024 49.168842,-0.400668 49.16884,-0.40121 49.169022,-0.401896 49.169166,-0.402531 49.169331,-0.403311 49.169584,-0.403986 49.169882,-0.404946 49.170359,-0.405729 49.170854,-0.405906 49.171005,-0.405729 49.171154,-0.405681 49.171548,-0.405555 49.171794,-0.405382 49.171992,-0.405203 49.172314,-0.40495 49.172967,-0.404685 49.173178,-0.404534 49.173765,-0.404367 49.174181,-0.404116 49.174567,-0.40379 49.174912,-0.403702 49.175109,-0.40359 49.175746,-0.403432 49.176022,-0.403399 49.176207,-0.403281 49.176537,-0.403245 49.176908,-0.403307 49.177189,-0.40333 49.177451,-0.40444 49.177656,-0.40472 49.177728,-0.405289 49.177978,-0.405641 49.178103,-0.405932 49.178236,-0.406305 49.178545,-0.406746 49.179013,-0.406846 49.179249,-0.407072 49.179685,-0.407946 49.180449,-0.408281 49.180799,-0.408518 49.181075,-0.408889 49.18157,-0.409218 49.181945,-0.409854 49.182559,-0.410279 49.182858,-0.412371 49.184264,-0.413763 49.186887,-0.412616 49.187001,-0.412809 49.188706,-0.412489 49.188556,-0.412156 49.188527,-0.409998 49.188281,-0.40909 49.188192,-0.407886 49.188045,-0.407398 49.187971,-0.407077 49.188342,-0.406746 49.188311,-0.406719 49.188541,-0.407197 49.188564,-0.407227 49.18886,-0.407341 49.189169,-0.407928 49.190529,-0.408202 49.191003,-0.40861 49.191484,-0.40909 49.192025,-0.409805 49.192863,-0.409979 49.193105,-0.410316 49.193419,-0.411494 49.194623,-0.411607 49.194757,-0.410534 49.195615,-0.409145 49.196516,-0.409262 49.196648,-0.408165 49.197343,-0.407975 49.197476,-0.407325 49.197846,-0.405263 49.198991,-0.404332 49.199485,-0.402052 49.200619,-0.401954 49.200659,-0.401183 49.200203,-0.40032 49.199734,-0.39942 49.199281,-0.397454 49.198391,-0.397188 49.198281,-0.396829 49.198203,-0.396746 49.198752,-0.396663 49.199062,-0.396426 49.199662,-0.396236 49.200002,-0.395857 49.200533,-0.395425 49.201011,-0.395305 49.201177,-0.394933 49.201556,-0.394697 49.201752,-0.394144 49.202128,-0.393541 49.202435,-0.393163 49.202584,-0.39252 49.202782,-0.391887 49.202914,-0.391442 49.203031,-0.391186 49.203869,-0.391082 49.204057,-0.390728 49.204403,-0.389685 49.205145,-0.388482 49.206091,-0.388227 49.206312,-0.387557 49.20685,-0.386665 49.207473,-0.386334 49.207729,-0.38605 49.207978,-0.384875 49.20887,-0.383782 49.209603,-0.383167 49.209996,-0.382451 49.210403,-0.381778 49.210736,-0.380578 49.211298,-0.380243 49.211425,-0.379901 49.211504,-0.378835 49.21172,-0.379058 49.211978,-0.378217 49.212597,-0.377878 49.213425,-0.377715 49.213653,-0.377597 49.213704,-0.377069 49.213713,-0.376573 49.213666,-0.376292 49.213588,-0.376093 49.213423,-0.375643 49.21363,-0.374589 49.214191,-0.373625 49.214734,-0.372699 49.215311,-0.3723 49.215487,-0.371873 49.215613,-0.370692 49.215999,-0.370054 49.216261,-0.368957 49.215884,-0.365025 49.214884,-0.364905 49.2146,-0.364524 49.214546,-0.363568 49.214325,-0.3637 49.214146,-0.363592 49.214071,-0.363135 49.213873,-0.3625 49.213639,-0.361502 49.213233,-0.361219 49.213134,-0.360518 49.212838,-0.360234 49.212734,-0.360181 49.212784,-0.359919 49.212664,-0.359617 49.212593,-0.358595 49.212293,-0.357978 49.212082,-0.357423 49.211829,-0.356762 49.211478,-0.356263 49.211078,-0.355031 49.209813,-0.355728 49.208292,-0.355767 49.208181,-0.353922 49.207679,-0.353608 49.208073,-0.353316 49.208483,-0.351284 49.207635,-0.352076 49.20696,-0.351265 49.206518,-0.350551 49.206207,-0.350812 49.205853,-0.35085 49.205665,-0.350667 49.205545,-0.351045 49.205164,-0.350122 49.20522,-0.349938 49.205186,-0.348881 49.204909,-0.349198 49.204606,-0.347589 49.203523,-0.348851 49.202544,-0.349741 49.201869,-0.350068 49.201654,-0.350394 49.20154,-0.349134 49.20117,-0.348494 49.200964,-0.347042 49.200447,-0.346374 49.200197,-0.344619 49.199462,-0.343931 49.19915,-0.343267 49.198833,-0.342701 49.198549,-0.341533 49.197911,-0.340977 49.19757,-0.340484 49.197241,-0.339456 49.19649,-0.338403 49.195574,-0.337975 49.195154,-0.336097 49.193225,-0.335923 49.193036,-0.336862 49.192373,-0.337407 49.192164,-0.337719 49.191952,-0.338017 49.191699,-0.337283 49.190903,-0.336465 49.190027,-0.336125 49.189505,-0.336029 49.189234,-0.335885 49.188975,-0.335359 49.188303,-0.334856 49.187763,-0.334714 49.187643,-0.333644 49.185865,-0.333629 49.185828,-0.334197 49.185776,-0.334756 49.185657,-0.335023 49.185534,-0.335207 49.185365,-0.335304 49.18521,-0.335244 49.184828,-0.334687 49.183541,-0.334614 49.183232,-0.334723 49.182903,-0.335042 49.182501,-0.335426 49.182185,-0.334914 49.18139,-0.334336 49.18154,-0.333924 49.180556,-0.33423 49.180556,-0.333433 49.179282,-0.333016 49.179351,-0.332609 49.177461,-0.333265 49.177257,-0.333639 49.177157,-0.333986 49.17709,-0.33384 49.176727,-0.331787 49.177315,-0.331223 49.177385,-0.331235 49.176979,-0.331936 49.176823,-0.331739 49.176552,-0.331164 49.17672,-0.330778 49.174702,-0.330723 49.173986,-0.330867 49.174004,-0.330855 49.172347,-0.331116 49.172435,-0.33131 49.167625,-0.331918 49.167564,-0.334021 49.167298,-0.335878 49.167077,-0.336404 49.166992,-0.337252 49.166742,-0.338364 49.166484,-0.339238 49.166258,-0.340235 49.165969,-0.341561 49.165554,-0.341719 49.165471,-0.342581 49.164769,-0.343149 49.164344,-0.342781 49.164141,-0.340218 49.162523,-0.339193 49.161854,-0.339039 49.161659,-0.340669 49.160344,-0.338979 49.159321,-0.337774 49.158528,-0.337291 49.158221,-0.338395 49.157604,-0.339418 49.157044,-0.34047 49.156454,-0.338851 49.155275,-0.338904 49.155201,-0.339124 49.155208,-0.339376 49.155121,-0.340973 49.154184,-0.341555 49.153867,-0.342901 49.153089,-0.343146 49.153021)))"
  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(((-0.343146 49.153021,-0.344011 49.154516,-0.344181 49.154826,-0.344692 49.155692,-0.347301 49.160279,-0.34893 49.163043,-0.349032 49.163011,-0.351206 49.162423,-0.351183 49.162382,-0.352075 49.162259,-0.351638 49.161252,-0.351978 49.161109,-0.353842 49.160371,-0.354227 49.160278,-0.354771 49.160204,-0.355388 49.160075,-0.355862 49.160012,-0.35592 49.159931,-0.357276 49.159673,-0.358832 49.15942,-0.3614 49.158753,-0.361512 49.158932,-0.361559 49.159144,-0.361737 49.159545,-0.363491 49.159539,-0.365058 49.159653,-0.366113 49.159805,-0.36606 49.159913,-0.367161 49.160029,-0.367039 49.160245,-0.367126 49.160338,-0.367542 49.16042,-0.367154 49.161279,-0.366696 49.16238,-0.366369 49.163237,-0.365393 49.165588,-0.366551 49.165757,-0.367419 49.166573,-0.367734 49.166546,-0.368691 49.166488,-0.369021 49.166643,-0.369394 49.166596,-0.369707 49.166138,-0.369761 49.165831,-0.369993 49.165534,-0.36999 49.165111,-0.370525 49.164931,-0.370799 49.165029,-0.371088 49.165019,-0.371073 49.164813,-0.371237 49.164687,-0.371635 49.164569,-0.372195 49.16437,-0.37207 49.164245,-0.37222 49.163839,-0.372673 49.16391,-0.372823 49.163605,-0.373602 49.16323,-0.374657 49.163297,-0.375154 49.163278,-0.375636 49.163391,-0.37587 49.163396,-0.376201 49.163468,-0.376853 49.163244,-0.377097 49.163145,-0.377393 49.16296,-0.377597 49.162759,-0.377924 49.162747,-0.378455 49.162595,-0.378652 49.162572,-0.378968 49.162597,-0.379375 49.162736,-0.379864 49.162721,-0.380041 49.162913,-0.380275 49.162926,-0.380525 49.1628,-0.381006 49.162718,-0.381154 49.16272,-0.381607 49.162803,-0.38192 49.162749,-0.382432 49.162812,-0.382874 49.162905,-0.383183 49.16292,-0.38365 49.162896,-0.384223 49.162986,-0.384519 49.162983,-0.385044 49.162871,-0.385204 49.162856,-0.385761 49.162861,-0.386686 49.162893,-0.386863 49.162874,-0.386989 49.162917,-0.38719 49.163165,-0.387348 49.163199,-0.38757 49.163161,-0.387746 49.163265,-0.388392 49.163499,-0.388508 49.163568,-0.388629 49.16384,-0.388737 49.163873,-0.389417 49.163893,-0.39016 49.163853,-0.39037 49.163885,-0.390521 49.163865,-0.39071 49.163755,-0.391104 49.163965,-0.391283 49.163985,-0.391479 49.163846,-0.391637 49.163871,-0.391718 49.164039,-0.391825 49.164133,-0.392344 49.164219,-0.392396 49.164333,-0.392202 49.164534,-0.39224 49.164675,-0.392467 49.164717,-0.393401 49.164738,-0.393628 49.164797,-0.393711 49.164874,-0.393734 49.16506,-0.393817 49.165238,-0.394168 49.165488,-0.394195 49.16558,-0.394095 49.165813,-0.394206 49.16592,-0.394488 49.165935,-0.394589 49.166029,-0.394382 49.166159,-0.394341 49.166304,-0.394475 49.166484,-0.393989 49.166479,-0.39469 49.167315,-0.3949 49.167642,-0.394757 49.168203,-0.395434 49.168118,-0.395943 49.168145,-0.396734 49.16824,-0.397261 49.168362,-0.397744 49.168449,-0.398263 49.168501,-0.398764 49.168427,-0.398954 49.168427,-0.399842 49.168505,-0.400501 49.168588,-0.40165 49.168642,-0.401843 49.168619,-0.401886 49.168808,-0.401024 49.168842,-0.400668 49.16884,-0.40121 49.169022,-0.401896 49.169166,-0.402531 49.169331,-0.403311 49.169584,-0.403986 49.169882,-0.404946 49.170359,-0.405729 49.170854,-0.405906 49.171005,-0.405729 49.171154,-0.405681 49.171548,-0.405555 49.171794,-0.405382 49.171992,-0.405203 49.172314,-0.40495 49.172967,-0.404685 49.173178,-0.404534 49.173765,-0.404367 49.174181,-0.404116 49.174567,-0.40379 49.174912,-0.403702 49.175109,-0.40359 49.175746,-0.403432 49.176022,-0.403399 49.176207,-0.403281 49.176537,-0.403245 49.176908,-0.403307 49.177189,-0.40333 49.177451,-0.40444 49.177656,-0.40472 49.177728,-0.405289 49.177978,-0.405641 49.178103,-0.405932 49.178236,-0.406305 49.178545,-0.406746 49.179013,-0.406846 49.179249,-0.407072 49.179685,-0.407946 49.180449,-0.408281 49.180799,-0.408518 49.181075,-0.408889 49.18157,-0.409218 49.181945,-0.409854 49.182559,-0.410279 49.182858,-0.412371 49.184264,-0.413763 49.186887,-0.412616 49.187001,-0.412809 49.188706,-0.412489 49.188556,-0.412156 49.188527,-0.409998 49.188281,-0.40909 49.188192,-0.407886 49.188045,-0.407398 49.187971,-0.407077 49.188342,-0.406746 49.188311,-0.406719 49.188541,-0.407197 49.188564,-0.407227 49.18886,-0.407341 49.189169,-0.407928 49.190529,-0.408202 49.191003,-0.40861 49.191484,-0.40909 49.192025,-0.409805 49.192863,-0.409979 49.193105,-0.410316 49.193419,-0.411494 49.194623,-0.411607 49.194757,-0.410534 49.195615,-0.409145 49.196516,-0.409262 49.196648,-0.408165 49.197343,-0.407975 49.197476,-0.407325 49.197846,-0.405263 49.198991,-0.404332 49.199485,-0.402052 49.200619,-0.401954 49.200659,-0.401183 49.200203,-0.40032 49.199734,-0.39942 49.199281,-0.397454 49.198391,-0.397188 49.198281,-0.396829 49.198203,-0.396746 49.198752,-0.396663 49.199062,-0.396426 49.199662,-0.396236 49.200002,-0.395857 49.200533,-0.395425 49.201011,-0.395305 49.201177,-0.394933 49.201556,-0.394697 49.201752,-0.394144 49.202128,-0.393541 49.202435,-0.393163 49.202584,-0.39252 49.202782,-0.391887 49.202914,-0.391442 49.203031,-0.391186 49.203869,-0.391082 49.204057,-0.390728 49.204403,-0.389685 49.205145,-0.388482 49.206091,-0.388227 49.206312,-0.387557 49.20685,-0.386665 49.207473,-0.386334 49.207729,-0.38605 49.207978,-0.384875 49.20887,-0.383782 49.209603,-0.383167 49.209996,-0.382451 49.210403,-0.381778 49.210736,-0.380578 49.211298,-0.380243 49.211425,-0.379901 49.211504,-0.378835 49.21172,-0.379058 49.211978,-0.378217 49.212597,-0.377878 49.213425,-0.377715 49.213653,-0.377597 49.213704,-0.377069 49.213713,-0.376573 49.213666,-0.376292 49.213588,-0.376093 49.213423,-0.375643 49.21363,-0.374589 49.214191,-0.373625 49.214734,-0.372699 49.215311,-0.3723 49.215487,-0.371873 49.215613,-0.370692 49.215999,-0.370054 49.216261,-0.368957 49.215884,-0.365025 49.214884,-0.364905 49.2146,-0.364524 49.214546,-0.363568 49.214325,-0.3637 49.214146,-0.363592 49.214071,-0.363135 49.213873,-0.3625 49.213639,-0.361502 49.213233,-0.361219 49.213134,-0.360518 49.212838,-0.360234 49.212734,-0.360181 49.212784,-0.359919 49.212664,-0.359617 49.212593,-0.358595 49.212293,-0.357978 49.212082,-0.357423 49.211829,-0.356762 49.211478,-0.356263 49.211078,-0.355031 49.209813,-0.355728 49.208292,-0.355767 49.208181,-0.353922 49.207679,-0.353608 49.208073,-0.353316 49.208483,-0.351284 49.207635,-0.352076 49.20696,-0.351265 49.206518,-0.350551 49.206207,-0.350812 49.205853,-0.35085 49.205665,-0.350667 49.205545,-0.351045 49.205164,-0.350122 49.20522,-0.349938 49.205186,-0.348881 49.204909,-0.349198 49.204606,-0.347589 49.203523,-0.348851 49.202544,-0.349741 49.201869,-0.350068 49.201654,-0.350394 49.20154,-0.349134 49.20117,-0.348494 49.200964,-0.347042 49.200447,-0.346374 49.200197,-0.344619 49.199462,-0.343931 49.19915,-0.343267 49.198833,-0.342701 49.198549,-0.341533 49.197911,-0.340977 49.19757,-0.340484 49.197241,-0.339456 49.19649,-0.338403 49.195574,-0.337975 49.195154,-0.336097 49.193225,-0.335923 49.193036,-0.336862 49.192373,-0.337407 49.192164,-0.337719 49.191952,-0.338017 49.191699,-0.337283 49.190903,-0.336465 49.190027,-0.336125 49.189505,-0.336029 49.189234,-0.335885 49.188975,-0.335359 49.188303,-0.334856 49.187763,-0.334714 49.187643,-0.333644 49.185865,-0.333629 49.185828,-0.334197 49.185776,-0.334756 49.185657,-0.335023 49.185534,-0.335207 49.185365,-0.335304 49.18521,-0.335244 49.184828,-0.334687 49.183541,-0.334614 49.183232,-0.334723 49.182903,-0.335042 49.182501,-0.335426 49.182185,-0.334914 49.18139,-0.334336 49.18154,-0.333924 49.180556,-0.33423 49.180556,-0.333433 49.179282,-0.333016 49.179351,-0.332609 49.177461,-0.333265 49.177257,-0.333639 49.177157,-0.333986 49.17709,-0.33384 49.176727,-0.331787 49.177315,-0.331223 49.177385,-0.331235 49.176979,-0.331936 49.176823,-0.331739 49.176552,-0.331164 49.17672,-0.330778 49.174702,-0.330723 49.173986,-0.330867 49.174004,-0.330855 49.172347,-0.331116 49.172435,-0.33131 49.167625,-0.331918 49.167564,-0.334021 49.167298,-0.335878 49.167077,-0.336404 49.166992,-0.337252 49.166742,-0.338364 49.166484,-0.339238 49.166258,-0.340235 49.165969,-0.341561 49.165554,-0.341719 49.165471,-0.342581 49.164769,-0.343149 49.164344,-0.342781 49.164141,-0.340218 49.162523,-0.339193 49.161854,-0.339039 49.161659,-0.340669 49.160344,-0.338979 49.159321,-0.337774 49.158528,-0.337291 49.158221,-0.338395 49.157604,-0.339418 49.157044,-0.34047 49.156454,-0.338851 49.155275,-0.338904 49.155201,-0.339124 49.155208,-0.339376 49.155121,-0.340973 49.154184,-0.341555 49.153867,-0.342901 49.153089,-0.343146 49.153021)))'') 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)

10.51 ms
(2.06%)

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:

[
  "14118"
]

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

2.91 ms
(0.57%)

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)

2.54 ms
(0.50%)

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(((-0.343146 49.153021,-0.344011 49.154516,-0.344181 49.154826,-0.344692 49.155692,-0.347301 49.160279,-0.34893 49.163043,-0.349032 49.163011,-0.351206 49.162423,-0.351183 49.162382,-0.352075 49.162259,-0.351638 49.161252,-0.351978 49.161109,-0.353842 49.160371,-0.354227 49.160278,-0.354771 49.160204,-0.355388 49.160075,-0.355862 49.160012,-0.35592 49.159931,-0.357276 49.159673,-0.358832 49.15942,-0.3614 49.158753,-0.361512 49.158932,-0.361559 49.159144,-0.361737 49.159545,-0.363491 49.159539,-0.365058 49.159653,-0.366113 49.159805,-0.36606 49.159913,-0.367161 49.160029,-0.367039 49.160245,-0.367126 49.160338,-0.367542 49.16042,-0.367154 49.161279,-0.366696 49.16238,-0.366369 49.163237,-0.365393 49.165588,-0.366551 49.165757,-0.367419 49.166573,-0.367734 49.166546,-0.368691 49.166488,-0.369021 49.166643,-0.369394 49.166596,-0.369707 49.166138,-0.369761 49.165831,-0.369993 49.165534,-0.36999 49.165111,-0.370525 49.164931,-0.370799 49.165029,-0.371088 49.165019,-0.371073 49.164813,-0.371237 49.164687,-0.371635 49.164569,-0.372195 49.16437,-0.37207 49.164245,-0.37222 49.163839,-0.372673 49.16391,-0.372823 49.163605,-0.373602 49.16323,-0.374657 49.163297,-0.375154 49.163278,-0.375636 49.163391,-0.37587 49.163396,-0.376201 49.163468,-0.376853 49.163244,-0.377097 49.163145,-0.377393 49.16296,-0.377597 49.162759,-0.377924 49.162747,-0.378455 49.162595,-0.378652 49.162572,-0.378968 49.162597,-0.379375 49.162736,-0.379864 49.162721,-0.380041 49.162913,-0.380275 49.162926,-0.380525 49.1628,-0.381006 49.162718,-0.381154 49.16272,-0.381607 49.162803,-0.38192 49.162749,-0.382432 49.162812,-0.382874 49.162905,-0.383183 49.16292,-0.38365 49.162896,-0.384223 49.162986,-0.384519 49.162983,-0.385044 49.162871,-0.385204 49.162856,-0.385761 49.162861,-0.386686 49.162893,-0.386863 49.162874,-0.386989 49.162917,-0.38719 49.163165,-0.387348 49.163199,-0.38757 49.163161,-0.387746 49.163265,-0.388392 49.163499,-0.388508 49.163568,-0.388629 49.16384,-0.388737 49.163873,-0.389417 49.163893,-0.39016 49.163853,-0.39037 49.163885,-0.390521 49.163865,-0.39071 49.163755,-0.391104 49.163965,-0.391283 49.163985,-0.391479 49.163846,-0.391637 49.163871,-0.391718 49.164039,-0.391825 49.164133,-0.392344 49.164219,-0.392396 49.164333,-0.392202 49.164534,-0.39224 49.164675,-0.392467 49.164717,-0.393401 49.164738,-0.393628 49.164797,-0.393711 49.164874,-0.393734 49.16506,-0.393817 49.165238,-0.394168 49.165488,-0.394195 49.16558,-0.394095 49.165813,-0.394206 49.16592,-0.394488 49.165935,-0.394589 49.166029,-0.394382 49.166159,-0.394341 49.166304,-0.394475 49.166484,-0.393989 49.166479,-0.39469 49.167315,-0.3949 49.167642,-0.394757 49.168203,-0.395434 49.168118,-0.395943 49.168145,-0.396734 49.16824,-0.397261 49.168362,-0.397744 49.168449,-0.398263 49.168501,-0.398764 49.168427,-0.398954 49.168427,-0.399842 49.168505,-0.400501 49.168588,-0.40165 49.168642,-0.401843 49.168619,-0.401886 49.168808,-0.401024 49.168842,-0.400668 49.16884,-0.40121 49.169022,-0.401896 49.169166,-0.402531 49.169331,-0.403311 49.169584,-0.403986 49.169882,-0.404946 49.170359,-0.405729 49.170854,-0.405906 49.171005,-0.405729 49.171154,-0.405681 49.171548,-0.405555 49.171794,-0.405382 49.171992,-0.405203 49.172314,-0.40495 49.172967,-0.404685 49.173178,-0.404534 49.173765,-0.404367 49.174181,-0.404116 49.174567,-0.40379 49.174912,-0.403702 49.175109,-0.40359 49.175746,-0.403432 49.176022,-0.403399 49.176207,-0.403281 49.176537,-0.403245 49.176908,-0.403307 49.177189,-0.40333 49.177451,-0.40444 49.177656,-0.40472 49.177728,-0.405289 49.177978,-0.405641 49.178103,-0.405932 49.178236,-0.406305 49.178545,-0.406746 49.179013,-0.406846 49.179249,-0.407072 49.179685,-0.407946 49.180449,-0.408281 49.180799,-0.408518 49.181075,-0.408889 49.18157,-0.409218 49.181945,-0.409854 49.182559,-0.410279 49.182858,-0.412371 49.184264,-0.413763 49.186887,-0.412616 49.187001,-0.412809 49.188706,-0.412489 49.188556,-0.412156 49.188527,-0.409998 49.188281,-0.40909 49.188192,-0.407886 49.188045,-0.407398 49.187971,-0.407077 49.188342,-0.406746 49.188311,-0.406719 49.188541,-0.407197 49.188564,-0.407227 49.18886,-0.407341 49.189169,-0.407928 49.190529,-0.408202 49.191003,-0.40861 49.191484,-0.40909 49.192025,-0.409805 49.192863,-0.409979 49.193105,-0.410316 49.193419,-0.411494 49.194623,-0.411607 49.194757,-0.410534 49.195615,-0.409145 49.196516,-0.409262 49.196648,-0.408165 49.197343,-0.407975 49.197476,-0.407325 49.197846,-0.405263 49.198991,-0.404332 49.199485,-0.402052 49.200619,-0.401954 49.200659,-0.401183 49.200203,-0.40032 49.199734,-0.39942 49.199281,-0.397454 49.198391,-0.397188 49.198281,-0.396829 49.198203,-0.396746 49.198752,-0.396663 49.199062,-0.396426 49.199662,-0.396236 49.200002,-0.395857 49.200533,-0.395425 49.201011,-0.395305 49.201177,-0.394933 49.201556,-0.394697 49.201752,-0.394144 49.202128,-0.393541 49.202435,-0.393163 49.202584,-0.39252 49.202782,-0.391887 49.202914,-0.391442 49.203031,-0.391186 49.203869,-0.391082 49.204057,-0.390728 49.204403,-0.389685 49.205145,-0.388482 49.206091,-0.388227 49.206312,-0.387557 49.20685,-0.386665 49.207473,-0.386334 49.207729,-0.38605 49.207978,-0.384875 49.20887,-0.383782 49.209603,-0.383167 49.209996,-0.382451 49.210403,-0.381778 49.210736,-0.380578 49.211298,-0.380243 49.211425,-0.379901 49.211504,-0.378835 49.21172,-0.379058 49.211978,-0.378217 49.212597,-0.377878 49.213425,-0.377715 49.213653,-0.377597 49.213704,-0.377069 49.213713,-0.376573 49.213666,-0.376292 49.213588,-0.376093 49.213423,-0.375643 49.21363,-0.374589 49.214191,-0.373625 49.214734,-0.372699 49.215311,-0.3723 49.215487,-0.371873 49.215613,-0.370692 49.215999,-0.370054 49.216261,-0.368957 49.215884,-0.365025 49.214884,-0.364905 49.2146,-0.364524 49.214546,-0.363568 49.214325,-0.3637 49.214146,-0.363592 49.214071,-0.363135 49.213873,-0.3625 49.213639,-0.361502 49.213233,-0.361219 49.213134,-0.360518 49.212838,-0.360234 49.212734,-0.360181 49.212784,-0.359919 49.212664,-0.359617 49.212593,-0.358595 49.212293,-0.357978 49.212082,-0.357423 49.211829,-0.356762 49.211478,-0.356263 49.211078,-0.355031 49.209813,-0.355728 49.208292,-0.355767 49.208181,-0.353922 49.207679,-0.353608 49.208073,-0.353316 49.208483,-0.351284 49.207635,-0.352076 49.20696,-0.351265 49.206518,-0.350551 49.206207,-0.350812 49.205853,-0.35085 49.205665,-0.350667 49.205545,-0.351045 49.205164,-0.350122 49.20522,-0.349938 49.205186,-0.348881 49.204909,-0.349198 49.204606,-0.347589 49.203523,-0.348851 49.202544,-0.349741 49.201869,-0.350068 49.201654,-0.350394 49.20154,-0.349134 49.20117,-0.348494 49.200964,-0.347042 49.200447,-0.346374 49.200197,-0.344619 49.199462,-0.343931 49.19915,-0.343267 49.198833,-0.342701 49.198549,-0.341533 49.197911,-0.340977 49.19757,-0.340484 49.197241,-0.339456 49.19649,-0.338403 49.195574,-0.337975 49.195154,-0.336097 49.193225,-0.335923 49.193036,-0.336862 49.192373,-0.337407 49.192164,-0.337719 49.191952,-0.338017 49.191699,-0.337283 49.190903,-0.336465 49.190027,-0.336125 49.189505,-0.336029 49.189234,-0.335885 49.188975,-0.335359 49.188303,-0.334856 49.187763,-0.334714 49.187643,-0.333644 49.185865,-0.333629 49.185828,-0.334197 49.185776,-0.334756 49.185657,-0.335023 49.185534,-0.335207 49.185365,-0.335304 49.18521,-0.335244 49.184828,-0.334687 49.183541,-0.334614 49.183232,-0.334723 49.182903,-0.335042 49.182501,-0.335426 49.182185,-0.334914 49.18139,-0.334336 49.18154,-0.333924 49.180556,-0.33423 49.180556,-0.333433 49.179282,-0.333016 49.179351,-0.332609 49.177461,-0.333265 49.177257,-0.333639 49.177157,-0.333986 49.17709,-0.33384 49.176727,-0.331787 49.177315,-0.331223 49.177385,-0.331235 49.176979,-0.331936 49.176823,-0.331739 49.176552,-0.331164 49.17672,-0.330778 49.174702,-0.330723 49.173986,-0.330867 49.174004,-0.330855 49.172347,-0.331116 49.172435,-0.33131 49.167625,-0.331918 49.167564,-0.334021 49.167298,-0.335878 49.167077,-0.336404 49.166992,-0.337252 49.166742,-0.338364 49.166484,-0.339238 49.166258,-0.340235 49.165969,-0.341561 49.165554,-0.341719 49.165471,-0.342581 49.164769,-0.343149 49.164344,-0.342781 49.164141,-0.340218 49.162523,-0.339193 49.161854,-0.339039 49.161659,-0.340669 49.160344,-0.338979 49.159321,-0.337774 49.158528,-0.337291 49.158221,-0.338395 49.157604,-0.339418 49.157044,-0.34047 49.156454,-0.338851 49.155275,-0.338904 49.155201,-0.339124 49.155208,-0.339376 49.155121,-0.340973 49.154184,-0.341555 49.153867,-0.342901 49.153089,-0.343146 49.153021)))"
]

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(((-0.343146 49.153021,-0.344011 49.154516,-0.344181 49.154826,-0.344692 49.155692,-0.347301 49.160279,-0.34893 49.163043,-0.349032 49.163011,-0.351206 49.162423,-0.351183 49.162382,-0.352075 49.162259,-0.351638 49.161252,-0.351978 49.161109,-0.353842 49.160371,-0.354227 49.160278,-0.354771 49.160204,-0.355388 49.160075,-0.355862 49.160012,-0.35592 49.159931,-0.357276 49.159673,-0.358832 49.15942,-0.3614 49.158753,-0.361512 49.158932,-0.361559 49.159144,-0.361737 49.159545,-0.363491 49.159539,-0.365058 49.159653,-0.366113 49.159805,-0.36606 49.159913,-0.367161 49.160029,-0.367039 49.160245,-0.367126 49.160338,-0.367542 49.16042,-0.367154 49.161279,-0.366696 49.16238,-0.366369 49.163237,-0.365393 49.165588,-0.366551 49.165757,-0.367419 49.166573,-0.367734 49.166546,-0.368691 49.166488,-0.369021 49.166643,-0.369394 49.166596,-0.369707 49.166138,-0.369761 49.165831,-0.369993 49.165534,-0.36999 49.165111,-0.370525 49.164931,-0.370799 49.165029,-0.371088 49.165019,-0.371073 49.164813,-0.371237 49.164687,-0.371635 49.164569,-0.372195 49.16437,-0.37207 49.164245,-0.37222 49.163839,-0.372673 49.16391,-0.372823 49.163605,-0.373602 49.16323,-0.374657 49.163297,-0.375154 49.163278,-0.375636 49.163391,-0.37587 49.163396,-0.376201 49.163468,-0.376853 49.163244,-0.377097 49.163145,-0.377393 49.16296,-0.377597 49.162759,-0.377924 49.162747,-0.378455 49.162595,-0.378652 49.162572,-0.378968 49.162597,-0.379375 49.162736,-0.379864 49.162721,-0.380041 49.162913,-0.380275 49.162926,-0.380525 49.1628,-0.381006 49.162718,-0.381154 49.16272,-0.381607 49.162803,-0.38192 49.162749,-0.382432 49.162812,-0.382874 49.162905,-0.383183 49.16292,-0.38365 49.162896,-0.384223 49.162986,-0.384519 49.162983,-0.385044 49.162871,-0.385204 49.162856,-0.385761 49.162861,-0.386686 49.162893,-0.386863 49.162874,-0.386989 49.162917,-0.38719 49.163165,-0.387348 49.163199,-0.38757 49.163161,-0.387746 49.163265,-0.388392 49.163499,-0.388508 49.163568,-0.388629 49.16384,-0.388737 49.163873,-0.389417 49.163893,-0.39016 49.163853,-0.39037 49.163885,-0.390521 49.163865,-0.39071 49.163755,-0.391104 49.163965,-0.391283 49.163985,-0.391479 49.163846,-0.391637 49.163871,-0.391718 49.164039,-0.391825 49.164133,-0.392344 49.164219,-0.392396 49.164333,-0.392202 49.164534,-0.39224 49.164675,-0.392467 49.164717,-0.393401 49.164738,-0.393628 49.164797,-0.393711 49.164874,-0.393734 49.16506,-0.393817 49.165238,-0.394168 49.165488,-0.394195 49.16558,-0.394095 49.165813,-0.394206 49.16592,-0.394488 49.165935,-0.394589 49.166029,-0.394382 49.166159,-0.394341 49.166304,-0.394475 49.166484,-0.393989 49.166479,-0.39469 49.167315,-0.3949 49.167642,-0.394757 49.168203,-0.395434 49.168118,-0.395943 49.168145,-0.396734 49.16824,-0.397261 49.168362,-0.397744 49.168449,-0.398263 49.168501,-0.398764 49.168427,-0.398954 49.168427,-0.399842 49.168505,-0.400501 49.168588,-0.40165 49.168642,-0.401843 49.168619,-0.401886 49.168808,-0.401024 49.168842,-0.400668 49.16884,-0.40121 49.169022,-0.401896 49.169166,-0.402531 49.169331,-0.403311 49.169584,-0.403986 49.169882,-0.404946 49.170359,-0.405729 49.170854,-0.405906 49.171005,-0.405729 49.171154,-0.405681 49.171548,-0.405555 49.171794,-0.405382 49.171992,-0.405203 49.172314,-0.40495 49.172967,-0.404685 49.173178,-0.404534 49.173765,-0.404367 49.174181,-0.404116 49.174567,-0.40379 49.174912,-0.403702 49.175109,-0.40359 49.175746,-0.403432 49.176022,-0.403399 49.176207,-0.403281 49.176537,-0.403245 49.176908,-0.403307 49.177189,-0.40333 49.177451,-0.40444 49.177656,-0.40472 49.177728,-0.405289 49.177978,-0.405641 49.178103,-0.405932 49.178236,-0.406305 49.178545,-0.406746 49.179013,-0.406846 49.179249,-0.407072 49.179685,-0.407946 49.180449,-0.408281 49.180799,-0.408518 49.181075,-0.408889 49.18157,-0.409218 49.181945,-0.409854 49.182559,-0.410279 49.182858,-0.412371 49.184264,-0.413763 49.186887,-0.412616 49.187001,-0.412809 49.188706,-0.412489 49.188556,-0.412156 49.188527,-0.409998 49.188281,-0.40909 49.188192,-0.407886 49.188045,-0.407398 49.187971,-0.407077 49.188342,-0.406746 49.188311,-0.406719 49.188541,-0.407197 49.188564,-0.407227 49.18886,-0.407341 49.189169,-0.407928 49.190529,-0.408202 49.191003,-0.40861 49.191484,-0.40909 49.192025,-0.409805 49.192863,-0.409979 49.193105,-0.410316 49.193419,-0.411494 49.194623,-0.411607 49.194757,-0.410534 49.195615,-0.409145 49.196516,-0.409262 49.196648,-0.408165 49.197343,-0.407975 49.197476,-0.407325 49.197846,-0.405263 49.198991,-0.404332 49.199485,-0.402052 49.200619,-0.401954 49.200659,-0.401183 49.200203,-0.40032 49.199734,-0.39942 49.199281,-0.397454 49.198391,-0.397188 49.198281,-0.396829 49.198203,-0.396746 49.198752,-0.396663 49.199062,-0.396426 49.199662,-0.396236 49.200002,-0.395857 49.200533,-0.395425 49.201011,-0.395305 49.201177,-0.394933 49.201556,-0.394697 49.201752,-0.394144 49.202128,-0.393541 49.202435,-0.393163 49.202584,-0.39252 49.202782,-0.391887 49.202914,-0.391442 49.203031,-0.391186 49.203869,-0.391082 49.204057,-0.390728 49.204403,-0.389685 49.205145,-0.388482 49.206091,-0.388227 49.206312,-0.387557 49.20685,-0.386665 49.207473,-0.386334 49.207729,-0.38605 49.207978,-0.384875 49.20887,-0.383782 49.209603,-0.383167 49.209996,-0.382451 49.210403,-0.381778 49.210736,-0.380578 49.211298,-0.380243 49.211425,-0.379901 49.211504,-0.378835 49.21172,-0.379058 49.211978,-0.378217 49.212597,-0.377878 49.213425,-0.377715 49.213653,-0.377597 49.213704,-0.377069 49.213713,-0.376573 49.213666,-0.376292 49.213588,-0.376093 49.213423,-0.375643 49.21363,-0.374589 49.214191,-0.373625 49.214734,-0.372699 49.215311,-0.3723 49.215487,-0.371873 49.215613,-0.370692 49.215999,-0.370054 49.216261,-0.368957 49.215884,-0.365025 49.214884,-0.364905 49.2146,-0.364524 49.214546,-0.363568 49.214325,-0.3637 49.214146,-0.363592 49.214071,-0.363135 49.213873,-0.3625 49.213639,-0.361502 49.213233,-0.361219 49.213134,-0.360518 49.212838,-0.360234 49.212734,-0.360181 49.212784,-0.359919 49.212664,-0.359617 49.212593,-0.358595 49.212293,-0.357978 49.212082,-0.357423 49.211829,-0.356762 49.211478,-0.356263 49.211078,-0.355031 49.209813,-0.355728 49.208292,-0.355767 49.208181,-0.353922 49.207679,-0.353608 49.208073,-0.353316 49.208483,-0.351284 49.207635,-0.352076 49.20696,-0.351265 49.206518,-0.350551 49.206207,-0.350812 49.205853,-0.35085 49.205665,-0.350667 49.205545,-0.351045 49.205164,-0.350122 49.20522,-0.349938 49.205186,-0.348881 49.204909,-0.349198 49.204606,-0.347589 49.203523,-0.348851 49.202544,-0.349741 49.201869,-0.350068 49.201654,-0.350394 49.20154,-0.349134 49.20117,-0.348494 49.200964,-0.347042 49.200447,-0.346374 49.200197,-0.344619 49.199462,-0.343931 49.19915,-0.343267 49.198833,-0.342701 49.198549,-0.341533 49.197911,-0.340977 49.19757,-0.340484 49.197241,-0.339456 49.19649,-0.338403 49.195574,-0.337975 49.195154,-0.336097 49.193225,-0.335923 49.193036,-0.336862 49.192373,-0.337407 49.192164,-0.337719 49.191952,-0.338017 49.191699,-0.337283 49.190903,-0.336465 49.190027,-0.336125 49.189505,-0.336029 49.189234,-0.335885 49.188975,-0.335359 49.188303,-0.334856 49.187763,-0.334714 49.187643,-0.333644 49.185865,-0.333629 49.185828,-0.334197 49.185776,-0.334756 49.185657,-0.335023 49.185534,-0.335207 49.185365,-0.335304 49.18521,-0.335244 49.184828,-0.334687 49.183541,-0.334614 49.183232,-0.334723 49.182903,-0.335042 49.182501,-0.335426 49.182185,-0.334914 49.18139,-0.334336 49.18154,-0.333924 49.180556,-0.33423 49.180556,-0.333433 49.179282,-0.333016 49.179351,-0.332609 49.177461,-0.333265 49.177257,-0.333639 49.177157,-0.333986 49.17709,-0.33384 49.176727,-0.331787 49.177315,-0.331223 49.177385,-0.331235 49.176979,-0.331936 49.176823,-0.331739 49.176552,-0.331164 49.17672,-0.330778 49.174702,-0.330723 49.173986,-0.330867 49.174004,-0.330855 49.172347,-0.331116 49.172435,-0.33131 49.167625,-0.331918 49.167564,-0.334021 49.167298,-0.335878 49.167077,-0.336404 49.166992,-0.337252 49.166742,-0.338364 49.166484,-0.339238 49.166258,-0.340235 49.165969,-0.341561 49.165554,-0.341719 49.165471,-0.342581 49.164769,-0.343149 49.164344,-0.342781 49.164141,-0.340218 49.162523,-0.339193 49.161854,-0.339039 49.161659,-0.340669 49.160344,-0.338979 49.159321,-0.337774 49.158528,-0.337291 49.158221,-0.338395 49.157604,-0.339418 49.157044,-0.34047 49.156454,-0.338851 49.155275,-0.338904 49.155201,-0.339124 49.155208,-0.339376 49.155121,-0.340973 49.154184,-0.341555 49.153867,-0.342901 49.153089,-0.343146 49.153021)))'), 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)

2.52 ms
(0.49%)

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.25 ms
(0.44%)

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)

2.24 ms
(0.44%)

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(((-0.343146 49.153021,-0.344011 49.154516,-0.344181 49.154826,-0.344692 49.155692,-0.347301 49.160279,-0.34893 49.163043,-0.349032 49.163011,-0.351206 49.162423,-0.351183 49.162382,-0.352075 49.162259,-0.351638 49.161252,-0.351978 49.161109,-0.353842 49.160371,-0.354227 49.160278,-0.354771 49.160204,-0.355388 49.160075,-0.355862 49.160012,-0.35592 49.159931,-0.357276 49.159673,-0.358832 49.15942,-0.3614 49.158753,-0.361512 49.158932,-0.361559 49.159144,-0.361737 49.159545,-0.363491 49.159539,-0.365058 49.159653,-0.366113 49.159805,-0.36606 49.159913,-0.367161 49.160029,-0.367039 49.160245,-0.367126 49.160338,-0.367542 49.16042,-0.367154 49.161279,-0.366696 49.16238,-0.366369 49.163237,-0.365393 49.165588,-0.366551 49.165757,-0.367419 49.166573,-0.367734 49.166546,-0.368691 49.166488,-0.369021 49.166643,-0.369394 49.166596,-0.369707 49.166138,-0.369761 49.165831,-0.369993 49.165534,-0.36999 49.165111,-0.370525 49.164931,-0.370799 49.165029,-0.371088 49.165019,-0.371073 49.164813,-0.371237 49.164687,-0.371635 49.164569,-0.372195 49.16437,-0.37207 49.164245,-0.37222 49.163839,-0.372673 49.16391,-0.372823 49.163605,-0.373602 49.16323,-0.374657 49.163297,-0.375154 49.163278,-0.375636 49.163391,-0.37587 49.163396,-0.376201 49.163468,-0.376853 49.163244,-0.377097 49.163145,-0.377393 49.16296,-0.377597 49.162759,-0.377924 49.162747,-0.378455 49.162595,-0.378652 49.162572,-0.378968 49.162597,-0.379375 49.162736,-0.379864 49.162721,-0.380041 49.162913,-0.380275 49.162926,-0.380525 49.1628,-0.381006 49.162718,-0.381154 49.16272,-0.381607 49.162803,-0.38192 49.162749,-0.382432 49.162812,-0.382874 49.162905,-0.383183 49.16292,-0.38365 49.162896,-0.384223 49.162986,-0.384519 49.162983,-0.385044 49.162871,-0.385204 49.162856,-0.385761 49.162861,-0.386686 49.162893,-0.386863 49.162874,-0.386989 49.162917,-0.38719 49.163165,-0.387348 49.163199,-0.38757 49.163161,-0.387746 49.163265,-0.388392 49.163499,-0.388508 49.163568,-0.388629 49.16384,-0.388737 49.163873,-0.389417 49.163893,-0.39016 49.163853,-0.39037 49.163885,-0.390521 49.163865,-0.39071 49.163755,-0.391104 49.163965,-0.391283 49.163985,-0.391479 49.163846,-0.391637 49.163871,-0.391718 49.164039,-0.391825 49.164133,-0.392344 49.164219,-0.392396 49.164333,-0.392202 49.164534,-0.39224 49.164675,-0.392467 49.164717,-0.393401 49.164738,-0.393628 49.164797,-0.393711 49.164874,-0.393734 49.16506,-0.393817 49.165238,-0.394168 49.165488,-0.394195 49.16558,-0.394095 49.165813,-0.394206 49.16592,-0.394488 49.165935,-0.394589 49.166029,-0.394382 49.166159,-0.394341 49.166304,-0.394475 49.166484,-0.393989 49.166479,-0.39469 49.167315,-0.3949 49.167642,-0.394757 49.168203,-0.395434 49.168118,-0.395943 49.168145,-0.396734 49.16824,-0.397261 49.168362,-0.397744 49.168449,-0.398263 49.168501,-0.398764 49.168427,-0.398954 49.168427,-0.399842 49.168505,-0.400501 49.168588,-0.40165 49.168642,-0.401843 49.168619,-0.401886 49.168808,-0.401024 49.168842,-0.400668 49.16884,-0.40121 49.169022,-0.401896 49.169166,-0.402531 49.169331,-0.403311 49.169584,-0.403986 49.169882,-0.404946 49.170359,-0.405729 49.170854,-0.405906 49.171005,-0.405729 49.171154,-0.405681 49.171548,-0.405555 49.171794,-0.405382 49.171992,-0.405203 49.172314,-0.40495 49.172967,-0.404685 49.173178,-0.404534 49.173765,-0.404367 49.174181,-0.404116 49.174567,-0.40379 49.174912,-0.403702 49.175109,-0.40359 49.175746,-0.403432 49.176022,-0.403399 49.176207,-0.403281 49.176537,-0.403245 49.176908,-0.403307 49.177189,-0.40333 49.177451,-0.40444 49.177656,-0.40472 49.177728,-0.405289 49.177978,-0.405641 49.178103,-0.405932 49.178236,-0.406305 49.178545,-0.406746 49.179013,-0.406846 49.179249,-0.407072 49.179685,-0.407946 49.180449,-0.408281 49.180799,-0.408518 49.181075,-0.408889 49.18157,-0.409218 49.181945,-0.409854 49.182559,-0.410279 49.182858,-0.412371 49.184264,-0.413763 49.186887,-0.412616 49.187001,-0.412809 49.188706,-0.412489 49.188556,-0.412156 49.188527,-0.409998 49.188281,-0.40909 49.188192,-0.407886 49.188045,-0.407398 49.187971,-0.407077 49.188342,-0.406746 49.188311,-0.406719 49.188541,-0.407197 49.188564,-0.407227 49.18886,-0.407341 49.189169,-0.407928 49.190529,-0.408202 49.191003,-0.40861 49.191484,-0.40909 49.192025,-0.409805 49.192863,-0.409979 49.193105,-0.410316 49.193419,-0.411494 49.194623,-0.411607 49.194757,-0.410534 49.195615,-0.409145 49.196516,-0.409262 49.196648,-0.408165 49.197343,-0.407975 49.197476,-0.407325 49.197846,-0.405263 49.198991,-0.404332 49.199485,-0.402052 49.200619,-0.401954 49.200659,-0.401183 49.200203,-0.40032 49.199734,-0.39942 49.199281,-0.397454 49.198391,-0.397188 49.198281,-0.396829 49.198203,-0.396746 49.198752,-0.396663 49.199062,-0.396426 49.199662,-0.396236 49.200002,-0.395857 49.200533,-0.395425 49.201011,-0.395305 49.201177,-0.394933 49.201556,-0.394697 49.201752,-0.394144 49.202128,-0.393541 49.202435,-0.393163 49.202584,-0.39252 49.202782,-0.391887 49.202914,-0.391442 49.203031,-0.391186 49.203869,-0.391082 49.204057,-0.390728 49.204403,-0.389685 49.205145,-0.388482 49.206091,-0.388227 49.206312,-0.387557 49.20685,-0.386665 49.207473,-0.386334 49.207729,-0.38605 49.207978,-0.384875 49.20887,-0.383782 49.209603,-0.383167 49.209996,-0.382451 49.210403,-0.381778 49.210736,-0.380578 49.211298,-0.380243 49.211425,-0.379901 49.211504,-0.378835 49.21172,-0.379058 49.211978,-0.378217 49.212597,-0.377878 49.213425,-0.377715 49.213653,-0.377597 49.213704,-0.377069 49.213713,-0.376573 49.213666,-0.376292 49.213588,-0.376093 49.213423,-0.375643 49.21363,-0.374589 49.214191,-0.373625 49.214734,-0.372699 49.215311,-0.3723 49.215487,-0.371873 49.215613,-0.370692 49.215999,-0.370054 49.216261,-0.368957 49.215884,-0.365025 49.214884,-0.364905 49.2146,-0.364524 49.214546,-0.363568 49.214325,-0.3637 49.214146,-0.363592 49.214071,-0.363135 49.213873,-0.3625 49.213639,-0.361502 49.213233,-0.361219 49.213134,-0.360518 49.212838,-0.360234 49.212734,-0.360181 49.212784,-0.359919 49.212664,-0.359617 49.212593,-0.358595 49.212293,-0.357978 49.212082,-0.357423 49.211829,-0.356762 49.211478,-0.356263 49.211078,-0.355031 49.209813,-0.355728 49.208292,-0.355767 49.208181,-0.353922 49.207679,-0.353608 49.208073,-0.353316 49.208483,-0.351284 49.207635,-0.352076 49.20696,-0.351265 49.206518,-0.350551 49.206207,-0.350812 49.205853,-0.35085 49.205665,-0.350667 49.205545,-0.351045 49.205164,-0.350122 49.20522,-0.349938 49.205186,-0.348881 49.204909,-0.349198 49.204606,-0.347589 49.203523,-0.348851 49.202544,-0.349741 49.201869,-0.350068 49.201654,-0.350394 49.20154,-0.349134 49.20117,-0.348494 49.200964,-0.347042 49.200447,-0.346374 49.200197,-0.344619 49.199462,-0.343931 49.19915,-0.343267 49.198833,-0.342701 49.198549,-0.341533 49.197911,-0.340977 49.19757,-0.340484 49.197241,-0.339456 49.19649,-0.338403 49.195574,-0.337975 49.195154,-0.336097 49.193225,-0.335923 49.193036,-0.336862 49.192373,-0.337407 49.192164,-0.337719 49.191952,-0.338017 49.191699,-0.337283 49.190903,-0.336465 49.190027,-0.336125 49.189505,-0.336029 49.189234,-0.335885 49.188975,-0.335359 49.188303,-0.334856 49.187763,-0.334714 49.187643,-0.333644 49.185865,-0.333629 49.185828,-0.334197 49.185776,-0.334756 49.185657,-0.335023 49.185534,-0.335207 49.185365,-0.335304 49.18521,-0.335244 49.184828,-0.334687 49.183541,-0.334614 49.183232,-0.334723 49.182903,-0.335042 49.182501,-0.335426 49.182185,-0.334914 49.18139,-0.334336 49.18154,-0.333924 49.180556,-0.33423 49.180556,-0.333433 49.179282,-0.333016 49.179351,-0.332609 49.177461,-0.333265 49.177257,-0.333639 49.177157,-0.333986 49.17709,-0.33384 49.176727,-0.331787 49.177315,-0.331223 49.177385,-0.331235 49.176979,-0.331936 49.176823,-0.331739 49.176552,-0.331164 49.17672,-0.330778 49.174702,-0.330723 49.173986,-0.330867 49.174004,-0.330855 49.172347,-0.331116 49.172435,-0.33131 49.167625,-0.331918 49.167564,-0.334021 49.167298,-0.335878 49.167077,-0.336404 49.166992,-0.337252 49.166742,-0.338364 49.166484,-0.339238 49.166258,-0.340235 49.165969,-0.341561 49.165554,-0.341719 49.165471,-0.342581 49.164769,-0.343149 49.164344,-0.342781 49.164141,-0.340218 49.162523,-0.339193 49.161854,-0.339039 49.161659,-0.340669 49.160344,-0.338979 49.159321,-0.337774 49.158528,-0.337291 49.158221,-0.338395 49.157604,-0.339418 49.157044,-0.34047 49.156454,-0.338851 49.155275,-0.338904 49.155201,-0.339124 49.155208,-0.339376 49.155121,-0.340973 49.154184,-0.341555 49.153867,-0.342901 49.153089,-0.343146 49.153021)))"
  "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(((-0.343146 49.153021,-0.344011 49.154516,-0.344181 49.154826,-0.344692 49.155692,-0.347301 49.160279,-0.34893 49.163043,-0.349032 49.163011,-0.351206 49.162423,-0.351183 49.162382,-0.352075 49.162259,-0.351638 49.161252,-0.351978 49.161109,-0.353842 49.160371,-0.354227 49.160278,-0.354771 49.160204,-0.355388 49.160075,-0.355862 49.160012,-0.35592 49.159931,-0.357276 49.159673,-0.358832 49.15942,-0.3614 49.158753,-0.361512 49.158932,-0.361559 49.159144,-0.361737 49.159545,-0.363491 49.159539,-0.365058 49.159653,-0.366113 49.159805,-0.36606 49.159913,-0.367161 49.160029,-0.367039 49.160245,-0.367126 49.160338,-0.367542 49.16042,-0.367154 49.161279,-0.366696 49.16238,-0.366369 49.163237,-0.365393 49.165588,-0.366551 49.165757,-0.367419 49.166573,-0.367734 49.166546,-0.368691 49.166488,-0.369021 49.166643,-0.369394 49.166596,-0.369707 49.166138,-0.369761 49.165831,-0.369993 49.165534,-0.36999 49.165111,-0.370525 49.164931,-0.370799 49.165029,-0.371088 49.165019,-0.371073 49.164813,-0.371237 49.164687,-0.371635 49.164569,-0.372195 49.16437,-0.37207 49.164245,-0.37222 49.163839,-0.372673 49.16391,-0.372823 49.163605,-0.373602 49.16323,-0.374657 49.163297,-0.375154 49.163278,-0.375636 49.163391,-0.37587 49.163396,-0.376201 49.163468,-0.376853 49.163244,-0.377097 49.163145,-0.377393 49.16296,-0.377597 49.162759,-0.377924 49.162747,-0.378455 49.162595,-0.378652 49.162572,-0.378968 49.162597,-0.379375 49.162736,-0.379864 49.162721,-0.380041 49.162913,-0.380275 49.162926,-0.380525 49.1628,-0.381006 49.162718,-0.381154 49.16272,-0.381607 49.162803,-0.38192 49.162749,-0.382432 49.162812,-0.382874 49.162905,-0.383183 49.16292,-0.38365 49.162896,-0.384223 49.162986,-0.384519 49.162983,-0.385044 49.162871,-0.385204 49.162856,-0.385761 49.162861,-0.386686 49.162893,-0.386863 49.162874,-0.386989 49.162917,-0.38719 49.163165,-0.387348 49.163199,-0.38757 49.163161,-0.387746 49.163265,-0.388392 49.163499,-0.388508 49.163568,-0.388629 49.16384,-0.388737 49.163873,-0.389417 49.163893,-0.39016 49.163853,-0.39037 49.163885,-0.390521 49.163865,-0.39071 49.163755,-0.391104 49.163965,-0.391283 49.163985,-0.391479 49.163846,-0.391637 49.163871,-0.391718 49.164039,-0.391825 49.164133,-0.392344 49.164219,-0.392396 49.164333,-0.392202 49.164534,-0.39224 49.164675,-0.392467 49.164717,-0.393401 49.164738,-0.393628 49.164797,-0.393711 49.164874,-0.393734 49.16506,-0.393817 49.165238,-0.394168 49.165488,-0.394195 49.16558,-0.394095 49.165813,-0.394206 49.16592,-0.394488 49.165935,-0.394589 49.166029,-0.394382 49.166159,-0.394341 49.166304,-0.394475 49.166484,-0.393989 49.166479,-0.39469 49.167315,-0.3949 49.167642,-0.394757 49.168203,-0.395434 49.168118,-0.395943 49.168145,-0.396734 49.16824,-0.397261 49.168362,-0.397744 49.168449,-0.398263 49.168501,-0.398764 49.168427,-0.398954 49.168427,-0.399842 49.168505,-0.400501 49.168588,-0.40165 49.168642,-0.401843 49.168619,-0.401886 49.168808,-0.401024 49.168842,-0.400668 49.16884,-0.40121 49.169022,-0.401896 49.169166,-0.402531 49.169331,-0.403311 49.169584,-0.403986 49.169882,-0.404946 49.170359,-0.405729 49.170854,-0.405906 49.171005,-0.405729 49.171154,-0.405681 49.171548,-0.405555 49.171794,-0.405382 49.171992,-0.405203 49.172314,-0.40495 49.172967,-0.404685 49.173178,-0.404534 49.173765,-0.404367 49.174181,-0.404116 49.174567,-0.40379 49.174912,-0.403702 49.175109,-0.40359 49.175746,-0.403432 49.176022,-0.403399 49.176207,-0.403281 49.176537,-0.403245 49.176908,-0.403307 49.177189,-0.40333 49.177451,-0.40444 49.177656,-0.40472 49.177728,-0.405289 49.177978,-0.405641 49.178103,-0.405932 49.178236,-0.406305 49.178545,-0.406746 49.179013,-0.406846 49.179249,-0.407072 49.179685,-0.407946 49.180449,-0.408281 49.180799,-0.408518 49.181075,-0.408889 49.18157,-0.409218 49.181945,-0.409854 49.182559,-0.410279 49.182858,-0.412371 49.184264,-0.413763 49.186887,-0.412616 49.187001,-0.412809 49.188706,-0.412489 49.188556,-0.412156 49.188527,-0.409998 49.188281,-0.40909 49.188192,-0.407886 49.188045,-0.407398 49.187971,-0.407077 49.188342,-0.406746 49.188311,-0.406719 49.188541,-0.407197 49.188564,-0.407227 49.18886,-0.407341 49.189169,-0.407928 49.190529,-0.408202 49.191003,-0.40861 49.191484,-0.40909 49.192025,-0.409805 49.192863,-0.409979 49.193105,-0.410316 49.193419,-0.411494 49.194623,-0.411607 49.194757,-0.410534 49.195615,-0.409145 49.196516,-0.409262 49.196648,-0.408165 49.197343,-0.407975 49.197476,-0.407325 49.197846,-0.405263 49.198991,-0.404332 49.199485,-0.402052 49.200619,-0.401954 49.200659,-0.401183 49.200203,-0.40032 49.199734,-0.39942 49.199281,-0.397454 49.198391,-0.397188 49.198281,-0.396829 49.198203,-0.396746 49.198752,-0.396663 49.199062,-0.396426 49.199662,-0.396236 49.200002,-0.395857 49.200533,-0.395425 49.201011,-0.395305 49.201177,-0.394933 49.201556,-0.394697 49.201752,-0.394144 49.202128,-0.393541 49.202435,-0.393163 49.202584,-0.39252 49.202782,-0.391887 49.202914,-0.391442 49.203031,-0.391186 49.203869,-0.391082 49.204057,-0.390728 49.204403,-0.389685 49.205145,-0.388482 49.206091,-0.388227 49.206312,-0.387557 49.20685,-0.386665 49.207473,-0.386334 49.207729,-0.38605 49.207978,-0.384875 49.20887,-0.383782 49.209603,-0.383167 49.209996,-0.382451 49.210403,-0.381778 49.210736,-0.380578 49.211298,-0.380243 49.211425,-0.379901 49.211504,-0.378835 49.21172,-0.379058 49.211978,-0.378217 49.212597,-0.377878 49.213425,-0.377715 49.213653,-0.377597 49.213704,-0.377069 49.213713,-0.376573 49.213666,-0.376292 49.213588,-0.376093 49.213423,-0.375643 49.21363,-0.374589 49.214191,-0.373625 49.214734,-0.372699 49.215311,-0.3723 49.215487,-0.371873 49.215613,-0.370692 49.215999,-0.370054 49.216261,-0.368957 49.215884,-0.365025 49.214884,-0.364905 49.2146,-0.364524 49.214546,-0.363568 49.214325,-0.3637 49.214146,-0.363592 49.214071,-0.363135 49.213873,-0.3625 49.213639,-0.361502 49.213233,-0.361219 49.213134,-0.360518 49.212838,-0.360234 49.212734,-0.360181 49.212784,-0.359919 49.212664,-0.359617 49.212593,-0.358595 49.212293,-0.357978 49.212082,-0.357423 49.211829,-0.356762 49.211478,-0.356263 49.211078,-0.355031 49.209813,-0.355728 49.208292,-0.355767 49.208181,-0.353922 49.207679,-0.353608 49.208073,-0.353316 49.208483,-0.351284 49.207635,-0.352076 49.20696,-0.351265 49.206518,-0.350551 49.206207,-0.350812 49.205853,-0.35085 49.205665,-0.350667 49.205545,-0.351045 49.205164,-0.350122 49.20522,-0.349938 49.205186,-0.348881 49.204909,-0.349198 49.204606,-0.347589 49.203523,-0.348851 49.202544,-0.349741 49.201869,-0.350068 49.201654,-0.350394 49.20154,-0.349134 49.20117,-0.348494 49.200964,-0.347042 49.200447,-0.346374 49.200197,-0.344619 49.199462,-0.343931 49.19915,-0.343267 49.198833,-0.342701 49.198549,-0.341533 49.197911,-0.340977 49.19757,-0.340484 49.197241,-0.339456 49.19649,-0.338403 49.195574,-0.337975 49.195154,-0.336097 49.193225,-0.335923 49.193036,-0.336862 49.192373,-0.337407 49.192164,-0.337719 49.191952,-0.338017 49.191699,-0.337283 49.190903,-0.336465 49.190027,-0.336125 49.189505,-0.336029 49.189234,-0.335885 49.188975,-0.335359 49.188303,-0.334856 49.187763,-0.334714 49.187643,-0.333644 49.185865,-0.333629 49.185828,-0.334197 49.185776,-0.334756 49.185657,-0.335023 49.185534,-0.335207 49.185365,-0.335304 49.18521,-0.335244 49.184828,-0.334687 49.183541,-0.334614 49.183232,-0.334723 49.182903,-0.335042 49.182501,-0.335426 49.182185,-0.334914 49.18139,-0.334336 49.18154,-0.333924 49.180556,-0.33423 49.180556,-0.333433 49.179282,-0.333016 49.179351,-0.332609 49.177461,-0.333265 49.177257,-0.333639 49.177157,-0.333986 49.17709,-0.33384 49.176727,-0.331787 49.177315,-0.331223 49.177385,-0.331235 49.176979,-0.331936 49.176823,-0.331739 49.176552,-0.331164 49.17672,-0.330778 49.174702,-0.330723 49.173986,-0.330867 49.174004,-0.330855 49.172347,-0.331116 49.172435,-0.33131 49.167625,-0.331918 49.167564,-0.334021 49.167298,-0.335878 49.167077,-0.336404 49.166992,-0.337252 49.166742,-0.338364 49.166484,-0.339238 49.166258,-0.340235 49.165969,-0.341561 49.165554,-0.341719 49.165471,-0.342581 49.164769,-0.343149 49.164344,-0.342781 49.164141,-0.340218 49.162523,-0.339193 49.161854,-0.339039 49.161659,-0.340669 49.160344,-0.338979 49.159321,-0.337774 49.158528,-0.337291 49.158221,-0.338395 49.157604,-0.339418 49.157044,-0.34047 49.156454,-0.338851 49.155275,-0.338904 49.155201,-0.339124 49.155208,-0.339376 49.155121,-0.340973 49.154184,-0.341555 49.153867,-0.342901 49.153089,-0.343146 49.153021)))', 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)

2.05 ms
(0.40%)

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)

1.97 ms
(0.39%)

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)

1.56 ms
(0.31%)

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(((-0.343146 49.153021,-0.344011 49.154516,-0.344181 49.154826,-0.344692 49.155692,-0.347301 49.160279,-0.34893 49.163043,-0.349032 49.163011,-0.351206 49.162423,-0.351183 49.162382,-0.352075 49.162259,-0.351638 49.161252,-0.351978 49.161109,-0.353842 49.160371,-0.354227 49.160278,-0.354771 49.160204,-0.355388 49.160075,-0.355862 49.160012,-0.35592 49.159931,-0.357276 49.159673,-0.358832 49.15942,-0.3614 49.158753,-0.361512 49.158932,-0.361559 49.159144,-0.361737 49.159545,-0.363491 49.159539,-0.365058 49.159653,-0.366113 49.159805,-0.36606 49.159913,-0.367161 49.160029,-0.367039 49.160245,-0.367126 49.160338,-0.367542 49.16042,-0.367154 49.161279,-0.366696 49.16238,-0.366369 49.163237,-0.365393 49.165588,-0.366551 49.165757,-0.367419 49.166573,-0.367734 49.166546,-0.368691 49.166488,-0.369021 49.166643,-0.369394 49.166596,-0.369707 49.166138,-0.369761 49.165831,-0.369993 49.165534,-0.36999 49.165111,-0.370525 49.164931,-0.370799 49.165029,-0.371088 49.165019,-0.371073 49.164813,-0.371237 49.164687,-0.371635 49.164569,-0.372195 49.16437,-0.37207 49.164245,-0.37222 49.163839,-0.372673 49.16391,-0.372823 49.163605,-0.373602 49.16323,-0.374657 49.163297,-0.375154 49.163278,-0.375636 49.163391,-0.37587 49.163396,-0.376201 49.163468,-0.376853 49.163244,-0.377097 49.163145,-0.377393 49.16296,-0.377597 49.162759,-0.377924 49.162747,-0.378455 49.162595,-0.378652 49.162572,-0.378968 49.162597,-0.379375 49.162736,-0.379864 49.162721,-0.380041 49.162913,-0.380275 49.162926,-0.380525 49.1628,-0.381006 49.162718,-0.381154 49.16272,-0.381607 49.162803,-0.38192 49.162749,-0.382432 49.162812,-0.382874 49.162905,-0.383183 49.16292,-0.38365 49.162896,-0.384223 49.162986,-0.384519 49.162983,-0.385044 49.162871,-0.385204 49.162856,-0.385761 49.162861,-0.386686 49.162893,-0.386863 49.162874,-0.386989 49.162917,-0.38719 49.163165,-0.387348 49.163199,-0.38757 49.163161,-0.387746 49.163265,-0.388392 49.163499,-0.388508 49.163568,-0.388629 49.16384,-0.388737 49.163873,-0.389417 49.163893,-0.39016 49.163853,-0.39037 49.163885,-0.390521 49.163865,-0.39071 49.163755,-0.391104 49.163965,-0.391283 49.163985,-0.391479 49.163846,-0.391637 49.163871,-0.391718 49.164039,-0.391825 49.164133,-0.392344 49.164219,-0.392396 49.164333,-0.392202 49.164534,-0.39224 49.164675,-0.392467 49.164717,-0.393401 49.164738,-0.393628 49.164797,-0.393711 49.164874,-0.393734 49.16506,-0.393817 49.165238,-0.394168 49.165488,-0.394195 49.16558,-0.394095 49.165813,-0.394206 49.16592,-0.394488 49.165935,-0.394589 49.166029,-0.394382 49.166159,-0.394341 49.166304,-0.394475 49.166484,-0.393989 49.166479,-0.39469 49.167315,-0.3949 49.167642,-0.394757 49.168203,-0.395434 49.168118,-0.395943 49.168145,-0.396734 49.16824,-0.397261 49.168362,-0.397744 49.168449,-0.398263 49.168501,-0.398764 49.168427,-0.398954 49.168427,-0.399842 49.168505,-0.400501 49.168588,-0.40165 49.168642,-0.401843 49.168619,-0.401886 49.168808,-0.401024 49.168842,-0.400668 49.16884,-0.40121 49.169022,-0.401896 49.169166,-0.402531 49.169331,-0.403311 49.169584,-0.403986 49.169882,-0.404946 49.170359,-0.405729 49.170854,-0.405906 49.171005,-0.405729 49.171154,-0.405681 49.171548,-0.405555 49.171794,-0.405382 49.171992,-0.405203 49.172314,-0.40495 49.172967,-0.404685 49.173178,-0.404534 49.173765,-0.404367 49.174181,-0.404116 49.174567,-0.40379 49.174912,-0.403702 49.175109,-0.40359 49.175746,-0.403432 49.176022,-0.403399 49.176207,-0.403281 49.176537,-0.403245 49.176908,-0.403307 49.177189,-0.40333 49.177451,-0.40444 49.177656,-0.40472 49.177728,-0.405289 49.177978,-0.405641 49.178103,-0.405932 49.178236,-0.406305 49.178545,-0.406746 49.179013,-0.406846 49.179249,-0.407072 49.179685,-0.407946 49.180449,-0.408281 49.180799,-0.408518 49.181075,-0.408889 49.18157,-0.409218 49.181945,-0.409854 49.182559,-0.410279 49.182858,-0.412371 49.184264,-0.413763 49.186887,-0.412616 49.187001,-0.412809 49.188706,-0.412489 49.188556,-0.412156 49.188527,-0.409998 49.188281,-0.40909 49.188192,-0.407886 49.188045,-0.407398 49.187971,-0.407077 49.188342,-0.406746 49.188311,-0.406719 49.188541,-0.407197 49.188564,-0.407227 49.18886,-0.407341 49.189169,-0.407928 49.190529,-0.408202 49.191003,-0.40861 49.191484,-0.40909 49.192025,-0.409805 49.192863,-0.409979 49.193105,-0.410316 49.193419,-0.411494 49.194623,-0.411607 49.194757,-0.410534 49.195615,-0.409145 49.196516,-0.409262 49.196648,-0.408165 49.197343,-0.407975 49.197476,-0.407325 49.197846,-0.405263 49.198991,-0.404332 49.199485,-0.402052 49.200619,-0.401954 49.200659,-0.401183 49.200203,-0.40032 49.199734,-0.39942 49.199281,-0.397454 49.198391,-0.397188 49.198281,-0.396829 49.198203,-0.396746 49.198752,-0.396663 49.199062,-0.396426 49.199662,-0.396236 49.200002,-0.395857 49.200533,-0.395425 49.201011,-0.395305 49.201177,-0.394933 49.201556,-0.394697 49.201752,-0.394144 49.202128,-0.393541 49.202435,-0.393163 49.202584,-0.39252 49.202782,-0.391887 49.202914,-0.391442 49.203031,-0.391186 49.203869,-0.391082 49.204057,-0.390728 49.204403,-0.389685 49.205145,-0.388482 49.206091,-0.388227 49.206312,-0.387557 49.20685,-0.386665 49.207473,-0.386334 49.207729,-0.38605 49.207978,-0.384875 49.20887,-0.383782 49.209603,-0.383167 49.209996,-0.382451 49.210403,-0.381778 49.210736,-0.380578 49.211298,-0.380243 49.211425,-0.379901 49.211504,-0.378835 49.21172,-0.379058 49.211978,-0.378217 49.212597,-0.377878 49.213425,-0.377715 49.213653,-0.377597 49.213704,-0.377069 49.213713,-0.376573 49.213666,-0.376292 49.213588,-0.376093 49.213423,-0.375643 49.21363,-0.374589 49.214191,-0.373625 49.214734,-0.372699 49.215311,-0.3723 49.215487,-0.371873 49.215613,-0.370692 49.215999,-0.370054 49.216261,-0.368957 49.215884,-0.365025 49.214884,-0.364905 49.2146,-0.364524 49.214546,-0.363568 49.214325,-0.3637 49.214146,-0.363592 49.214071,-0.363135 49.213873,-0.3625 49.213639,-0.361502 49.213233,-0.361219 49.213134,-0.360518 49.212838,-0.360234 49.212734,-0.360181 49.212784,-0.359919 49.212664,-0.359617 49.212593,-0.358595 49.212293,-0.357978 49.212082,-0.357423 49.211829,-0.356762 49.211478,-0.356263 49.211078,-0.355031 49.209813,-0.355728 49.208292,-0.355767 49.208181,-0.353922 49.207679,-0.353608 49.208073,-0.353316 49.208483,-0.351284 49.207635,-0.352076 49.20696,-0.351265 49.206518,-0.350551 49.206207,-0.350812 49.205853,-0.35085 49.205665,-0.350667 49.205545,-0.351045 49.205164,-0.350122 49.20522,-0.349938 49.205186,-0.348881 49.204909,-0.349198 49.204606,-0.347589 49.203523,-0.348851 49.202544,-0.349741 49.201869,-0.350068 49.201654,-0.350394 49.20154,-0.349134 49.20117,-0.348494 49.200964,-0.347042 49.200447,-0.346374 49.200197,-0.344619 49.199462,-0.343931 49.19915,-0.343267 49.198833,-0.342701 49.198549,-0.341533 49.197911,-0.340977 49.19757,-0.340484 49.197241,-0.339456 49.19649,-0.338403 49.195574,-0.337975 49.195154,-0.336097 49.193225,-0.335923 49.193036,-0.336862 49.192373,-0.337407 49.192164,-0.337719 49.191952,-0.338017 49.191699,-0.337283 49.190903,-0.336465 49.190027,-0.336125 49.189505,-0.336029 49.189234,-0.335885 49.188975,-0.335359 49.188303,-0.334856 49.187763,-0.334714 49.187643,-0.333644 49.185865,-0.333629 49.185828,-0.334197 49.185776,-0.334756 49.185657,-0.335023 49.185534,-0.335207 49.185365,-0.335304 49.18521,-0.335244 49.184828,-0.334687 49.183541,-0.334614 49.183232,-0.334723 49.182903,-0.335042 49.182501,-0.335426 49.182185,-0.334914 49.18139,-0.334336 49.18154,-0.333924 49.180556,-0.33423 49.180556,-0.333433 49.179282,-0.333016 49.179351,-0.332609 49.177461,-0.333265 49.177257,-0.333639 49.177157,-0.333986 49.17709,-0.33384 49.176727,-0.331787 49.177315,-0.331223 49.177385,-0.331235 49.176979,-0.331936 49.176823,-0.331739 49.176552,-0.331164 49.17672,-0.330778 49.174702,-0.330723 49.173986,-0.330867 49.174004,-0.330855 49.172347,-0.331116 49.172435,-0.33131 49.167625,-0.331918 49.167564,-0.334021 49.167298,-0.335878 49.167077,-0.336404 49.166992,-0.337252 49.166742,-0.338364 49.166484,-0.339238 49.166258,-0.340235 49.165969,-0.341561 49.165554,-0.341719 49.165471,-0.342581 49.164769,-0.343149 49.164344,-0.342781 49.164141,-0.340218 49.162523,-0.339193 49.161854,-0.339039 49.161659,-0.340669 49.160344,-0.338979 49.159321,-0.337774 49.158528,-0.337291 49.158221,-0.338395 49.157604,-0.339418 49.157044,-0.34047 49.156454,-0.338851 49.155275,-0.338904 49.155201,-0.339124 49.155208,-0.339376 49.155121,-0.340973 49.154184,-0.341555 49.153867,-0.342901 49.153089,-0.343146 49.153021)))"
  "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(((-0.343146 49.153021,-0.344011 49.154516,-0.344181 49.154826,-0.344692 49.155692,-0.347301 49.160279,-0.34893 49.163043,-0.349032 49.163011,-0.351206 49.162423,-0.351183 49.162382,-0.352075 49.162259,-0.351638 49.161252,-0.351978 49.161109,-0.353842 49.160371,-0.354227 49.160278,-0.354771 49.160204,-0.355388 49.160075,-0.355862 49.160012,-0.35592 49.159931,-0.357276 49.159673,-0.358832 49.15942,-0.3614 49.158753,-0.361512 49.158932,-0.361559 49.159144,-0.361737 49.159545,-0.363491 49.159539,-0.365058 49.159653,-0.366113 49.159805,-0.36606 49.159913,-0.367161 49.160029,-0.367039 49.160245,-0.367126 49.160338,-0.367542 49.16042,-0.367154 49.161279,-0.366696 49.16238,-0.366369 49.163237,-0.365393 49.165588,-0.366551 49.165757,-0.367419 49.166573,-0.367734 49.166546,-0.368691 49.166488,-0.369021 49.166643,-0.369394 49.166596,-0.369707 49.166138,-0.369761 49.165831,-0.369993 49.165534,-0.36999 49.165111,-0.370525 49.164931,-0.370799 49.165029,-0.371088 49.165019,-0.371073 49.164813,-0.371237 49.164687,-0.371635 49.164569,-0.372195 49.16437,-0.37207 49.164245,-0.37222 49.163839,-0.372673 49.16391,-0.372823 49.163605,-0.373602 49.16323,-0.374657 49.163297,-0.375154 49.163278,-0.375636 49.163391,-0.37587 49.163396,-0.376201 49.163468,-0.376853 49.163244,-0.377097 49.163145,-0.377393 49.16296,-0.377597 49.162759,-0.377924 49.162747,-0.378455 49.162595,-0.378652 49.162572,-0.378968 49.162597,-0.379375 49.162736,-0.379864 49.162721,-0.380041 49.162913,-0.380275 49.162926,-0.380525 49.1628,-0.381006 49.162718,-0.381154 49.16272,-0.381607 49.162803,-0.38192 49.162749,-0.382432 49.162812,-0.382874 49.162905,-0.383183 49.16292,-0.38365 49.162896,-0.384223 49.162986,-0.384519 49.162983,-0.385044 49.162871,-0.385204 49.162856,-0.385761 49.162861,-0.386686 49.162893,-0.386863 49.162874,-0.386989 49.162917,-0.38719 49.163165,-0.387348 49.163199,-0.38757 49.163161,-0.387746 49.163265,-0.388392 49.163499,-0.388508 49.163568,-0.388629 49.16384,-0.388737 49.163873,-0.389417 49.163893,-0.39016 49.163853,-0.39037 49.163885,-0.390521 49.163865,-0.39071 49.163755,-0.391104 49.163965,-0.391283 49.163985,-0.391479 49.163846,-0.391637 49.163871,-0.391718 49.164039,-0.391825 49.164133,-0.392344 49.164219,-0.392396 49.164333,-0.392202 49.164534,-0.39224 49.164675,-0.392467 49.164717,-0.393401 49.164738,-0.393628 49.164797,-0.393711 49.164874,-0.393734 49.16506,-0.393817 49.165238,-0.394168 49.165488,-0.394195 49.16558,-0.394095 49.165813,-0.394206 49.16592,-0.394488 49.165935,-0.394589 49.166029,-0.394382 49.166159,-0.394341 49.166304,-0.394475 49.166484,-0.393989 49.166479,-0.39469 49.167315,-0.3949 49.167642,-0.394757 49.168203,-0.395434 49.168118,-0.395943 49.168145,-0.396734 49.16824,-0.397261 49.168362,-0.397744 49.168449,-0.398263 49.168501,-0.398764 49.168427,-0.398954 49.168427,-0.399842 49.168505,-0.400501 49.168588,-0.40165 49.168642,-0.401843 49.168619,-0.401886 49.168808,-0.401024 49.168842,-0.400668 49.16884,-0.40121 49.169022,-0.401896 49.169166,-0.402531 49.169331,-0.403311 49.169584,-0.403986 49.169882,-0.404946 49.170359,-0.405729 49.170854,-0.405906 49.171005,-0.405729 49.171154,-0.405681 49.171548,-0.405555 49.171794,-0.405382 49.171992,-0.405203 49.172314,-0.40495 49.172967,-0.404685 49.173178,-0.404534 49.173765,-0.404367 49.174181,-0.404116 49.174567,-0.40379 49.174912,-0.403702 49.175109,-0.40359 49.175746,-0.403432 49.176022,-0.403399 49.176207,-0.403281 49.176537,-0.403245 49.176908,-0.403307 49.177189,-0.40333 49.177451,-0.40444 49.177656,-0.40472 49.177728,-0.405289 49.177978,-0.405641 49.178103,-0.405932 49.178236,-0.406305 49.178545,-0.406746 49.179013,-0.406846 49.179249,-0.407072 49.179685,-0.407946 49.180449,-0.408281 49.180799,-0.408518 49.181075,-0.408889 49.18157,-0.409218 49.181945,-0.409854 49.182559,-0.410279 49.182858,-0.412371 49.184264,-0.413763 49.186887,-0.412616 49.187001,-0.412809 49.188706,-0.412489 49.188556,-0.412156 49.188527,-0.409998 49.188281,-0.40909 49.188192,-0.407886 49.188045,-0.407398 49.187971,-0.407077 49.188342,-0.406746 49.188311,-0.406719 49.188541,-0.407197 49.188564,-0.407227 49.18886,-0.407341 49.189169,-0.407928 49.190529,-0.408202 49.191003,-0.40861 49.191484,-0.40909 49.192025,-0.409805 49.192863,-0.409979 49.193105,-0.410316 49.193419,-0.411494 49.194623,-0.411607 49.194757,-0.410534 49.195615,-0.409145 49.196516,-0.409262 49.196648,-0.408165 49.197343,-0.407975 49.197476,-0.407325 49.197846,-0.405263 49.198991,-0.404332 49.199485,-0.402052 49.200619,-0.401954 49.200659,-0.401183 49.200203,-0.40032 49.199734,-0.39942 49.199281,-0.397454 49.198391,-0.397188 49.198281,-0.396829 49.198203,-0.396746 49.198752,-0.396663 49.199062,-0.396426 49.199662,-0.396236 49.200002,-0.395857 49.200533,-0.395425 49.201011,-0.395305 49.201177,-0.394933 49.201556,-0.394697 49.201752,-0.394144 49.202128,-0.393541 49.202435,-0.393163 49.202584,-0.39252 49.202782,-0.391887 49.202914,-0.391442 49.203031,-0.391186 49.203869,-0.391082 49.204057,-0.390728 49.204403,-0.389685 49.205145,-0.388482 49.206091,-0.388227 49.206312,-0.387557 49.20685,-0.386665 49.207473,-0.386334 49.207729,-0.38605 49.207978,-0.384875 49.20887,-0.383782 49.209603,-0.383167 49.209996,-0.382451 49.210403,-0.381778 49.210736,-0.380578 49.211298,-0.380243 49.211425,-0.379901 49.211504,-0.378835 49.21172,-0.379058 49.211978,-0.378217 49.212597,-0.377878 49.213425,-0.377715 49.213653,-0.377597 49.213704,-0.377069 49.213713,-0.376573 49.213666,-0.376292 49.213588,-0.376093 49.213423,-0.375643 49.21363,-0.374589 49.214191,-0.373625 49.214734,-0.372699 49.215311,-0.3723 49.215487,-0.371873 49.215613,-0.370692 49.215999,-0.370054 49.216261,-0.368957 49.215884,-0.365025 49.214884,-0.364905 49.2146,-0.364524 49.214546,-0.363568 49.214325,-0.3637 49.214146,-0.363592 49.214071,-0.363135 49.213873,-0.3625 49.213639,-0.361502 49.213233,-0.361219 49.213134,-0.360518 49.212838,-0.360234 49.212734,-0.360181 49.212784,-0.359919 49.212664,-0.359617 49.212593,-0.358595 49.212293,-0.357978 49.212082,-0.357423 49.211829,-0.356762 49.211478,-0.356263 49.211078,-0.355031 49.209813,-0.355728 49.208292,-0.355767 49.208181,-0.353922 49.207679,-0.353608 49.208073,-0.353316 49.208483,-0.351284 49.207635,-0.352076 49.20696,-0.351265 49.206518,-0.350551 49.206207,-0.350812 49.205853,-0.35085 49.205665,-0.350667 49.205545,-0.351045 49.205164,-0.350122 49.20522,-0.349938 49.205186,-0.348881 49.204909,-0.349198 49.204606,-0.347589 49.203523,-0.348851 49.202544,-0.349741 49.201869,-0.350068 49.201654,-0.350394 49.20154,-0.349134 49.20117,-0.348494 49.200964,-0.347042 49.200447,-0.346374 49.200197,-0.344619 49.199462,-0.343931 49.19915,-0.343267 49.198833,-0.342701 49.198549,-0.341533 49.197911,-0.340977 49.19757,-0.340484 49.197241,-0.339456 49.19649,-0.338403 49.195574,-0.337975 49.195154,-0.336097 49.193225,-0.335923 49.193036,-0.336862 49.192373,-0.337407 49.192164,-0.337719 49.191952,-0.338017 49.191699,-0.337283 49.190903,-0.336465 49.190027,-0.336125 49.189505,-0.336029 49.189234,-0.335885 49.188975,-0.335359 49.188303,-0.334856 49.187763,-0.334714 49.187643,-0.333644 49.185865,-0.333629 49.185828,-0.334197 49.185776,-0.334756 49.185657,-0.335023 49.185534,-0.335207 49.185365,-0.335304 49.18521,-0.335244 49.184828,-0.334687 49.183541,-0.334614 49.183232,-0.334723 49.182903,-0.335042 49.182501,-0.335426 49.182185,-0.334914 49.18139,-0.334336 49.18154,-0.333924 49.180556,-0.33423 49.180556,-0.333433 49.179282,-0.333016 49.179351,-0.332609 49.177461,-0.333265 49.177257,-0.333639 49.177157,-0.333986 49.17709,-0.33384 49.176727,-0.331787 49.177315,-0.331223 49.177385,-0.331235 49.176979,-0.331936 49.176823,-0.331739 49.176552,-0.331164 49.17672,-0.330778 49.174702,-0.330723 49.173986,-0.330867 49.174004,-0.330855 49.172347,-0.331116 49.172435,-0.33131 49.167625,-0.331918 49.167564,-0.334021 49.167298,-0.335878 49.167077,-0.336404 49.166992,-0.337252 49.166742,-0.338364 49.166484,-0.339238 49.166258,-0.340235 49.165969,-0.341561 49.165554,-0.341719 49.165471,-0.342581 49.164769,-0.343149 49.164344,-0.342781 49.164141,-0.340218 49.162523,-0.339193 49.161854,-0.339039 49.161659,-0.340669 49.160344,-0.338979 49.159321,-0.337774 49.158528,-0.337291 49.158221,-0.338395 49.157604,-0.339418 49.157044,-0.34047 49.156454,-0.338851 49.155275,-0.338904 49.155201,-0.339124 49.155208,-0.339376 49.155121,-0.340973 49.154184,-0.341555 49.153867,-0.342901 49.153089,-0.343146 49.153021)))', 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)

1.53 ms
(0.30%)

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(((-0.343146 49.153021,-0.344011 49.154516,-0.344181 49.154826,-0.344692 49.155692,-0.347301 49.160279,-0.34893 49.163043,-0.349032 49.163011,-0.351206 49.162423,-0.351183 49.162382,-0.352075 49.162259,-0.351638 49.161252,-0.351978 49.161109,-0.353842 49.160371,-0.354227 49.160278,-0.354771 49.160204,-0.355388 49.160075,-0.355862 49.160012,-0.35592 49.159931,-0.357276 49.159673,-0.358832 49.15942,-0.3614 49.158753,-0.361512 49.158932,-0.361559 49.159144,-0.361737 49.159545,-0.363491 49.159539,-0.365058 49.159653,-0.366113 49.159805,-0.36606 49.159913,-0.367161 49.160029,-0.367039 49.160245,-0.367126 49.160338,-0.367542 49.16042,-0.367154 49.161279,-0.366696 49.16238,-0.366369 49.163237,-0.365393 49.165588,-0.366551 49.165757,-0.367419 49.166573,-0.367734 49.166546,-0.368691 49.166488,-0.369021 49.166643,-0.369394 49.166596,-0.369707 49.166138,-0.369761 49.165831,-0.369993 49.165534,-0.36999 49.165111,-0.370525 49.164931,-0.370799 49.165029,-0.371088 49.165019,-0.371073 49.164813,-0.371237 49.164687,-0.371635 49.164569,-0.372195 49.16437,-0.37207 49.164245,-0.37222 49.163839,-0.372673 49.16391,-0.372823 49.163605,-0.373602 49.16323,-0.374657 49.163297,-0.375154 49.163278,-0.375636 49.163391,-0.37587 49.163396,-0.376201 49.163468,-0.376853 49.163244,-0.377097 49.163145,-0.377393 49.16296,-0.377597 49.162759,-0.377924 49.162747,-0.378455 49.162595,-0.378652 49.162572,-0.378968 49.162597,-0.379375 49.162736,-0.379864 49.162721,-0.380041 49.162913,-0.380275 49.162926,-0.380525 49.1628,-0.381006 49.162718,-0.381154 49.16272,-0.381607 49.162803,-0.38192 49.162749,-0.382432 49.162812,-0.382874 49.162905,-0.383183 49.16292,-0.38365 49.162896,-0.384223 49.162986,-0.384519 49.162983,-0.385044 49.162871,-0.385204 49.162856,-0.385761 49.162861,-0.386686 49.162893,-0.386863 49.162874,-0.386989 49.162917,-0.38719 49.163165,-0.387348 49.163199,-0.38757 49.163161,-0.387746 49.163265,-0.388392 49.163499,-0.388508 49.163568,-0.388629 49.16384,-0.388737 49.163873,-0.389417 49.163893,-0.39016 49.163853,-0.39037 49.163885,-0.390521 49.163865,-0.39071 49.163755,-0.391104 49.163965,-0.391283 49.163985,-0.391479 49.163846,-0.391637 49.163871,-0.391718 49.164039,-0.391825 49.164133,-0.392344 49.164219,-0.392396 49.164333,-0.392202 49.164534,-0.39224 49.164675,-0.392467 49.164717,-0.393401 49.164738,-0.393628 49.164797,-0.393711 49.164874,-0.393734 49.16506,-0.393817 49.165238,-0.394168 49.165488,-0.394195 49.16558,-0.394095 49.165813,-0.394206 49.16592,-0.394488 49.165935,-0.394589 49.166029,-0.394382 49.166159,-0.394341 49.166304,-0.394475 49.166484,-0.393989 49.166479,-0.39469 49.167315,-0.3949 49.167642,-0.394757 49.168203,-0.395434 49.168118,-0.395943 49.168145,-0.396734 49.16824,-0.397261 49.168362,-0.397744 49.168449,-0.398263 49.168501,-0.398764 49.168427,-0.398954 49.168427,-0.399842 49.168505,-0.400501 49.168588,-0.40165 49.168642,-0.401843 49.168619,-0.401886 49.168808,-0.401024 49.168842,-0.400668 49.16884,-0.40121 49.169022,-0.401896 49.169166,-0.402531 49.169331,-0.403311 49.169584,-0.403986 49.169882,-0.404946 49.170359,-0.405729 49.170854,-0.405906 49.171005,-0.405729 49.171154,-0.405681 49.171548,-0.405555 49.171794,-0.405382 49.171992,-0.405203 49.172314,-0.40495 49.172967,-0.404685 49.173178,-0.404534 49.173765,-0.404367 49.174181,-0.404116 49.174567,-0.40379 49.174912,-0.403702 49.175109,-0.40359 49.175746,-0.403432 49.176022,-0.403399 49.176207,-0.403281 49.176537,-0.403245 49.176908,-0.403307 49.177189,-0.40333 49.177451,-0.40444 49.177656,-0.40472 49.177728,-0.405289 49.177978,-0.405641 49.178103,-0.405932 49.178236,-0.406305 49.178545,-0.406746 49.179013,-0.406846 49.179249,-0.407072 49.179685,-0.407946 49.180449,-0.408281 49.180799,-0.408518 49.181075,-0.408889 49.18157,-0.409218 49.181945,-0.409854 49.182559,-0.410279 49.182858,-0.412371 49.184264,-0.413763 49.186887,-0.412616 49.187001,-0.412809 49.188706,-0.412489 49.188556,-0.412156 49.188527,-0.409998 49.188281,-0.40909 49.188192,-0.407886 49.188045,-0.407398 49.187971,-0.407077 49.188342,-0.406746 49.188311,-0.406719 49.188541,-0.407197 49.188564,-0.407227 49.18886,-0.407341 49.189169,-0.407928 49.190529,-0.408202 49.191003,-0.40861 49.191484,-0.40909 49.192025,-0.409805 49.192863,-0.409979 49.193105,-0.410316 49.193419,-0.411494 49.194623,-0.411607 49.194757,-0.410534 49.195615,-0.409145 49.196516,-0.409262 49.196648,-0.408165 49.197343,-0.407975 49.197476,-0.407325 49.197846,-0.405263 49.198991,-0.404332 49.199485,-0.402052 49.200619,-0.401954 49.200659,-0.401183 49.200203,-0.40032 49.199734,-0.39942 49.199281,-0.397454 49.198391,-0.397188 49.198281,-0.396829 49.198203,-0.396746 49.198752,-0.396663 49.199062,-0.396426 49.199662,-0.396236 49.200002,-0.395857 49.200533,-0.395425 49.201011,-0.395305 49.201177,-0.394933 49.201556,-0.394697 49.201752,-0.394144 49.202128,-0.393541 49.202435,-0.393163 49.202584,-0.39252 49.202782,-0.391887 49.202914,-0.391442 49.203031,-0.391186 49.203869,-0.391082 49.204057,-0.390728 49.204403,-0.389685 49.205145,-0.388482 49.206091,-0.388227 49.206312,-0.387557 49.20685,-0.386665 49.207473,-0.386334 49.207729,-0.38605 49.207978,-0.384875 49.20887,-0.383782 49.209603,-0.383167 49.209996,-0.382451 49.210403,-0.381778 49.210736,-0.380578 49.211298,-0.380243 49.211425,-0.379901 49.211504,-0.378835 49.21172,-0.379058 49.211978,-0.378217 49.212597,-0.377878 49.213425,-0.377715 49.213653,-0.377597 49.213704,-0.377069 49.213713,-0.376573 49.213666,-0.376292 49.213588,-0.376093 49.213423,-0.375643 49.21363,-0.374589 49.214191,-0.373625 49.214734,-0.372699 49.215311,-0.3723 49.215487,-0.371873 49.215613,-0.370692 49.215999,-0.370054 49.216261,-0.368957 49.215884,-0.365025 49.214884,-0.364905 49.2146,-0.364524 49.214546,-0.363568 49.214325,-0.3637 49.214146,-0.363592 49.214071,-0.363135 49.213873,-0.3625 49.213639,-0.361502 49.213233,-0.361219 49.213134,-0.360518 49.212838,-0.360234 49.212734,-0.360181 49.212784,-0.359919 49.212664,-0.359617 49.212593,-0.358595 49.212293,-0.357978 49.212082,-0.357423 49.211829,-0.356762 49.211478,-0.356263 49.211078,-0.355031 49.209813,-0.355728 49.208292,-0.355767 49.208181,-0.353922 49.207679,-0.353608 49.208073,-0.353316 49.208483,-0.351284 49.207635,-0.352076 49.20696,-0.351265 49.206518,-0.350551 49.206207,-0.350812 49.205853,-0.35085 49.205665,-0.350667 49.205545,-0.351045 49.205164,-0.350122 49.20522,-0.349938 49.205186,-0.348881 49.204909,-0.349198 49.204606,-0.347589 49.203523,-0.348851 49.202544,-0.349741 49.201869,-0.350068 49.201654,-0.350394 49.20154,-0.349134 49.20117,-0.348494 49.200964,-0.347042 49.200447,-0.346374 49.200197,-0.344619 49.199462,-0.343931 49.19915,-0.343267 49.198833,-0.342701 49.198549,-0.341533 49.197911,-0.340977 49.19757,-0.340484 49.197241,-0.339456 49.19649,-0.338403 49.195574,-0.337975 49.195154,-0.336097 49.193225,-0.335923 49.193036,-0.336862 49.192373,-0.337407 49.192164,-0.337719 49.191952,-0.338017 49.191699,-0.337283 49.190903,-0.336465 49.190027,-0.336125 49.189505,-0.336029 49.189234,-0.335885 49.188975,-0.335359 49.188303,-0.334856 49.187763,-0.334714 49.187643,-0.333644 49.185865,-0.333629 49.185828,-0.334197 49.185776,-0.334756 49.185657,-0.335023 49.185534,-0.335207 49.185365,-0.335304 49.18521,-0.335244 49.184828,-0.334687 49.183541,-0.334614 49.183232,-0.334723 49.182903,-0.335042 49.182501,-0.335426 49.182185,-0.334914 49.18139,-0.334336 49.18154,-0.333924 49.180556,-0.33423 49.180556,-0.333433 49.179282,-0.333016 49.179351,-0.332609 49.177461,-0.333265 49.177257,-0.333639 49.177157,-0.333986 49.17709,-0.33384 49.176727,-0.331787 49.177315,-0.331223 49.177385,-0.331235 49.176979,-0.331936 49.176823,-0.331739 49.176552,-0.331164 49.17672,-0.330778 49.174702,-0.330723 49.173986,-0.330867 49.174004,-0.330855 49.172347,-0.331116 49.172435,-0.33131 49.167625,-0.331918 49.167564,-0.334021 49.167298,-0.335878 49.167077,-0.336404 49.166992,-0.337252 49.166742,-0.338364 49.166484,-0.339238 49.166258,-0.340235 49.165969,-0.341561 49.165554,-0.341719 49.165471,-0.342581 49.164769,-0.343149 49.164344,-0.342781 49.164141,-0.340218 49.162523,-0.339193 49.161854,-0.339039 49.161659,-0.340669 49.160344,-0.338979 49.159321,-0.337774 49.158528,-0.337291 49.158221,-0.338395 49.157604,-0.339418 49.157044,-0.34047 49.156454,-0.338851 49.155275,-0.338904 49.155201,-0.339124 49.155208,-0.339376 49.155121,-0.340973 49.154184,-0.341555 49.153867,-0.342901 49.153089,-0.343146 49.153021)))"
]

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(((-0.343146 49.153021,-0.344011 49.154516,-0.344181 49.154826,-0.344692 49.155692,-0.347301 49.160279,-0.34893 49.163043,-0.349032 49.163011,-0.351206 49.162423,-0.351183 49.162382,-0.352075 49.162259,-0.351638 49.161252,-0.351978 49.161109,-0.353842 49.160371,-0.354227 49.160278,-0.354771 49.160204,-0.355388 49.160075,-0.355862 49.160012,-0.35592 49.159931,-0.357276 49.159673,-0.358832 49.15942,-0.3614 49.158753,-0.361512 49.158932,-0.361559 49.159144,-0.361737 49.159545,-0.363491 49.159539,-0.365058 49.159653,-0.366113 49.159805,-0.36606 49.159913,-0.367161 49.160029,-0.367039 49.160245,-0.367126 49.160338,-0.367542 49.16042,-0.367154 49.161279,-0.366696 49.16238,-0.366369 49.163237,-0.365393 49.165588,-0.366551 49.165757,-0.367419 49.166573,-0.367734 49.166546,-0.368691 49.166488,-0.369021 49.166643,-0.369394 49.166596,-0.369707 49.166138,-0.369761 49.165831,-0.369993 49.165534,-0.36999 49.165111,-0.370525 49.164931,-0.370799 49.165029,-0.371088 49.165019,-0.371073 49.164813,-0.371237 49.164687,-0.371635 49.164569,-0.372195 49.16437,-0.37207 49.164245,-0.37222 49.163839,-0.372673 49.16391,-0.372823 49.163605,-0.373602 49.16323,-0.374657 49.163297,-0.375154 49.163278,-0.375636 49.163391,-0.37587 49.163396,-0.376201 49.163468,-0.376853 49.163244,-0.377097 49.163145,-0.377393 49.16296,-0.377597 49.162759,-0.377924 49.162747,-0.378455 49.162595,-0.378652 49.162572,-0.378968 49.162597,-0.379375 49.162736,-0.379864 49.162721,-0.380041 49.162913,-0.380275 49.162926,-0.380525 49.1628,-0.381006 49.162718,-0.381154 49.16272,-0.381607 49.162803,-0.38192 49.162749,-0.382432 49.162812,-0.382874 49.162905,-0.383183 49.16292,-0.38365 49.162896,-0.384223 49.162986,-0.384519 49.162983,-0.385044 49.162871,-0.385204 49.162856,-0.385761 49.162861,-0.386686 49.162893,-0.386863 49.162874,-0.386989 49.162917,-0.38719 49.163165,-0.387348 49.163199,-0.38757 49.163161,-0.387746 49.163265,-0.388392 49.163499,-0.388508 49.163568,-0.388629 49.16384,-0.388737 49.163873,-0.389417 49.163893,-0.39016 49.163853,-0.39037 49.163885,-0.390521 49.163865,-0.39071 49.163755,-0.391104 49.163965,-0.391283 49.163985,-0.391479 49.163846,-0.391637 49.163871,-0.391718 49.164039,-0.391825 49.164133,-0.392344 49.164219,-0.392396 49.164333,-0.392202 49.164534,-0.39224 49.164675,-0.392467 49.164717,-0.393401 49.164738,-0.393628 49.164797,-0.393711 49.164874,-0.393734 49.16506,-0.393817 49.165238,-0.394168 49.165488,-0.394195 49.16558,-0.394095 49.165813,-0.394206 49.16592,-0.394488 49.165935,-0.394589 49.166029,-0.394382 49.166159,-0.394341 49.166304,-0.394475 49.166484,-0.393989 49.166479,-0.39469 49.167315,-0.3949 49.167642,-0.394757 49.168203,-0.395434 49.168118,-0.395943 49.168145,-0.396734 49.16824,-0.397261 49.168362,-0.397744 49.168449,-0.398263 49.168501,-0.398764 49.168427,-0.398954 49.168427,-0.399842 49.168505,-0.400501 49.168588,-0.40165 49.168642,-0.401843 49.168619,-0.401886 49.168808,-0.401024 49.168842,-0.400668 49.16884,-0.40121 49.169022,-0.401896 49.169166,-0.402531 49.169331,-0.403311 49.169584,-0.403986 49.169882,-0.404946 49.170359,-0.405729 49.170854,-0.405906 49.171005,-0.405729 49.171154,-0.405681 49.171548,-0.405555 49.171794,-0.405382 49.171992,-0.405203 49.172314,-0.40495 49.172967,-0.404685 49.173178,-0.404534 49.173765,-0.404367 49.174181,-0.404116 49.174567,-0.40379 49.174912,-0.403702 49.175109,-0.40359 49.175746,-0.403432 49.176022,-0.403399 49.176207,-0.403281 49.176537,-0.403245 49.176908,-0.403307 49.177189,-0.40333 49.177451,-0.40444 49.177656,-0.40472 49.177728,-0.405289 49.177978,-0.405641 49.178103,-0.405932 49.178236,-0.406305 49.178545,-0.406746 49.179013,-0.406846 49.179249,-0.407072 49.179685,-0.407946 49.180449,-0.408281 49.180799,-0.408518 49.181075,-0.408889 49.18157,-0.409218 49.181945,-0.409854 49.182559,-0.410279 49.182858,-0.412371 49.184264,-0.413763 49.186887,-0.412616 49.187001,-0.412809 49.188706,-0.412489 49.188556,-0.412156 49.188527,-0.409998 49.188281,-0.40909 49.188192,-0.407886 49.188045,-0.407398 49.187971,-0.407077 49.188342,-0.406746 49.188311,-0.406719 49.188541,-0.407197 49.188564,-0.407227 49.18886,-0.407341 49.189169,-0.407928 49.190529,-0.408202 49.191003,-0.40861 49.191484,-0.40909 49.192025,-0.409805 49.192863,-0.409979 49.193105,-0.410316 49.193419,-0.411494 49.194623,-0.411607 49.194757,-0.410534 49.195615,-0.409145 49.196516,-0.409262 49.196648,-0.408165 49.197343,-0.407975 49.197476,-0.407325 49.197846,-0.405263 49.198991,-0.404332 49.199485,-0.402052 49.200619,-0.401954 49.200659,-0.401183 49.200203,-0.40032 49.199734,-0.39942 49.199281,-0.397454 49.198391,-0.397188 49.198281,-0.396829 49.198203,-0.396746 49.198752,-0.396663 49.199062,-0.396426 49.199662,-0.396236 49.200002,-0.395857 49.200533,-0.395425 49.201011,-0.395305 49.201177,-0.394933 49.201556,-0.394697 49.201752,-0.394144 49.202128,-0.393541 49.202435,-0.393163 49.202584,-0.39252 49.202782,-0.391887 49.202914,-0.391442 49.203031,-0.391186 49.203869,-0.391082 49.204057,-0.390728 49.204403,-0.389685 49.205145,-0.388482 49.206091,-0.388227 49.206312,-0.387557 49.20685,-0.386665 49.207473,-0.386334 49.207729,-0.38605 49.207978,-0.384875 49.20887,-0.383782 49.209603,-0.383167 49.209996,-0.382451 49.210403,-0.381778 49.210736,-0.380578 49.211298,-0.380243 49.211425,-0.379901 49.211504,-0.378835 49.21172,-0.379058 49.211978,-0.378217 49.212597,-0.377878 49.213425,-0.377715 49.213653,-0.377597 49.213704,-0.377069 49.213713,-0.376573 49.213666,-0.376292 49.213588,-0.376093 49.213423,-0.375643 49.21363,-0.374589 49.214191,-0.373625 49.214734,-0.372699 49.215311,-0.3723 49.215487,-0.371873 49.215613,-0.370692 49.215999,-0.370054 49.216261,-0.368957 49.215884,-0.365025 49.214884,-0.364905 49.2146,-0.364524 49.214546,-0.363568 49.214325,-0.3637 49.214146,-0.363592 49.214071,-0.363135 49.213873,-0.3625 49.213639,-0.361502 49.213233,-0.361219 49.213134,-0.360518 49.212838,-0.360234 49.212734,-0.360181 49.212784,-0.359919 49.212664,-0.359617 49.212593,-0.358595 49.212293,-0.357978 49.212082,-0.357423 49.211829,-0.356762 49.211478,-0.356263 49.211078,-0.355031 49.209813,-0.355728 49.208292,-0.355767 49.208181,-0.353922 49.207679,-0.353608 49.208073,-0.353316 49.208483,-0.351284 49.207635,-0.352076 49.20696,-0.351265 49.206518,-0.350551 49.206207,-0.350812 49.205853,-0.35085 49.205665,-0.350667 49.205545,-0.351045 49.205164,-0.350122 49.20522,-0.349938 49.205186,-0.348881 49.204909,-0.349198 49.204606,-0.347589 49.203523,-0.348851 49.202544,-0.349741 49.201869,-0.350068 49.201654,-0.350394 49.20154,-0.349134 49.20117,-0.348494 49.200964,-0.347042 49.200447,-0.346374 49.200197,-0.344619 49.199462,-0.343931 49.19915,-0.343267 49.198833,-0.342701 49.198549,-0.341533 49.197911,-0.340977 49.19757,-0.340484 49.197241,-0.339456 49.19649,-0.338403 49.195574,-0.337975 49.195154,-0.336097 49.193225,-0.335923 49.193036,-0.336862 49.192373,-0.337407 49.192164,-0.337719 49.191952,-0.338017 49.191699,-0.337283 49.190903,-0.336465 49.190027,-0.336125 49.189505,-0.336029 49.189234,-0.335885 49.188975,-0.335359 49.188303,-0.334856 49.187763,-0.334714 49.187643,-0.333644 49.185865,-0.333629 49.185828,-0.334197 49.185776,-0.334756 49.185657,-0.335023 49.185534,-0.335207 49.185365,-0.335304 49.18521,-0.335244 49.184828,-0.334687 49.183541,-0.334614 49.183232,-0.334723 49.182903,-0.335042 49.182501,-0.335426 49.182185,-0.334914 49.18139,-0.334336 49.18154,-0.333924 49.180556,-0.33423 49.180556,-0.333433 49.179282,-0.333016 49.179351,-0.332609 49.177461,-0.333265 49.177257,-0.333639 49.177157,-0.333986 49.17709,-0.33384 49.176727,-0.331787 49.177315,-0.331223 49.177385,-0.331235 49.176979,-0.331936 49.176823,-0.331739 49.176552,-0.331164 49.17672,-0.330778 49.174702,-0.330723 49.173986,-0.330867 49.174004,-0.330855 49.172347,-0.331116 49.172435,-0.33131 49.167625,-0.331918 49.167564,-0.334021 49.167298,-0.335878 49.167077,-0.336404 49.166992,-0.337252 49.166742,-0.338364 49.166484,-0.339238 49.166258,-0.340235 49.165969,-0.341561 49.165554,-0.341719 49.165471,-0.342581 49.164769,-0.343149 49.164344,-0.342781 49.164141,-0.340218 49.162523,-0.339193 49.161854,-0.339039 49.161659,-0.340669 49.160344,-0.338979 49.159321,-0.337774 49.158528,-0.337291 49.158221,-0.338395 49.157604,-0.339418 49.157044,-0.34047 49.156454,-0.338851 49.155275,-0.338904 49.155201,-0.339124 49.155208,-0.339376 49.155121,-0.340973 49.154184,-0.341555 49.153867,-0.342901 49.153089,-0.343146 49.153021)))', 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)

1.15 ms
(0.23%)

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)

0.98 ms
(0.19%)

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(((-0.343146 49.153021,-0.344011 49.154516,-0.344181 49.154826,-0.344692 49.155692,-0.347301 49.160279,-0.34893 49.163043,-0.349032 49.163011,-0.351206 49.162423,-0.351183 49.162382,-0.352075 49.162259,-0.351638 49.161252,-0.351978 49.161109,-0.353842 49.160371,-0.354227 49.160278,-0.354771 49.160204,-0.355388 49.160075,-0.355862 49.160012,-0.35592 49.159931,-0.357276 49.159673,-0.358832 49.15942,-0.3614 49.158753,-0.361512 49.158932,-0.361559 49.159144,-0.361737 49.159545,-0.363491 49.159539,-0.365058 49.159653,-0.366113 49.159805,-0.36606 49.159913,-0.367161 49.160029,-0.367039 49.160245,-0.367126 49.160338,-0.367542 49.16042,-0.367154 49.161279,-0.366696 49.16238,-0.366369 49.163237,-0.365393 49.165588,-0.366551 49.165757,-0.367419 49.166573,-0.367734 49.166546,-0.368691 49.166488,-0.369021 49.166643,-0.369394 49.166596,-0.369707 49.166138,-0.369761 49.165831,-0.369993 49.165534,-0.36999 49.165111,-0.370525 49.164931,-0.370799 49.165029,-0.371088 49.165019,-0.371073 49.164813,-0.371237 49.164687,-0.371635 49.164569,-0.372195 49.16437,-0.37207 49.164245,-0.37222 49.163839,-0.372673 49.16391,-0.372823 49.163605,-0.373602 49.16323,-0.374657 49.163297,-0.375154 49.163278,-0.375636 49.163391,-0.37587 49.163396,-0.376201 49.163468,-0.376853 49.163244,-0.377097 49.163145,-0.377393 49.16296,-0.377597 49.162759,-0.377924 49.162747,-0.378455 49.162595,-0.378652 49.162572,-0.378968 49.162597,-0.379375 49.162736,-0.379864 49.162721,-0.380041 49.162913,-0.380275 49.162926,-0.380525 49.1628,-0.381006 49.162718,-0.381154 49.16272,-0.381607 49.162803,-0.38192 49.162749,-0.382432 49.162812,-0.382874 49.162905,-0.383183 49.16292,-0.38365 49.162896,-0.384223 49.162986,-0.384519 49.162983,-0.385044 49.162871,-0.385204 49.162856,-0.385761 49.162861,-0.386686 49.162893,-0.386863 49.162874,-0.386989 49.162917,-0.38719 49.163165,-0.387348 49.163199,-0.38757 49.163161,-0.387746 49.163265,-0.388392 49.163499,-0.388508 49.163568,-0.388629 49.16384,-0.388737 49.163873,-0.389417 49.163893,-0.39016 49.163853,-0.39037 49.163885,-0.390521 49.163865,-0.39071 49.163755,-0.391104 49.163965,-0.391283 49.163985,-0.391479 49.163846,-0.391637 49.163871,-0.391718 49.164039,-0.391825 49.164133,-0.392344 49.164219,-0.392396 49.164333,-0.392202 49.164534,-0.39224 49.164675,-0.392467 49.164717,-0.393401 49.164738,-0.393628 49.164797,-0.393711 49.164874,-0.393734 49.16506,-0.393817 49.165238,-0.394168 49.165488,-0.394195 49.16558,-0.394095 49.165813,-0.394206 49.16592,-0.394488 49.165935,-0.394589 49.166029,-0.394382 49.166159,-0.394341 49.166304,-0.394475 49.166484,-0.393989 49.166479,-0.39469 49.167315,-0.3949 49.167642,-0.394757 49.168203,-0.395434 49.168118,-0.395943 49.168145,-0.396734 49.16824,-0.397261 49.168362,-0.397744 49.168449,-0.398263 49.168501,-0.398764 49.168427,-0.398954 49.168427,-0.399842 49.168505,-0.400501 49.168588,-0.40165 49.168642,-0.401843 49.168619,-0.401886 49.168808,-0.401024 49.168842,-0.400668 49.16884,-0.40121 49.169022,-0.401896 49.169166,-0.402531 49.169331,-0.403311 49.169584,-0.403986 49.169882,-0.404946 49.170359,-0.405729 49.170854,-0.405906 49.171005,-0.405729 49.171154,-0.405681 49.171548,-0.405555 49.171794,-0.405382 49.171992,-0.405203 49.172314,-0.40495 49.172967,-0.404685 49.173178,-0.404534 49.173765,-0.404367 49.174181,-0.404116 49.174567,-0.40379 49.174912,-0.403702 49.175109,-0.40359 49.175746,-0.403432 49.176022,-0.403399 49.176207,-0.403281 49.176537,-0.403245 49.176908,-0.403307 49.177189,-0.40333 49.177451,-0.40444 49.177656,-0.40472 49.177728,-0.405289 49.177978,-0.405641 49.178103,-0.405932 49.178236,-0.406305 49.178545,-0.406746 49.179013,-0.406846 49.179249,-0.407072 49.179685,-0.407946 49.180449,-0.408281 49.180799,-0.408518 49.181075,-0.408889 49.18157,-0.409218 49.181945,-0.409854 49.182559,-0.410279 49.182858,-0.412371 49.184264,-0.413763 49.186887,-0.412616 49.187001,-0.412809 49.188706,-0.412489 49.188556,-0.412156 49.188527,-0.409998 49.188281,-0.40909 49.188192,-0.407886 49.188045,-0.407398 49.187971,-0.407077 49.188342,-0.406746 49.188311,-0.406719 49.188541,-0.407197 49.188564,-0.407227 49.18886,-0.407341 49.189169,-0.407928 49.190529,-0.408202 49.191003,-0.40861 49.191484,-0.40909 49.192025,-0.409805 49.192863,-0.409979 49.193105,-0.410316 49.193419,-0.411494 49.194623,-0.411607 49.194757,-0.410534 49.195615,-0.409145 49.196516,-0.409262 49.196648,-0.408165 49.197343,-0.407975 49.197476,-0.407325 49.197846,-0.405263 49.198991,-0.404332 49.199485,-0.402052 49.200619,-0.401954 49.200659,-0.401183 49.200203,-0.40032 49.199734,-0.39942 49.199281,-0.397454 49.198391,-0.397188 49.198281,-0.396829 49.198203,-0.396746 49.198752,-0.396663 49.199062,-0.396426 49.199662,-0.396236 49.200002,-0.395857 49.200533,-0.395425 49.201011,-0.395305 49.201177,-0.394933 49.201556,-0.394697 49.201752,-0.394144 49.202128,-0.393541 49.202435,-0.393163 49.202584,-0.39252 49.202782,-0.391887 49.202914,-0.391442 49.203031,-0.391186 49.203869,-0.391082 49.204057,-0.390728 49.204403,-0.389685 49.205145,-0.388482 49.206091,-0.388227 49.206312,-0.387557 49.20685,-0.386665 49.207473,-0.386334 49.207729,-0.38605 49.207978,-0.384875 49.20887,-0.383782 49.209603,-0.383167 49.209996,-0.382451 49.210403,-0.381778 49.210736,-0.380578 49.211298,-0.380243 49.211425,-0.379901 49.211504,-0.378835 49.21172,-0.379058 49.211978,-0.378217 49.212597,-0.377878 49.213425,-0.377715 49.213653,-0.377597 49.213704,-0.377069 49.213713,-0.376573 49.213666,-0.376292 49.213588,-0.376093 49.213423,-0.375643 49.21363,-0.374589 49.214191,-0.373625 49.214734,-0.372699 49.215311,-0.3723 49.215487,-0.371873 49.215613,-0.370692 49.215999,-0.370054 49.216261,-0.368957 49.215884,-0.365025 49.214884,-0.364905 49.2146,-0.364524 49.214546,-0.363568 49.214325,-0.3637 49.214146,-0.363592 49.214071,-0.363135 49.213873,-0.3625 49.213639,-0.361502 49.213233,-0.361219 49.213134,-0.360518 49.212838,-0.360234 49.212734,-0.360181 49.212784,-0.359919 49.212664,-0.359617 49.212593,-0.358595 49.212293,-0.357978 49.212082,-0.357423 49.211829,-0.356762 49.211478,-0.356263 49.211078,-0.355031 49.209813,-0.355728 49.208292,-0.355767 49.208181,-0.353922 49.207679,-0.353608 49.208073,-0.353316 49.208483,-0.351284 49.207635,-0.352076 49.20696,-0.351265 49.206518,-0.350551 49.206207,-0.350812 49.205853,-0.35085 49.205665,-0.350667 49.205545,-0.351045 49.205164,-0.350122 49.20522,-0.349938 49.205186,-0.348881 49.204909,-0.349198 49.204606,-0.347589 49.203523,-0.348851 49.202544,-0.349741 49.201869,-0.350068 49.201654,-0.350394 49.20154,-0.349134 49.20117,-0.348494 49.200964,-0.347042 49.200447,-0.346374 49.200197,-0.344619 49.199462,-0.343931 49.19915,-0.343267 49.198833,-0.342701 49.198549,-0.341533 49.197911,-0.340977 49.19757,-0.340484 49.197241,-0.339456 49.19649,-0.338403 49.195574,-0.337975 49.195154,-0.336097 49.193225,-0.335923 49.193036,-0.336862 49.192373,-0.337407 49.192164,-0.337719 49.191952,-0.338017 49.191699,-0.337283 49.190903,-0.336465 49.190027,-0.336125 49.189505,-0.336029 49.189234,-0.335885 49.188975,-0.335359 49.188303,-0.334856 49.187763,-0.334714 49.187643,-0.333644 49.185865,-0.333629 49.185828,-0.334197 49.185776,-0.334756 49.185657,-0.335023 49.185534,-0.335207 49.185365,-0.335304 49.18521,-0.335244 49.184828,-0.334687 49.183541,-0.334614 49.183232,-0.334723 49.182903,-0.335042 49.182501,-0.335426 49.182185,-0.334914 49.18139,-0.334336 49.18154,-0.333924 49.180556,-0.33423 49.180556,-0.333433 49.179282,-0.333016 49.179351,-0.332609 49.177461,-0.333265 49.177257,-0.333639 49.177157,-0.333986 49.17709,-0.33384 49.176727,-0.331787 49.177315,-0.331223 49.177385,-0.331235 49.176979,-0.331936 49.176823,-0.331739 49.176552,-0.331164 49.17672,-0.330778 49.174702,-0.330723 49.173986,-0.330867 49.174004,-0.330855 49.172347,-0.331116 49.172435,-0.33131 49.167625,-0.331918 49.167564,-0.334021 49.167298,-0.335878 49.167077,-0.336404 49.166992,-0.337252 49.166742,-0.338364 49.166484,-0.339238 49.166258,-0.340235 49.165969,-0.341561 49.165554,-0.341719 49.165471,-0.342581 49.164769,-0.343149 49.164344,-0.342781 49.164141,-0.340218 49.162523,-0.339193 49.161854,-0.339039 49.161659,-0.340669 49.160344,-0.338979 49.159321,-0.337774 49.158528,-0.337291 49.158221,-0.338395 49.157604,-0.339418 49.157044,-0.34047 49.156454,-0.338851 49.155275,-0.338904 49.155201,-0.339124 49.155208,-0.339376 49.155121,-0.340973 49.154184,-0.341555 49.153867,-0.342901 49.153089,-0.343146 49.153021)))"
]

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(((-0.343146 49.153021,-0.344011 49.154516,-0.344181 49.154826,-0.344692 49.155692,-0.347301 49.160279,-0.34893 49.163043,-0.349032 49.163011,-0.351206 49.162423,-0.351183 49.162382,-0.352075 49.162259,-0.351638 49.161252,-0.351978 49.161109,-0.353842 49.160371,-0.354227 49.160278,-0.354771 49.160204,-0.355388 49.160075,-0.355862 49.160012,-0.35592 49.159931,-0.357276 49.159673,-0.358832 49.15942,-0.3614 49.158753,-0.361512 49.158932,-0.361559 49.159144,-0.361737 49.159545,-0.363491 49.159539,-0.365058 49.159653,-0.366113 49.159805,-0.36606 49.159913,-0.367161 49.160029,-0.367039 49.160245,-0.367126 49.160338,-0.367542 49.16042,-0.367154 49.161279,-0.366696 49.16238,-0.366369 49.163237,-0.365393 49.165588,-0.366551 49.165757,-0.367419 49.166573,-0.367734 49.166546,-0.368691 49.166488,-0.369021 49.166643,-0.369394 49.166596,-0.369707 49.166138,-0.369761 49.165831,-0.369993 49.165534,-0.36999 49.165111,-0.370525 49.164931,-0.370799 49.165029,-0.371088 49.165019,-0.371073 49.164813,-0.371237 49.164687,-0.371635 49.164569,-0.372195 49.16437,-0.37207 49.164245,-0.37222 49.163839,-0.372673 49.16391,-0.372823 49.163605,-0.373602 49.16323,-0.374657 49.163297,-0.375154 49.163278,-0.375636 49.163391,-0.37587 49.163396,-0.376201 49.163468,-0.376853 49.163244,-0.377097 49.163145,-0.377393 49.16296,-0.377597 49.162759,-0.377924 49.162747,-0.378455 49.162595,-0.378652 49.162572,-0.378968 49.162597,-0.379375 49.162736,-0.379864 49.162721,-0.380041 49.162913,-0.380275 49.162926,-0.380525 49.1628,-0.381006 49.162718,-0.381154 49.16272,-0.381607 49.162803,-0.38192 49.162749,-0.382432 49.162812,-0.382874 49.162905,-0.383183 49.16292,-0.38365 49.162896,-0.384223 49.162986,-0.384519 49.162983,-0.385044 49.162871,-0.385204 49.162856,-0.385761 49.162861,-0.386686 49.162893,-0.386863 49.162874,-0.386989 49.162917,-0.38719 49.163165,-0.387348 49.163199,-0.38757 49.163161,-0.387746 49.163265,-0.388392 49.163499,-0.388508 49.163568,-0.388629 49.16384,-0.388737 49.163873,-0.389417 49.163893,-0.39016 49.163853,-0.39037 49.163885,-0.390521 49.163865,-0.39071 49.163755,-0.391104 49.163965,-0.391283 49.163985,-0.391479 49.163846,-0.391637 49.163871,-0.391718 49.164039,-0.391825 49.164133,-0.392344 49.164219,-0.392396 49.164333,-0.392202 49.164534,-0.39224 49.164675,-0.392467 49.164717,-0.393401 49.164738,-0.393628 49.164797,-0.393711 49.164874,-0.393734 49.16506,-0.393817 49.165238,-0.394168 49.165488,-0.394195 49.16558,-0.394095 49.165813,-0.394206 49.16592,-0.394488 49.165935,-0.394589 49.166029,-0.394382 49.166159,-0.394341 49.166304,-0.394475 49.166484,-0.393989 49.166479,-0.39469 49.167315,-0.3949 49.167642,-0.394757 49.168203,-0.395434 49.168118,-0.395943 49.168145,-0.396734 49.16824,-0.397261 49.168362,-0.397744 49.168449,-0.398263 49.168501,-0.398764 49.168427,-0.398954 49.168427,-0.399842 49.168505,-0.400501 49.168588,-0.40165 49.168642,-0.401843 49.168619,-0.401886 49.168808,-0.401024 49.168842,-0.400668 49.16884,-0.40121 49.169022,-0.401896 49.169166,-0.402531 49.169331,-0.403311 49.169584,-0.403986 49.169882,-0.404946 49.170359,-0.405729 49.170854,-0.405906 49.171005,-0.405729 49.171154,-0.405681 49.171548,-0.405555 49.171794,-0.405382 49.171992,-0.405203 49.172314,-0.40495 49.172967,-0.404685 49.173178,-0.404534 49.173765,-0.404367 49.174181,-0.404116 49.174567,-0.40379 49.174912,-0.403702 49.175109,-0.40359 49.175746,-0.403432 49.176022,-0.403399 49.176207,-0.403281 49.176537,-0.403245 49.176908,-0.403307 49.177189,-0.40333 49.177451,-0.40444 49.177656,-0.40472 49.177728,-0.405289 49.177978,-0.405641 49.178103,-0.405932 49.178236,-0.406305 49.178545,-0.406746 49.179013,-0.406846 49.179249,-0.407072 49.179685,-0.407946 49.180449,-0.408281 49.180799,-0.408518 49.181075,-0.408889 49.18157,-0.409218 49.181945,-0.409854 49.182559,-0.410279 49.182858,-0.412371 49.184264,-0.413763 49.186887,-0.412616 49.187001,-0.412809 49.188706,-0.412489 49.188556,-0.412156 49.188527,-0.409998 49.188281,-0.40909 49.188192,-0.407886 49.188045,-0.407398 49.187971,-0.407077 49.188342,-0.406746 49.188311,-0.406719 49.188541,-0.407197 49.188564,-0.407227 49.18886,-0.407341 49.189169,-0.407928 49.190529,-0.408202 49.191003,-0.40861 49.191484,-0.40909 49.192025,-0.409805 49.192863,-0.409979 49.193105,-0.410316 49.193419,-0.411494 49.194623,-0.411607 49.194757,-0.410534 49.195615,-0.409145 49.196516,-0.409262 49.196648,-0.408165 49.197343,-0.407975 49.197476,-0.407325 49.197846,-0.405263 49.198991,-0.404332 49.199485,-0.402052 49.200619,-0.401954 49.200659,-0.401183 49.200203,-0.40032 49.199734,-0.39942 49.199281,-0.397454 49.198391,-0.397188 49.198281,-0.396829 49.198203,-0.396746 49.198752,-0.396663 49.199062,-0.396426 49.199662,-0.396236 49.200002,-0.395857 49.200533,-0.395425 49.201011,-0.395305 49.201177,-0.394933 49.201556,-0.394697 49.201752,-0.394144 49.202128,-0.393541 49.202435,-0.393163 49.202584,-0.39252 49.202782,-0.391887 49.202914,-0.391442 49.203031,-0.391186 49.203869,-0.391082 49.204057,-0.390728 49.204403,-0.389685 49.205145,-0.388482 49.206091,-0.388227 49.206312,-0.387557 49.20685,-0.386665 49.207473,-0.386334 49.207729,-0.38605 49.207978,-0.384875 49.20887,-0.383782 49.209603,-0.383167 49.209996,-0.382451 49.210403,-0.381778 49.210736,-0.380578 49.211298,-0.380243 49.211425,-0.379901 49.211504,-0.378835 49.21172,-0.379058 49.211978,-0.378217 49.212597,-0.377878 49.213425,-0.377715 49.213653,-0.377597 49.213704,-0.377069 49.213713,-0.376573 49.213666,-0.376292 49.213588,-0.376093 49.213423,-0.375643 49.21363,-0.374589 49.214191,-0.373625 49.214734,-0.372699 49.215311,-0.3723 49.215487,-0.371873 49.215613,-0.370692 49.215999,-0.370054 49.216261,-0.368957 49.215884,-0.365025 49.214884,-0.364905 49.2146,-0.364524 49.214546,-0.363568 49.214325,-0.3637 49.214146,-0.363592 49.214071,-0.363135 49.213873,-0.3625 49.213639,-0.361502 49.213233,-0.361219 49.213134,-0.360518 49.212838,-0.360234 49.212734,-0.360181 49.212784,-0.359919 49.212664,-0.359617 49.212593,-0.358595 49.212293,-0.357978 49.212082,-0.357423 49.211829,-0.356762 49.211478,-0.356263 49.211078,-0.355031 49.209813,-0.355728 49.208292,-0.355767 49.208181,-0.353922 49.207679,-0.353608 49.208073,-0.353316 49.208483,-0.351284 49.207635,-0.352076 49.20696,-0.351265 49.206518,-0.350551 49.206207,-0.350812 49.205853,-0.35085 49.205665,-0.350667 49.205545,-0.351045 49.205164,-0.350122 49.20522,-0.349938 49.205186,-0.348881 49.204909,-0.349198 49.204606,-0.347589 49.203523,-0.348851 49.202544,-0.349741 49.201869,-0.350068 49.201654,-0.350394 49.20154,-0.349134 49.20117,-0.348494 49.200964,-0.347042 49.200447,-0.346374 49.200197,-0.344619 49.199462,-0.343931 49.19915,-0.343267 49.198833,-0.342701 49.198549,-0.341533 49.197911,-0.340977 49.19757,-0.340484 49.197241,-0.339456 49.19649,-0.338403 49.195574,-0.337975 49.195154,-0.336097 49.193225,-0.335923 49.193036,-0.336862 49.192373,-0.337407 49.192164,-0.337719 49.191952,-0.338017 49.191699,-0.337283 49.190903,-0.336465 49.190027,-0.336125 49.189505,-0.336029 49.189234,-0.335885 49.188975,-0.335359 49.188303,-0.334856 49.187763,-0.334714 49.187643,-0.333644 49.185865,-0.333629 49.185828,-0.334197 49.185776,-0.334756 49.185657,-0.335023 49.185534,-0.335207 49.185365,-0.335304 49.18521,-0.335244 49.184828,-0.334687 49.183541,-0.334614 49.183232,-0.334723 49.182903,-0.335042 49.182501,-0.335426 49.182185,-0.334914 49.18139,-0.334336 49.18154,-0.333924 49.180556,-0.33423 49.180556,-0.333433 49.179282,-0.333016 49.179351,-0.332609 49.177461,-0.333265 49.177257,-0.333639 49.177157,-0.333986 49.17709,-0.33384 49.176727,-0.331787 49.177315,-0.331223 49.177385,-0.331235 49.176979,-0.331936 49.176823,-0.331739 49.176552,-0.331164 49.17672,-0.330778 49.174702,-0.330723 49.173986,-0.330867 49.174004,-0.330855 49.172347,-0.331116 49.172435,-0.33131 49.167625,-0.331918 49.167564,-0.334021 49.167298,-0.335878 49.167077,-0.336404 49.166992,-0.337252 49.166742,-0.338364 49.166484,-0.339238 49.166258,-0.340235 49.165969,-0.341561 49.165554,-0.341719 49.165471,-0.342581 49.164769,-0.343149 49.164344,-0.342781 49.164141,-0.340218 49.162523,-0.339193 49.161854,-0.339039 49.161659,-0.340669 49.160344,-0.338979 49.159321,-0.337774 49.158528,-0.337291 49.158221,-0.338395 49.157604,-0.339418 49.157044,-0.34047 49.156454,-0.338851 49.155275,-0.338904 49.155201,-0.339124 49.155208,-0.339376 49.155121,-0.340973 49.154184,-0.341555 49.153867,-0.342901 49.153089,-0.343146 49.153021)))', 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.88 ms
(0.17%)

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(((-0.343146 49.153021,-0.344011 49.154516,-0.344181 49.154826,-0.344692 49.155692,-0.347301 49.160279,-0.34893 49.163043,-0.349032 49.163011,-0.351206 49.162423,-0.351183 49.162382,-0.352075 49.162259,-0.351638 49.161252,-0.351978 49.161109,-0.353842 49.160371,-0.354227 49.160278,-0.354771 49.160204,-0.355388 49.160075,-0.355862 49.160012,-0.35592 49.159931,-0.357276 49.159673,-0.358832 49.15942,-0.3614 49.158753,-0.361512 49.158932,-0.361559 49.159144,-0.361737 49.159545,-0.363491 49.159539,-0.365058 49.159653,-0.366113 49.159805,-0.36606 49.159913,-0.367161 49.160029,-0.367039 49.160245,-0.367126 49.160338,-0.367542 49.16042,-0.367154 49.161279,-0.366696 49.16238,-0.366369 49.163237,-0.365393 49.165588,-0.366551 49.165757,-0.367419 49.166573,-0.367734 49.166546,-0.368691 49.166488,-0.369021 49.166643,-0.369394 49.166596,-0.369707 49.166138,-0.369761 49.165831,-0.369993 49.165534,-0.36999 49.165111,-0.370525 49.164931,-0.370799 49.165029,-0.371088 49.165019,-0.371073 49.164813,-0.371237 49.164687,-0.371635 49.164569,-0.372195 49.16437,-0.37207 49.164245,-0.37222 49.163839,-0.372673 49.16391,-0.372823 49.163605,-0.373602 49.16323,-0.374657 49.163297,-0.375154 49.163278,-0.375636 49.163391,-0.37587 49.163396,-0.376201 49.163468,-0.376853 49.163244,-0.377097 49.163145,-0.377393 49.16296,-0.377597 49.162759,-0.377924 49.162747,-0.378455 49.162595,-0.378652 49.162572,-0.378968 49.162597,-0.379375 49.162736,-0.379864 49.162721,-0.380041 49.162913,-0.380275 49.162926,-0.380525 49.1628,-0.381006 49.162718,-0.381154 49.16272,-0.381607 49.162803,-0.38192 49.162749,-0.382432 49.162812,-0.382874 49.162905,-0.383183 49.16292,-0.38365 49.162896,-0.384223 49.162986,-0.384519 49.162983,-0.385044 49.162871,-0.385204 49.162856,-0.385761 49.162861,-0.386686 49.162893,-0.386863 49.162874,-0.386989 49.162917,-0.38719 49.163165,-0.387348 49.163199,-0.38757 49.163161,-0.387746 49.163265,-0.388392 49.163499,-0.388508 49.163568,-0.388629 49.16384,-0.388737 49.163873,-0.389417 49.163893,-0.39016 49.163853,-0.39037 49.163885,-0.390521 49.163865,-0.39071 49.163755,-0.391104 49.163965,-0.391283 49.163985,-0.391479 49.163846,-0.391637 49.163871,-0.391718 49.164039,-0.391825 49.164133,-0.392344 49.164219,-0.392396 49.164333,-0.392202 49.164534,-0.39224 49.164675,-0.392467 49.164717,-0.393401 49.164738,-0.393628 49.164797,-0.393711 49.164874,-0.393734 49.16506,-0.393817 49.165238,-0.394168 49.165488,-0.394195 49.16558,-0.394095 49.165813,-0.394206 49.16592,-0.394488 49.165935,-0.394589 49.166029,-0.394382 49.166159,-0.394341 49.166304,-0.394475 49.166484,-0.393989 49.166479,-0.39469 49.167315,-0.3949 49.167642,-0.394757 49.168203,-0.395434 49.168118,-0.395943 49.168145,-0.396734 49.16824,-0.397261 49.168362,-0.397744 49.168449,-0.398263 49.168501,-0.398764 49.168427,-0.398954 49.168427,-0.399842 49.168505,-0.400501 49.168588,-0.40165 49.168642,-0.401843 49.168619,-0.401886 49.168808,-0.401024 49.168842,-0.400668 49.16884,-0.40121 49.169022,-0.401896 49.169166,-0.402531 49.169331,-0.403311 49.169584,-0.403986 49.169882,-0.404946 49.170359,-0.405729 49.170854,-0.405906 49.171005,-0.405729 49.171154,-0.405681 49.171548,-0.405555 49.171794,-0.405382 49.171992,-0.405203 49.172314,-0.40495 49.172967,-0.404685 49.173178,-0.404534 49.173765,-0.404367 49.174181,-0.404116 49.174567,-0.40379 49.174912,-0.403702 49.175109,-0.40359 49.175746,-0.403432 49.176022,-0.403399 49.176207,-0.403281 49.176537,-0.403245 49.176908,-0.403307 49.177189,-0.40333 49.177451,-0.40444 49.177656,-0.40472 49.177728,-0.405289 49.177978,-0.405641 49.178103,-0.405932 49.178236,-0.406305 49.178545,-0.406746 49.179013,-0.406846 49.179249,-0.407072 49.179685,-0.407946 49.180449,-0.408281 49.180799,-0.408518 49.181075,-0.408889 49.18157,-0.409218 49.181945,-0.409854 49.182559,-0.410279 49.182858,-0.412371 49.184264,-0.413763 49.186887,-0.412616 49.187001,-0.412809 49.188706,-0.412489 49.188556,-0.412156 49.188527,-0.409998 49.188281,-0.40909 49.188192,-0.407886 49.188045,-0.407398 49.187971,-0.407077 49.188342,-0.406746 49.188311,-0.406719 49.188541,-0.407197 49.188564,-0.407227 49.18886,-0.407341 49.189169,-0.407928 49.190529,-0.408202 49.191003,-0.40861 49.191484,-0.40909 49.192025,-0.409805 49.192863,-0.409979 49.193105,-0.410316 49.193419,-0.411494 49.194623,-0.411607 49.194757,-0.410534 49.195615,-0.409145 49.196516,-0.409262 49.196648,-0.408165 49.197343,-0.407975 49.197476,-0.407325 49.197846,-0.405263 49.198991,-0.404332 49.199485,-0.402052 49.200619,-0.401954 49.200659,-0.401183 49.200203,-0.40032 49.199734,-0.39942 49.199281,-0.397454 49.198391,-0.397188 49.198281,-0.396829 49.198203,-0.396746 49.198752,-0.396663 49.199062,-0.396426 49.199662,-0.396236 49.200002,-0.395857 49.200533,-0.395425 49.201011,-0.395305 49.201177,-0.394933 49.201556,-0.394697 49.201752,-0.394144 49.202128,-0.393541 49.202435,-0.393163 49.202584,-0.39252 49.202782,-0.391887 49.202914,-0.391442 49.203031,-0.391186 49.203869,-0.391082 49.204057,-0.390728 49.204403,-0.389685 49.205145,-0.388482 49.206091,-0.388227 49.206312,-0.387557 49.20685,-0.386665 49.207473,-0.386334 49.207729,-0.38605 49.207978,-0.384875 49.20887,-0.383782 49.209603,-0.383167 49.209996,-0.382451 49.210403,-0.381778 49.210736,-0.380578 49.211298,-0.380243 49.211425,-0.379901 49.211504,-0.378835 49.21172,-0.379058 49.211978,-0.378217 49.212597,-0.377878 49.213425,-0.377715 49.213653,-0.377597 49.213704,-0.377069 49.213713,-0.376573 49.213666,-0.376292 49.213588,-0.376093 49.213423,-0.375643 49.21363,-0.374589 49.214191,-0.373625 49.214734,-0.372699 49.215311,-0.3723 49.215487,-0.371873 49.215613,-0.370692 49.215999,-0.370054 49.216261,-0.368957 49.215884,-0.365025 49.214884,-0.364905 49.2146,-0.364524 49.214546,-0.363568 49.214325,-0.3637 49.214146,-0.363592 49.214071,-0.363135 49.213873,-0.3625 49.213639,-0.361502 49.213233,-0.361219 49.213134,-0.360518 49.212838,-0.360234 49.212734,-0.360181 49.212784,-0.359919 49.212664,-0.359617 49.212593,-0.358595 49.212293,-0.357978 49.212082,-0.357423 49.211829,-0.356762 49.211478,-0.356263 49.211078,-0.355031 49.209813,-0.355728 49.208292,-0.355767 49.208181,-0.353922 49.207679,-0.353608 49.208073,-0.353316 49.208483,-0.351284 49.207635,-0.352076 49.20696,-0.351265 49.206518,-0.350551 49.206207,-0.350812 49.205853,-0.35085 49.205665,-0.350667 49.205545,-0.351045 49.205164,-0.350122 49.20522,-0.349938 49.205186,-0.348881 49.204909,-0.349198 49.204606,-0.347589 49.203523,-0.348851 49.202544,-0.349741 49.201869,-0.350068 49.201654,-0.350394 49.20154,-0.349134 49.20117,-0.348494 49.200964,-0.347042 49.200447,-0.346374 49.200197,-0.344619 49.199462,-0.343931 49.19915,-0.343267 49.198833,-0.342701 49.198549,-0.341533 49.197911,-0.340977 49.19757,-0.340484 49.197241,-0.339456 49.19649,-0.338403 49.195574,-0.337975 49.195154,-0.336097 49.193225,-0.335923 49.193036,-0.336862 49.192373,-0.337407 49.192164,-0.337719 49.191952,-0.338017 49.191699,-0.337283 49.190903,-0.336465 49.190027,-0.336125 49.189505,-0.336029 49.189234,-0.335885 49.188975,-0.335359 49.188303,-0.334856 49.187763,-0.334714 49.187643,-0.333644 49.185865,-0.333629 49.185828,-0.334197 49.185776,-0.334756 49.185657,-0.335023 49.185534,-0.335207 49.185365,-0.335304 49.18521,-0.335244 49.184828,-0.334687 49.183541,-0.334614 49.183232,-0.334723 49.182903,-0.335042 49.182501,-0.335426 49.182185,-0.334914 49.18139,-0.334336 49.18154,-0.333924 49.180556,-0.33423 49.180556,-0.333433 49.179282,-0.333016 49.179351,-0.332609 49.177461,-0.333265 49.177257,-0.333639 49.177157,-0.333986 49.17709,-0.33384 49.176727,-0.331787 49.177315,-0.331223 49.177385,-0.331235 49.176979,-0.331936 49.176823,-0.331739 49.176552,-0.331164 49.17672,-0.330778 49.174702,-0.330723 49.173986,-0.330867 49.174004,-0.330855 49.172347,-0.331116 49.172435,-0.33131 49.167625,-0.331918 49.167564,-0.334021 49.167298,-0.335878 49.167077,-0.336404 49.166992,-0.337252 49.166742,-0.338364 49.166484,-0.339238 49.166258,-0.340235 49.165969,-0.341561 49.165554,-0.341719 49.165471,-0.342581 49.164769,-0.343149 49.164344,-0.342781 49.164141,-0.340218 49.162523,-0.339193 49.161854,-0.339039 49.161659,-0.340669 49.160344,-0.338979 49.159321,-0.337774 49.158528,-0.337291 49.158221,-0.338395 49.157604,-0.339418 49.157044,-0.34047 49.156454,-0.338851 49.155275,-0.338904 49.155201,-0.339124 49.155208,-0.339376 49.155121,-0.340973 49.154184,-0.341555 49.153867,-0.342901 49.153089,-0.343146 49.153021)))"
]

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(((-0.343146 49.153021,-0.344011 49.154516,-0.344181 49.154826,-0.344692 49.155692,-0.347301 49.160279,-0.34893 49.163043,-0.349032 49.163011,-0.351206 49.162423,-0.351183 49.162382,-0.352075 49.162259,-0.351638 49.161252,-0.351978 49.161109,-0.353842 49.160371,-0.354227 49.160278,-0.354771 49.160204,-0.355388 49.160075,-0.355862 49.160012,-0.35592 49.159931,-0.357276 49.159673,-0.358832 49.15942,-0.3614 49.158753,-0.361512 49.158932,-0.361559 49.159144,-0.361737 49.159545,-0.363491 49.159539,-0.365058 49.159653,-0.366113 49.159805,-0.36606 49.159913,-0.367161 49.160029,-0.367039 49.160245,-0.367126 49.160338,-0.367542 49.16042,-0.367154 49.161279,-0.366696 49.16238,-0.366369 49.163237,-0.365393 49.165588,-0.366551 49.165757,-0.367419 49.166573,-0.367734 49.166546,-0.368691 49.166488,-0.369021 49.166643,-0.369394 49.166596,-0.369707 49.166138,-0.369761 49.165831,-0.369993 49.165534,-0.36999 49.165111,-0.370525 49.164931,-0.370799 49.165029,-0.371088 49.165019,-0.371073 49.164813,-0.371237 49.164687,-0.371635 49.164569,-0.372195 49.16437,-0.37207 49.164245,-0.37222 49.163839,-0.372673 49.16391,-0.372823 49.163605,-0.373602 49.16323,-0.374657 49.163297,-0.375154 49.163278,-0.375636 49.163391,-0.37587 49.163396,-0.376201 49.163468,-0.376853 49.163244,-0.377097 49.163145,-0.377393 49.16296,-0.377597 49.162759,-0.377924 49.162747,-0.378455 49.162595,-0.378652 49.162572,-0.378968 49.162597,-0.379375 49.162736,-0.379864 49.162721,-0.380041 49.162913,-0.380275 49.162926,-0.380525 49.1628,-0.381006 49.162718,-0.381154 49.16272,-0.381607 49.162803,-0.38192 49.162749,-0.382432 49.162812,-0.382874 49.162905,-0.383183 49.16292,-0.38365 49.162896,-0.384223 49.162986,-0.384519 49.162983,-0.385044 49.162871,-0.385204 49.162856,-0.385761 49.162861,-0.386686 49.162893,-0.386863 49.162874,-0.386989 49.162917,-0.38719 49.163165,-0.387348 49.163199,-0.38757 49.163161,-0.387746 49.163265,-0.388392 49.163499,-0.388508 49.163568,-0.388629 49.16384,-0.388737 49.163873,-0.389417 49.163893,-0.39016 49.163853,-0.39037 49.163885,-0.390521 49.163865,-0.39071 49.163755,-0.391104 49.163965,-0.391283 49.163985,-0.391479 49.163846,-0.391637 49.163871,-0.391718 49.164039,-0.391825 49.164133,-0.392344 49.164219,-0.392396 49.164333,-0.392202 49.164534,-0.39224 49.164675,-0.392467 49.164717,-0.393401 49.164738,-0.393628 49.164797,-0.393711 49.164874,-0.393734 49.16506,-0.393817 49.165238,-0.394168 49.165488,-0.394195 49.16558,-0.394095 49.165813,-0.394206 49.16592,-0.394488 49.165935,-0.394589 49.166029,-0.394382 49.166159,-0.394341 49.166304,-0.394475 49.166484,-0.393989 49.166479,-0.39469 49.167315,-0.3949 49.167642,-0.394757 49.168203,-0.395434 49.168118,-0.395943 49.168145,-0.396734 49.16824,-0.397261 49.168362,-0.397744 49.168449,-0.398263 49.168501,-0.398764 49.168427,-0.398954 49.168427,-0.399842 49.168505,-0.400501 49.168588,-0.40165 49.168642,-0.401843 49.168619,-0.401886 49.168808,-0.401024 49.168842,-0.400668 49.16884,-0.40121 49.169022,-0.401896 49.169166,-0.402531 49.169331,-0.403311 49.169584,-0.403986 49.169882,-0.404946 49.170359,-0.405729 49.170854,-0.405906 49.171005,-0.405729 49.171154,-0.405681 49.171548,-0.405555 49.171794,-0.405382 49.171992,-0.405203 49.172314,-0.40495 49.172967,-0.404685 49.173178,-0.404534 49.173765,-0.404367 49.174181,-0.404116 49.174567,-0.40379 49.174912,-0.403702 49.175109,-0.40359 49.175746,-0.403432 49.176022,-0.403399 49.176207,-0.403281 49.176537,-0.403245 49.176908,-0.403307 49.177189,-0.40333 49.177451,-0.40444 49.177656,-0.40472 49.177728,-0.405289 49.177978,-0.405641 49.178103,-0.405932 49.178236,-0.406305 49.178545,-0.406746 49.179013,-0.406846 49.179249,-0.407072 49.179685,-0.407946 49.180449,-0.408281 49.180799,-0.408518 49.181075,-0.408889 49.18157,-0.409218 49.181945,-0.409854 49.182559,-0.410279 49.182858,-0.412371 49.184264,-0.413763 49.186887,-0.412616 49.187001,-0.412809 49.188706,-0.412489 49.188556,-0.412156 49.188527,-0.409998 49.188281,-0.40909 49.188192,-0.407886 49.188045,-0.407398 49.187971,-0.407077 49.188342,-0.406746 49.188311,-0.406719 49.188541,-0.407197 49.188564,-0.407227 49.18886,-0.407341 49.189169,-0.407928 49.190529,-0.408202 49.191003,-0.40861 49.191484,-0.40909 49.192025,-0.409805 49.192863,-0.409979 49.193105,-0.410316 49.193419,-0.411494 49.194623,-0.411607 49.194757,-0.410534 49.195615,-0.409145 49.196516,-0.409262 49.196648,-0.408165 49.197343,-0.407975 49.197476,-0.407325 49.197846,-0.405263 49.198991,-0.404332 49.199485,-0.402052 49.200619,-0.401954 49.200659,-0.401183 49.200203,-0.40032 49.199734,-0.39942 49.199281,-0.397454 49.198391,-0.397188 49.198281,-0.396829 49.198203,-0.396746 49.198752,-0.396663 49.199062,-0.396426 49.199662,-0.396236 49.200002,-0.395857 49.200533,-0.395425 49.201011,-0.395305 49.201177,-0.394933 49.201556,-0.394697 49.201752,-0.394144 49.202128,-0.393541 49.202435,-0.393163 49.202584,-0.39252 49.202782,-0.391887 49.202914,-0.391442 49.203031,-0.391186 49.203869,-0.391082 49.204057,-0.390728 49.204403,-0.389685 49.205145,-0.388482 49.206091,-0.388227 49.206312,-0.387557 49.20685,-0.386665 49.207473,-0.386334 49.207729,-0.38605 49.207978,-0.384875 49.20887,-0.383782 49.209603,-0.383167 49.209996,-0.382451 49.210403,-0.381778 49.210736,-0.380578 49.211298,-0.380243 49.211425,-0.379901 49.211504,-0.378835 49.21172,-0.379058 49.211978,-0.378217 49.212597,-0.377878 49.213425,-0.377715 49.213653,-0.377597 49.213704,-0.377069 49.213713,-0.376573 49.213666,-0.376292 49.213588,-0.376093 49.213423,-0.375643 49.21363,-0.374589 49.214191,-0.373625 49.214734,-0.372699 49.215311,-0.3723 49.215487,-0.371873 49.215613,-0.370692 49.215999,-0.370054 49.216261,-0.368957 49.215884,-0.365025 49.214884,-0.364905 49.2146,-0.364524 49.214546,-0.363568 49.214325,-0.3637 49.214146,-0.363592 49.214071,-0.363135 49.213873,-0.3625 49.213639,-0.361502 49.213233,-0.361219 49.213134,-0.360518 49.212838,-0.360234 49.212734,-0.360181 49.212784,-0.359919 49.212664,-0.359617 49.212593,-0.358595 49.212293,-0.357978 49.212082,-0.357423 49.211829,-0.356762 49.211478,-0.356263 49.211078,-0.355031 49.209813,-0.355728 49.208292,-0.355767 49.208181,-0.353922 49.207679,-0.353608 49.208073,-0.353316 49.208483,-0.351284 49.207635,-0.352076 49.20696,-0.351265 49.206518,-0.350551 49.206207,-0.350812 49.205853,-0.35085 49.205665,-0.350667 49.205545,-0.351045 49.205164,-0.350122 49.20522,-0.349938 49.205186,-0.348881 49.204909,-0.349198 49.204606,-0.347589 49.203523,-0.348851 49.202544,-0.349741 49.201869,-0.350068 49.201654,-0.350394 49.20154,-0.349134 49.20117,-0.348494 49.200964,-0.347042 49.200447,-0.346374 49.200197,-0.344619 49.199462,-0.343931 49.19915,-0.343267 49.198833,-0.342701 49.198549,-0.341533 49.197911,-0.340977 49.19757,-0.340484 49.197241,-0.339456 49.19649,-0.338403 49.195574,-0.337975 49.195154,-0.336097 49.193225,-0.335923 49.193036,-0.336862 49.192373,-0.337407 49.192164,-0.337719 49.191952,-0.338017 49.191699,-0.337283 49.190903,-0.336465 49.190027,-0.336125 49.189505,-0.336029 49.189234,-0.335885 49.188975,-0.335359 49.188303,-0.334856 49.187763,-0.334714 49.187643,-0.333644 49.185865,-0.333629 49.185828,-0.334197 49.185776,-0.334756 49.185657,-0.335023 49.185534,-0.335207 49.185365,-0.335304 49.18521,-0.335244 49.184828,-0.334687 49.183541,-0.334614 49.183232,-0.334723 49.182903,-0.335042 49.182501,-0.335426 49.182185,-0.334914 49.18139,-0.334336 49.18154,-0.333924 49.180556,-0.33423 49.180556,-0.333433 49.179282,-0.333016 49.179351,-0.332609 49.177461,-0.333265 49.177257,-0.333639 49.177157,-0.333986 49.17709,-0.33384 49.176727,-0.331787 49.177315,-0.331223 49.177385,-0.331235 49.176979,-0.331936 49.176823,-0.331739 49.176552,-0.331164 49.17672,-0.330778 49.174702,-0.330723 49.173986,-0.330867 49.174004,-0.330855 49.172347,-0.331116 49.172435,-0.33131 49.167625,-0.331918 49.167564,-0.334021 49.167298,-0.335878 49.167077,-0.336404 49.166992,-0.337252 49.166742,-0.338364 49.166484,-0.339238 49.166258,-0.340235 49.165969,-0.341561 49.165554,-0.341719 49.165471,-0.342581 49.164769,-0.343149 49.164344,-0.342781 49.164141,-0.340218 49.162523,-0.339193 49.161854,-0.339039 49.161659,-0.340669 49.160344,-0.338979 49.159321,-0.337774 49.158528,-0.337291 49.158221,-0.338395 49.157604,-0.339418 49.157044,-0.34047 49.156454,-0.338851 49.155275,-0.338904 49.155201,-0.339124 49.155208,-0.339376 49.155121,-0.340973 49.154184,-0.341555 49.153867,-0.342901 49.153089,-0.343146 49.153021)))', 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.85 ms
(0.17%)

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(((-0.343146 49.153021,-0.344011 49.154516,-0.344181 49.154826,-0.344692 49.155692,-0.347301 49.160279,-0.34893 49.163043,-0.349032 49.163011,-0.351206 49.162423,-0.351183 49.162382,-0.352075 49.162259,-0.351638 49.161252,-0.351978 49.161109,-0.353842 49.160371,-0.354227 49.160278,-0.354771 49.160204,-0.355388 49.160075,-0.355862 49.160012,-0.35592 49.159931,-0.357276 49.159673,-0.358832 49.15942,-0.3614 49.158753,-0.361512 49.158932,-0.361559 49.159144,-0.361737 49.159545,-0.363491 49.159539,-0.365058 49.159653,-0.366113 49.159805,-0.36606 49.159913,-0.367161 49.160029,-0.367039 49.160245,-0.367126 49.160338,-0.367542 49.16042,-0.367154 49.161279,-0.366696 49.16238,-0.366369 49.163237,-0.365393 49.165588,-0.366551 49.165757,-0.367419 49.166573,-0.367734 49.166546,-0.368691 49.166488,-0.369021 49.166643,-0.369394 49.166596,-0.369707 49.166138,-0.369761 49.165831,-0.369993 49.165534,-0.36999 49.165111,-0.370525 49.164931,-0.370799 49.165029,-0.371088 49.165019,-0.371073 49.164813,-0.371237 49.164687,-0.371635 49.164569,-0.372195 49.16437,-0.37207 49.164245,-0.37222 49.163839,-0.372673 49.16391,-0.372823 49.163605,-0.373602 49.16323,-0.374657 49.163297,-0.375154 49.163278,-0.375636 49.163391,-0.37587 49.163396,-0.376201 49.163468,-0.376853 49.163244,-0.377097 49.163145,-0.377393 49.16296,-0.377597 49.162759,-0.377924 49.162747,-0.378455 49.162595,-0.378652 49.162572,-0.378968 49.162597,-0.379375 49.162736,-0.379864 49.162721,-0.380041 49.162913,-0.380275 49.162926,-0.380525 49.1628,-0.381006 49.162718,-0.381154 49.16272,-0.381607 49.162803,-0.38192 49.162749,-0.382432 49.162812,-0.382874 49.162905,-0.383183 49.16292,-0.38365 49.162896,-0.384223 49.162986,-0.384519 49.162983,-0.385044 49.162871,-0.385204 49.162856,-0.385761 49.162861,-0.386686 49.162893,-0.386863 49.162874,-0.386989 49.162917,-0.38719 49.163165,-0.387348 49.163199,-0.38757 49.163161,-0.387746 49.163265,-0.388392 49.163499,-0.388508 49.163568,-0.388629 49.16384,-0.388737 49.163873,-0.389417 49.163893,-0.39016 49.163853,-0.39037 49.163885,-0.390521 49.163865,-0.39071 49.163755,-0.391104 49.163965,-0.391283 49.163985,-0.391479 49.163846,-0.391637 49.163871,-0.391718 49.164039,-0.391825 49.164133,-0.392344 49.164219,-0.392396 49.164333,-0.392202 49.164534,-0.39224 49.164675,-0.392467 49.164717,-0.393401 49.164738,-0.393628 49.164797,-0.393711 49.164874,-0.393734 49.16506,-0.393817 49.165238,-0.394168 49.165488,-0.394195 49.16558,-0.394095 49.165813,-0.394206 49.16592,-0.394488 49.165935,-0.394589 49.166029,-0.394382 49.166159,-0.394341 49.166304,-0.394475 49.166484,-0.393989 49.166479,-0.39469 49.167315,-0.3949 49.167642,-0.394757 49.168203,-0.395434 49.168118,-0.395943 49.168145,-0.396734 49.16824,-0.397261 49.168362,-0.397744 49.168449,-0.398263 49.168501,-0.398764 49.168427,-0.398954 49.168427,-0.399842 49.168505,-0.400501 49.168588,-0.40165 49.168642,-0.401843 49.168619,-0.401886 49.168808,-0.401024 49.168842,-0.400668 49.16884,-0.40121 49.169022,-0.401896 49.169166,-0.402531 49.169331,-0.403311 49.169584,-0.403986 49.169882,-0.404946 49.170359,-0.405729 49.170854,-0.405906 49.171005,-0.405729 49.171154,-0.405681 49.171548,-0.405555 49.171794,-0.405382 49.171992,-0.405203 49.172314,-0.40495 49.172967,-0.404685 49.173178,-0.404534 49.173765,-0.404367 49.174181,-0.404116 49.174567,-0.40379 49.174912,-0.403702 49.175109,-0.40359 49.175746,-0.403432 49.176022,-0.403399 49.176207,-0.403281 49.176537,-0.403245 49.176908,-0.403307 49.177189,-0.40333 49.177451,-0.40444 49.177656,-0.40472 49.177728,-0.405289 49.177978,-0.405641 49.178103,-0.405932 49.178236,-0.406305 49.178545,-0.406746 49.179013,-0.406846 49.179249,-0.407072 49.179685,-0.407946 49.180449,-0.408281 49.180799,-0.408518 49.181075,-0.408889 49.18157,-0.409218 49.181945,-0.409854 49.182559,-0.410279 49.182858,-0.412371 49.184264,-0.413763 49.186887,-0.412616 49.187001,-0.412809 49.188706,-0.412489 49.188556,-0.412156 49.188527,-0.409998 49.188281,-0.40909 49.188192,-0.407886 49.188045,-0.407398 49.187971,-0.407077 49.188342,-0.406746 49.188311,-0.406719 49.188541,-0.407197 49.188564,-0.407227 49.18886,-0.407341 49.189169,-0.407928 49.190529,-0.408202 49.191003,-0.40861 49.191484,-0.40909 49.192025,-0.409805 49.192863,-0.409979 49.193105,-0.410316 49.193419,-0.411494 49.194623,-0.411607 49.194757,-0.410534 49.195615,-0.409145 49.196516,-0.409262 49.196648,-0.408165 49.197343,-0.407975 49.197476,-0.407325 49.197846,-0.405263 49.198991,-0.404332 49.199485,-0.402052 49.200619,-0.401954 49.200659,-0.401183 49.200203,-0.40032 49.199734,-0.39942 49.199281,-0.397454 49.198391,-0.397188 49.198281,-0.396829 49.198203,-0.396746 49.198752,-0.396663 49.199062,-0.396426 49.199662,-0.396236 49.200002,-0.395857 49.200533,-0.395425 49.201011,-0.395305 49.201177,-0.394933 49.201556,-0.394697 49.201752,-0.394144 49.202128,-0.393541 49.202435,-0.393163 49.202584,-0.39252 49.202782,-0.391887 49.202914,-0.391442 49.203031,-0.391186 49.203869,-0.391082 49.204057,-0.390728 49.204403,-0.389685 49.205145,-0.388482 49.206091,-0.388227 49.206312,-0.387557 49.20685,-0.386665 49.207473,-0.386334 49.207729,-0.38605 49.207978,-0.384875 49.20887,-0.383782 49.209603,-0.383167 49.209996,-0.382451 49.210403,-0.381778 49.210736,-0.380578 49.211298,-0.380243 49.211425,-0.379901 49.211504,-0.378835 49.21172,-0.379058 49.211978,-0.378217 49.212597,-0.377878 49.213425,-0.377715 49.213653,-0.377597 49.213704,-0.377069 49.213713,-0.376573 49.213666,-0.376292 49.213588,-0.376093 49.213423,-0.375643 49.21363,-0.374589 49.214191,-0.373625 49.214734,-0.372699 49.215311,-0.3723 49.215487,-0.371873 49.215613,-0.370692 49.215999,-0.370054 49.216261,-0.368957 49.215884,-0.365025 49.214884,-0.364905 49.2146,-0.364524 49.214546,-0.363568 49.214325,-0.3637 49.214146,-0.363592 49.214071,-0.363135 49.213873,-0.3625 49.213639,-0.361502 49.213233,-0.361219 49.213134,-0.360518 49.212838,-0.360234 49.212734,-0.360181 49.212784,-0.359919 49.212664,-0.359617 49.212593,-0.358595 49.212293,-0.357978 49.212082,-0.357423 49.211829,-0.356762 49.211478,-0.356263 49.211078,-0.355031 49.209813,-0.355728 49.208292,-0.355767 49.208181,-0.353922 49.207679,-0.353608 49.208073,-0.353316 49.208483,-0.351284 49.207635,-0.352076 49.20696,-0.351265 49.206518,-0.350551 49.206207,-0.350812 49.205853,-0.35085 49.205665,-0.350667 49.205545,-0.351045 49.205164,-0.350122 49.20522,-0.349938 49.205186,-0.348881 49.204909,-0.349198 49.204606,-0.347589 49.203523,-0.348851 49.202544,-0.349741 49.201869,-0.350068 49.201654,-0.350394 49.20154,-0.349134 49.20117,-0.348494 49.200964,-0.347042 49.200447,-0.346374 49.200197,-0.344619 49.199462,-0.343931 49.19915,-0.343267 49.198833,-0.342701 49.198549,-0.341533 49.197911,-0.340977 49.19757,-0.340484 49.197241,-0.339456 49.19649,-0.338403 49.195574,-0.337975 49.195154,-0.336097 49.193225,-0.335923 49.193036,-0.336862 49.192373,-0.337407 49.192164,-0.337719 49.191952,-0.338017 49.191699,-0.337283 49.190903,-0.336465 49.190027,-0.336125 49.189505,-0.336029 49.189234,-0.335885 49.188975,-0.335359 49.188303,-0.334856 49.187763,-0.334714 49.187643,-0.333644 49.185865,-0.333629 49.185828,-0.334197 49.185776,-0.334756 49.185657,-0.335023 49.185534,-0.335207 49.185365,-0.335304 49.18521,-0.335244 49.184828,-0.334687 49.183541,-0.334614 49.183232,-0.334723 49.182903,-0.335042 49.182501,-0.335426 49.182185,-0.334914 49.18139,-0.334336 49.18154,-0.333924 49.180556,-0.33423 49.180556,-0.333433 49.179282,-0.333016 49.179351,-0.332609 49.177461,-0.333265 49.177257,-0.333639 49.177157,-0.333986 49.17709,-0.33384 49.176727,-0.331787 49.177315,-0.331223 49.177385,-0.331235 49.176979,-0.331936 49.176823,-0.331739 49.176552,-0.331164 49.17672,-0.330778 49.174702,-0.330723 49.173986,-0.330867 49.174004,-0.330855 49.172347,-0.331116 49.172435,-0.33131 49.167625,-0.331918 49.167564,-0.334021 49.167298,-0.335878 49.167077,-0.336404 49.166992,-0.337252 49.166742,-0.338364 49.166484,-0.339238 49.166258,-0.340235 49.165969,-0.341561 49.165554,-0.341719 49.165471,-0.342581 49.164769,-0.343149 49.164344,-0.342781 49.164141,-0.340218 49.162523,-0.339193 49.161854,-0.339039 49.161659,-0.340669 49.160344,-0.338979 49.159321,-0.337774 49.158528,-0.337291 49.158221,-0.338395 49.157604,-0.339418 49.157044,-0.34047 49.156454,-0.338851 49.155275,-0.338904 49.155201,-0.339124 49.155208,-0.339376 49.155121,-0.340973 49.154184,-0.341555 49.153867,-0.342901 49.153089,-0.343146 49.153021)))"
]

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(((-0.343146 49.153021,-0.344011 49.154516,-0.344181 49.154826,-0.344692 49.155692,-0.347301 49.160279,-0.34893 49.163043,-0.349032 49.163011,-0.351206 49.162423,-0.351183 49.162382,-0.352075 49.162259,-0.351638 49.161252,-0.351978 49.161109,-0.353842 49.160371,-0.354227 49.160278,-0.354771 49.160204,-0.355388 49.160075,-0.355862 49.160012,-0.35592 49.159931,-0.357276 49.159673,-0.358832 49.15942,-0.3614 49.158753,-0.361512 49.158932,-0.361559 49.159144,-0.361737 49.159545,-0.363491 49.159539,-0.365058 49.159653,-0.366113 49.159805,-0.36606 49.159913,-0.367161 49.160029,-0.367039 49.160245,-0.367126 49.160338,-0.367542 49.16042,-0.367154 49.161279,-0.366696 49.16238,-0.366369 49.163237,-0.365393 49.165588,-0.366551 49.165757,-0.367419 49.166573,-0.367734 49.166546,-0.368691 49.166488,-0.369021 49.166643,-0.369394 49.166596,-0.369707 49.166138,-0.369761 49.165831,-0.369993 49.165534,-0.36999 49.165111,-0.370525 49.164931,-0.370799 49.165029,-0.371088 49.165019,-0.371073 49.164813,-0.371237 49.164687,-0.371635 49.164569,-0.372195 49.16437,-0.37207 49.164245,-0.37222 49.163839,-0.372673 49.16391,-0.372823 49.163605,-0.373602 49.16323,-0.374657 49.163297,-0.375154 49.163278,-0.375636 49.163391,-0.37587 49.163396,-0.376201 49.163468,-0.376853 49.163244,-0.377097 49.163145,-0.377393 49.16296,-0.377597 49.162759,-0.377924 49.162747,-0.378455 49.162595,-0.378652 49.162572,-0.378968 49.162597,-0.379375 49.162736,-0.379864 49.162721,-0.380041 49.162913,-0.380275 49.162926,-0.380525 49.1628,-0.381006 49.162718,-0.381154 49.16272,-0.381607 49.162803,-0.38192 49.162749,-0.382432 49.162812,-0.382874 49.162905,-0.383183 49.16292,-0.38365 49.162896,-0.384223 49.162986,-0.384519 49.162983,-0.385044 49.162871,-0.385204 49.162856,-0.385761 49.162861,-0.386686 49.162893,-0.386863 49.162874,-0.386989 49.162917,-0.38719 49.163165,-0.387348 49.163199,-0.38757 49.163161,-0.387746 49.163265,-0.388392 49.163499,-0.388508 49.163568,-0.388629 49.16384,-0.388737 49.163873,-0.389417 49.163893,-0.39016 49.163853,-0.39037 49.163885,-0.390521 49.163865,-0.39071 49.163755,-0.391104 49.163965,-0.391283 49.163985,-0.391479 49.163846,-0.391637 49.163871,-0.391718 49.164039,-0.391825 49.164133,-0.392344 49.164219,-0.392396 49.164333,-0.392202 49.164534,-0.39224 49.164675,-0.392467 49.164717,-0.393401 49.164738,-0.393628 49.164797,-0.393711 49.164874,-0.393734 49.16506,-0.393817 49.165238,-0.394168 49.165488,-0.394195 49.16558,-0.394095 49.165813,-0.394206 49.16592,-0.394488 49.165935,-0.394589 49.166029,-0.394382 49.166159,-0.394341 49.166304,-0.394475 49.166484,-0.393989 49.166479,-0.39469 49.167315,-0.3949 49.167642,-0.394757 49.168203,-0.395434 49.168118,-0.395943 49.168145,-0.396734 49.16824,-0.397261 49.168362,-0.397744 49.168449,-0.398263 49.168501,-0.398764 49.168427,-0.398954 49.168427,-0.399842 49.168505,-0.400501 49.168588,-0.40165 49.168642,-0.401843 49.168619,-0.401886 49.168808,-0.401024 49.168842,-0.400668 49.16884,-0.40121 49.169022,-0.401896 49.169166,-0.402531 49.169331,-0.403311 49.169584,-0.403986 49.169882,-0.404946 49.170359,-0.405729 49.170854,-0.405906 49.171005,-0.405729 49.171154,-0.405681 49.171548,-0.405555 49.171794,-0.405382 49.171992,-0.405203 49.172314,-0.40495 49.172967,-0.404685 49.173178,-0.404534 49.173765,-0.404367 49.174181,-0.404116 49.174567,-0.40379 49.174912,-0.403702 49.175109,-0.40359 49.175746,-0.403432 49.176022,-0.403399 49.176207,-0.403281 49.176537,-0.403245 49.176908,-0.403307 49.177189,-0.40333 49.177451,-0.40444 49.177656,-0.40472 49.177728,-0.405289 49.177978,-0.405641 49.178103,-0.405932 49.178236,-0.406305 49.178545,-0.406746 49.179013,-0.406846 49.179249,-0.407072 49.179685,-0.407946 49.180449,-0.408281 49.180799,-0.408518 49.181075,-0.408889 49.18157,-0.409218 49.181945,-0.409854 49.182559,-0.410279 49.182858,-0.412371 49.184264,-0.413763 49.186887,-0.412616 49.187001,-0.412809 49.188706,-0.412489 49.188556,-0.412156 49.188527,-0.409998 49.188281,-0.40909 49.188192,-0.407886 49.188045,-0.407398 49.187971,-0.407077 49.188342,-0.406746 49.188311,-0.406719 49.188541,-0.407197 49.188564,-0.407227 49.18886,-0.407341 49.189169,-0.407928 49.190529,-0.408202 49.191003,-0.40861 49.191484,-0.40909 49.192025,-0.409805 49.192863,-0.409979 49.193105,-0.410316 49.193419,-0.411494 49.194623,-0.411607 49.194757,-0.410534 49.195615,-0.409145 49.196516,-0.409262 49.196648,-0.408165 49.197343,-0.407975 49.197476,-0.407325 49.197846,-0.405263 49.198991,-0.404332 49.199485,-0.402052 49.200619,-0.401954 49.200659,-0.401183 49.200203,-0.40032 49.199734,-0.39942 49.199281,-0.397454 49.198391,-0.397188 49.198281,-0.396829 49.198203,-0.396746 49.198752,-0.396663 49.199062,-0.396426 49.199662,-0.396236 49.200002,-0.395857 49.200533,-0.395425 49.201011,-0.395305 49.201177,-0.394933 49.201556,-0.394697 49.201752,-0.394144 49.202128,-0.393541 49.202435,-0.393163 49.202584,-0.39252 49.202782,-0.391887 49.202914,-0.391442 49.203031,-0.391186 49.203869,-0.391082 49.204057,-0.390728 49.204403,-0.389685 49.205145,-0.388482 49.206091,-0.388227 49.206312,-0.387557 49.20685,-0.386665 49.207473,-0.386334 49.207729,-0.38605 49.207978,-0.384875 49.20887,-0.383782 49.209603,-0.383167 49.209996,-0.382451 49.210403,-0.381778 49.210736,-0.380578 49.211298,-0.380243 49.211425,-0.379901 49.211504,-0.378835 49.21172,-0.379058 49.211978,-0.378217 49.212597,-0.377878 49.213425,-0.377715 49.213653,-0.377597 49.213704,-0.377069 49.213713,-0.376573 49.213666,-0.376292 49.213588,-0.376093 49.213423,-0.375643 49.21363,-0.374589 49.214191,-0.373625 49.214734,-0.372699 49.215311,-0.3723 49.215487,-0.371873 49.215613,-0.370692 49.215999,-0.370054 49.216261,-0.368957 49.215884,-0.365025 49.214884,-0.364905 49.2146,-0.364524 49.214546,-0.363568 49.214325,-0.3637 49.214146,-0.363592 49.214071,-0.363135 49.213873,-0.3625 49.213639,-0.361502 49.213233,-0.361219 49.213134,-0.360518 49.212838,-0.360234 49.212734,-0.360181 49.212784,-0.359919 49.212664,-0.359617 49.212593,-0.358595 49.212293,-0.357978 49.212082,-0.357423 49.211829,-0.356762 49.211478,-0.356263 49.211078,-0.355031 49.209813,-0.355728 49.208292,-0.355767 49.208181,-0.353922 49.207679,-0.353608 49.208073,-0.353316 49.208483,-0.351284 49.207635,-0.352076 49.20696,-0.351265 49.206518,-0.350551 49.206207,-0.350812 49.205853,-0.35085 49.205665,-0.350667 49.205545,-0.351045 49.205164,-0.350122 49.20522,-0.349938 49.205186,-0.348881 49.204909,-0.349198 49.204606,-0.347589 49.203523,-0.348851 49.202544,-0.349741 49.201869,-0.350068 49.201654,-0.350394 49.20154,-0.349134 49.20117,-0.348494 49.200964,-0.347042 49.200447,-0.346374 49.200197,-0.344619 49.199462,-0.343931 49.19915,-0.343267 49.198833,-0.342701 49.198549,-0.341533 49.197911,-0.340977 49.19757,-0.340484 49.197241,-0.339456 49.19649,-0.338403 49.195574,-0.337975 49.195154,-0.336097 49.193225,-0.335923 49.193036,-0.336862 49.192373,-0.337407 49.192164,-0.337719 49.191952,-0.338017 49.191699,-0.337283 49.190903,-0.336465 49.190027,-0.336125 49.189505,-0.336029 49.189234,-0.335885 49.188975,-0.335359 49.188303,-0.334856 49.187763,-0.334714 49.187643,-0.333644 49.185865,-0.333629 49.185828,-0.334197 49.185776,-0.334756 49.185657,-0.335023 49.185534,-0.335207 49.185365,-0.335304 49.18521,-0.335244 49.184828,-0.334687 49.183541,-0.334614 49.183232,-0.334723 49.182903,-0.335042 49.182501,-0.335426 49.182185,-0.334914 49.18139,-0.334336 49.18154,-0.333924 49.180556,-0.33423 49.180556,-0.333433 49.179282,-0.333016 49.179351,-0.332609 49.177461,-0.333265 49.177257,-0.333639 49.177157,-0.333986 49.17709,-0.33384 49.176727,-0.331787 49.177315,-0.331223 49.177385,-0.331235 49.176979,-0.331936 49.176823,-0.331739 49.176552,-0.331164 49.17672,-0.330778 49.174702,-0.330723 49.173986,-0.330867 49.174004,-0.330855 49.172347,-0.331116 49.172435,-0.33131 49.167625,-0.331918 49.167564,-0.334021 49.167298,-0.335878 49.167077,-0.336404 49.166992,-0.337252 49.166742,-0.338364 49.166484,-0.339238 49.166258,-0.340235 49.165969,-0.341561 49.165554,-0.341719 49.165471,-0.342581 49.164769,-0.343149 49.164344,-0.342781 49.164141,-0.340218 49.162523,-0.339193 49.161854,-0.339039 49.161659,-0.340669 49.160344,-0.338979 49.159321,-0.337774 49.158528,-0.337291 49.158221,-0.338395 49.157604,-0.339418 49.157044,-0.34047 49.156454,-0.338851 49.155275,-0.338904 49.155201,-0.339124 49.155208,-0.339376 49.155121,-0.340973 49.154184,-0.341555 49.153867,-0.342901 49.153089,-0.343146 49.153021)))', 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.74 ms
(0.14%)

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

Parameters:

[
  "14118"
]

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

#	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.40 ms
(0.08%)

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:

[
  "14118"
]

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'14118'') = ?
      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.38 ms
(0.07%)

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.30 ms
(0.06%)

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	5543
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/14118

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