Symfony Profiler

Query Metrics

37 Database Queries

29 Different statements

3279.99 ms Query time

0 Invalid entities

4953 Managed entities

Grouped Statements

Show all queries

default connection

Time▼

Count

Info

3112.49 ms
(94.89%)

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:

[
  26714
]

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

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

101.92 ms
(3.11%)

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:

[
  "14554"
]

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

2.73 ms
(0.08%)

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

1.08 ms
(0.03%)

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:

[
  26714
]

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

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

0.74 ms
(0.02%)

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:

[
  26714
]

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

0.30 ms
(0.01%)

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

Parameters:

[
  "14554"
]

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

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

0.30 ms
(0.01%)

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

Parameters:

[
  "stats_de_ville"
  "{"insee_code":"14554","nom":"14554"}"
  "2026-07-13 00:48:32"
  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\":\"14554\",\"nom\":\"14554\"}', '2026-07-13 00:48:32', 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)

0.27 ms
(0.01%)

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:

[
  26714
]

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 = 26714 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.20 ms
(0.01%)

"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

20.19 ms
(0.62%)

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.281639 49.104009,-0.280597 49.103962,-0.279419 49.103882,-0.279067 49.103847,-0.278039 49.103695,-0.277589 49.103597,-0.276636 49.103334,-0.276251 49.103289,-0.275175 49.103214,-0.274669 49.103179,-0.27427 49.10312,-0.273791 49.103165,-0.272643 49.103149,-0.271199 49.10315,-0.27048 49.103161,-0.270091 49.103208,-0.269928 49.10325,-0.269463 49.103452,-0.269158 49.103697,-0.268685 49.104206,-0.267869 49.104125,-0.266834 49.103957,-0.264891 49.103627,-0.263961 49.103475,-0.263149 49.10338,-0.262477 49.103326,-0.261924 49.103299,-0.261157 49.103219,-0.26121 49.102886,-0.261247 49.102208,-0.261296 49.101797,-0.261072 49.101833,-0.259638 49.101707,-0.259387 49.101639,-0.25888 49.101876,-0.255704 49.100741,-0.255669 49.100625,-0.25355 49.100445,-0.253803 49.099935,-0.254115 49.099744,-0.254771 49.099231,-0.25258 49.098581,-0.252465 49.098718,-0.248931 49.097673,-0.249954 49.09685,-0.250457 49.096418,-0.250798 49.096177,-0.251047 49.095948,-0.25127 49.096078,-0.251197 49.09613,-0.251497 49.096245,-0.2523 49.09552,-0.252015 49.095408,-0.25261 49.094902,-0.253344 49.094315,-0.254041 49.093839,-0.254984 49.093262,-0.257733 49.091424,-0.257515 49.09125,-0.258346 49.090834,-0.25941 49.090321,-0.260171 49.089995,-0.261795 49.089337,-0.262558 49.089034,-0.264735 49.088054,-0.264861 49.088112,-0.2674 49.086668,-0.267342 49.086653,-0.266505 49.086177,-0.265735 49.086524,-0.264724 49.085788,-0.264444 49.085947,-0.263548 49.085303,-0.262941 49.085666,-0.26066 49.084099,-0.260458 49.083919,-0.26008 49.084027,-0.259427 49.083452,-0.259405 49.083349,-0.259163 49.083444,-0.258518 49.082874,-0.258095 49.082408,-0.257792 49.082108,-0.257333 49.081454,-0.256983 49.081049,-0.256746 49.080703,-0.256467 49.080359,-0.255868 49.079923,-0.255531 49.079595,-0.251939 49.07756,-0.251623 49.077396,-0.253522 49.076006,-0.253601 49.075895,-0.253262 49.075695,-0.254681 49.074356,-0.255557 49.074626,-0.256808 49.075046,-0.257752 49.075378,-0.258816 49.075736,-0.259381 49.075861,-0.260226 49.076023,-0.261291 49.076128,-0.261812 49.076227,-0.262979 49.076519,-0.264034 49.076742,-0.264599 49.076914,-0.265316 49.077192,-0.266976 49.078106,-0.26743 49.078262,-0.26803 49.078421,-0.268189 49.07825,-0.269144 49.077508,-0.269517 49.07719,-0.269702 49.077003,-0.269919 49.076841,-0.270427 49.076396,-0.270865 49.075918,-0.271321 49.075516,-0.271445 49.075381,-0.272216 49.074814,-0.272948 49.074407,-0.27351 49.074115,-0.273952 49.073829,-0.274304 49.073556,-0.275055 49.072893,-0.275395 49.072641,-0.275599 49.072423,-0.275655 49.072306,-0.27586 49.072209,-0.27598 49.072094,-0.276381 49.071809,-0.277055 49.071486,-0.277816 49.071163,-0.278317 49.071047,-0.278984 49.070872,-0.279496 49.07071,-0.280004 49.070597,-0.280335 49.070555,-0.280776 49.070231,-0.281149 49.0698,-0.286624 49.070177,-0.286767 49.070739,-0.290001 49.070812,-0.291871 49.070962,-0.293537 49.070917,-0.293567 49.071186,-0.295939 49.070942,-0.297086 49.072953,-0.297806 49.073338,-0.298302 49.073809,-0.300092 49.072711,-0.300245 49.072516,-0.304037 49.073998,-0.303551 49.074325,-0.304248 49.074756,-0.303909 49.075018,-0.304379 49.075311,-0.304721 49.075085,-0.305501 49.075074,-0.306305 49.074771,-0.306377 49.07487,-0.307341 49.076058,-0.30726 49.076699,-0.307055 49.076714,-0.307061 49.077252,-0.306843 49.077239,-0.306118 49.077147,-0.305737 49.077119,-0.30517 49.077157,-0.30488 49.077314,-0.304219 49.077807,-0.308388 49.078557,-0.308387 49.078323,-0.308813 49.07833,-0.309191 49.078473,-0.308355 49.080024,-0.311282 49.081042,-0.31052 49.081997,-0.313176 49.082956,-0.313035 49.083089,-0.312336 49.083837,-0.311518 49.084842,-0.311131 49.085297,-0.307985 49.089184,-0.307152 49.090515,-0.306873 49.09153,-0.306825 49.091766,-0.306445 49.091865,-0.306477 49.091902,-0.306672 49.092696,-0.30666 49.092732,-0.308308 49.092526,-0.30941 49.094483,-0.309256 49.094822,-0.3089 49.09534,-0.30869 49.095694,-0.308375 49.096332,-0.308036 49.097288,-0.307974 49.09754,-0.307879 49.098072,-0.307845 49.098622,-0.30791 49.100018,-0.307962 49.100672,-0.30807 49.103133,-0.304338 49.102556,-0.303284 49.102403,-0.302378 49.102322,-0.301869 49.102336,-0.298536 49.10264,-0.297423 49.102729,-0.295778 49.102873,-0.295512 49.102904,-0.295311 49.10298,-0.293928 49.103261,-0.292761 49.103418,-0.289876 49.103751,-0.288868 49.103842,-0.287203 49.103964,-0.284986 49.104118,-0.284161 49.104157,-0.28364 49.104134,-0.281639 49.104009)))"
  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.281639 49.104009,-0.280597 49.103962,-0.279419 49.103882,-0.279067 49.103847,-0.278039 49.103695,-0.277589 49.103597,-0.276636 49.103334,-0.276251 49.103289,-0.275175 49.103214,-0.274669 49.103179,-0.27427 49.10312,-0.273791 49.103165,-0.272643 49.103149,-0.271199 49.10315,-0.27048 49.103161,-0.270091 49.103208,-0.269928 49.10325,-0.269463 49.103452,-0.269158 49.103697,-0.268685 49.104206,-0.267869 49.104125,-0.266834 49.103957,-0.264891 49.103627,-0.263961 49.103475,-0.263149 49.10338,-0.262477 49.103326,-0.261924 49.103299,-0.261157 49.103219,-0.26121 49.102886,-0.261247 49.102208,-0.261296 49.101797,-0.261072 49.101833,-0.259638 49.101707,-0.259387 49.101639,-0.25888 49.101876,-0.255704 49.100741,-0.255669 49.100625,-0.25355 49.100445,-0.253803 49.099935,-0.254115 49.099744,-0.254771 49.099231,-0.25258 49.098581,-0.252465 49.098718,-0.248931 49.097673,-0.249954 49.09685,-0.250457 49.096418,-0.250798 49.096177,-0.251047 49.095948,-0.25127 49.096078,-0.251197 49.09613,-0.251497 49.096245,-0.2523 49.09552,-0.252015 49.095408,-0.25261 49.094902,-0.253344 49.094315,-0.254041 49.093839,-0.254984 49.093262,-0.257733 49.091424,-0.257515 49.09125,-0.258346 49.090834,-0.25941 49.090321,-0.260171 49.089995,-0.261795 49.089337,-0.262558 49.089034,-0.264735 49.088054,-0.264861 49.088112,-0.2674 49.086668,-0.267342 49.086653,-0.266505 49.086177,-0.265735 49.086524,-0.264724 49.085788,-0.264444 49.085947,-0.263548 49.085303,-0.262941 49.085666,-0.26066 49.084099,-0.260458 49.083919,-0.26008 49.084027,-0.259427 49.083452,-0.259405 49.083349,-0.259163 49.083444,-0.258518 49.082874,-0.258095 49.082408,-0.257792 49.082108,-0.257333 49.081454,-0.256983 49.081049,-0.256746 49.080703,-0.256467 49.080359,-0.255868 49.079923,-0.255531 49.079595,-0.251939 49.07756,-0.251623 49.077396,-0.253522 49.076006,-0.253601 49.075895,-0.253262 49.075695,-0.254681 49.074356,-0.255557 49.074626,-0.256808 49.075046,-0.257752 49.075378,-0.258816 49.075736,-0.259381 49.075861,-0.260226 49.076023,-0.261291 49.076128,-0.261812 49.076227,-0.262979 49.076519,-0.264034 49.076742,-0.264599 49.076914,-0.265316 49.077192,-0.266976 49.078106,-0.26743 49.078262,-0.26803 49.078421,-0.268189 49.07825,-0.269144 49.077508,-0.269517 49.07719,-0.269702 49.077003,-0.269919 49.076841,-0.270427 49.076396,-0.270865 49.075918,-0.271321 49.075516,-0.271445 49.075381,-0.272216 49.074814,-0.272948 49.074407,-0.27351 49.074115,-0.273952 49.073829,-0.274304 49.073556,-0.275055 49.072893,-0.275395 49.072641,-0.275599 49.072423,-0.275655 49.072306,-0.27586 49.072209,-0.27598 49.072094,-0.276381 49.071809,-0.277055 49.071486,-0.277816 49.071163,-0.278317 49.071047,-0.278984 49.070872,-0.279496 49.07071,-0.280004 49.070597,-0.280335 49.070555,-0.280776 49.070231,-0.281149 49.0698,-0.286624 49.070177,-0.286767 49.070739,-0.290001 49.070812,-0.291871 49.070962,-0.293537 49.070917,-0.293567 49.071186,-0.295939 49.070942,-0.297086 49.072953,-0.297806 49.073338,-0.298302 49.073809,-0.300092 49.072711,-0.300245 49.072516,-0.304037 49.073998,-0.303551 49.074325,-0.304248 49.074756,-0.303909 49.075018,-0.304379 49.075311,-0.304721 49.075085,-0.305501 49.075074,-0.306305 49.074771,-0.306377 49.07487,-0.307341 49.076058,-0.30726 49.076699,-0.307055 49.076714,-0.307061 49.077252,-0.306843 49.077239,-0.306118 49.077147,-0.305737 49.077119,-0.30517 49.077157,-0.30488 49.077314,-0.304219 49.077807,-0.308388 49.078557,-0.308387 49.078323,-0.308813 49.07833,-0.309191 49.078473,-0.308355 49.080024,-0.311282 49.081042,-0.31052 49.081997,-0.313176 49.082956,-0.313035 49.083089,-0.312336 49.083837,-0.311518 49.084842,-0.311131 49.085297,-0.307985 49.089184,-0.307152 49.090515,-0.306873 49.09153,-0.306825 49.091766,-0.306445 49.091865,-0.306477 49.091902,-0.306672 49.092696,-0.30666 49.092732,-0.308308 49.092526,-0.30941 49.094483,-0.309256 49.094822,-0.3089 49.09534,-0.30869 49.095694,-0.308375 49.096332,-0.308036 49.097288,-0.307974 49.09754,-0.307879 49.098072,-0.307845 49.098622,-0.30791 49.100018,-0.307962 49.100672,-0.30807 49.103133,-0.304338 49.102556,-0.303284 49.102403,-0.302378 49.102322,-0.301869 49.102336,-0.298536 49.10264,-0.297423 49.102729,-0.295778 49.102873,-0.295512 49.102904,-0.295311 49.10298,-0.293928 49.103261,-0.292761 49.103418,-0.289876 49.103751,-0.288868 49.103842,-0.287203 49.103964,-0.284986 49.104118,-0.284161 49.104157,-0.28364 49.104134,-0.281639 49.104009)))'') 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)

9.45 ms
(0.29%)

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->'shop') = 'vacant'
              OR EXISTS (SELECT 1 FROM unnest(akeys(tags)) k WHERE k LIKE 'was:%')
            )

Parameters:

[
  "MULTIPOLYGON(((-0.281639 49.104009,-0.280597 49.103962,-0.279419 49.103882,-0.279067 49.103847,-0.278039 49.103695,-0.277589 49.103597,-0.276636 49.103334,-0.276251 49.103289,-0.275175 49.103214,-0.274669 49.103179,-0.27427 49.10312,-0.273791 49.103165,-0.272643 49.103149,-0.271199 49.10315,-0.27048 49.103161,-0.270091 49.103208,-0.269928 49.10325,-0.269463 49.103452,-0.269158 49.103697,-0.268685 49.104206,-0.267869 49.104125,-0.266834 49.103957,-0.264891 49.103627,-0.263961 49.103475,-0.263149 49.10338,-0.262477 49.103326,-0.261924 49.103299,-0.261157 49.103219,-0.26121 49.102886,-0.261247 49.102208,-0.261296 49.101797,-0.261072 49.101833,-0.259638 49.101707,-0.259387 49.101639,-0.25888 49.101876,-0.255704 49.100741,-0.255669 49.100625,-0.25355 49.100445,-0.253803 49.099935,-0.254115 49.099744,-0.254771 49.099231,-0.25258 49.098581,-0.252465 49.098718,-0.248931 49.097673,-0.249954 49.09685,-0.250457 49.096418,-0.250798 49.096177,-0.251047 49.095948,-0.25127 49.096078,-0.251197 49.09613,-0.251497 49.096245,-0.2523 49.09552,-0.252015 49.095408,-0.25261 49.094902,-0.253344 49.094315,-0.254041 49.093839,-0.254984 49.093262,-0.257733 49.091424,-0.257515 49.09125,-0.258346 49.090834,-0.25941 49.090321,-0.260171 49.089995,-0.261795 49.089337,-0.262558 49.089034,-0.264735 49.088054,-0.264861 49.088112,-0.2674 49.086668,-0.267342 49.086653,-0.266505 49.086177,-0.265735 49.086524,-0.264724 49.085788,-0.264444 49.085947,-0.263548 49.085303,-0.262941 49.085666,-0.26066 49.084099,-0.260458 49.083919,-0.26008 49.084027,-0.259427 49.083452,-0.259405 49.083349,-0.259163 49.083444,-0.258518 49.082874,-0.258095 49.082408,-0.257792 49.082108,-0.257333 49.081454,-0.256983 49.081049,-0.256746 49.080703,-0.256467 49.080359,-0.255868 49.079923,-0.255531 49.079595,-0.251939 49.07756,-0.251623 49.077396,-0.253522 49.076006,-0.253601 49.075895,-0.253262 49.075695,-0.254681 49.074356,-0.255557 49.074626,-0.256808 49.075046,-0.257752 49.075378,-0.258816 49.075736,-0.259381 49.075861,-0.260226 49.076023,-0.261291 49.076128,-0.261812 49.076227,-0.262979 49.076519,-0.264034 49.076742,-0.264599 49.076914,-0.265316 49.077192,-0.266976 49.078106,-0.26743 49.078262,-0.26803 49.078421,-0.268189 49.07825,-0.269144 49.077508,-0.269517 49.07719,-0.269702 49.077003,-0.269919 49.076841,-0.270427 49.076396,-0.270865 49.075918,-0.271321 49.075516,-0.271445 49.075381,-0.272216 49.074814,-0.272948 49.074407,-0.27351 49.074115,-0.273952 49.073829,-0.274304 49.073556,-0.275055 49.072893,-0.275395 49.072641,-0.275599 49.072423,-0.275655 49.072306,-0.27586 49.072209,-0.27598 49.072094,-0.276381 49.071809,-0.277055 49.071486,-0.277816 49.071163,-0.278317 49.071047,-0.278984 49.070872,-0.279496 49.07071,-0.280004 49.070597,-0.280335 49.070555,-0.280776 49.070231,-0.281149 49.0698,-0.286624 49.070177,-0.286767 49.070739,-0.290001 49.070812,-0.291871 49.070962,-0.293537 49.070917,-0.293567 49.071186,-0.295939 49.070942,-0.297086 49.072953,-0.297806 49.073338,-0.298302 49.073809,-0.300092 49.072711,-0.300245 49.072516,-0.304037 49.073998,-0.303551 49.074325,-0.304248 49.074756,-0.303909 49.075018,-0.304379 49.075311,-0.304721 49.075085,-0.305501 49.075074,-0.306305 49.074771,-0.306377 49.07487,-0.307341 49.076058,-0.30726 49.076699,-0.307055 49.076714,-0.307061 49.077252,-0.306843 49.077239,-0.306118 49.077147,-0.305737 49.077119,-0.30517 49.077157,-0.30488 49.077314,-0.304219 49.077807,-0.308388 49.078557,-0.308387 49.078323,-0.308813 49.07833,-0.309191 49.078473,-0.308355 49.080024,-0.311282 49.081042,-0.31052 49.081997,-0.313176 49.082956,-0.313035 49.083089,-0.312336 49.083837,-0.311518 49.084842,-0.311131 49.085297,-0.307985 49.089184,-0.307152 49.090515,-0.306873 49.09153,-0.306825 49.091766,-0.306445 49.091865,-0.306477 49.091902,-0.306672 49.092696,-0.30666 49.092732,-0.308308 49.092526,-0.30941 49.094483,-0.309256 49.094822,-0.3089 49.09534,-0.30869 49.095694,-0.308375 49.096332,-0.308036 49.097288,-0.307974 49.09754,-0.307879 49.098072,-0.307845 49.098622,-0.30791 49.100018,-0.307962 49.100672,-0.30807 49.103133,-0.304338 49.102556,-0.303284 49.102403,-0.302378 49.102322,-0.301869 49.102336,-0.298536 49.10264,-0.297423 49.102729,-0.295778 49.102873,-0.295512 49.102904,-0.295311 49.10298,-0.293928 49.103261,-0.292761 49.103418,-0.289876 49.103751,-0.288868 49.103842,-0.287203 49.103964,-0.284986 49.104118,-0.284161 49.104157,-0.28364 49.104134,-0.281639 49.104009)))"
]

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 -> 'shop') = 'vacant'
    OR EXISTS (
      SELECT
        1
      FROM
        unnest(
          akeys(tags)
        ) k
      WHERE
        k LIKE 'was:%'
    )
  )

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.281639 49.104009,-0.280597 49.103962,-0.279419 49.103882,-0.279067 49.103847,-0.278039 49.103695,-0.277589 49.103597,-0.276636 49.103334,-0.276251 49.103289,-0.275175 49.103214,-0.274669 49.103179,-0.27427 49.10312,-0.273791 49.103165,-0.272643 49.103149,-0.271199 49.10315,-0.27048 49.103161,-0.270091 49.103208,-0.269928 49.10325,-0.269463 49.103452,-0.269158 49.103697,-0.268685 49.104206,-0.267869 49.104125,-0.266834 49.103957,-0.264891 49.103627,-0.263961 49.103475,-0.263149 49.10338,-0.262477 49.103326,-0.261924 49.103299,-0.261157 49.103219,-0.26121 49.102886,-0.261247 49.102208,-0.261296 49.101797,-0.261072 49.101833,-0.259638 49.101707,-0.259387 49.101639,-0.25888 49.101876,-0.255704 49.100741,-0.255669 49.100625,-0.25355 49.100445,-0.253803 49.099935,-0.254115 49.099744,-0.254771 49.099231,-0.25258 49.098581,-0.252465 49.098718,-0.248931 49.097673,-0.249954 49.09685,-0.250457 49.096418,-0.250798 49.096177,-0.251047 49.095948,-0.25127 49.096078,-0.251197 49.09613,-0.251497 49.096245,-0.2523 49.09552,-0.252015 49.095408,-0.25261 49.094902,-0.253344 49.094315,-0.254041 49.093839,-0.254984 49.093262,-0.257733 49.091424,-0.257515 49.09125,-0.258346 49.090834,-0.25941 49.090321,-0.260171 49.089995,-0.261795 49.089337,-0.262558 49.089034,-0.264735 49.088054,-0.264861 49.088112,-0.2674 49.086668,-0.267342 49.086653,-0.266505 49.086177,-0.265735 49.086524,-0.264724 49.085788,-0.264444 49.085947,-0.263548 49.085303,-0.262941 49.085666,-0.26066 49.084099,-0.260458 49.083919,-0.26008 49.084027,-0.259427 49.083452,-0.259405 49.083349,-0.259163 49.083444,-0.258518 49.082874,-0.258095 49.082408,-0.257792 49.082108,-0.257333 49.081454,-0.256983 49.081049,-0.256746 49.080703,-0.256467 49.080359,-0.255868 49.079923,-0.255531 49.079595,-0.251939 49.07756,-0.251623 49.077396,-0.253522 49.076006,-0.253601 49.075895,-0.253262 49.075695,-0.254681 49.074356,-0.255557 49.074626,-0.256808 49.075046,-0.257752 49.075378,-0.258816 49.075736,-0.259381 49.075861,-0.260226 49.076023,-0.261291 49.076128,-0.261812 49.076227,-0.262979 49.076519,-0.264034 49.076742,-0.264599 49.076914,-0.265316 49.077192,-0.266976 49.078106,-0.26743 49.078262,-0.26803 49.078421,-0.268189 49.07825,-0.269144 49.077508,-0.269517 49.07719,-0.269702 49.077003,-0.269919 49.076841,-0.270427 49.076396,-0.270865 49.075918,-0.271321 49.075516,-0.271445 49.075381,-0.272216 49.074814,-0.272948 49.074407,-0.27351 49.074115,-0.273952 49.073829,-0.274304 49.073556,-0.275055 49.072893,-0.275395 49.072641,-0.275599 49.072423,-0.275655 49.072306,-0.27586 49.072209,-0.27598 49.072094,-0.276381 49.071809,-0.277055 49.071486,-0.277816 49.071163,-0.278317 49.071047,-0.278984 49.070872,-0.279496 49.07071,-0.280004 49.070597,-0.280335 49.070555,-0.280776 49.070231,-0.281149 49.0698,-0.286624 49.070177,-0.286767 49.070739,-0.290001 49.070812,-0.291871 49.070962,-0.293537 49.070917,-0.293567 49.071186,-0.295939 49.070942,-0.297086 49.072953,-0.297806 49.073338,-0.298302 49.073809,-0.300092 49.072711,-0.300245 49.072516,-0.304037 49.073998,-0.303551 49.074325,-0.304248 49.074756,-0.303909 49.075018,-0.304379 49.075311,-0.304721 49.075085,-0.305501 49.075074,-0.306305 49.074771,-0.306377 49.07487,-0.307341 49.076058,-0.30726 49.076699,-0.307055 49.076714,-0.307061 49.077252,-0.306843 49.077239,-0.306118 49.077147,-0.305737 49.077119,-0.30517 49.077157,-0.30488 49.077314,-0.304219 49.077807,-0.308388 49.078557,-0.308387 49.078323,-0.308813 49.07833,-0.309191 49.078473,-0.308355 49.080024,-0.311282 49.081042,-0.31052 49.081997,-0.313176 49.082956,-0.313035 49.083089,-0.312336 49.083837,-0.311518 49.084842,-0.311131 49.085297,-0.307985 49.089184,-0.307152 49.090515,-0.306873 49.09153,-0.306825 49.091766,-0.306445 49.091865,-0.306477 49.091902,-0.306672 49.092696,-0.30666 49.092732,-0.308308 49.092526,-0.30941 49.094483,-0.309256 49.094822,-0.3089 49.09534,-0.30869 49.095694,-0.308375 49.096332,-0.308036 49.097288,-0.307974 49.09754,-0.307879 49.098072,-0.307845 49.098622,-0.30791 49.100018,-0.307962 49.100672,-0.30807 49.103133,-0.304338 49.102556,-0.303284 49.102403,-0.302378 49.102322,-0.301869 49.102336,-0.298536 49.10264,-0.297423 49.102729,-0.295778 49.102873,-0.295512 49.102904,-0.295311 49.10298,-0.293928 49.103261,-0.292761 49.103418,-0.289876 49.103751,-0.288868 49.103842,-0.287203 49.103964,-0.284986 49.104118,-0.284161 49.104157,-0.28364 49.104134,-0.281639 49.104009)))', 4326))
            AND (
              (tags->'shop') = 'vacant'
              OR EXISTS (SELECT 1 FROM unnest(akeys(tags)) k WHERE k LIKE 'was:%')
            );

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

8.20 ms
(0.25%)

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:

[
  "14554"
]

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'14554'') = ?
  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.86 ms
(0.09%)

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

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->getClosedLikePlaces (line 177)
5	App\Controller\AdminController->stats (line 827)
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.37 ms
(0.07%)

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.20 ms
(0.07%)

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.281639 49.104009,-0.280597 49.103962,-0.279419 49.103882,-0.279067 49.103847,-0.278039 49.103695,-0.277589 49.103597,-0.276636 49.103334,-0.276251 49.103289,-0.275175 49.103214,-0.274669 49.103179,-0.27427 49.10312,-0.273791 49.103165,-0.272643 49.103149,-0.271199 49.10315,-0.27048 49.103161,-0.270091 49.103208,-0.269928 49.10325,-0.269463 49.103452,-0.269158 49.103697,-0.268685 49.104206,-0.267869 49.104125,-0.266834 49.103957,-0.264891 49.103627,-0.263961 49.103475,-0.263149 49.10338,-0.262477 49.103326,-0.261924 49.103299,-0.261157 49.103219,-0.26121 49.102886,-0.261247 49.102208,-0.261296 49.101797,-0.261072 49.101833,-0.259638 49.101707,-0.259387 49.101639,-0.25888 49.101876,-0.255704 49.100741,-0.255669 49.100625,-0.25355 49.100445,-0.253803 49.099935,-0.254115 49.099744,-0.254771 49.099231,-0.25258 49.098581,-0.252465 49.098718,-0.248931 49.097673,-0.249954 49.09685,-0.250457 49.096418,-0.250798 49.096177,-0.251047 49.095948,-0.25127 49.096078,-0.251197 49.09613,-0.251497 49.096245,-0.2523 49.09552,-0.252015 49.095408,-0.25261 49.094902,-0.253344 49.094315,-0.254041 49.093839,-0.254984 49.093262,-0.257733 49.091424,-0.257515 49.09125,-0.258346 49.090834,-0.25941 49.090321,-0.260171 49.089995,-0.261795 49.089337,-0.262558 49.089034,-0.264735 49.088054,-0.264861 49.088112,-0.2674 49.086668,-0.267342 49.086653,-0.266505 49.086177,-0.265735 49.086524,-0.264724 49.085788,-0.264444 49.085947,-0.263548 49.085303,-0.262941 49.085666,-0.26066 49.084099,-0.260458 49.083919,-0.26008 49.084027,-0.259427 49.083452,-0.259405 49.083349,-0.259163 49.083444,-0.258518 49.082874,-0.258095 49.082408,-0.257792 49.082108,-0.257333 49.081454,-0.256983 49.081049,-0.256746 49.080703,-0.256467 49.080359,-0.255868 49.079923,-0.255531 49.079595,-0.251939 49.07756,-0.251623 49.077396,-0.253522 49.076006,-0.253601 49.075895,-0.253262 49.075695,-0.254681 49.074356,-0.255557 49.074626,-0.256808 49.075046,-0.257752 49.075378,-0.258816 49.075736,-0.259381 49.075861,-0.260226 49.076023,-0.261291 49.076128,-0.261812 49.076227,-0.262979 49.076519,-0.264034 49.076742,-0.264599 49.076914,-0.265316 49.077192,-0.266976 49.078106,-0.26743 49.078262,-0.26803 49.078421,-0.268189 49.07825,-0.269144 49.077508,-0.269517 49.07719,-0.269702 49.077003,-0.269919 49.076841,-0.270427 49.076396,-0.270865 49.075918,-0.271321 49.075516,-0.271445 49.075381,-0.272216 49.074814,-0.272948 49.074407,-0.27351 49.074115,-0.273952 49.073829,-0.274304 49.073556,-0.275055 49.072893,-0.275395 49.072641,-0.275599 49.072423,-0.275655 49.072306,-0.27586 49.072209,-0.27598 49.072094,-0.276381 49.071809,-0.277055 49.071486,-0.277816 49.071163,-0.278317 49.071047,-0.278984 49.070872,-0.279496 49.07071,-0.280004 49.070597,-0.280335 49.070555,-0.280776 49.070231,-0.281149 49.0698,-0.286624 49.070177,-0.286767 49.070739,-0.290001 49.070812,-0.291871 49.070962,-0.293537 49.070917,-0.293567 49.071186,-0.295939 49.070942,-0.297086 49.072953,-0.297806 49.073338,-0.298302 49.073809,-0.300092 49.072711,-0.300245 49.072516,-0.304037 49.073998,-0.303551 49.074325,-0.304248 49.074756,-0.303909 49.075018,-0.304379 49.075311,-0.304721 49.075085,-0.305501 49.075074,-0.306305 49.074771,-0.306377 49.07487,-0.307341 49.076058,-0.30726 49.076699,-0.307055 49.076714,-0.307061 49.077252,-0.306843 49.077239,-0.306118 49.077147,-0.305737 49.077119,-0.30517 49.077157,-0.30488 49.077314,-0.304219 49.077807,-0.308388 49.078557,-0.308387 49.078323,-0.308813 49.07833,-0.309191 49.078473,-0.308355 49.080024,-0.311282 49.081042,-0.31052 49.081997,-0.313176 49.082956,-0.313035 49.083089,-0.312336 49.083837,-0.311518 49.084842,-0.311131 49.085297,-0.307985 49.089184,-0.307152 49.090515,-0.306873 49.09153,-0.306825 49.091766,-0.306445 49.091865,-0.306477 49.091902,-0.306672 49.092696,-0.30666 49.092732,-0.308308 49.092526,-0.30941 49.094483,-0.309256 49.094822,-0.3089 49.09534,-0.30869 49.095694,-0.308375 49.096332,-0.308036 49.097288,-0.307974 49.09754,-0.307879 49.098072,-0.307845 49.098622,-0.30791 49.100018,-0.307962 49.100672,-0.30807 49.103133,-0.304338 49.102556,-0.303284 49.102403,-0.302378 49.102322,-0.301869 49.102336,-0.298536 49.10264,-0.297423 49.102729,-0.295778 49.102873,-0.295512 49.102904,-0.295311 49.10298,-0.293928 49.103261,-0.292761 49.103418,-0.289876 49.103751,-0.288868 49.103842,-0.287203 49.103964,-0.284986 49.104118,-0.284161 49.104157,-0.28364 49.104134,-0.281639 49.104009)))"
  "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.281639 49.104009,-0.280597 49.103962,-0.279419 49.103882,-0.279067 49.103847,-0.278039 49.103695,-0.277589 49.103597,-0.276636 49.103334,-0.276251 49.103289,-0.275175 49.103214,-0.274669 49.103179,-0.27427 49.10312,-0.273791 49.103165,-0.272643 49.103149,-0.271199 49.10315,-0.27048 49.103161,-0.270091 49.103208,-0.269928 49.10325,-0.269463 49.103452,-0.269158 49.103697,-0.268685 49.104206,-0.267869 49.104125,-0.266834 49.103957,-0.264891 49.103627,-0.263961 49.103475,-0.263149 49.10338,-0.262477 49.103326,-0.261924 49.103299,-0.261157 49.103219,-0.26121 49.102886,-0.261247 49.102208,-0.261296 49.101797,-0.261072 49.101833,-0.259638 49.101707,-0.259387 49.101639,-0.25888 49.101876,-0.255704 49.100741,-0.255669 49.100625,-0.25355 49.100445,-0.253803 49.099935,-0.254115 49.099744,-0.254771 49.099231,-0.25258 49.098581,-0.252465 49.098718,-0.248931 49.097673,-0.249954 49.09685,-0.250457 49.096418,-0.250798 49.096177,-0.251047 49.095948,-0.25127 49.096078,-0.251197 49.09613,-0.251497 49.096245,-0.2523 49.09552,-0.252015 49.095408,-0.25261 49.094902,-0.253344 49.094315,-0.254041 49.093839,-0.254984 49.093262,-0.257733 49.091424,-0.257515 49.09125,-0.258346 49.090834,-0.25941 49.090321,-0.260171 49.089995,-0.261795 49.089337,-0.262558 49.089034,-0.264735 49.088054,-0.264861 49.088112,-0.2674 49.086668,-0.267342 49.086653,-0.266505 49.086177,-0.265735 49.086524,-0.264724 49.085788,-0.264444 49.085947,-0.263548 49.085303,-0.262941 49.085666,-0.26066 49.084099,-0.260458 49.083919,-0.26008 49.084027,-0.259427 49.083452,-0.259405 49.083349,-0.259163 49.083444,-0.258518 49.082874,-0.258095 49.082408,-0.257792 49.082108,-0.257333 49.081454,-0.256983 49.081049,-0.256746 49.080703,-0.256467 49.080359,-0.255868 49.079923,-0.255531 49.079595,-0.251939 49.07756,-0.251623 49.077396,-0.253522 49.076006,-0.253601 49.075895,-0.253262 49.075695,-0.254681 49.074356,-0.255557 49.074626,-0.256808 49.075046,-0.257752 49.075378,-0.258816 49.075736,-0.259381 49.075861,-0.260226 49.076023,-0.261291 49.076128,-0.261812 49.076227,-0.262979 49.076519,-0.264034 49.076742,-0.264599 49.076914,-0.265316 49.077192,-0.266976 49.078106,-0.26743 49.078262,-0.26803 49.078421,-0.268189 49.07825,-0.269144 49.077508,-0.269517 49.07719,-0.269702 49.077003,-0.269919 49.076841,-0.270427 49.076396,-0.270865 49.075918,-0.271321 49.075516,-0.271445 49.075381,-0.272216 49.074814,-0.272948 49.074407,-0.27351 49.074115,-0.273952 49.073829,-0.274304 49.073556,-0.275055 49.072893,-0.275395 49.072641,-0.275599 49.072423,-0.275655 49.072306,-0.27586 49.072209,-0.27598 49.072094,-0.276381 49.071809,-0.277055 49.071486,-0.277816 49.071163,-0.278317 49.071047,-0.278984 49.070872,-0.279496 49.07071,-0.280004 49.070597,-0.280335 49.070555,-0.280776 49.070231,-0.281149 49.0698,-0.286624 49.070177,-0.286767 49.070739,-0.290001 49.070812,-0.291871 49.070962,-0.293537 49.070917,-0.293567 49.071186,-0.295939 49.070942,-0.297086 49.072953,-0.297806 49.073338,-0.298302 49.073809,-0.300092 49.072711,-0.300245 49.072516,-0.304037 49.073998,-0.303551 49.074325,-0.304248 49.074756,-0.303909 49.075018,-0.304379 49.075311,-0.304721 49.075085,-0.305501 49.075074,-0.306305 49.074771,-0.306377 49.07487,-0.307341 49.076058,-0.30726 49.076699,-0.307055 49.076714,-0.307061 49.077252,-0.306843 49.077239,-0.306118 49.077147,-0.305737 49.077119,-0.30517 49.077157,-0.30488 49.077314,-0.304219 49.077807,-0.308388 49.078557,-0.308387 49.078323,-0.308813 49.07833,-0.309191 49.078473,-0.308355 49.080024,-0.311282 49.081042,-0.31052 49.081997,-0.313176 49.082956,-0.313035 49.083089,-0.312336 49.083837,-0.311518 49.084842,-0.311131 49.085297,-0.307985 49.089184,-0.307152 49.090515,-0.306873 49.09153,-0.306825 49.091766,-0.306445 49.091865,-0.306477 49.091902,-0.306672 49.092696,-0.30666 49.092732,-0.308308 49.092526,-0.30941 49.094483,-0.309256 49.094822,-0.3089 49.09534,-0.30869 49.095694,-0.308375 49.096332,-0.308036 49.097288,-0.307974 49.09754,-0.307879 49.098072,-0.307845 49.098622,-0.30791 49.100018,-0.307962 49.100672,-0.30807 49.103133,-0.304338 49.102556,-0.303284 49.102403,-0.302378 49.102322,-0.301869 49.102336,-0.298536 49.10264,-0.297423 49.102729,-0.295778 49.102873,-0.295512 49.102904,-0.295311 49.10298,-0.293928 49.103261,-0.292761 49.103418,-0.289876 49.103751,-0.288868 49.103842,-0.287203 49.103964,-0.284986 49.104118,-0.284161 49.104157,-0.28364 49.104134,-0.281639 49.104009)))', 4326) AS g),
in_zone AS (
  SELECT h.osm_type, h.osm_id, h.version, h.valid_from, h.visible
  FROM osm_object_history h, zone z
  WHERE h.geom IS NOT NULL AND ST_Intersects(h.geom, z.g)
),
creations AS (
  SELECT (valid_from AT TIME ZONE 'UTC')::date AS d
  FROM in_zone
  WHERE version = 1 AND visible = true AND valid_from >= NOW() - ('30' || ' days')::interval
),
deletions AS (
  SELECT (del.valid_from AT TIME ZONE 'UTC')::date AS d
  FROM osm_object_history del
  JOIN in_zone prev
    ON prev.osm_type = del.osm_type
   AND prev.osm_id   = del.osm_id
   AND prev.version  = del.version - 1
  WHERE del.visible = false
    AND del.valid_from >= NOW() - ('30' || ' days')::interval
)
SELECT d::text AS date,
       SUM(n_creations)::int AS creations,
       SUM(n_deletions)::int AS deletions
FROM (
  SELECT d, COUNT(*) AS n_creations, 0 AS n_deletions FROM creations GROUP BY d
  UNION ALL
  SELECT d, 0, COUNT(*) FROM deletions GROUP BY d
) u
GROUP BY d
ORDER BY d;

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

1.93 ms
(0.06%)

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.281639 49.104009,-0.280597 49.103962,-0.279419 49.103882,-0.279067 49.103847,-0.278039 49.103695,-0.277589 49.103597,-0.276636 49.103334,-0.276251 49.103289,-0.275175 49.103214,-0.274669 49.103179,-0.27427 49.10312,-0.273791 49.103165,-0.272643 49.103149,-0.271199 49.10315,-0.27048 49.103161,-0.270091 49.103208,-0.269928 49.10325,-0.269463 49.103452,-0.269158 49.103697,-0.268685 49.104206,-0.267869 49.104125,-0.266834 49.103957,-0.264891 49.103627,-0.263961 49.103475,-0.263149 49.10338,-0.262477 49.103326,-0.261924 49.103299,-0.261157 49.103219,-0.26121 49.102886,-0.261247 49.102208,-0.261296 49.101797,-0.261072 49.101833,-0.259638 49.101707,-0.259387 49.101639,-0.25888 49.101876,-0.255704 49.100741,-0.255669 49.100625,-0.25355 49.100445,-0.253803 49.099935,-0.254115 49.099744,-0.254771 49.099231,-0.25258 49.098581,-0.252465 49.098718,-0.248931 49.097673,-0.249954 49.09685,-0.250457 49.096418,-0.250798 49.096177,-0.251047 49.095948,-0.25127 49.096078,-0.251197 49.09613,-0.251497 49.096245,-0.2523 49.09552,-0.252015 49.095408,-0.25261 49.094902,-0.253344 49.094315,-0.254041 49.093839,-0.254984 49.093262,-0.257733 49.091424,-0.257515 49.09125,-0.258346 49.090834,-0.25941 49.090321,-0.260171 49.089995,-0.261795 49.089337,-0.262558 49.089034,-0.264735 49.088054,-0.264861 49.088112,-0.2674 49.086668,-0.267342 49.086653,-0.266505 49.086177,-0.265735 49.086524,-0.264724 49.085788,-0.264444 49.085947,-0.263548 49.085303,-0.262941 49.085666,-0.26066 49.084099,-0.260458 49.083919,-0.26008 49.084027,-0.259427 49.083452,-0.259405 49.083349,-0.259163 49.083444,-0.258518 49.082874,-0.258095 49.082408,-0.257792 49.082108,-0.257333 49.081454,-0.256983 49.081049,-0.256746 49.080703,-0.256467 49.080359,-0.255868 49.079923,-0.255531 49.079595,-0.251939 49.07756,-0.251623 49.077396,-0.253522 49.076006,-0.253601 49.075895,-0.253262 49.075695,-0.254681 49.074356,-0.255557 49.074626,-0.256808 49.075046,-0.257752 49.075378,-0.258816 49.075736,-0.259381 49.075861,-0.260226 49.076023,-0.261291 49.076128,-0.261812 49.076227,-0.262979 49.076519,-0.264034 49.076742,-0.264599 49.076914,-0.265316 49.077192,-0.266976 49.078106,-0.26743 49.078262,-0.26803 49.078421,-0.268189 49.07825,-0.269144 49.077508,-0.269517 49.07719,-0.269702 49.077003,-0.269919 49.076841,-0.270427 49.076396,-0.270865 49.075918,-0.271321 49.075516,-0.271445 49.075381,-0.272216 49.074814,-0.272948 49.074407,-0.27351 49.074115,-0.273952 49.073829,-0.274304 49.073556,-0.275055 49.072893,-0.275395 49.072641,-0.275599 49.072423,-0.275655 49.072306,-0.27586 49.072209,-0.27598 49.072094,-0.276381 49.071809,-0.277055 49.071486,-0.277816 49.071163,-0.278317 49.071047,-0.278984 49.070872,-0.279496 49.07071,-0.280004 49.070597,-0.280335 49.070555,-0.280776 49.070231,-0.281149 49.0698,-0.286624 49.070177,-0.286767 49.070739,-0.290001 49.070812,-0.291871 49.070962,-0.293537 49.070917,-0.293567 49.071186,-0.295939 49.070942,-0.297086 49.072953,-0.297806 49.073338,-0.298302 49.073809,-0.300092 49.072711,-0.300245 49.072516,-0.304037 49.073998,-0.303551 49.074325,-0.304248 49.074756,-0.303909 49.075018,-0.304379 49.075311,-0.304721 49.075085,-0.305501 49.075074,-0.306305 49.074771,-0.306377 49.07487,-0.307341 49.076058,-0.30726 49.076699,-0.307055 49.076714,-0.307061 49.077252,-0.306843 49.077239,-0.306118 49.077147,-0.305737 49.077119,-0.30517 49.077157,-0.30488 49.077314,-0.304219 49.077807,-0.308388 49.078557,-0.308387 49.078323,-0.308813 49.07833,-0.309191 49.078473,-0.308355 49.080024,-0.311282 49.081042,-0.31052 49.081997,-0.313176 49.082956,-0.313035 49.083089,-0.312336 49.083837,-0.311518 49.084842,-0.311131 49.085297,-0.307985 49.089184,-0.307152 49.090515,-0.306873 49.09153,-0.306825 49.091766,-0.306445 49.091865,-0.306477 49.091902,-0.306672 49.092696,-0.30666 49.092732,-0.308308 49.092526,-0.30941 49.094483,-0.309256 49.094822,-0.3089 49.09534,-0.30869 49.095694,-0.308375 49.096332,-0.308036 49.097288,-0.307974 49.09754,-0.307879 49.098072,-0.307845 49.098622,-0.30791 49.100018,-0.307962 49.100672,-0.30807 49.103133,-0.304338 49.102556,-0.303284 49.102403,-0.302378 49.102322,-0.301869 49.102336,-0.298536 49.10264,-0.297423 49.102729,-0.295778 49.102873,-0.295512 49.102904,-0.295311 49.10298,-0.293928 49.103261,-0.292761 49.103418,-0.289876 49.103751,-0.288868 49.103842,-0.287203 49.103964,-0.284986 49.104118,-0.284161 49.104157,-0.28364 49.104134,-0.281639 49.104009)))"
]

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.281639 49.104009,-0.280597 49.103962,-0.279419 49.103882,-0.279067 49.103847,-0.278039 49.103695,-0.277589 49.103597,-0.276636 49.103334,-0.276251 49.103289,-0.275175 49.103214,-0.274669 49.103179,-0.27427 49.10312,-0.273791 49.103165,-0.272643 49.103149,-0.271199 49.10315,-0.27048 49.103161,-0.270091 49.103208,-0.269928 49.10325,-0.269463 49.103452,-0.269158 49.103697,-0.268685 49.104206,-0.267869 49.104125,-0.266834 49.103957,-0.264891 49.103627,-0.263961 49.103475,-0.263149 49.10338,-0.262477 49.103326,-0.261924 49.103299,-0.261157 49.103219,-0.26121 49.102886,-0.261247 49.102208,-0.261296 49.101797,-0.261072 49.101833,-0.259638 49.101707,-0.259387 49.101639,-0.25888 49.101876,-0.255704 49.100741,-0.255669 49.100625,-0.25355 49.100445,-0.253803 49.099935,-0.254115 49.099744,-0.254771 49.099231,-0.25258 49.098581,-0.252465 49.098718,-0.248931 49.097673,-0.249954 49.09685,-0.250457 49.096418,-0.250798 49.096177,-0.251047 49.095948,-0.25127 49.096078,-0.251197 49.09613,-0.251497 49.096245,-0.2523 49.09552,-0.252015 49.095408,-0.25261 49.094902,-0.253344 49.094315,-0.254041 49.093839,-0.254984 49.093262,-0.257733 49.091424,-0.257515 49.09125,-0.258346 49.090834,-0.25941 49.090321,-0.260171 49.089995,-0.261795 49.089337,-0.262558 49.089034,-0.264735 49.088054,-0.264861 49.088112,-0.2674 49.086668,-0.267342 49.086653,-0.266505 49.086177,-0.265735 49.086524,-0.264724 49.085788,-0.264444 49.085947,-0.263548 49.085303,-0.262941 49.085666,-0.26066 49.084099,-0.260458 49.083919,-0.26008 49.084027,-0.259427 49.083452,-0.259405 49.083349,-0.259163 49.083444,-0.258518 49.082874,-0.258095 49.082408,-0.257792 49.082108,-0.257333 49.081454,-0.256983 49.081049,-0.256746 49.080703,-0.256467 49.080359,-0.255868 49.079923,-0.255531 49.079595,-0.251939 49.07756,-0.251623 49.077396,-0.253522 49.076006,-0.253601 49.075895,-0.253262 49.075695,-0.254681 49.074356,-0.255557 49.074626,-0.256808 49.075046,-0.257752 49.075378,-0.258816 49.075736,-0.259381 49.075861,-0.260226 49.076023,-0.261291 49.076128,-0.261812 49.076227,-0.262979 49.076519,-0.264034 49.076742,-0.264599 49.076914,-0.265316 49.077192,-0.266976 49.078106,-0.26743 49.078262,-0.26803 49.078421,-0.268189 49.07825,-0.269144 49.077508,-0.269517 49.07719,-0.269702 49.077003,-0.269919 49.076841,-0.270427 49.076396,-0.270865 49.075918,-0.271321 49.075516,-0.271445 49.075381,-0.272216 49.074814,-0.272948 49.074407,-0.27351 49.074115,-0.273952 49.073829,-0.274304 49.073556,-0.275055 49.072893,-0.275395 49.072641,-0.275599 49.072423,-0.275655 49.072306,-0.27586 49.072209,-0.27598 49.072094,-0.276381 49.071809,-0.277055 49.071486,-0.277816 49.071163,-0.278317 49.071047,-0.278984 49.070872,-0.279496 49.07071,-0.280004 49.070597,-0.280335 49.070555,-0.280776 49.070231,-0.281149 49.0698,-0.286624 49.070177,-0.286767 49.070739,-0.290001 49.070812,-0.291871 49.070962,-0.293537 49.070917,-0.293567 49.071186,-0.295939 49.070942,-0.297086 49.072953,-0.297806 49.073338,-0.298302 49.073809,-0.300092 49.072711,-0.300245 49.072516,-0.304037 49.073998,-0.303551 49.074325,-0.304248 49.074756,-0.303909 49.075018,-0.304379 49.075311,-0.304721 49.075085,-0.305501 49.075074,-0.306305 49.074771,-0.306377 49.07487,-0.307341 49.076058,-0.30726 49.076699,-0.307055 49.076714,-0.307061 49.077252,-0.306843 49.077239,-0.306118 49.077147,-0.305737 49.077119,-0.30517 49.077157,-0.30488 49.077314,-0.304219 49.077807,-0.308388 49.078557,-0.308387 49.078323,-0.308813 49.07833,-0.309191 49.078473,-0.308355 49.080024,-0.311282 49.081042,-0.31052 49.081997,-0.313176 49.082956,-0.313035 49.083089,-0.312336 49.083837,-0.311518 49.084842,-0.311131 49.085297,-0.307985 49.089184,-0.307152 49.090515,-0.306873 49.09153,-0.306825 49.091766,-0.306445 49.091865,-0.306477 49.091902,-0.306672 49.092696,-0.30666 49.092732,-0.308308 49.092526,-0.30941 49.094483,-0.309256 49.094822,-0.3089 49.09534,-0.30869 49.095694,-0.308375 49.096332,-0.308036 49.097288,-0.307974 49.09754,-0.307879 49.098072,-0.307845 49.098622,-0.30791 49.100018,-0.307962 49.100672,-0.30807 49.103133,-0.304338 49.102556,-0.303284 49.102403,-0.302378 49.102322,-0.301869 49.102336,-0.298536 49.10264,-0.297423 49.102729,-0.295778 49.102873,-0.295512 49.102904,-0.295311 49.10298,-0.293928 49.103261,-0.292761 49.103418,-0.289876 49.103751,-0.288868 49.103842,-0.287203 49.103964,-0.284986 49.104118,-0.284161 49.104157,-0.28364 49.104134,-0.281639 49.104009)))'), 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)

1.90 ms
(0.06%)

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.34 ms
(0.04%)

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.281639 49.104009,-0.280597 49.103962,-0.279419 49.103882,-0.279067 49.103847,-0.278039 49.103695,-0.277589 49.103597,-0.276636 49.103334,-0.276251 49.103289,-0.275175 49.103214,-0.274669 49.103179,-0.27427 49.10312,-0.273791 49.103165,-0.272643 49.103149,-0.271199 49.10315,-0.27048 49.103161,-0.270091 49.103208,-0.269928 49.10325,-0.269463 49.103452,-0.269158 49.103697,-0.268685 49.104206,-0.267869 49.104125,-0.266834 49.103957,-0.264891 49.103627,-0.263961 49.103475,-0.263149 49.10338,-0.262477 49.103326,-0.261924 49.103299,-0.261157 49.103219,-0.26121 49.102886,-0.261247 49.102208,-0.261296 49.101797,-0.261072 49.101833,-0.259638 49.101707,-0.259387 49.101639,-0.25888 49.101876,-0.255704 49.100741,-0.255669 49.100625,-0.25355 49.100445,-0.253803 49.099935,-0.254115 49.099744,-0.254771 49.099231,-0.25258 49.098581,-0.252465 49.098718,-0.248931 49.097673,-0.249954 49.09685,-0.250457 49.096418,-0.250798 49.096177,-0.251047 49.095948,-0.25127 49.096078,-0.251197 49.09613,-0.251497 49.096245,-0.2523 49.09552,-0.252015 49.095408,-0.25261 49.094902,-0.253344 49.094315,-0.254041 49.093839,-0.254984 49.093262,-0.257733 49.091424,-0.257515 49.09125,-0.258346 49.090834,-0.25941 49.090321,-0.260171 49.089995,-0.261795 49.089337,-0.262558 49.089034,-0.264735 49.088054,-0.264861 49.088112,-0.2674 49.086668,-0.267342 49.086653,-0.266505 49.086177,-0.265735 49.086524,-0.264724 49.085788,-0.264444 49.085947,-0.263548 49.085303,-0.262941 49.085666,-0.26066 49.084099,-0.260458 49.083919,-0.26008 49.084027,-0.259427 49.083452,-0.259405 49.083349,-0.259163 49.083444,-0.258518 49.082874,-0.258095 49.082408,-0.257792 49.082108,-0.257333 49.081454,-0.256983 49.081049,-0.256746 49.080703,-0.256467 49.080359,-0.255868 49.079923,-0.255531 49.079595,-0.251939 49.07756,-0.251623 49.077396,-0.253522 49.076006,-0.253601 49.075895,-0.253262 49.075695,-0.254681 49.074356,-0.255557 49.074626,-0.256808 49.075046,-0.257752 49.075378,-0.258816 49.075736,-0.259381 49.075861,-0.260226 49.076023,-0.261291 49.076128,-0.261812 49.076227,-0.262979 49.076519,-0.264034 49.076742,-0.264599 49.076914,-0.265316 49.077192,-0.266976 49.078106,-0.26743 49.078262,-0.26803 49.078421,-0.268189 49.07825,-0.269144 49.077508,-0.269517 49.07719,-0.269702 49.077003,-0.269919 49.076841,-0.270427 49.076396,-0.270865 49.075918,-0.271321 49.075516,-0.271445 49.075381,-0.272216 49.074814,-0.272948 49.074407,-0.27351 49.074115,-0.273952 49.073829,-0.274304 49.073556,-0.275055 49.072893,-0.275395 49.072641,-0.275599 49.072423,-0.275655 49.072306,-0.27586 49.072209,-0.27598 49.072094,-0.276381 49.071809,-0.277055 49.071486,-0.277816 49.071163,-0.278317 49.071047,-0.278984 49.070872,-0.279496 49.07071,-0.280004 49.070597,-0.280335 49.070555,-0.280776 49.070231,-0.281149 49.0698,-0.286624 49.070177,-0.286767 49.070739,-0.290001 49.070812,-0.291871 49.070962,-0.293537 49.070917,-0.293567 49.071186,-0.295939 49.070942,-0.297086 49.072953,-0.297806 49.073338,-0.298302 49.073809,-0.300092 49.072711,-0.300245 49.072516,-0.304037 49.073998,-0.303551 49.074325,-0.304248 49.074756,-0.303909 49.075018,-0.304379 49.075311,-0.304721 49.075085,-0.305501 49.075074,-0.306305 49.074771,-0.306377 49.07487,-0.307341 49.076058,-0.30726 49.076699,-0.307055 49.076714,-0.307061 49.077252,-0.306843 49.077239,-0.306118 49.077147,-0.305737 49.077119,-0.30517 49.077157,-0.30488 49.077314,-0.304219 49.077807,-0.308388 49.078557,-0.308387 49.078323,-0.308813 49.07833,-0.309191 49.078473,-0.308355 49.080024,-0.311282 49.081042,-0.31052 49.081997,-0.313176 49.082956,-0.313035 49.083089,-0.312336 49.083837,-0.311518 49.084842,-0.311131 49.085297,-0.307985 49.089184,-0.307152 49.090515,-0.306873 49.09153,-0.306825 49.091766,-0.306445 49.091865,-0.306477 49.091902,-0.306672 49.092696,-0.30666 49.092732,-0.308308 49.092526,-0.30941 49.094483,-0.309256 49.094822,-0.3089 49.09534,-0.30869 49.095694,-0.308375 49.096332,-0.308036 49.097288,-0.307974 49.09754,-0.307879 49.098072,-0.307845 49.098622,-0.30791 49.100018,-0.307962 49.100672,-0.30807 49.103133,-0.304338 49.102556,-0.303284 49.102403,-0.302378 49.102322,-0.301869 49.102336,-0.298536 49.10264,-0.297423 49.102729,-0.295778 49.102873,-0.295512 49.102904,-0.295311 49.10298,-0.293928 49.103261,-0.292761 49.103418,-0.289876 49.103751,-0.288868 49.103842,-0.287203 49.103964,-0.284986 49.104118,-0.284161 49.104157,-0.28364 49.104134,-0.281639 49.104009)))"
  "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.281639 49.104009,-0.280597 49.103962,-0.279419 49.103882,-0.279067 49.103847,-0.278039 49.103695,-0.277589 49.103597,-0.276636 49.103334,-0.276251 49.103289,-0.275175 49.103214,-0.274669 49.103179,-0.27427 49.10312,-0.273791 49.103165,-0.272643 49.103149,-0.271199 49.10315,-0.27048 49.103161,-0.270091 49.103208,-0.269928 49.10325,-0.269463 49.103452,-0.269158 49.103697,-0.268685 49.104206,-0.267869 49.104125,-0.266834 49.103957,-0.264891 49.103627,-0.263961 49.103475,-0.263149 49.10338,-0.262477 49.103326,-0.261924 49.103299,-0.261157 49.103219,-0.26121 49.102886,-0.261247 49.102208,-0.261296 49.101797,-0.261072 49.101833,-0.259638 49.101707,-0.259387 49.101639,-0.25888 49.101876,-0.255704 49.100741,-0.255669 49.100625,-0.25355 49.100445,-0.253803 49.099935,-0.254115 49.099744,-0.254771 49.099231,-0.25258 49.098581,-0.252465 49.098718,-0.248931 49.097673,-0.249954 49.09685,-0.250457 49.096418,-0.250798 49.096177,-0.251047 49.095948,-0.25127 49.096078,-0.251197 49.09613,-0.251497 49.096245,-0.2523 49.09552,-0.252015 49.095408,-0.25261 49.094902,-0.253344 49.094315,-0.254041 49.093839,-0.254984 49.093262,-0.257733 49.091424,-0.257515 49.09125,-0.258346 49.090834,-0.25941 49.090321,-0.260171 49.089995,-0.261795 49.089337,-0.262558 49.089034,-0.264735 49.088054,-0.264861 49.088112,-0.2674 49.086668,-0.267342 49.086653,-0.266505 49.086177,-0.265735 49.086524,-0.264724 49.085788,-0.264444 49.085947,-0.263548 49.085303,-0.262941 49.085666,-0.26066 49.084099,-0.260458 49.083919,-0.26008 49.084027,-0.259427 49.083452,-0.259405 49.083349,-0.259163 49.083444,-0.258518 49.082874,-0.258095 49.082408,-0.257792 49.082108,-0.257333 49.081454,-0.256983 49.081049,-0.256746 49.080703,-0.256467 49.080359,-0.255868 49.079923,-0.255531 49.079595,-0.251939 49.07756,-0.251623 49.077396,-0.253522 49.076006,-0.253601 49.075895,-0.253262 49.075695,-0.254681 49.074356,-0.255557 49.074626,-0.256808 49.075046,-0.257752 49.075378,-0.258816 49.075736,-0.259381 49.075861,-0.260226 49.076023,-0.261291 49.076128,-0.261812 49.076227,-0.262979 49.076519,-0.264034 49.076742,-0.264599 49.076914,-0.265316 49.077192,-0.266976 49.078106,-0.26743 49.078262,-0.26803 49.078421,-0.268189 49.07825,-0.269144 49.077508,-0.269517 49.07719,-0.269702 49.077003,-0.269919 49.076841,-0.270427 49.076396,-0.270865 49.075918,-0.271321 49.075516,-0.271445 49.075381,-0.272216 49.074814,-0.272948 49.074407,-0.27351 49.074115,-0.273952 49.073829,-0.274304 49.073556,-0.275055 49.072893,-0.275395 49.072641,-0.275599 49.072423,-0.275655 49.072306,-0.27586 49.072209,-0.27598 49.072094,-0.276381 49.071809,-0.277055 49.071486,-0.277816 49.071163,-0.278317 49.071047,-0.278984 49.070872,-0.279496 49.07071,-0.280004 49.070597,-0.280335 49.070555,-0.280776 49.070231,-0.281149 49.0698,-0.286624 49.070177,-0.286767 49.070739,-0.290001 49.070812,-0.291871 49.070962,-0.293537 49.070917,-0.293567 49.071186,-0.295939 49.070942,-0.297086 49.072953,-0.297806 49.073338,-0.298302 49.073809,-0.300092 49.072711,-0.300245 49.072516,-0.304037 49.073998,-0.303551 49.074325,-0.304248 49.074756,-0.303909 49.075018,-0.304379 49.075311,-0.304721 49.075085,-0.305501 49.075074,-0.306305 49.074771,-0.306377 49.07487,-0.307341 49.076058,-0.30726 49.076699,-0.307055 49.076714,-0.307061 49.077252,-0.306843 49.077239,-0.306118 49.077147,-0.305737 49.077119,-0.30517 49.077157,-0.30488 49.077314,-0.304219 49.077807,-0.308388 49.078557,-0.308387 49.078323,-0.308813 49.07833,-0.309191 49.078473,-0.308355 49.080024,-0.311282 49.081042,-0.31052 49.081997,-0.313176 49.082956,-0.313035 49.083089,-0.312336 49.083837,-0.311518 49.084842,-0.311131 49.085297,-0.307985 49.089184,-0.307152 49.090515,-0.306873 49.09153,-0.306825 49.091766,-0.306445 49.091865,-0.306477 49.091902,-0.306672 49.092696,-0.30666 49.092732,-0.308308 49.092526,-0.30941 49.094483,-0.309256 49.094822,-0.3089 49.09534,-0.30869 49.095694,-0.308375 49.096332,-0.308036 49.097288,-0.307974 49.09754,-0.307879 49.098072,-0.307845 49.098622,-0.30791 49.100018,-0.307962 49.100672,-0.30807 49.103133,-0.304338 49.102556,-0.303284 49.102403,-0.302378 49.102322,-0.301869 49.102336,-0.298536 49.10264,-0.297423 49.102729,-0.295778 49.102873,-0.295512 49.102904,-0.295311 49.10298,-0.293928 49.103261,-0.292761 49.103418,-0.289876 49.103751,-0.288868 49.103842,-0.287203 49.103964,-0.284986 49.104118,-0.284161 49.104157,-0.28364 49.104134,-0.281639 49.104009)))', 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.29 ms
(0.04%)

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.281639 49.104009,-0.280597 49.103962,-0.279419 49.103882,-0.279067 49.103847,-0.278039 49.103695,-0.277589 49.103597,-0.276636 49.103334,-0.276251 49.103289,-0.275175 49.103214,-0.274669 49.103179,-0.27427 49.10312,-0.273791 49.103165,-0.272643 49.103149,-0.271199 49.10315,-0.27048 49.103161,-0.270091 49.103208,-0.269928 49.10325,-0.269463 49.103452,-0.269158 49.103697,-0.268685 49.104206,-0.267869 49.104125,-0.266834 49.103957,-0.264891 49.103627,-0.263961 49.103475,-0.263149 49.10338,-0.262477 49.103326,-0.261924 49.103299,-0.261157 49.103219,-0.26121 49.102886,-0.261247 49.102208,-0.261296 49.101797,-0.261072 49.101833,-0.259638 49.101707,-0.259387 49.101639,-0.25888 49.101876,-0.255704 49.100741,-0.255669 49.100625,-0.25355 49.100445,-0.253803 49.099935,-0.254115 49.099744,-0.254771 49.099231,-0.25258 49.098581,-0.252465 49.098718,-0.248931 49.097673,-0.249954 49.09685,-0.250457 49.096418,-0.250798 49.096177,-0.251047 49.095948,-0.25127 49.096078,-0.251197 49.09613,-0.251497 49.096245,-0.2523 49.09552,-0.252015 49.095408,-0.25261 49.094902,-0.253344 49.094315,-0.254041 49.093839,-0.254984 49.093262,-0.257733 49.091424,-0.257515 49.09125,-0.258346 49.090834,-0.25941 49.090321,-0.260171 49.089995,-0.261795 49.089337,-0.262558 49.089034,-0.264735 49.088054,-0.264861 49.088112,-0.2674 49.086668,-0.267342 49.086653,-0.266505 49.086177,-0.265735 49.086524,-0.264724 49.085788,-0.264444 49.085947,-0.263548 49.085303,-0.262941 49.085666,-0.26066 49.084099,-0.260458 49.083919,-0.26008 49.084027,-0.259427 49.083452,-0.259405 49.083349,-0.259163 49.083444,-0.258518 49.082874,-0.258095 49.082408,-0.257792 49.082108,-0.257333 49.081454,-0.256983 49.081049,-0.256746 49.080703,-0.256467 49.080359,-0.255868 49.079923,-0.255531 49.079595,-0.251939 49.07756,-0.251623 49.077396,-0.253522 49.076006,-0.253601 49.075895,-0.253262 49.075695,-0.254681 49.074356,-0.255557 49.074626,-0.256808 49.075046,-0.257752 49.075378,-0.258816 49.075736,-0.259381 49.075861,-0.260226 49.076023,-0.261291 49.076128,-0.261812 49.076227,-0.262979 49.076519,-0.264034 49.076742,-0.264599 49.076914,-0.265316 49.077192,-0.266976 49.078106,-0.26743 49.078262,-0.26803 49.078421,-0.268189 49.07825,-0.269144 49.077508,-0.269517 49.07719,-0.269702 49.077003,-0.269919 49.076841,-0.270427 49.076396,-0.270865 49.075918,-0.271321 49.075516,-0.271445 49.075381,-0.272216 49.074814,-0.272948 49.074407,-0.27351 49.074115,-0.273952 49.073829,-0.274304 49.073556,-0.275055 49.072893,-0.275395 49.072641,-0.275599 49.072423,-0.275655 49.072306,-0.27586 49.072209,-0.27598 49.072094,-0.276381 49.071809,-0.277055 49.071486,-0.277816 49.071163,-0.278317 49.071047,-0.278984 49.070872,-0.279496 49.07071,-0.280004 49.070597,-0.280335 49.070555,-0.280776 49.070231,-0.281149 49.0698,-0.286624 49.070177,-0.286767 49.070739,-0.290001 49.070812,-0.291871 49.070962,-0.293537 49.070917,-0.293567 49.071186,-0.295939 49.070942,-0.297086 49.072953,-0.297806 49.073338,-0.298302 49.073809,-0.300092 49.072711,-0.300245 49.072516,-0.304037 49.073998,-0.303551 49.074325,-0.304248 49.074756,-0.303909 49.075018,-0.304379 49.075311,-0.304721 49.075085,-0.305501 49.075074,-0.306305 49.074771,-0.306377 49.07487,-0.307341 49.076058,-0.30726 49.076699,-0.307055 49.076714,-0.307061 49.077252,-0.306843 49.077239,-0.306118 49.077147,-0.305737 49.077119,-0.30517 49.077157,-0.30488 49.077314,-0.304219 49.077807,-0.308388 49.078557,-0.308387 49.078323,-0.308813 49.07833,-0.309191 49.078473,-0.308355 49.080024,-0.311282 49.081042,-0.31052 49.081997,-0.313176 49.082956,-0.313035 49.083089,-0.312336 49.083837,-0.311518 49.084842,-0.311131 49.085297,-0.307985 49.089184,-0.307152 49.090515,-0.306873 49.09153,-0.306825 49.091766,-0.306445 49.091865,-0.306477 49.091902,-0.306672 49.092696,-0.30666 49.092732,-0.308308 49.092526,-0.30941 49.094483,-0.309256 49.094822,-0.3089 49.09534,-0.30869 49.095694,-0.308375 49.096332,-0.308036 49.097288,-0.307974 49.09754,-0.307879 49.098072,-0.307845 49.098622,-0.30791 49.100018,-0.307962 49.100672,-0.30807 49.103133,-0.304338 49.102556,-0.303284 49.102403,-0.302378 49.102322,-0.301869 49.102336,-0.298536 49.10264,-0.297423 49.102729,-0.295778 49.102873,-0.295512 49.102904,-0.295311 49.10298,-0.293928 49.103261,-0.292761 49.103418,-0.289876 49.103751,-0.288868 49.103842,-0.287203 49.103964,-0.284986 49.104118,-0.284161 49.104157,-0.28364 49.104134,-0.281639 49.104009)))"
]

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.281639 49.104009,-0.280597 49.103962,-0.279419 49.103882,-0.279067 49.103847,-0.278039 49.103695,-0.277589 49.103597,-0.276636 49.103334,-0.276251 49.103289,-0.275175 49.103214,-0.274669 49.103179,-0.27427 49.10312,-0.273791 49.103165,-0.272643 49.103149,-0.271199 49.10315,-0.27048 49.103161,-0.270091 49.103208,-0.269928 49.10325,-0.269463 49.103452,-0.269158 49.103697,-0.268685 49.104206,-0.267869 49.104125,-0.266834 49.103957,-0.264891 49.103627,-0.263961 49.103475,-0.263149 49.10338,-0.262477 49.103326,-0.261924 49.103299,-0.261157 49.103219,-0.26121 49.102886,-0.261247 49.102208,-0.261296 49.101797,-0.261072 49.101833,-0.259638 49.101707,-0.259387 49.101639,-0.25888 49.101876,-0.255704 49.100741,-0.255669 49.100625,-0.25355 49.100445,-0.253803 49.099935,-0.254115 49.099744,-0.254771 49.099231,-0.25258 49.098581,-0.252465 49.098718,-0.248931 49.097673,-0.249954 49.09685,-0.250457 49.096418,-0.250798 49.096177,-0.251047 49.095948,-0.25127 49.096078,-0.251197 49.09613,-0.251497 49.096245,-0.2523 49.09552,-0.252015 49.095408,-0.25261 49.094902,-0.253344 49.094315,-0.254041 49.093839,-0.254984 49.093262,-0.257733 49.091424,-0.257515 49.09125,-0.258346 49.090834,-0.25941 49.090321,-0.260171 49.089995,-0.261795 49.089337,-0.262558 49.089034,-0.264735 49.088054,-0.264861 49.088112,-0.2674 49.086668,-0.267342 49.086653,-0.266505 49.086177,-0.265735 49.086524,-0.264724 49.085788,-0.264444 49.085947,-0.263548 49.085303,-0.262941 49.085666,-0.26066 49.084099,-0.260458 49.083919,-0.26008 49.084027,-0.259427 49.083452,-0.259405 49.083349,-0.259163 49.083444,-0.258518 49.082874,-0.258095 49.082408,-0.257792 49.082108,-0.257333 49.081454,-0.256983 49.081049,-0.256746 49.080703,-0.256467 49.080359,-0.255868 49.079923,-0.255531 49.079595,-0.251939 49.07756,-0.251623 49.077396,-0.253522 49.076006,-0.253601 49.075895,-0.253262 49.075695,-0.254681 49.074356,-0.255557 49.074626,-0.256808 49.075046,-0.257752 49.075378,-0.258816 49.075736,-0.259381 49.075861,-0.260226 49.076023,-0.261291 49.076128,-0.261812 49.076227,-0.262979 49.076519,-0.264034 49.076742,-0.264599 49.076914,-0.265316 49.077192,-0.266976 49.078106,-0.26743 49.078262,-0.26803 49.078421,-0.268189 49.07825,-0.269144 49.077508,-0.269517 49.07719,-0.269702 49.077003,-0.269919 49.076841,-0.270427 49.076396,-0.270865 49.075918,-0.271321 49.075516,-0.271445 49.075381,-0.272216 49.074814,-0.272948 49.074407,-0.27351 49.074115,-0.273952 49.073829,-0.274304 49.073556,-0.275055 49.072893,-0.275395 49.072641,-0.275599 49.072423,-0.275655 49.072306,-0.27586 49.072209,-0.27598 49.072094,-0.276381 49.071809,-0.277055 49.071486,-0.277816 49.071163,-0.278317 49.071047,-0.278984 49.070872,-0.279496 49.07071,-0.280004 49.070597,-0.280335 49.070555,-0.280776 49.070231,-0.281149 49.0698,-0.286624 49.070177,-0.286767 49.070739,-0.290001 49.070812,-0.291871 49.070962,-0.293537 49.070917,-0.293567 49.071186,-0.295939 49.070942,-0.297086 49.072953,-0.297806 49.073338,-0.298302 49.073809,-0.300092 49.072711,-0.300245 49.072516,-0.304037 49.073998,-0.303551 49.074325,-0.304248 49.074756,-0.303909 49.075018,-0.304379 49.075311,-0.304721 49.075085,-0.305501 49.075074,-0.306305 49.074771,-0.306377 49.07487,-0.307341 49.076058,-0.30726 49.076699,-0.307055 49.076714,-0.307061 49.077252,-0.306843 49.077239,-0.306118 49.077147,-0.305737 49.077119,-0.30517 49.077157,-0.30488 49.077314,-0.304219 49.077807,-0.308388 49.078557,-0.308387 49.078323,-0.308813 49.07833,-0.309191 49.078473,-0.308355 49.080024,-0.311282 49.081042,-0.31052 49.081997,-0.313176 49.082956,-0.313035 49.083089,-0.312336 49.083837,-0.311518 49.084842,-0.311131 49.085297,-0.307985 49.089184,-0.307152 49.090515,-0.306873 49.09153,-0.306825 49.091766,-0.306445 49.091865,-0.306477 49.091902,-0.306672 49.092696,-0.30666 49.092732,-0.308308 49.092526,-0.30941 49.094483,-0.309256 49.094822,-0.3089 49.09534,-0.30869 49.095694,-0.308375 49.096332,-0.308036 49.097288,-0.307974 49.09754,-0.307879 49.098072,-0.307845 49.098622,-0.30791 49.100018,-0.307962 49.100672,-0.30807 49.103133,-0.304338 49.102556,-0.303284 49.102403,-0.302378 49.102322,-0.301869 49.102336,-0.298536 49.10264,-0.297423 49.102729,-0.295778 49.102873,-0.295512 49.102904,-0.295311 49.10298,-0.293928 49.103261,-0.292761 49.103418,-0.289876 49.103751,-0.288868 49.103842,-0.287203 49.103964,-0.284986 49.104118,-0.284161 49.104157,-0.28364 49.104134,-0.281639 49.104009)))', 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.21 ms
(0.04%)

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.76 ms
(0.02%)

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->getClosedLikePlaces (line 177)
5	App\Controller\AdminController->stats (line 827)
6	Symfony\Component\HttpKernel\HttpKernel->handleRaw (line 183)
7	Symfony\Component\HttpKernel\HttpKernel->handle (line 76)
8	Symfony\Component\HttpKernel\Kernel->handle (line 182)
9	Symfony\Component\Runtime\Runner\Symfony\HttpKernelRunner->run (line 35)
10	require_once (line 29)
11	require_once (line 5)

0.69 ms
(0.02%)

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.281639 49.104009,-0.280597 49.103962,-0.279419 49.103882,-0.279067 49.103847,-0.278039 49.103695,-0.277589 49.103597,-0.276636 49.103334,-0.276251 49.103289,-0.275175 49.103214,-0.274669 49.103179,-0.27427 49.10312,-0.273791 49.103165,-0.272643 49.103149,-0.271199 49.10315,-0.27048 49.103161,-0.270091 49.103208,-0.269928 49.10325,-0.269463 49.103452,-0.269158 49.103697,-0.268685 49.104206,-0.267869 49.104125,-0.266834 49.103957,-0.264891 49.103627,-0.263961 49.103475,-0.263149 49.10338,-0.262477 49.103326,-0.261924 49.103299,-0.261157 49.103219,-0.26121 49.102886,-0.261247 49.102208,-0.261296 49.101797,-0.261072 49.101833,-0.259638 49.101707,-0.259387 49.101639,-0.25888 49.101876,-0.255704 49.100741,-0.255669 49.100625,-0.25355 49.100445,-0.253803 49.099935,-0.254115 49.099744,-0.254771 49.099231,-0.25258 49.098581,-0.252465 49.098718,-0.248931 49.097673,-0.249954 49.09685,-0.250457 49.096418,-0.250798 49.096177,-0.251047 49.095948,-0.25127 49.096078,-0.251197 49.09613,-0.251497 49.096245,-0.2523 49.09552,-0.252015 49.095408,-0.25261 49.094902,-0.253344 49.094315,-0.254041 49.093839,-0.254984 49.093262,-0.257733 49.091424,-0.257515 49.09125,-0.258346 49.090834,-0.25941 49.090321,-0.260171 49.089995,-0.261795 49.089337,-0.262558 49.089034,-0.264735 49.088054,-0.264861 49.088112,-0.2674 49.086668,-0.267342 49.086653,-0.266505 49.086177,-0.265735 49.086524,-0.264724 49.085788,-0.264444 49.085947,-0.263548 49.085303,-0.262941 49.085666,-0.26066 49.084099,-0.260458 49.083919,-0.26008 49.084027,-0.259427 49.083452,-0.259405 49.083349,-0.259163 49.083444,-0.258518 49.082874,-0.258095 49.082408,-0.257792 49.082108,-0.257333 49.081454,-0.256983 49.081049,-0.256746 49.080703,-0.256467 49.080359,-0.255868 49.079923,-0.255531 49.079595,-0.251939 49.07756,-0.251623 49.077396,-0.253522 49.076006,-0.253601 49.075895,-0.253262 49.075695,-0.254681 49.074356,-0.255557 49.074626,-0.256808 49.075046,-0.257752 49.075378,-0.258816 49.075736,-0.259381 49.075861,-0.260226 49.076023,-0.261291 49.076128,-0.261812 49.076227,-0.262979 49.076519,-0.264034 49.076742,-0.264599 49.076914,-0.265316 49.077192,-0.266976 49.078106,-0.26743 49.078262,-0.26803 49.078421,-0.268189 49.07825,-0.269144 49.077508,-0.269517 49.07719,-0.269702 49.077003,-0.269919 49.076841,-0.270427 49.076396,-0.270865 49.075918,-0.271321 49.075516,-0.271445 49.075381,-0.272216 49.074814,-0.272948 49.074407,-0.27351 49.074115,-0.273952 49.073829,-0.274304 49.073556,-0.275055 49.072893,-0.275395 49.072641,-0.275599 49.072423,-0.275655 49.072306,-0.27586 49.072209,-0.27598 49.072094,-0.276381 49.071809,-0.277055 49.071486,-0.277816 49.071163,-0.278317 49.071047,-0.278984 49.070872,-0.279496 49.07071,-0.280004 49.070597,-0.280335 49.070555,-0.280776 49.070231,-0.281149 49.0698,-0.286624 49.070177,-0.286767 49.070739,-0.290001 49.070812,-0.291871 49.070962,-0.293537 49.070917,-0.293567 49.071186,-0.295939 49.070942,-0.297086 49.072953,-0.297806 49.073338,-0.298302 49.073809,-0.300092 49.072711,-0.300245 49.072516,-0.304037 49.073998,-0.303551 49.074325,-0.304248 49.074756,-0.303909 49.075018,-0.304379 49.075311,-0.304721 49.075085,-0.305501 49.075074,-0.306305 49.074771,-0.306377 49.07487,-0.307341 49.076058,-0.30726 49.076699,-0.307055 49.076714,-0.307061 49.077252,-0.306843 49.077239,-0.306118 49.077147,-0.305737 49.077119,-0.30517 49.077157,-0.30488 49.077314,-0.304219 49.077807,-0.308388 49.078557,-0.308387 49.078323,-0.308813 49.07833,-0.309191 49.078473,-0.308355 49.080024,-0.311282 49.081042,-0.31052 49.081997,-0.313176 49.082956,-0.313035 49.083089,-0.312336 49.083837,-0.311518 49.084842,-0.311131 49.085297,-0.307985 49.089184,-0.307152 49.090515,-0.306873 49.09153,-0.306825 49.091766,-0.306445 49.091865,-0.306477 49.091902,-0.306672 49.092696,-0.30666 49.092732,-0.308308 49.092526,-0.30941 49.094483,-0.309256 49.094822,-0.3089 49.09534,-0.30869 49.095694,-0.308375 49.096332,-0.308036 49.097288,-0.307974 49.09754,-0.307879 49.098072,-0.307845 49.098622,-0.30791 49.100018,-0.307962 49.100672,-0.30807 49.103133,-0.304338 49.102556,-0.303284 49.102403,-0.302378 49.102322,-0.301869 49.102336,-0.298536 49.10264,-0.297423 49.102729,-0.295778 49.102873,-0.295512 49.102904,-0.295311 49.10298,-0.293928 49.103261,-0.292761 49.103418,-0.289876 49.103751,-0.288868 49.103842,-0.287203 49.103964,-0.284986 49.104118,-0.284161 49.104157,-0.28364 49.104134,-0.281639 49.104009)))"
]

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.281639 49.104009,-0.280597 49.103962,-0.279419 49.103882,-0.279067 49.103847,-0.278039 49.103695,-0.277589 49.103597,-0.276636 49.103334,-0.276251 49.103289,-0.275175 49.103214,-0.274669 49.103179,-0.27427 49.10312,-0.273791 49.103165,-0.272643 49.103149,-0.271199 49.10315,-0.27048 49.103161,-0.270091 49.103208,-0.269928 49.10325,-0.269463 49.103452,-0.269158 49.103697,-0.268685 49.104206,-0.267869 49.104125,-0.266834 49.103957,-0.264891 49.103627,-0.263961 49.103475,-0.263149 49.10338,-0.262477 49.103326,-0.261924 49.103299,-0.261157 49.103219,-0.26121 49.102886,-0.261247 49.102208,-0.261296 49.101797,-0.261072 49.101833,-0.259638 49.101707,-0.259387 49.101639,-0.25888 49.101876,-0.255704 49.100741,-0.255669 49.100625,-0.25355 49.100445,-0.253803 49.099935,-0.254115 49.099744,-0.254771 49.099231,-0.25258 49.098581,-0.252465 49.098718,-0.248931 49.097673,-0.249954 49.09685,-0.250457 49.096418,-0.250798 49.096177,-0.251047 49.095948,-0.25127 49.096078,-0.251197 49.09613,-0.251497 49.096245,-0.2523 49.09552,-0.252015 49.095408,-0.25261 49.094902,-0.253344 49.094315,-0.254041 49.093839,-0.254984 49.093262,-0.257733 49.091424,-0.257515 49.09125,-0.258346 49.090834,-0.25941 49.090321,-0.260171 49.089995,-0.261795 49.089337,-0.262558 49.089034,-0.264735 49.088054,-0.264861 49.088112,-0.2674 49.086668,-0.267342 49.086653,-0.266505 49.086177,-0.265735 49.086524,-0.264724 49.085788,-0.264444 49.085947,-0.263548 49.085303,-0.262941 49.085666,-0.26066 49.084099,-0.260458 49.083919,-0.26008 49.084027,-0.259427 49.083452,-0.259405 49.083349,-0.259163 49.083444,-0.258518 49.082874,-0.258095 49.082408,-0.257792 49.082108,-0.257333 49.081454,-0.256983 49.081049,-0.256746 49.080703,-0.256467 49.080359,-0.255868 49.079923,-0.255531 49.079595,-0.251939 49.07756,-0.251623 49.077396,-0.253522 49.076006,-0.253601 49.075895,-0.253262 49.075695,-0.254681 49.074356,-0.255557 49.074626,-0.256808 49.075046,-0.257752 49.075378,-0.258816 49.075736,-0.259381 49.075861,-0.260226 49.076023,-0.261291 49.076128,-0.261812 49.076227,-0.262979 49.076519,-0.264034 49.076742,-0.264599 49.076914,-0.265316 49.077192,-0.266976 49.078106,-0.26743 49.078262,-0.26803 49.078421,-0.268189 49.07825,-0.269144 49.077508,-0.269517 49.07719,-0.269702 49.077003,-0.269919 49.076841,-0.270427 49.076396,-0.270865 49.075918,-0.271321 49.075516,-0.271445 49.075381,-0.272216 49.074814,-0.272948 49.074407,-0.27351 49.074115,-0.273952 49.073829,-0.274304 49.073556,-0.275055 49.072893,-0.275395 49.072641,-0.275599 49.072423,-0.275655 49.072306,-0.27586 49.072209,-0.27598 49.072094,-0.276381 49.071809,-0.277055 49.071486,-0.277816 49.071163,-0.278317 49.071047,-0.278984 49.070872,-0.279496 49.07071,-0.280004 49.070597,-0.280335 49.070555,-0.280776 49.070231,-0.281149 49.0698,-0.286624 49.070177,-0.286767 49.070739,-0.290001 49.070812,-0.291871 49.070962,-0.293537 49.070917,-0.293567 49.071186,-0.295939 49.070942,-0.297086 49.072953,-0.297806 49.073338,-0.298302 49.073809,-0.300092 49.072711,-0.300245 49.072516,-0.304037 49.073998,-0.303551 49.074325,-0.304248 49.074756,-0.303909 49.075018,-0.304379 49.075311,-0.304721 49.075085,-0.305501 49.075074,-0.306305 49.074771,-0.306377 49.07487,-0.307341 49.076058,-0.30726 49.076699,-0.307055 49.076714,-0.307061 49.077252,-0.306843 49.077239,-0.306118 49.077147,-0.305737 49.077119,-0.30517 49.077157,-0.30488 49.077314,-0.304219 49.077807,-0.308388 49.078557,-0.308387 49.078323,-0.308813 49.07833,-0.309191 49.078473,-0.308355 49.080024,-0.311282 49.081042,-0.31052 49.081997,-0.313176 49.082956,-0.313035 49.083089,-0.312336 49.083837,-0.311518 49.084842,-0.311131 49.085297,-0.307985 49.089184,-0.307152 49.090515,-0.306873 49.09153,-0.306825 49.091766,-0.306445 49.091865,-0.306477 49.091902,-0.306672 49.092696,-0.30666 49.092732,-0.308308 49.092526,-0.30941 49.094483,-0.309256 49.094822,-0.3089 49.09534,-0.30869 49.095694,-0.308375 49.096332,-0.308036 49.097288,-0.307974 49.09754,-0.307879 49.098072,-0.307845 49.098622,-0.30791 49.100018,-0.307962 49.100672,-0.30807 49.103133,-0.304338 49.102556,-0.303284 49.102403,-0.302378 49.102322,-0.301869 49.102336,-0.298536 49.10264,-0.297423 49.102729,-0.295778 49.102873,-0.295512 49.102904,-0.295311 49.10298,-0.293928 49.103261,-0.292761 49.103418,-0.289876 49.103751,-0.288868 49.103842,-0.287203 49.103964,-0.284986 49.104118,-0.284161 49.104157,-0.28364 49.104134,-0.281639 49.104009)))', 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.64 ms
(0.02%)

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

Parameters:

[
  "14554"
]

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

#	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.64 ms
(0.02%)

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.281639 49.104009,-0.280597 49.103962,-0.279419 49.103882,-0.279067 49.103847,-0.278039 49.103695,-0.277589 49.103597,-0.276636 49.103334,-0.276251 49.103289,-0.275175 49.103214,-0.274669 49.103179,-0.27427 49.10312,-0.273791 49.103165,-0.272643 49.103149,-0.271199 49.10315,-0.27048 49.103161,-0.270091 49.103208,-0.269928 49.10325,-0.269463 49.103452,-0.269158 49.103697,-0.268685 49.104206,-0.267869 49.104125,-0.266834 49.103957,-0.264891 49.103627,-0.263961 49.103475,-0.263149 49.10338,-0.262477 49.103326,-0.261924 49.103299,-0.261157 49.103219,-0.26121 49.102886,-0.261247 49.102208,-0.261296 49.101797,-0.261072 49.101833,-0.259638 49.101707,-0.259387 49.101639,-0.25888 49.101876,-0.255704 49.100741,-0.255669 49.100625,-0.25355 49.100445,-0.253803 49.099935,-0.254115 49.099744,-0.254771 49.099231,-0.25258 49.098581,-0.252465 49.098718,-0.248931 49.097673,-0.249954 49.09685,-0.250457 49.096418,-0.250798 49.096177,-0.251047 49.095948,-0.25127 49.096078,-0.251197 49.09613,-0.251497 49.096245,-0.2523 49.09552,-0.252015 49.095408,-0.25261 49.094902,-0.253344 49.094315,-0.254041 49.093839,-0.254984 49.093262,-0.257733 49.091424,-0.257515 49.09125,-0.258346 49.090834,-0.25941 49.090321,-0.260171 49.089995,-0.261795 49.089337,-0.262558 49.089034,-0.264735 49.088054,-0.264861 49.088112,-0.2674 49.086668,-0.267342 49.086653,-0.266505 49.086177,-0.265735 49.086524,-0.264724 49.085788,-0.264444 49.085947,-0.263548 49.085303,-0.262941 49.085666,-0.26066 49.084099,-0.260458 49.083919,-0.26008 49.084027,-0.259427 49.083452,-0.259405 49.083349,-0.259163 49.083444,-0.258518 49.082874,-0.258095 49.082408,-0.257792 49.082108,-0.257333 49.081454,-0.256983 49.081049,-0.256746 49.080703,-0.256467 49.080359,-0.255868 49.079923,-0.255531 49.079595,-0.251939 49.07756,-0.251623 49.077396,-0.253522 49.076006,-0.253601 49.075895,-0.253262 49.075695,-0.254681 49.074356,-0.255557 49.074626,-0.256808 49.075046,-0.257752 49.075378,-0.258816 49.075736,-0.259381 49.075861,-0.260226 49.076023,-0.261291 49.076128,-0.261812 49.076227,-0.262979 49.076519,-0.264034 49.076742,-0.264599 49.076914,-0.265316 49.077192,-0.266976 49.078106,-0.26743 49.078262,-0.26803 49.078421,-0.268189 49.07825,-0.269144 49.077508,-0.269517 49.07719,-0.269702 49.077003,-0.269919 49.076841,-0.270427 49.076396,-0.270865 49.075918,-0.271321 49.075516,-0.271445 49.075381,-0.272216 49.074814,-0.272948 49.074407,-0.27351 49.074115,-0.273952 49.073829,-0.274304 49.073556,-0.275055 49.072893,-0.275395 49.072641,-0.275599 49.072423,-0.275655 49.072306,-0.27586 49.072209,-0.27598 49.072094,-0.276381 49.071809,-0.277055 49.071486,-0.277816 49.071163,-0.278317 49.071047,-0.278984 49.070872,-0.279496 49.07071,-0.280004 49.070597,-0.280335 49.070555,-0.280776 49.070231,-0.281149 49.0698,-0.286624 49.070177,-0.286767 49.070739,-0.290001 49.070812,-0.291871 49.070962,-0.293537 49.070917,-0.293567 49.071186,-0.295939 49.070942,-0.297086 49.072953,-0.297806 49.073338,-0.298302 49.073809,-0.300092 49.072711,-0.300245 49.072516,-0.304037 49.073998,-0.303551 49.074325,-0.304248 49.074756,-0.303909 49.075018,-0.304379 49.075311,-0.304721 49.075085,-0.305501 49.075074,-0.306305 49.074771,-0.306377 49.07487,-0.307341 49.076058,-0.30726 49.076699,-0.307055 49.076714,-0.307061 49.077252,-0.306843 49.077239,-0.306118 49.077147,-0.305737 49.077119,-0.30517 49.077157,-0.30488 49.077314,-0.304219 49.077807,-0.308388 49.078557,-0.308387 49.078323,-0.308813 49.07833,-0.309191 49.078473,-0.308355 49.080024,-0.311282 49.081042,-0.31052 49.081997,-0.313176 49.082956,-0.313035 49.083089,-0.312336 49.083837,-0.311518 49.084842,-0.311131 49.085297,-0.307985 49.089184,-0.307152 49.090515,-0.306873 49.09153,-0.306825 49.091766,-0.306445 49.091865,-0.306477 49.091902,-0.306672 49.092696,-0.30666 49.092732,-0.308308 49.092526,-0.30941 49.094483,-0.309256 49.094822,-0.3089 49.09534,-0.30869 49.095694,-0.308375 49.096332,-0.308036 49.097288,-0.307974 49.09754,-0.307879 49.098072,-0.307845 49.098622,-0.30791 49.100018,-0.307962 49.100672,-0.30807 49.103133,-0.304338 49.102556,-0.303284 49.102403,-0.302378 49.102322,-0.301869 49.102336,-0.298536 49.10264,-0.297423 49.102729,-0.295778 49.102873,-0.295512 49.102904,-0.295311 49.10298,-0.293928 49.103261,-0.292761 49.103418,-0.289876 49.103751,-0.288868 49.103842,-0.287203 49.103964,-0.284986 49.104118,-0.284161 49.104157,-0.28364 49.104134,-0.281639 49.104009)))"
]

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.281639 49.104009,-0.280597 49.103962,-0.279419 49.103882,-0.279067 49.103847,-0.278039 49.103695,-0.277589 49.103597,-0.276636 49.103334,-0.276251 49.103289,-0.275175 49.103214,-0.274669 49.103179,-0.27427 49.10312,-0.273791 49.103165,-0.272643 49.103149,-0.271199 49.10315,-0.27048 49.103161,-0.270091 49.103208,-0.269928 49.10325,-0.269463 49.103452,-0.269158 49.103697,-0.268685 49.104206,-0.267869 49.104125,-0.266834 49.103957,-0.264891 49.103627,-0.263961 49.103475,-0.263149 49.10338,-0.262477 49.103326,-0.261924 49.103299,-0.261157 49.103219,-0.26121 49.102886,-0.261247 49.102208,-0.261296 49.101797,-0.261072 49.101833,-0.259638 49.101707,-0.259387 49.101639,-0.25888 49.101876,-0.255704 49.100741,-0.255669 49.100625,-0.25355 49.100445,-0.253803 49.099935,-0.254115 49.099744,-0.254771 49.099231,-0.25258 49.098581,-0.252465 49.098718,-0.248931 49.097673,-0.249954 49.09685,-0.250457 49.096418,-0.250798 49.096177,-0.251047 49.095948,-0.25127 49.096078,-0.251197 49.09613,-0.251497 49.096245,-0.2523 49.09552,-0.252015 49.095408,-0.25261 49.094902,-0.253344 49.094315,-0.254041 49.093839,-0.254984 49.093262,-0.257733 49.091424,-0.257515 49.09125,-0.258346 49.090834,-0.25941 49.090321,-0.260171 49.089995,-0.261795 49.089337,-0.262558 49.089034,-0.264735 49.088054,-0.264861 49.088112,-0.2674 49.086668,-0.267342 49.086653,-0.266505 49.086177,-0.265735 49.086524,-0.264724 49.085788,-0.264444 49.085947,-0.263548 49.085303,-0.262941 49.085666,-0.26066 49.084099,-0.260458 49.083919,-0.26008 49.084027,-0.259427 49.083452,-0.259405 49.083349,-0.259163 49.083444,-0.258518 49.082874,-0.258095 49.082408,-0.257792 49.082108,-0.257333 49.081454,-0.256983 49.081049,-0.256746 49.080703,-0.256467 49.080359,-0.255868 49.079923,-0.255531 49.079595,-0.251939 49.07756,-0.251623 49.077396,-0.253522 49.076006,-0.253601 49.075895,-0.253262 49.075695,-0.254681 49.074356,-0.255557 49.074626,-0.256808 49.075046,-0.257752 49.075378,-0.258816 49.075736,-0.259381 49.075861,-0.260226 49.076023,-0.261291 49.076128,-0.261812 49.076227,-0.262979 49.076519,-0.264034 49.076742,-0.264599 49.076914,-0.265316 49.077192,-0.266976 49.078106,-0.26743 49.078262,-0.26803 49.078421,-0.268189 49.07825,-0.269144 49.077508,-0.269517 49.07719,-0.269702 49.077003,-0.269919 49.076841,-0.270427 49.076396,-0.270865 49.075918,-0.271321 49.075516,-0.271445 49.075381,-0.272216 49.074814,-0.272948 49.074407,-0.27351 49.074115,-0.273952 49.073829,-0.274304 49.073556,-0.275055 49.072893,-0.275395 49.072641,-0.275599 49.072423,-0.275655 49.072306,-0.27586 49.072209,-0.27598 49.072094,-0.276381 49.071809,-0.277055 49.071486,-0.277816 49.071163,-0.278317 49.071047,-0.278984 49.070872,-0.279496 49.07071,-0.280004 49.070597,-0.280335 49.070555,-0.280776 49.070231,-0.281149 49.0698,-0.286624 49.070177,-0.286767 49.070739,-0.290001 49.070812,-0.291871 49.070962,-0.293537 49.070917,-0.293567 49.071186,-0.295939 49.070942,-0.297086 49.072953,-0.297806 49.073338,-0.298302 49.073809,-0.300092 49.072711,-0.300245 49.072516,-0.304037 49.073998,-0.303551 49.074325,-0.304248 49.074756,-0.303909 49.075018,-0.304379 49.075311,-0.304721 49.075085,-0.305501 49.075074,-0.306305 49.074771,-0.306377 49.07487,-0.307341 49.076058,-0.30726 49.076699,-0.307055 49.076714,-0.307061 49.077252,-0.306843 49.077239,-0.306118 49.077147,-0.305737 49.077119,-0.30517 49.077157,-0.30488 49.077314,-0.304219 49.077807,-0.308388 49.078557,-0.308387 49.078323,-0.308813 49.07833,-0.309191 49.078473,-0.308355 49.080024,-0.311282 49.081042,-0.31052 49.081997,-0.313176 49.082956,-0.313035 49.083089,-0.312336 49.083837,-0.311518 49.084842,-0.311131 49.085297,-0.307985 49.089184,-0.307152 49.090515,-0.306873 49.09153,-0.306825 49.091766,-0.306445 49.091865,-0.306477 49.091902,-0.306672 49.092696,-0.30666 49.092732,-0.308308 49.092526,-0.30941 49.094483,-0.309256 49.094822,-0.3089 49.09534,-0.30869 49.095694,-0.308375 49.096332,-0.308036 49.097288,-0.307974 49.09754,-0.307879 49.098072,-0.307845 49.098622,-0.30791 49.100018,-0.307962 49.100672,-0.30807 49.103133,-0.304338 49.102556,-0.303284 49.102403,-0.302378 49.102322,-0.301869 49.102336,-0.298536 49.10264,-0.297423 49.102729,-0.295778 49.102873,-0.295512 49.102904,-0.295311 49.10298,-0.293928 49.103261,-0.292761 49.103418,-0.289876 49.103751,-0.288868 49.103842,-0.287203 49.103964,-0.284986 49.104118,-0.284161 49.104157,-0.28364 49.104134,-0.281639 49.104009)))', 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.58 ms
(0.02%)

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.281639 49.104009,-0.280597 49.103962,-0.279419 49.103882,-0.279067 49.103847,-0.278039 49.103695,-0.277589 49.103597,-0.276636 49.103334,-0.276251 49.103289,-0.275175 49.103214,-0.274669 49.103179,-0.27427 49.10312,-0.273791 49.103165,-0.272643 49.103149,-0.271199 49.10315,-0.27048 49.103161,-0.270091 49.103208,-0.269928 49.10325,-0.269463 49.103452,-0.269158 49.103697,-0.268685 49.104206,-0.267869 49.104125,-0.266834 49.103957,-0.264891 49.103627,-0.263961 49.103475,-0.263149 49.10338,-0.262477 49.103326,-0.261924 49.103299,-0.261157 49.103219,-0.26121 49.102886,-0.261247 49.102208,-0.261296 49.101797,-0.261072 49.101833,-0.259638 49.101707,-0.259387 49.101639,-0.25888 49.101876,-0.255704 49.100741,-0.255669 49.100625,-0.25355 49.100445,-0.253803 49.099935,-0.254115 49.099744,-0.254771 49.099231,-0.25258 49.098581,-0.252465 49.098718,-0.248931 49.097673,-0.249954 49.09685,-0.250457 49.096418,-0.250798 49.096177,-0.251047 49.095948,-0.25127 49.096078,-0.251197 49.09613,-0.251497 49.096245,-0.2523 49.09552,-0.252015 49.095408,-0.25261 49.094902,-0.253344 49.094315,-0.254041 49.093839,-0.254984 49.093262,-0.257733 49.091424,-0.257515 49.09125,-0.258346 49.090834,-0.25941 49.090321,-0.260171 49.089995,-0.261795 49.089337,-0.262558 49.089034,-0.264735 49.088054,-0.264861 49.088112,-0.2674 49.086668,-0.267342 49.086653,-0.266505 49.086177,-0.265735 49.086524,-0.264724 49.085788,-0.264444 49.085947,-0.263548 49.085303,-0.262941 49.085666,-0.26066 49.084099,-0.260458 49.083919,-0.26008 49.084027,-0.259427 49.083452,-0.259405 49.083349,-0.259163 49.083444,-0.258518 49.082874,-0.258095 49.082408,-0.257792 49.082108,-0.257333 49.081454,-0.256983 49.081049,-0.256746 49.080703,-0.256467 49.080359,-0.255868 49.079923,-0.255531 49.079595,-0.251939 49.07756,-0.251623 49.077396,-0.253522 49.076006,-0.253601 49.075895,-0.253262 49.075695,-0.254681 49.074356,-0.255557 49.074626,-0.256808 49.075046,-0.257752 49.075378,-0.258816 49.075736,-0.259381 49.075861,-0.260226 49.076023,-0.261291 49.076128,-0.261812 49.076227,-0.262979 49.076519,-0.264034 49.076742,-0.264599 49.076914,-0.265316 49.077192,-0.266976 49.078106,-0.26743 49.078262,-0.26803 49.078421,-0.268189 49.07825,-0.269144 49.077508,-0.269517 49.07719,-0.269702 49.077003,-0.269919 49.076841,-0.270427 49.076396,-0.270865 49.075918,-0.271321 49.075516,-0.271445 49.075381,-0.272216 49.074814,-0.272948 49.074407,-0.27351 49.074115,-0.273952 49.073829,-0.274304 49.073556,-0.275055 49.072893,-0.275395 49.072641,-0.275599 49.072423,-0.275655 49.072306,-0.27586 49.072209,-0.27598 49.072094,-0.276381 49.071809,-0.277055 49.071486,-0.277816 49.071163,-0.278317 49.071047,-0.278984 49.070872,-0.279496 49.07071,-0.280004 49.070597,-0.280335 49.070555,-0.280776 49.070231,-0.281149 49.0698,-0.286624 49.070177,-0.286767 49.070739,-0.290001 49.070812,-0.291871 49.070962,-0.293537 49.070917,-0.293567 49.071186,-0.295939 49.070942,-0.297086 49.072953,-0.297806 49.073338,-0.298302 49.073809,-0.300092 49.072711,-0.300245 49.072516,-0.304037 49.073998,-0.303551 49.074325,-0.304248 49.074756,-0.303909 49.075018,-0.304379 49.075311,-0.304721 49.075085,-0.305501 49.075074,-0.306305 49.074771,-0.306377 49.07487,-0.307341 49.076058,-0.30726 49.076699,-0.307055 49.076714,-0.307061 49.077252,-0.306843 49.077239,-0.306118 49.077147,-0.305737 49.077119,-0.30517 49.077157,-0.30488 49.077314,-0.304219 49.077807,-0.308388 49.078557,-0.308387 49.078323,-0.308813 49.07833,-0.309191 49.078473,-0.308355 49.080024,-0.311282 49.081042,-0.31052 49.081997,-0.313176 49.082956,-0.313035 49.083089,-0.312336 49.083837,-0.311518 49.084842,-0.311131 49.085297,-0.307985 49.089184,-0.307152 49.090515,-0.306873 49.09153,-0.306825 49.091766,-0.306445 49.091865,-0.306477 49.091902,-0.306672 49.092696,-0.30666 49.092732,-0.308308 49.092526,-0.30941 49.094483,-0.309256 49.094822,-0.3089 49.09534,-0.30869 49.095694,-0.308375 49.096332,-0.308036 49.097288,-0.307974 49.09754,-0.307879 49.098072,-0.307845 49.098622,-0.30791 49.100018,-0.307962 49.100672,-0.30807 49.103133,-0.304338 49.102556,-0.303284 49.102403,-0.302378 49.102322,-0.301869 49.102336,-0.298536 49.10264,-0.297423 49.102729,-0.295778 49.102873,-0.295512 49.102904,-0.295311 49.10298,-0.293928 49.103261,-0.292761 49.103418,-0.289876 49.103751,-0.288868 49.103842,-0.287203 49.103964,-0.284986 49.104118,-0.284161 49.104157,-0.28364 49.104134,-0.281639 49.104009)))"
]

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.281639 49.104009,-0.280597 49.103962,-0.279419 49.103882,-0.279067 49.103847,-0.278039 49.103695,-0.277589 49.103597,-0.276636 49.103334,-0.276251 49.103289,-0.275175 49.103214,-0.274669 49.103179,-0.27427 49.10312,-0.273791 49.103165,-0.272643 49.103149,-0.271199 49.10315,-0.27048 49.103161,-0.270091 49.103208,-0.269928 49.10325,-0.269463 49.103452,-0.269158 49.103697,-0.268685 49.104206,-0.267869 49.104125,-0.266834 49.103957,-0.264891 49.103627,-0.263961 49.103475,-0.263149 49.10338,-0.262477 49.103326,-0.261924 49.103299,-0.261157 49.103219,-0.26121 49.102886,-0.261247 49.102208,-0.261296 49.101797,-0.261072 49.101833,-0.259638 49.101707,-0.259387 49.101639,-0.25888 49.101876,-0.255704 49.100741,-0.255669 49.100625,-0.25355 49.100445,-0.253803 49.099935,-0.254115 49.099744,-0.254771 49.099231,-0.25258 49.098581,-0.252465 49.098718,-0.248931 49.097673,-0.249954 49.09685,-0.250457 49.096418,-0.250798 49.096177,-0.251047 49.095948,-0.25127 49.096078,-0.251197 49.09613,-0.251497 49.096245,-0.2523 49.09552,-0.252015 49.095408,-0.25261 49.094902,-0.253344 49.094315,-0.254041 49.093839,-0.254984 49.093262,-0.257733 49.091424,-0.257515 49.09125,-0.258346 49.090834,-0.25941 49.090321,-0.260171 49.089995,-0.261795 49.089337,-0.262558 49.089034,-0.264735 49.088054,-0.264861 49.088112,-0.2674 49.086668,-0.267342 49.086653,-0.266505 49.086177,-0.265735 49.086524,-0.264724 49.085788,-0.264444 49.085947,-0.263548 49.085303,-0.262941 49.085666,-0.26066 49.084099,-0.260458 49.083919,-0.26008 49.084027,-0.259427 49.083452,-0.259405 49.083349,-0.259163 49.083444,-0.258518 49.082874,-0.258095 49.082408,-0.257792 49.082108,-0.257333 49.081454,-0.256983 49.081049,-0.256746 49.080703,-0.256467 49.080359,-0.255868 49.079923,-0.255531 49.079595,-0.251939 49.07756,-0.251623 49.077396,-0.253522 49.076006,-0.253601 49.075895,-0.253262 49.075695,-0.254681 49.074356,-0.255557 49.074626,-0.256808 49.075046,-0.257752 49.075378,-0.258816 49.075736,-0.259381 49.075861,-0.260226 49.076023,-0.261291 49.076128,-0.261812 49.076227,-0.262979 49.076519,-0.264034 49.076742,-0.264599 49.076914,-0.265316 49.077192,-0.266976 49.078106,-0.26743 49.078262,-0.26803 49.078421,-0.268189 49.07825,-0.269144 49.077508,-0.269517 49.07719,-0.269702 49.077003,-0.269919 49.076841,-0.270427 49.076396,-0.270865 49.075918,-0.271321 49.075516,-0.271445 49.075381,-0.272216 49.074814,-0.272948 49.074407,-0.27351 49.074115,-0.273952 49.073829,-0.274304 49.073556,-0.275055 49.072893,-0.275395 49.072641,-0.275599 49.072423,-0.275655 49.072306,-0.27586 49.072209,-0.27598 49.072094,-0.276381 49.071809,-0.277055 49.071486,-0.277816 49.071163,-0.278317 49.071047,-0.278984 49.070872,-0.279496 49.07071,-0.280004 49.070597,-0.280335 49.070555,-0.280776 49.070231,-0.281149 49.0698,-0.286624 49.070177,-0.286767 49.070739,-0.290001 49.070812,-0.291871 49.070962,-0.293537 49.070917,-0.293567 49.071186,-0.295939 49.070942,-0.297086 49.072953,-0.297806 49.073338,-0.298302 49.073809,-0.300092 49.072711,-0.300245 49.072516,-0.304037 49.073998,-0.303551 49.074325,-0.304248 49.074756,-0.303909 49.075018,-0.304379 49.075311,-0.304721 49.075085,-0.305501 49.075074,-0.306305 49.074771,-0.306377 49.07487,-0.307341 49.076058,-0.30726 49.076699,-0.307055 49.076714,-0.307061 49.077252,-0.306843 49.077239,-0.306118 49.077147,-0.305737 49.077119,-0.30517 49.077157,-0.30488 49.077314,-0.304219 49.077807,-0.308388 49.078557,-0.308387 49.078323,-0.308813 49.07833,-0.309191 49.078473,-0.308355 49.080024,-0.311282 49.081042,-0.31052 49.081997,-0.313176 49.082956,-0.313035 49.083089,-0.312336 49.083837,-0.311518 49.084842,-0.311131 49.085297,-0.307985 49.089184,-0.307152 49.090515,-0.306873 49.09153,-0.306825 49.091766,-0.306445 49.091865,-0.306477 49.091902,-0.306672 49.092696,-0.30666 49.092732,-0.308308 49.092526,-0.30941 49.094483,-0.309256 49.094822,-0.3089 49.09534,-0.30869 49.095694,-0.308375 49.096332,-0.308036 49.097288,-0.307974 49.09754,-0.307879 49.098072,-0.307845 49.098622,-0.30791 49.100018,-0.307962 49.100672,-0.30807 49.103133,-0.304338 49.102556,-0.303284 49.102403,-0.302378 49.102322,-0.301869 49.102336,-0.298536 49.10264,-0.297423 49.102729,-0.295778 49.102873,-0.295512 49.102904,-0.295311 49.10298,-0.293928 49.103261,-0.292761 49.103418,-0.289876 49.103751,-0.288868 49.103842,-0.287203 49.103964,-0.284986 49.104118,-0.284161 49.104157,-0.28364 49.104134,-0.281639 49.104009)))', 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.41 ms
(0.01%)

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:

[
  "14554"
]

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'14554'') = ?
      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.34 ms
(0.01%)

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.23 ms
(0.01%)

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	4913
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/14554

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