Symfony Profiler

Query Metrics

37 Database Queries

29 Different statements

807.98 ms Query time

0 Invalid entities

4953 Managed entities

Grouped Statements

Show all queries

default connection

Time▼

Count

Info

306.82 ms
(37.97%)

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

157.68 ms
(19.52%)

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:

[
  26600
]

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

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

57.50 ms
(7.12%)

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

Parameters:

[
  "stats_de_ville"
  "{"insee_code":"12084","nom":"12084"}"
  "2026-07-15 07:08:13"
  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\":\"12084\",\"nom\":\"12084\"}', '2026-07-15 07:08:13', 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)

1.64 ms
(0.20%)

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:

[
  26600
]

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

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

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:

[
  26600
]

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 = 26600 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.49 ms
(0.06%)

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:

[
  "12084"
]

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

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

0.47 ms
(0.06%)

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

Parameters:

[
  "12084"
]

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

#	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.39 ms
(0.05%)

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:

[
  26600
]

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 = 26600 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.24 ms
(0.03%)

"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

147.11 ms
(18.21%)

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

Parameters:

[
  "12084"
]

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

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

37.54 ms
(4.65%)

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(((3.103998 44.044162,3.100957 44.044598,3.098404 44.044786,3.097906 44.044789,3.09703 44.044527,3.095091 44.044045,3.095087 44.043731,3.095207 44.04346,3.095505 44.042379,3.095864 44.041252,3.095983 44.040802,3.095976 44.040352,3.09535 44.040177,3.092665 44.039747,3.092663 44.039612,3.093151 44.038799,3.0937 44.037842,3.094005 44.03739,3.094375 44.037073,3.096357 44.036205,3.096039 44.035713,3.095472 44.035268,3.094342 44.034646,3.091827 44.03291,3.0912 44.032644,3.089949 44.032338,3.087949 44.031804,3.087134 44.031405,3.085381 44.030833,3.084446 44.030749,3.083822 44.030753,3.082878 44.03094,3.078592 44.032002,3.078647 44.033836,3.078704 44.034455,3.079406 44.038764,3.07899 44.038662,3.076457 44.038151,3.075549 44.038596,3.074306 44.039069,3.07259 44.041089,3.071915 44.041903,3.07118 44.042896,3.070996 44.043078,3.070813 44.043348,3.070444 44.043756,3.070319 44.043756,3.069507 44.043537,3.067237 44.042563,3.06443 44.04214,3.06321 44.04198,3.062473 44.042168,3.06101 44.042715,3.060847 44.042898,3.060575 44.043064,3.060183 44.043249,3.059749 44.043372,3.059536 44.043387,3.058782 44.04335,3.058216 44.043304,3.056477 44.043046,3.056171 44.043417,3.055205 44.044618,3.055205 44.04612,3.054707 44.046972,3.054329 44.047341,3.05398 44.047627,3.053848 44.047762,3.053877 44.047965,3.054125 44.048227,3.054227 44.048514,3.054252 44.048963,3.054242 44.049152,3.05412 44.049355,3.053959 44.049496,3.053855 44.049754,3.053893 44.049946,3.053563 44.050632,3.05004 44.049863,3.044086 44.048374,3.043578 44.048756,3.037957 44.051477,3.036346 44.052296,3.035428 44.05374,3.035062 44.054372,3.034107 44.056176,3.032144 44.058155,3.032223 44.058559,3.032842 44.059484,3.031542 44.059929,3.030262 44.060443,3.028968 44.061377,3.028366 44.06155,3.028017 44.061497,3.026988 44.061491,3.02674 44.061718,3.025674 44.061761,3.025343 44.061795,3.025049 44.061906,3.024708 44.061994,3.024088 44.062106,3.023747 44.062045,3.023092 44.062018,3.022709 44.062049,3.02254 44.062106,3.022324 44.062233,3.022122 44.062315,3.021468 44.062689,3.021346 44.062895,3.021178 44.063057,3.02073 44.063282,3.020506 44.06347,3.020148 44.064151,3.019949 44.064694,3.019912 44.065396,3.019872 44.065535,3.019927 44.065716,3.019874 44.066054,3.019896 44.066528,3.019845 44.066633,3.019933 44.066826,3.019922 44.067153,3.019819 44.067546,3.019907 44.067764,3.019506 44.068592,3.016594 44.071594,3.016541 44.071619,3.016367 44.071871,3.016133 44.071843,3.016029 44.071923,3.015798 44.072021,3.013366 44.073122,3.012986 44.073126,3.012848 44.073175,3.011851 44.073342,3.011479 44.073449,3.010135 44.073638,3.009371 44.073751,3.009642 44.074331,3.009769 44.074555,3.010639 44.074503,3.010847 44.076032,3.01092 44.076975,3.010872 44.078198,3.010917 44.078746,3.010405 44.079044,3.010645 44.079317,3.010699 44.079562,3.010325 44.080228,3.010564 44.080746,3.011223 44.081227,3.011503 44.081372,3.011475 44.082492,3.011739 44.082721,3.012055 44.083316,3.012409 44.084038,3.013635 44.083812,3.015417 44.083764,3.016062 44.083835,3.017206 44.08396,3.0192 44.084469,3.021916 44.08526,3.022957 44.085596,3.024484 44.086153,3.026148 44.086769,3.027328 44.087383,3.027861 44.087781,3.028895 44.089123,3.029775 44.090178,3.031084 44.091638,3.031638 44.092134,3.032565 44.092532,3.033632 44.092819,3.034198 44.093095,3.034619 44.093368,3.035568 44.093522,3.036096 44.093504,3.036651 44.093409,3.037073 44.093306,3.037371 44.09321,3.038235 44.092779,3.038471 44.092615,3.039229 44.091881,3.039588 44.091633,3.039915 44.091482,3.040588 44.091245,3.041225 44.090984,3.041774 44.0908,3.042199 44.090694,3.042716 44.09068,3.0433 44.090697,3.043986 44.090801,3.045082 44.091011,3.046002 44.091306,3.046811 44.091502,3.047505 44.091588,3.048029 44.091585,3.048908 44.091472,3.049186 44.091386,3.049504 44.091177,3.049733 44.090888,3.049857 44.090621,3.049897 44.090176,3.049788 44.089733,3.049635 44.089367,3.049501 44.088903,3.049524 44.088511,3.049723 44.088035,3.05008 44.087497,3.050523 44.086925,3.05098 44.086507,3.05154 44.086172,3.052134 44.085948,3.052913 44.085763,3.053545 44.085686,3.054086 44.085687,3.054743 44.085756,3.055265 44.085841,3.055902 44.085973,3.056785 44.08617,3.057666 44.086379,3.058536 44.086599,3.05946 44.086865,3.059902 44.087027,3.060355 44.087292,3.060486 44.087398,3.06083 44.087746,3.061581 44.088609,3.061952 44.088995,3.062326 44.089465,3.062469 44.089593,3.062662 44.089701,3.063245 44.089905,3.063987 44.090116,3.064649 44.09027,3.065086 44.090353,3.065879 44.090417,3.067434 44.090563,3.067723 44.090017,3.067912 44.089518,3.068354 44.089169,3.068441 44.089026,3.068495 44.088831,3.068509 44.088639,3.068476 44.088516,3.068608 44.087924,3.068791 44.087608,3.06885 44.087338,3.069031 44.086932,3.069473 44.0863,3.069965 44.085802,3.070337 44.08562,3.070649 44.085618,3.072652 44.086188,3.072919 44.085398,3.073686 44.085445,3.074441 44.08551,3.077757 44.0852,3.078276 44.085275,3.07876 44.085372,3.0792 44.085315,3.079115 44.083447,3.079345 44.081953,3.07965 44.081456,3.080876 44.07983,3.081875 44.079958,3.083099 44.078106,3.085942 44.075209,3.086311 44.074847,3.087356 44.073671,3.089135 44.071492,3.089439 44.070905,3.08962 44.070499,3.089678 44.070139,3.08992 44.069553,3.090039 44.069103,3.090342 44.068516,3.090466 44.068336,3.091073 44.067163,3.091501 44.066575,3.09192 44.066145,3.092234 44.065906,3.093546 44.065091,3.093955 44.06479,3.094632 44.064216,3.095013 44.063931,3.095308 44.063784,3.095614 44.063689,3.095787 44.063262,3.096057 44.062884,3.09675 44.061984,3.09697 44.061671,3.097263 44.061176,3.097663 44.060403,3.097886 44.060008,3.098749 44.058359,3.098889 44.058048,3.101797 44.05602,3.102415 44.055656,3.103158 44.055336,3.104566 44.053393,3.105116 44.05258,3.105102 44.051501,3.104897 44.050189,3.104828 44.049695,3.104693 44.048887,3.104423 44.046506,3.103914 44.044721,3.103965 44.044531,3.103998 44.044162)))"
  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(((3.103998 44.044162,3.100957 44.044598,3.098404 44.044786,3.097906 44.044789,3.09703 44.044527,3.095091 44.044045,3.095087 44.043731,3.095207 44.04346,3.095505 44.042379,3.095864 44.041252,3.095983 44.040802,3.095976 44.040352,3.09535 44.040177,3.092665 44.039747,3.092663 44.039612,3.093151 44.038799,3.0937 44.037842,3.094005 44.03739,3.094375 44.037073,3.096357 44.036205,3.096039 44.035713,3.095472 44.035268,3.094342 44.034646,3.091827 44.03291,3.0912 44.032644,3.089949 44.032338,3.087949 44.031804,3.087134 44.031405,3.085381 44.030833,3.084446 44.030749,3.083822 44.030753,3.082878 44.03094,3.078592 44.032002,3.078647 44.033836,3.078704 44.034455,3.079406 44.038764,3.07899 44.038662,3.076457 44.038151,3.075549 44.038596,3.074306 44.039069,3.07259 44.041089,3.071915 44.041903,3.07118 44.042896,3.070996 44.043078,3.070813 44.043348,3.070444 44.043756,3.070319 44.043756,3.069507 44.043537,3.067237 44.042563,3.06443 44.04214,3.06321 44.04198,3.062473 44.042168,3.06101 44.042715,3.060847 44.042898,3.060575 44.043064,3.060183 44.043249,3.059749 44.043372,3.059536 44.043387,3.058782 44.04335,3.058216 44.043304,3.056477 44.043046,3.056171 44.043417,3.055205 44.044618,3.055205 44.04612,3.054707 44.046972,3.054329 44.047341,3.05398 44.047627,3.053848 44.047762,3.053877 44.047965,3.054125 44.048227,3.054227 44.048514,3.054252 44.048963,3.054242 44.049152,3.05412 44.049355,3.053959 44.049496,3.053855 44.049754,3.053893 44.049946,3.053563 44.050632,3.05004 44.049863,3.044086 44.048374,3.043578 44.048756,3.037957 44.051477,3.036346 44.052296,3.035428 44.05374,3.035062 44.054372,3.034107 44.056176,3.032144 44.058155,3.032223 44.058559,3.032842 44.059484,3.031542 44.059929,3.030262 44.060443,3.028968 44.061377,3.028366 44.06155,3.028017 44.061497,3.026988 44.061491,3.02674 44.061718,3.025674 44.061761,3.025343 44.061795,3.025049 44.061906,3.024708 44.061994,3.024088 44.062106,3.023747 44.062045,3.023092 44.062018,3.022709 44.062049,3.02254 44.062106,3.022324 44.062233,3.022122 44.062315,3.021468 44.062689,3.021346 44.062895,3.021178 44.063057,3.02073 44.063282,3.020506 44.06347,3.020148 44.064151,3.019949 44.064694,3.019912 44.065396,3.019872 44.065535,3.019927 44.065716,3.019874 44.066054,3.019896 44.066528,3.019845 44.066633,3.019933 44.066826,3.019922 44.067153,3.019819 44.067546,3.019907 44.067764,3.019506 44.068592,3.016594 44.071594,3.016541 44.071619,3.016367 44.071871,3.016133 44.071843,3.016029 44.071923,3.015798 44.072021,3.013366 44.073122,3.012986 44.073126,3.012848 44.073175,3.011851 44.073342,3.011479 44.073449,3.010135 44.073638,3.009371 44.073751,3.009642 44.074331,3.009769 44.074555,3.010639 44.074503,3.010847 44.076032,3.01092 44.076975,3.010872 44.078198,3.010917 44.078746,3.010405 44.079044,3.010645 44.079317,3.010699 44.079562,3.010325 44.080228,3.010564 44.080746,3.011223 44.081227,3.011503 44.081372,3.011475 44.082492,3.011739 44.082721,3.012055 44.083316,3.012409 44.084038,3.013635 44.083812,3.015417 44.083764,3.016062 44.083835,3.017206 44.08396,3.0192 44.084469,3.021916 44.08526,3.022957 44.085596,3.024484 44.086153,3.026148 44.086769,3.027328 44.087383,3.027861 44.087781,3.028895 44.089123,3.029775 44.090178,3.031084 44.091638,3.031638 44.092134,3.032565 44.092532,3.033632 44.092819,3.034198 44.093095,3.034619 44.093368,3.035568 44.093522,3.036096 44.093504,3.036651 44.093409,3.037073 44.093306,3.037371 44.09321,3.038235 44.092779,3.038471 44.092615,3.039229 44.091881,3.039588 44.091633,3.039915 44.091482,3.040588 44.091245,3.041225 44.090984,3.041774 44.0908,3.042199 44.090694,3.042716 44.09068,3.0433 44.090697,3.043986 44.090801,3.045082 44.091011,3.046002 44.091306,3.046811 44.091502,3.047505 44.091588,3.048029 44.091585,3.048908 44.091472,3.049186 44.091386,3.049504 44.091177,3.049733 44.090888,3.049857 44.090621,3.049897 44.090176,3.049788 44.089733,3.049635 44.089367,3.049501 44.088903,3.049524 44.088511,3.049723 44.088035,3.05008 44.087497,3.050523 44.086925,3.05098 44.086507,3.05154 44.086172,3.052134 44.085948,3.052913 44.085763,3.053545 44.085686,3.054086 44.085687,3.054743 44.085756,3.055265 44.085841,3.055902 44.085973,3.056785 44.08617,3.057666 44.086379,3.058536 44.086599,3.05946 44.086865,3.059902 44.087027,3.060355 44.087292,3.060486 44.087398,3.06083 44.087746,3.061581 44.088609,3.061952 44.088995,3.062326 44.089465,3.062469 44.089593,3.062662 44.089701,3.063245 44.089905,3.063987 44.090116,3.064649 44.09027,3.065086 44.090353,3.065879 44.090417,3.067434 44.090563,3.067723 44.090017,3.067912 44.089518,3.068354 44.089169,3.068441 44.089026,3.068495 44.088831,3.068509 44.088639,3.068476 44.088516,3.068608 44.087924,3.068791 44.087608,3.06885 44.087338,3.069031 44.086932,3.069473 44.0863,3.069965 44.085802,3.070337 44.08562,3.070649 44.085618,3.072652 44.086188,3.072919 44.085398,3.073686 44.085445,3.074441 44.08551,3.077757 44.0852,3.078276 44.085275,3.07876 44.085372,3.0792 44.085315,3.079115 44.083447,3.079345 44.081953,3.07965 44.081456,3.080876 44.07983,3.081875 44.079958,3.083099 44.078106,3.085942 44.075209,3.086311 44.074847,3.087356 44.073671,3.089135 44.071492,3.089439 44.070905,3.08962 44.070499,3.089678 44.070139,3.08992 44.069553,3.090039 44.069103,3.090342 44.068516,3.090466 44.068336,3.091073 44.067163,3.091501 44.066575,3.09192 44.066145,3.092234 44.065906,3.093546 44.065091,3.093955 44.06479,3.094632 44.064216,3.095013 44.063931,3.095308 44.063784,3.095614 44.063689,3.095787 44.063262,3.096057 44.062884,3.09675 44.061984,3.09697 44.061671,3.097263 44.061176,3.097663 44.060403,3.097886 44.060008,3.098749 44.058359,3.098889 44.058048,3.101797 44.05602,3.102415 44.055656,3.103158 44.055336,3.104566 44.053393,3.105116 44.05258,3.105102 44.051501,3.104897 44.050189,3.104828 44.049695,3.104693 44.048887,3.104423 44.046506,3.103914 44.044721,3.103965 44.044531,3.103998 44.044162)))'') 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)

35.50 ms
(4.39%)

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(((3.103998 44.044162,3.100957 44.044598,3.098404 44.044786,3.097906 44.044789,3.09703 44.044527,3.095091 44.044045,3.095087 44.043731,3.095207 44.04346,3.095505 44.042379,3.095864 44.041252,3.095983 44.040802,3.095976 44.040352,3.09535 44.040177,3.092665 44.039747,3.092663 44.039612,3.093151 44.038799,3.0937 44.037842,3.094005 44.03739,3.094375 44.037073,3.096357 44.036205,3.096039 44.035713,3.095472 44.035268,3.094342 44.034646,3.091827 44.03291,3.0912 44.032644,3.089949 44.032338,3.087949 44.031804,3.087134 44.031405,3.085381 44.030833,3.084446 44.030749,3.083822 44.030753,3.082878 44.03094,3.078592 44.032002,3.078647 44.033836,3.078704 44.034455,3.079406 44.038764,3.07899 44.038662,3.076457 44.038151,3.075549 44.038596,3.074306 44.039069,3.07259 44.041089,3.071915 44.041903,3.07118 44.042896,3.070996 44.043078,3.070813 44.043348,3.070444 44.043756,3.070319 44.043756,3.069507 44.043537,3.067237 44.042563,3.06443 44.04214,3.06321 44.04198,3.062473 44.042168,3.06101 44.042715,3.060847 44.042898,3.060575 44.043064,3.060183 44.043249,3.059749 44.043372,3.059536 44.043387,3.058782 44.04335,3.058216 44.043304,3.056477 44.043046,3.056171 44.043417,3.055205 44.044618,3.055205 44.04612,3.054707 44.046972,3.054329 44.047341,3.05398 44.047627,3.053848 44.047762,3.053877 44.047965,3.054125 44.048227,3.054227 44.048514,3.054252 44.048963,3.054242 44.049152,3.05412 44.049355,3.053959 44.049496,3.053855 44.049754,3.053893 44.049946,3.053563 44.050632,3.05004 44.049863,3.044086 44.048374,3.043578 44.048756,3.037957 44.051477,3.036346 44.052296,3.035428 44.05374,3.035062 44.054372,3.034107 44.056176,3.032144 44.058155,3.032223 44.058559,3.032842 44.059484,3.031542 44.059929,3.030262 44.060443,3.028968 44.061377,3.028366 44.06155,3.028017 44.061497,3.026988 44.061491,3.02674 44.061718,3.025674 44.061761,3.025343 44.061795,3.025049 44.061906,3.024708 44.061994,3.024088 44.062106,3.023747 44.062045,3.023092 44.062018,3.022709 44.062049,3.02254 44.062106,3.022324 44.062233,3.022122 44.062315,3.021468 44.062689,3.021346 44.062895,3.021178 44.063057,3.02073 44.063282,3.020506 44.06347,3.020148 44.064151,3.019949 44.064694,3.019912 44.065396,3.019872 44.065535,3.019927 44.065716,3.019874 44.066054,3.019896 44.066528,3.019845 44.066633,3.019933 44.066826,3.019922 44.067153,3.019819 44.067546,3.019907 44.067764,3.019506 44.068592,3.016594 44.071594,3.016541 44.071619,3.016367 44.071871,3.016133 44.071843,3.016029 44.071923,3.015798 44.072021,3.013366 44.073122,3.012986 44.073126,3.012848 44.073175,3.011851 44.073342,3.011479 44.073449,3.010135 44.073638,3.009371 44.073751,3.009642 44.074331,3.009769 44.074555,3.010639 44.074503,3.010847 44.076032,3.01092 44.076975,3.010872 44.078198,3.010917 44.078746,3.010405 44.079044,3.010645 44.079317,3.010699 44.079562,3.010325 44.080228,3.010564 44.080746,3.011223 44.081227,3.011503 44.081372,3.011475 44.082492,3.011739 44.082721,3.012055 44.083316,3.012409 44.084038,3.013635 44.083812,3.015417 44.083764,3.016062 44.083835,3.017206 44.08396,3.0192 44.084469,3.021916 44.08526,3.022957 44.085596,3.024484 44.086153,3.026148 44.086769,3.027328 44.087383,3.027861 44.087781,3.028895 44.089123,3.029775 44.090178,3.031084 44.091638,3.031638 44.092134,3.032565 44.092532,3.033632 44.092819,3.034198 44.093095,3.034619 44.093368,3.035568 44.093522,3.036096 44.093504,3.036651 44.093409,3.037073 44.093306,3.037371 44.09321,3.038235 44.092779,3.038471 44.092615,3.039229 44.091881,3.039588 44.091633,3.039915 44.091482,3.040588 44.091245,3.041225 44.090984,3.041774 44.0908,3.042199 44.090694,3.042716 44.09068,3.0433 44.090697,3.043986 44.090801,3.045082 44.091011,3.046002 44.091306,3.046811 44.091502,3.047505 44.091588,3.048029 44.091585,3.048908 44.091472,3.049186 44.091386,3.049504 44.091177,3.049733 44.090888,3.049857 44.090621,3.049897 44.090176,3.049788 44.089733,3.049635 44.089367,3.049501 44.088903,3.049524 44.088511,3.049723 44.088035,3.05008 44.087497,3.050523 44.086925,3.05098 44.086507,3.05154 44.086172,3.052134 44.085948,3.052913 44.085763,3.053545 44.085686,3.054086 44.085687,3.054743 44.085756,3.055265 44.085841,3.055902 44.085973,3.056785 44.08617,3.057666 44.086379,3.058536 44.086599,3.05946 44.086865,3.059902 44.087027,3.060355 44.087292,3.060486 44.087398,3.06083 44.087746,3.061581 44.088609,3.061952 44.088995,3.062326 44.089465,3.062469 44.089593,3.062662 44.089701,3.063245 44.089905,3.063987 44.090116,3.064649 44.09027,3.065086 44.090353,3.065879 44.090417,3.067434 44.090563,3.067723 44.090017,3.067912 44.089518,3.068354 44.089169,3.068441 44.089026,3.068495 44.088831,3.068509 44.088639,3.068476 44.088516,3.068608 44.087924,3.068791 44.087608,3.06885 44.087338,3.069031 44.086932,3.069473 44.0863,3.069965 44.085802,3.070337 44.08562,3.070649 44.085618,3.072652 44.086188,3.072919 44.085398,3.073686 44.085445,3.074441 44.08551,3.077757 44.0852,3.078276 44.085275,3.07876 44.085372,3.0792 44.085315,3.079115 44.083447,3.079345 44.081953,3.07965 44.081456,3.080876 44.07983,3.081875 44.079958,3.083099 44.078106,3.085942 44.075209,3.086311 44.074847,3.087356 44.073671,3.089135 44.071492,3.089439 44.070905,3.08962 44.070499,3.089678 44.070139,3.08992 44.069553,3.090039 44.069103,3.090342 44.068516,3.090466 44.068336,3.091073 44.067163,3.091501 44.066575,3.09192 44.066145,3.092234 44.065906,3.093546 44.065091,3.093955 44.06479,3.094632 44.064216,3.095013 44.063931,3.095308 44.063784,3.095614 44.063689,3.095787 44.063262,3.096057 44.062884,3.09675 44.061984,3.09697 44.061671,3.097263 44.061176,3.097663 44.060403,3.097886 44.060008,3.098749 44.058359,3.098889 44.058048,3.101797 44.05602,3.102415 44.055656,3.103158 44.055336,3.104566 44.053393,3.105116 44.05258,3.105102 44.051501,3.104897 44.050189,3.104828 44.049695,3.104693 44.048887,3.104423 44.046506,3.103914 44.044721,3.103965 44.044531,3.103998 44.044162)))"
]

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(((3.103998 44.044162,3.100957 44.044598,3.098404 44.044786,3.097906 44.044789,3.09703 44.044527,3.095091 44.044045,3.095087 44.043731,3.095207 44.04346,3.095505 44.042379,3.095864 44.041252,3.095983 44.040802,3.095976 44.040352,3.09535 44.040177,3.092665 44.039747,3.092663 44.039612,3.093151 44.038799,3.0937 44.037842,3.094005 44.03739,3.094375 44.037073,3.096357 44.036205,3.096039 44.035713,3.095472 44.035268,3.094342 44.034646,3.091827 44.03291,3.0912 44.032644,3.089949 44.032338,3.087949 44.031804,3.087134 44.031405,3.085381 44.030833,3.084446 44.030749,3.083822 44.030753,3.082878 44.03094,3.078592 44.032002,3.078647 44.033836,3.078704 44.034455,3.079406 44.038764,3.07899 44.038662,3.076457 44.038151,3.075549 44.038596,3.074306 44.039069,3.07259 44.041089,3.071915 44.041903,3.07118 44.042896,3.070996 44.043078,3.070813 44.043348,3.070444 44.043756,3.070319 44.043756,3.069507 44.043537,3.067237 44.042563,3.06443 44.04214,3.06321 44.04198,3.062473 44.042168,3.06101 44.042715,3.060847 44.042898,3.060575 44.043064,3.060183 44.043249,3.059749 44.043372,3.059536 44.043387,3.058782 44.04335,3.058216 44.043304,3.056477 44.043046,3.056171 44.043417,3.055205 44.044618,3.055205 44.04612,3.054707 44.046972,3.054329 44.047341,3.05398 44.047627,3.053848 44.047762,3.053877 44.047965,3.054125 44.048227,3.054227 44.048514,3.054252 44.048963,3.054242 44.049152,3.05412 44.049355,3.053959 44.049496,3.053855 44.049754,3.053893 44.049946,3.053563 44.050632,3.05004 44.049863,3.044086 44.048374,3.043578 44.048756,3.037957 44.051477,3.036346 44.052296,3.035428 44.05374,3.035062 44.054372,3.034107 44.056176,3.032144 44.058155,3.032223 44.058559,3.032842 44.059484,3.031542 44.059929,3.030262 44.060443,3.028968 44.061377,3.028366 44.06155,3.028017 44.061497,3.026988 44.061491,3.02674 44.061718,3.025674 44.061761,3.025343 44.061795,3.025049 44.061906,3.024708 44.061994,3.024088 44.062106,3.023747 44.062045,3.023092 44.062018,3.022709 44.062049,3.02254 44.062106,3.022324 44.062233,3.022122 44.062315,3.021468 44.062689,3.021346 44.062895,3.021178 44.063057,3.02073 44.063282,3.020506 44.06347,3.020148 44.064151,3.019949 44.064694,3.019912 44.065396,3.019872 44.065535,3.019927 44.065716,3.019874 44.066054,3.019896 44.066528,3.019845 44.066633,3.019933 44.066826,3.019922 44.067153,3.019819 44.067546,3.019907 44.067764,3.019506 44.068592,3.016594 44.071594,3.016541 44.071619,3.016367 44.071871,3.016133 44.071843,3.016029 44.071923,3.015798 44.072021,3.013366 44.073122,3.012986 44.073126,3.012848 44.073175,3.011851 44.073342,3.011479 44.073449,3.010135 44.073638,3.009371 44.073751,3.009642 44.074331,3.009769 44.074555,3.010639 44.074503,3.010847 44.076032,3.01092 44.076975,3.010872 44.078198,3.010917 44.078746,3.010405 44.079044,3.010645 44.079317,3.010699 44.079562,3.010325 44.080228,3.010564 44.080746,3.011223 44.081227,3.011503 44.081372,3.011475 44.082492,3.011739 44.082721,3.012055 44.083316,3.012409 44.084038,3.013635 44.083812,3.015417 44.083764,3.016062 44.083835,3.017206 44.08396,3.0192 44.084469,3.021916 44.08526,3.022957 44.085596,3.024484 44.086153,3.026148 44.086769,3.027328 44.087383,3.027861 44.087781,3.028895 44.089123,3.029775 44.090178,3.031084 44.091638,3.031638 44.092134,3.032565 44.092532,3.033632 44.092819,3.034198 44.093095,3.034619 44.093368,3.035568 44.093522,3.036096 44.093504,3.036651 44.093409,3.037073 44.093306,3.037371 44.09321,3.038235 44.092779,3.038471 44.092615,3.039229 44.091881,3.039588 44.091633,3.039915 44.091482,3.040588 44.091245,3.041225 44.090984,3.041774 44.0908,3.042199 44.090694,3.042716 44.09068,3.0433 44.090697,3.043986 44.090801,3.045082 44.091011,3.046002 44.091306,3.046811 44.091502,3.047505 44.091588,3.048029 44.091585,3.048908 44.091472,3.049186 44.091386,3.049504 44.091177,3.049733 44.090888,3.049857 44.090621,3.049897 44.090176,3.049788 44.089733,3.049635 44.089367,3.049501 44.088903,3.049524 44.088511,3.049723 44.088035,3.05008 44.087497,3.050523 44.086925,3.05098 44.086507,3.05154 44.086172,3.052134 44.085948,3.052913 44.085763,3.053545 44.085686,3.054086 44.085687,3.054743 44.085756,3.055265 44.085841,3.055902 44.085973,3.056785 44.08617,3.057666 44.086379,3.058536 44.086599,3.05946 44.086865,3.059902 44.087027,3.060355 44.087292,3.060486 44.087398,3.06083 44.087746,3.061581 44.088609,3.061952 44.088995,3.062326 44.089465,3.062469 44.089593,3.062662 44.089701,3.063245 44.089905,3.063987 44.090116,3.064649 44.09027,3.065086 44.090353,3.065879 44.090417,3.067434 44.090563,3.067723 44.090017,3.067912 44.089518,3.068354 44.089169,3.068441 44.089026,3.068495 44.088831,3.068509 44.088639,3.068476 44.088516,3.068608 44.087924,3.068791 44.087608,3.06885 44.087338,3.069031 44.086932,3.069473 44.0863,3.069965 44.085802,3.070337 44.08562,3.070649 44.085618,3.072652 44.086188,3.072919 44.085398,3.073686 44.085445,3.074441 44.08551,3.077757 44.0852,3.078276 44.085275,3.07876 44.085372,3.0792 44.085315,3.079115 44.083447,3.079345 44.081953,3.07965 44.081456,3.080876 44.07983,3.081875 44.079958,3.083099 44.078106,3.085942 44.075209,3.086311 44.074847,3.087356 44.073671,3.089135 44.071492,3.089439 44.070905,3.08962 44.070499,3.089678 44.070139,3.08992 44.069553,3.090039 44.069103,3.090342 44.068516,3.090466 44.068336,3.091073 44.067163,3.091501 44.066575,3.09192 44.066145,3.092234 44.065906,3.093546 44.065091,3.093955 44.06479,3.094632 44.064216,3.095013 44.063931,3.095308 44.063784,3.095614 44.063689,3.095787 44.063262,3.096057 44.062884,3.09675 44.061984,3.09697 44.061671,3.097263 44.061176,3.097663 44.060403,3.097886 44.060008,3.098749 44.058359,3.098889 44.058048,3.101797 44.05602,3.102415 44.055656,3.103158 44.055336,3.104566 44.053393,3.105116 44.05258,3.105102 44.051501,3.104897 44.050189,3.104828 44.049695,3.104693 44.048887,3.104423 44.046506,3.103914 44.044721,3.103965 44.044531,3.103998 44.044162)))', 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)

16.56 ms
(2.05%)

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(((3.103998 44.044162,3.100957 44.044598,3.098404 44.044786,3.097906 44.044789,3.09703 44.044527,3.095091 44.044045,3.095087 44.043731,3.095207 44.04346,3.095505 44.042379,3.095864 44.041252,3.095983 44.040802,3.095976 44.040352,3.09535 44.040177,3.092665 44.039747,3.092663 44.039612,3.093151 44.038799,3.0937 44.037842,3.094005 44.03739,3.094375 44.037073,3.096357 44.036205,3.096039 44.035713,3.095472 44.035268,3.094342 44.034646,3.091827 44.03291,3.0912 44.032644,3.089949 44.032338,3.087949 44.031804,3.087134 44.031405,3.085381 44.030833,3.084446 44.030749,3.083822 44.030753,3.082878 44.03094,3.078592 44.032002,3.078647 44.033836,3.078704 44.034455,3.079406 44.038764,3.07899 44.038662,3.076457 44.038151,3.075549 44.038596,3.074306 44.039069,3.07259 44.041089,3.071915 44.041903,3.07118 44.042896,3.070996 44.043078,3.070813 44.043348,3.070444 44.043756,3.070319 44.043756,3.069507 44.043537,3.067237 44.042563,3.06443 44.04214,3.06321 44.04198,3.062473 44.042168,3.06101 44.042715,3.060847 44.042898,3.060575 44.043064,3.060183 44.043249,3.059749 44.043372,3.059536 44.043387,3.058782 44.04335,3.058216 44.043304,3.056477 44.043046,3.056171 44.043417,3.055205 44.044618,3.055205 44.04612,3.054707 44.046972,3.054329 44.047341,3.05398 44.047627,3.053848 44.047762,3.053877 44.047965,3.054125 44.048227,3.054227 44.048514,3.054252 44.048963,3.054242 44.049152,3.05412 44.049355,3.053959 44.049496,3.053855 44.049754,3.053893 44.049946,3.053563 44.050632,3.05004 44.049863,3.044086 44.048374,3.043578 44.048756,3.037957 44.051477,3.036346 44.052296,3.035428 44.05374,3.035062 44.054372,3.034107 44.056176,3.032144 44.058155,3.032223 44.058559,3.032842 44.059484,3.031542 44.059929,3.030262 44.060443,3.028968 44.061377,3.028366 44.06155,3.028017 44.061497,3.026988 44.061491,3.02674 44.061718,3.025674 44.061761,3.025343 44.061795,3.025049 44.061906,3.024708 44.061994,3.024088 44.062106,3.023747 44.062045,3.023092 44.062018,3.022709 44.062049,3.02254 44.062106,3.022324 44.062233,3.022122 44.062315,3.021468 44.062689,3.021346 44.062895,3.021178 44.063057,3.02073 44.063282,3.020506 44.06347,3.020148 44.064151,3.019949 44.064694,3.019912 44.065396,3.019872 44.065535,3.019927 44.065716,3.019874 44.066054,3.019896 44.066528,3.019845 44.066633,3.019933 44.066826,3.019922 44.067153,3.019819 44.067546,3.019907 44.067764,3.019506 44.068592,3.016594 44.071594,3.016541 44.071619,3.016367 44.071871,3.016133 44.071843,3.016029 44.071923,3.015798 44.072021,3.013366 44.073122,3.012986 44.073126,3.012848 44.073175,3.011851 44.073342,3.011479 44.073449,3.010135 44.073638,3.009371 44.073751,3.009642 44.074331,3.009769 44.074555,3.010639 44.074503,3.010847 44.076032,3.01092 44.076975,3.010872 44.078198,3.010917 44.078746,3.010405 44.079044,3.010645 44.079317,3.010699 44.079562,3.010325 44.080228,3.010564 44.080746,3.011223 44.081227,3.011503 44.081372,3.011475 44.082492,3.011739 44.082721,3.012055 44.083316,3.012409 44.084038,3.013635 44.083812,3.015417 44.083764,3.016062 44.083835,3.017206 44.08396,3.0192 44.084469,3.021916 44.08526,3.022957 44.085596,3.024484 44.086153,3.026148 44.086769,3.027328 44.087383,3.027861 44.087781,3.028895 44.089123,3.029775 44.090178,3.031084 44.091638,3.031638 44.092134,3.032565 44.092532,3.033632 44.092819,3.034198 44.093095,3.034619 44.093368,3.035568 44.093522,3.036096 44.093504,3.036651 44.093409,3.037073 44.093306,3.037371 44.09321,3.038235 44.092779,3.038471 44.092615,3.039229 44.091881,3.039588 44.091633,3.039915 44.091482,3.040588 44.091245,3.041225 44.090984,3.041774 44.0908,3.042199 44.090694,3.042716 44.09068,3.0433 44.090697,3.043986 44.090801,3.045082 44.091011,3.046002 44.091306,3.046811 44.091502,3.047505 44.091588,3.048029 44.091585,3.048908 44.091472,3.049186 44.091386,3.049504 44.091177,3.049733 44.090888,3.049857 44.090621,3.049897 44.090176,3.049788 44.089733,3.049635 44.089367,3.049501 44.088903,3.049524 44.088511,3.049723 44.088035,3.05008 44.087497,3.050523 44.086925,3.05098 44.086507,3.05154 44.086172,3.052134 44.085948,3.052913 44.085763,3.053545 44.085686,3.054086 44.085687,3.054743 44.085756,3.055265 44.085841,3.055902 44.085973,3.056785 44.08617,3.057666 44.086379,3.058536 44.086599,3.05946 44.086865,3.059902 44.087027,3.060355 44.087292,3.060486 44.087398,3.06083 44.087746,3.061581 44.088609,3.061952 44.088995,3.062326 44.089465,3.062469 44.089593,3.062662 44.089701,3.063245 44.089905,3.063987 44.090116,3.064649 44.09027,3.065086 44.090353,3.065879 44.090417,3.067434 44.090563,3.067723 44.090017,3.067912 44.089518,3.068354 44.089169,3.068441 44.089026,3.068495 44.088831,3.068509 44.088639,3.068476 44.088516,3.068608 44.087924,3.068791 44.087608,3.06885 44.087338,3.069031 44.086932,3.069473 44.0863,3.069965 44.085802,3.070337 44.08562,3.070649 44.085618,3.072652 44.086188,3.072919 44.085398,3.073686 44.085445,3.074441 44.08551,3.077757 44.0852,3.078276 44.085275,3.07876 44.085372,3.0792 44.085315,3.079115 44.083447,3.079345 44.081953,3.07965 44.081456,3.080876 44.07983,3.081875 44.079958,3.083099 44.078106,3.085942 44.075209,3.086311 44.074847,3.087356 44.073671,3.089135 44.071492,3.089439 44.070905,3.08962 44.070499,3.089678 44.070139,3.08992 44.069553,3.090039 44.069103,3.090342 44.068516,3.090466 44.068336,3.091073 44.067163,3.091501 44.066575,3.09192 44.066145,3.092234 44.065906,3.093546 44.065091,3.093955 44.06479,3.094632 44.064216,3.095013 44.063931,3.095308 44.063784,3.095614 44.063689,3.095787 44.063262,3.096057 44.062884,3.09675 44.061984,3.09697 44.061671,3.097263 44.061176,3.097663 44.060403,3.097886 44.060008,3.098749 44.058359,3.098889 44.058048,3.101797 44.05602,3.102415 44.055656,3.103158 44.055336,3.104566 44.053393,3.105116 44.05258,3.105102 44.051501,3.104897 44.050189,3.104828 44.049695,3.104693 44.048887,3.104423 44.046506,3.103914 44.044721,3.103965 44.044531,3.103998 44.044162)))"
]

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(((3.103998 44.044162,3.100957 44.044598,3.098404 44.044786,3.097906 44.044789,3.09703 44.044527,3.095091 44.044045,3.095087 44.043731,3.095207 44.04346,3.095505 44.042379,3.095864 44.041252,3.095983 44.040802,3.095976 44.040352,3.09535 44.040177,3.092665 44.039747,3.092663 44.039612,3.093151 44.038799,3.0937 44.037842,3.094005 44.03739,3.094375 44.037073,3.096357 44.036205,3.096039 44.035713,3.095472 44.035268,3.094342 44.034646,3.091827 44.03291,3.0912 44.032644,3.089949 44.032338,3.087949 44.031804,3.087134 44.031405,3.085381 44.030833,3.084446 44.030749,3.083822 44.030753,3.082878 44.03094,3.078592 44.032002,3.078647 44.033836,3.078704 44.034455,3.079406 44.038764,3.07899 44.038662,3.076457 44.038151,3.075549 44.038596,3.074306 44.039069,3.07259 44.041089,3.071915 44.041903,3.07118 44.042896,3.070996 44.043078,3.070813 44.043348,3.070444 44.043756,3.070319 44.043756,3.069507 44.043537,3.067237 44.042563,3.06443 44.04214,3.06321 44.04198,3.062473 44.042168,3.06101 44.042715,3.060847 44.042898,3.060575 44.043064,3.060183 44.043249,3.059749 44.043372,3.059536 44.043387,3.058782 44.04335,3.058216 44.043304,3.056477 44.043046,3.056171 44.043417,3.055205 44.044618,3.055205 44.04612,3.054707 44.046972,3.054329 44.047341,3.05398 44.047627,3.053848 44.047762,3.053877 44.047965,3.054125 44.048227,3.054227 44.048514,3.054252 44.048963,3.054242 44.049152,3.05412 44.049355,3.053959 44.049496,3.053855 44.049754,3.053893 44.049946,3.053563 44.050632,3.05004 44.049863,3.044086 44.048374,3.043578 44.048756,3.037957 44.051477,3.036346 44.052296,3.035428 44.05374,3.035062 44.054372,3.034107 44.056176,3.032144 44.058155,3.032223 44.058559,3.032842 44.059484,3.031542 44.059929,3.030262 44.060443,3.028968 44.061377,3.028366 44.06155,3.028017 44.061497,3.026988 44.061491,3.02674 44.061718,3.025674 44.061761,3.025343 44.061795,3.025049 44.061906,3.024708 44.061994,3.024088 44.062106,3.023747 44.062045,3.023092 44.062018,3.022709 44.062049,3.02254 44.062106,3.022324 44.062233,3.022122 44.062315,3.021468 44.062689,3.021346 44.062895,3.021178 44.063057,3.02073 44.063282,3.020506 44.06347,3.020148 44.064151,3.019949 44.064694,3.019912 44.065396,3.019872 44.065535,3.019927 44.065716,3.019874 44.066054,3.019896 44.066528,3.019845 44.066633,3.019933 44.066826,3.019922 44.067153,3.019819 44.067546,3.019907 44.067764,3.019506 44.068592,3.016594 44.071594,3.016541 44.071619,3.016367 44.071871,3.016133 44.071843,3.016029 44.071923,3.015798 44.072021,3.013366 44.073122,3.012986 44.073126,3.012848 44.073175,3.011851 44.073342,3.011479 44.073449,3.010135 44.073638,3.009371 44.073751,3.009642 44.074331,3.009769 44.074555,3.010639 44.074503,3.010847 44.076032,3.01092 44.076975,3.010872 44.078198,3.010917 44.078746,3.010405 44.079044,3.010645 44.079317,3.010699 44.079562,3.010325 44.080228,3.010564 44.080746,3.011223 44.081227,3.011503 44.081372,3.011475 44.082492,3.011739 44.082721,3.012055 44.083316,3.012409 44.084038,3.013635 44.083812,3.015417 44.083764,3.016062 44.083835,3.017206 44.08396,3.0192 44.084469,3.021916 44.08526,3.022957 44.085596,3.024484 44.086153,3.026148 44.086769,3.027328 44.087383,3.027861 44.087781,3.028895 44.089123,3.029775 44.090178,3.031084 44.091638,3.031638 44.092134,3.032565 44.092532,3.033632 44.092819,3.034198 44.093095,3.034619 44.093368,3.035568 44.093522,3.036096 44.093504,3.036651 44.093409,3.037073 44.093306,3.037371 44.09321,3.038235 44.092779,3.038471 44.092615,3.039229 44.091881,3.039588 44.091633,3.039915 44.091482,3.040588 44.091245,3.041225 44.090984,3.041774 44.0908,3.042199 44.090694,3.042716 44.09068,3.0433 44.090697,3.043986 44.090801,3.045082 44.091011,3.046002 44.091306,3.046811 44.091502,3.047505 44.091588,3.048029 44.091585,3.048908 44.091472,3.049186 44.091386,3.049504 44.091177,3.049733 44.090888,3.049857 44.090621,3.049897 44.090176,3.049788 44.089733,3.049635 44.089367,3.049501 44.088903,3.049524 44.088511,3.049723 44.088035,3.05008 44.087497,3.050523 44.086925,3.05098 44.086507,3.05154 44.086172,3.052134 44.085948,3.052913 44.085763,3.053545 44.085686,3.054086 44.085687,3.054743 44.085756,3.055265 44.085841,3.055902 44.085973,3.056785 44.08617,3.057666 44.086379,3.058536 44.086599,3.05946 44.086865,3.059902 44.087027,3.060355 44.087292,3.060486 44.087398,3.06083 44.087746,3.061581 44.088609,3.061952 44.088995,3.062326 44.089465,3.062469 44.089593,3.062662 44.089701,3.063245 44.089905,3.063987 44.090116,3.064649 44.09027,3.065086 44.090353,3.065879 44.090417,3.067434 44.090563,3.067723 44.090017,3.067912 44.089518,3.068354 44.089169,3.068441 44.089026,3.068495 44.088831,3.068509 44.088639,3.068476 44.088516,3.068608 44.087924,3.068791 44.087608,3.06885 44.087338,3.069031 44.086932,3.069473 44.0863,3.069965 44.085802,3.070337 44.08562,3.070649 44.085618,3.072652 44.086188,3.072919 44.085398,3.073686 44.085445,3.074441 44.08551,3.077757 44.0852,3.078276 44.085275,3.07876 44.085372,3.0792 44.085315,3.079115 44.083447,3.079345 44.081953,3.07965 44.081456,3.080876 44.07983,3.081875 44.079958,3.083099 44.078106,3.085942 44.075209,3.086311 44.074847,3.087356 44.073671,3.089135 44.071492,3.089439 44.070905,3.08962 44.070499,3.089678 44.070139,3.08992 44.069553,3.090039 44.069103,3.090342 44.068516,3.090466 44.068336,3.091073 44.067163,3.091501 44.066575,3.09192 44.066145,3.092234 44.065906,3.093546 44.065091,3.093955 44.06479,3.094632 44.064216,3.095013 44.063931,3.095308 44.063784,3.095614 44.063689,3.095787 44.063262,3.096057 44.062884,3.09675 44.061984,3.09697 44.061671,3.097263 44.061176,3.097663 44.060403,3.097886 44.060008,3.098749 44.058359,3.098889 44.058048,3.101797 44.05602,3.102415 44.055656,3.103158 44.055336,3.104566 44.053393,3.105116 44.05258,3.105102 44.051501,3.104897 44.050189,3.104828 44.049695,3.104693 44.048887,3.104423 44.046506,3.103914 44.044721,3.103965 44.044531,3.103998 44.044162)))'), 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)

9.36 ms
(1.16%)

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:

[
  "12084"
]

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

7.79 ms
(0.96%)

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(((3.103998 44.044162,3.100957 44.044598,3.098404 44.044786,3.097906 44.044789,3.09703 44.044527,3.095091 44.044045,3.095087 44.043731,3.095207 44.04346,3.095505 44.042379,3.095864 44.041252,3.095983 44.040802,3.095976 44.040352,3.09535 44.040177,3.092665 44.039747,3.092663 44.039612,3.093151 44.038799,3.0937 44.037842,3.094005 44.03739,3.094375 44.037073,3.096357 44.036205,3.096039 44.035713,3.095472 44.035268,3.094342 44.034646,3.091827 44.03291,3.0912 44.032644,3.089949 44.032338,3.087949 44.031804,3.087134 44.031405,3.085381 44.030833,3.084446 44.030749,3.083822 44.030753,3.082878 44.03094,3.078592 44.032002,3.078647 44.033836,3.078704 44.034455,3.079406 44.038764,3.07899 44.038662,3.076457 44.038151,3.075549 44.038596,3.074306 44.039069,3.07259 44.041089,3.071915 44.041903,3.07118 44.042896,3.070996 44.043078,3.070813 44.043348,3.070444 44.043756,3.070319 44.043756,3.069507 44.043537,3.067237 44.042563,3.06443 44.04214,3.06321 44.04198,3.062473 44.042168,3.06101 44.042715,3.060847 44.042898,3.060575 44.043064,3.060183 44.043249,3.059749 44.043372,3.059536 44.043387,3.058782 44.04335,3.058216 44.043304,3.056477 44.043046,3.056171 44.043417,3.055205 44.044618,3.055205 44.04612,3.054707 44.046972,3.054329 44.047341,3.05398 44.047627,3.053848 44.047762,3.053877 44.047965,3.054125 44.048227,3.054227 44.048514,3.054252 44.048963,3.054242 44.049152,3.05412 44.049355,3.053959 44.049496,3.053855 44.049754,3.053893 44.049946,3.053563 44.050632,3.05004 44.049863,3.044086 44.048374,3.043578 44.048756,3.037957 44.051477,3.036346 44.052296,3.035428 44.05374,3.035062 44.054372,3.034107 44.056176,3.032144 44.058155,3.032223 44.058559,3.032842 44.059484,3.031542 44.059929,3.030262 44.060443,3.028968 44.061377,3.028366 44.06155,3.028017 44.061497,3.026988 44.061491,3.02674 44.061718,3.025674 44.061761,3.025343 44.061795,3.025049 44.061906,3.024708 44.061994,3.024088 44.062106,3.023747 44.062045,3.023092 44.062018,3.022709 44.062049,3.02254 44.062106,3.022324 44.062233,3.022122 44.062315,3.021468 44.062689,3.021346 44.062895,3.021178 44.063057,3.02073 44.063282,3.020506 44.06347,3.020148 44.064151,3.019949 44.064694,3.019912 44.065396,3.019872 44.065535,3.019927 44.065716,3.019874 44.066054,3.019896 44.066528,3.019845 44.066633,3.019933 44.066826,3.019922 44.067153,3.019819 44.067546,3.019907 44.067764,3.019506 44.068592,3.016594 44.071594,3.016541 44.071619,3.016367 44.071871,3.016133 44.071843,3.016029 44.071923,3.015798 44.072021,3.013366 44.073122,3.012986 44.073126,3.012848 44.073175,3.011851 44.073342,3.011479 44.073449,3.010135 44.073638,3.009371 44.073751,3.009642 44.074331,3.009769 44.074555,3.010639 44.074503,3.010847 44.076032,3.01092 44.076975,3.010872 44.078198,3.010917 44.078746,3.010405 44.079044,3.010645 44.079317,3.010699 44.079562,3.010325 44.080228,3.010564 44.080746,3.011223 44.081227,3.011503 44.081372,3.011475 44.082492,3.011739 44.082721,3.012055 44.083316,3.012409 44.084038,3.013635 44.083812,3.015417 44.083764,3.016062 44.083835,3.017206 44.08396,3.0192 44.084469,3.021916 44.08526,3.022957 44.085596,3.024484 44.086153,3.026148 44.086769,3.027328 44.087383,3.027861 44.087781,3.028895 44.089123,3.029775 44.090178,3.031084 44.091638,3.031638 44.092134,3.032565 44.092532,3.033632 44.092819,3.034198 44.093095,3.034619 44.093368,3.035568 44.093522,3.036096 44.093504,3.036651 44.093409,3.037073 44.093306,3.037371 44.09321,3.038235 44.092779,3.038471 44.092615,3.039229 44.091881,3.039588 44.091633,3.039915 44.091482,3.040588 44.091245,3.041225 44.090984,3.041774 44.0908,3.042199 44.090694,3.042716 44.09068,3.0433 44.090697,3.043986 44.090801,3.045082 44.091011,3.046002 44.091306,3.046811 44.091502,3.047505 44.091588,3.048029 44.091585,3.048908 44.091472,3.049186 44.091386,3.049504 44.091177,3.049733 44.090888,3.049857 44.090621,3.049897 44.090176,3.049788 44.089733,3.049635 44.089367,3.049501 44.088903,3.049524 44.088511,3.049723 44.088035,3.05008 44.087497,3.050523 44.086925,3.05098 44.086507,3.05154 44.086172,3.052134 44.085948,3.052913 44.085763,3.053545 44.085686,3.054086 44.085687,3.054743 44.085756,3.055265 44.085841,3.055902 44.085973,3.056785 44.08617,3.057666 44.086379,3.058536 44.086599,3.05946 44.086865,3.059902 44.087027,3.060355 44.087292,3.060486 44.087398,3.06083 44.087746,3.061581 44.088609,3.061952 44.088995,3.062326 44.089465,3.062469 44.089593,3.062662 44.089701,3.063245 44.089905,3.063987 44.090116,3.064649 44.09027,3.065086 44.090353,3.065879 44.090417,3.067434 44.090563,3.067723 44.090017,3.067912 44.089518,3.068354 44.089169,3.068441 44.089026,3.068495 44.088831,3.068509 44.088639,3.068476 44.088516,3.068608 44.087924,3.068791 44.087608,3.06885 44.087338,3.069031 44.086932,3.069473 44.0863,3.069965 44.085802,3.070337 44.08562,3.070649 44.085618,3.072652 44.086188,3.072919 44.085398,3.073686 44.085445,3.074441 44.08551,3.077757 44.0852,3.078276 44.085275,3.07876 44.085372,3.0792 44.085315,3.079115 44.083447,3.079345 44.081953,3.07965 44.081456,3.080876 44.07983,3.081875 44.079958,3.083099 44.078106,3.085942 44.075209,3.086311 44.074847,3.087356 44.073671,3.089135 44.071492,3.089439 44.070905,3.08962 44.070499,3.089678 44.070139,3.08992 44.069553,3.090039 44.069103,3.090342 44.068516,3.090466 44.068336,3.091073 44.067163,3.091501 44.066575,3.09192 44.066145,3.092234 44.065906,3.093546 44.065091,3.093955 44.06479,3.094632 44.064216,3.095013 44.063931,3.095308 44.063784,3.095614 44.063689,3.095787 44.063262,3.096057 44.062884,3.09675 44.061984,3.09697 44.061671,3.097263 44.061176,3.097663 44.060403,3.097886 44.060008,3.098749 44.058359,3.098889 44.058048,3.101797 44.05602,3.102415 44.055656,3.103158 44.055336,3.104566 44.053393,3.105116 44.05258,3.105102 44.051501,3.104897 44.050189,3.104828 44.049695,3.104693 44.048887,3.104423 44.046506,3.103914 44.044721,3.103965 44.044531,3.103998 44.044162)))"
]

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(((3.103998 44.044162,3.100957 44.044598,3.098404 44.044786,3.097906 44.044789,3.09703 44.044527,3.095091 44.044045,3.095087 44.043731,3.095207 44.04346,3.095505 44.042379,3.095864 44.041252,3.095983 44.040802,3.095976 44.040352,3.09535 44.040177,3.092665 44.039747,3.092663 44.039612,3.093151 44.038799,3.0937 44.037842,3.094005 44.03739,3.094375 44.037073,3.096357 44.036205,3.096039 44.035713,3.095472 44.035268,3.094342 44.034646,3.091827 44.03291,3.0912 44.032644,3.089949 44.032338,3.087949 44.031804,3.087134 44.031405,3.085381 44.030833,3.084446 44.030749,3.083822 44.030753,3.082878 44.03094,3.078592 44.032002,3.078647 44.033836,3.078704 44.034455,3.079406 44.038764,3.07899 44.038662,3.076457 44.038151,3.075549 44.038596,3.074306 44.039069,3.07259 44.041089,3.071915 44.041903,3.07118 44.042896,3.070996 44.043078,3.070813 44.043348,3.070444 44.043756,3.070319 44.043756,3.069507 44.043537,3.067237 44.042563,3.06443 44.04214,3.06321 44.04198,3.062473 44.042168,3.06101 44.042715,3.060847 44.042898,3.060575 44.043064,3.060183 44.043249,3.059749 44.043372,3.059536 44.043387,3.058782 44.04335,3.058216 44.043304,3.056477 44.043046,3.056171 44.043417,3.055205 44.044618,3.055205 44.04612,3.054707 44.046972,3.054329 44.047341,3.05398 44.047627,3.053848 44.047762,3.053877 44.047965,3.054125 44.048227,3.054227 44.048514,3.054252 44.048963,3.054242 44.049152,3.05412 44.049355,3.053959 44.049496,3.053855 44.049754,3.053893 44.049946,3.053563 44.050632,3.05004 44.049863,3.044086 44.048374,3.043578 44.048756,3.037957 44.051477,3.036346 44.052296,3.035428 44.05374,3.035062 44.054372,3.034107 44.056176,3.032144 44.058155,3.032223 44.058559,3.032842 44.059484,3.031542 44.059929,3.030262 44.060443,3.028968 44.061377,3.028366 44.06155,3.028017 44.061497,3.026988 44.061491,3.02674 44.061718,3.025674 44.061761,3.025343 44.061795,3.025049 44.061906,3.024708 44.061994,3.024088 44.062106,3.023747 44.062045,3.023092 44.062018,3.022709 44.062049,3.02254 44.062106,3.022324 44.062233,3.022122 44.062315,3.021468 44.062689,3.021346 44.062895,3.021178 44.063057,3.02073 44.063282,3.020506 44.06347,3.020148 44.064151,3.019949 44.064694,3.019912 44.065396,3.019872 44.065535,3.019927 44.065716,3.019874 44.066054,3.019896 44.066528,3.019845 44.066633,3.019933 44.066826,3.019922 44.067153,3.019819 44.067546,3.019907 44.067764,3.019506 44.068592,3.016594 44.071594,3.016541 44.071619,3.016367 44.071871,3.016133 44.071843,3.016029 44.071923,3.015798 44.072021,3.013366 44.073122,3.012986 44.073126,3.012848 44.073175,3.011851 44.073342,3.011479 44.073449,3.010135 44.073638,3.009371 44.073751,3.009642 44.074331,3.009769 44.074555,3.010639 44.074503,3.010847 44.076032,3.01092 44.076975,3.010872 44.078198,3.010917 44.078746,3.010405 44.079044,3.010645 44.079317,3.010699 44.079562,3.010325 44.080228,3.010564 44.080746,3.011223 44.081227,3.011503 44.081372,3.011475 44.082492,3.011739 44.082721,3.012055 44.083316,3.012409 44.084038,3.013635 44.083812,3.015417 44.083764,3.016062 44.083835,3.017206 44.08396,3.0192 44.084469,3.021916 44.08526,3.022957 44.085596,3.024484 44.086153,3.026148 44.086769,3.027328 44.087383,3.027861 44.087781,3.028895 44.089123,3.029775 44.090178,3.031084 44.091638,3.031638 44.092134,3.032565 44.092532,3.033632 44.092819,3.034198 44.093095,3.034619 44.093368,3.035568 44.093522,3.036096 44.093504,3.036651 44.093409,3.037073 44.093306,3.037371 44.09321,3.038235 44.092779,3.038471 44.092615,3.039229 44.091881,3.039588 44.091633,3.039915 44.091482,3.040588 44.091245,3.041225 44.090984,3.041774 44.0908,3.042199 44.090694,3.042716 44.09068,3.0433 44.090697,3.043986 44.090801,3.045082 44.091011,3.046002 44.091306,3.046811 44.091502,3.047505 44.091588,3.048029 44.091585,3.048908 44.091472,3.049186 44.091386,3.049504 44.091177,3.049733 44.090888,3.049857 44.090621,3.049897 44.090176,3.049788 44.089733,3.049635 44.089367,3.049501 44.088903,3.049524 44.088511,3.049723 44.088035,3.05008 44.087497,3.050523 44.086925,3.05098 44.086507,3.05154 44.086172,3.052134 44.085948,3.052913 44.085763,3.053545 44.085686,3.054086 44.085687,3.054743 44.085756,3.055265 44.085841,3.055902 44.085973,3.056785 44.08617,3.057666 44.086379,3.058536 44.086599,3.05946 44.086865,3.059902 44.087027,3.060355 44.087292,3.060486 44.087398,3.06083 44.087746,3.061581 44.088609,3.061952 44.088995,3.062326 44.089465,3.062469 44.089593,3.062662 44.089701,3.063245 44.089905,3.063987 44.090116,3.064649 44.09027,3.065086 44.090353,3.065879 44.090417,3.067434 44.090563,3.067723 44.090017,3.067912 44.089518,3.068354 44.089169,3.068441 44.089026,3.068495 44.088831,3.068509 44.088639,3.068476 44.088516,3.068608 44.087924,3.068791 44.087608,3.06885 44.087338,3.069031 44.086932,3.069473 44.0863,3.069965 44.085802,3.070337 44.08562,3.070649 44.085618,3.072652 44.086188,3.072919 44.085398,3.073686 44.085445,3.074441 44.08551,3.077757 44.0852,3.078276 44.085275,3.07876 44.085372,3.0792 44.085315,3.079115 44.083447,3.079345 44.081953,3.07965 44.081456,3.080876 44.07983,3.081875 44.079958,3.083099 44.078106,3.085942 44.075209,3.086311 44.074847,3.087356 44.073671,3.089135 44.071492,3.089439 44.070905,3.08962 44.070499,3.089678 44.070139,3.08992 44.069553,3.090039 44.069103,3.090342 44.068516,3.090466 44.068336,3.091073 44.067163,3.091501 44.066575,3.09192 44.066145,3.092234 44.065906,3.093546 44.065091,3.093955 44.06479,3.094632 44.064216,3.095013 44.063931,3.095308 44.063784,3.095614 44.063689,3.095787 44.063262,3.096057 44.062884,3.09675 44.061984,3.09697 44.061671,3.097263 44.061176,3.097663 44.060403,3.097886 44.060008,3.098749 44.058359,3.098889 44.058048,3.101797 44.05602,3.102415 44.055656,3.103158 44.055336,3.104566 44.053393,3.105116 44.05258,3.105102 44.051501,3.104897 44.050189,3.104828 44.049695,3.104693 44.048887,3.104423 44.046506,3.103914 44.044721,3.103965 44.044531,3.103998 44.044162)))', 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)

7.78 ms
(0.96%)

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(((3.103998 44.044162,3.100957 44.044598,3.098404 44.044786,3.097906 44.044789,3.09703 44.044527,3.095091 44.044045,3.095087 44.043731,3.095207 44.04346,3.095505 44.042379,3.095864 44.041252,3.095983 44.040802,3.095976 44.040352,3.09535 44.040177,3.092665 44.039747,3.092663 44.039612,3.093151 44.038799,3.0937 44.037842,3.094005 44.03739,3.094375 44.037073,3.096357 44.036205,3.096039 44.035713,3.095472 44.035268,3.094342 44.034646,3.091827 44.03291,3.0912 44.032644,3.089949 44.032338,3.087949 44.031804,3.087134 44.031405,3.085381 44.030833,3.084446 44.030749,3.083822 44.030753,3.082878 44.03094,3.078592 44.032002,3.078647 44.033836,3.078704 44.034455,3.079406 44.038764,3.07899 44.038662,3.076457 44.038151,3.075549 44.038596,3.074306 44.039069,3.07259 44.041089,3.071915 44.041903,3.07118 44.042896,3.070996 44.043078,3.070813 44.043348,3.070444 44.043756,3.070319 44.043756,3.069507 44.043537,3.067237 44.042563,3.06443 44.04214,3.06321 44.04198,3.062473 44.042168,3.06101 44.042715,3.060847 44.042898,3.060575 44.043064,3.060183 44.043249,3.059749 44.043372,3.059536 44.043387,3.058782 44.04335,3.058216 44.043304,3.056477 44.043046,3.056171 44.043417,3.055205 44.044618,3.055205 44.04612,3.054707 44.046972,3.054329 44.047341,3.05398 44.047627,3.053848 44.047762,3.053877 44.047965,3.054125 44.048227,3.054227 44.048514,3.054252 44.048963,3.054242 44.049152,3.05412 44.049355,3.053959 44.049496,3.053855 44.049754,3.053893 44.049946,3.053563 44.050632,3.05004 44.049863,3.044086 44.048374,3.043578 44.048756,3.037957 44.051477,3.036346 44.052296,3.035428 44.05374,3.035062 44.054372,3.034107 44.056176,3.032144 44.058155,3.032223 44.058559,3.032842 44.059484,3.031542 44.059929,3.030262 44.060443,3.028968 44.061377,3.028366 44.06155,3.028017 44.061497,3.026988 44.061491,3.02674 44.061718,3.025674 44.061761,3.025343 44.061795,3.025049 44.061906,3.024708 44.061994,3.024088 44.062106,3.023747 44.062045,3.023092 44.062018,3.022709 44.062049,3.02254 44.062106,3.022324 44.062233,3.022122 44.062315,3.021468 44.062689,3.021346 44.062895,3.021178 44.063057,3.02073 44.063282,3.020506 44.06347,3.020148 44.064151,3.019949 44.064694,3.019912 44.065396,3.019872 44.065535,3.019927 44.065716,3.019874 44.066054,3.019896 44.066528,3.019845 44.066633,3.019933 44.066826,3.019922 44.067153,3.019819 44.067546,3.019907 44.067764,3.019506 44.068592,3.016594 44.071594,3.016541 44.071619,3.016367 44.071871,3.016133 44.071843,3.016029 44.071923,3.015798 44.072021,3.013366 44.073122,3.012986 44.073126,3.012848 44.073175,3.011851 44.073342,3.011479 44.073449,3.010135 44.073638,3.009371 44.073751,3.009642 44.074331,3.009769 44.074555,3.010639 44.074503,3.010847 44.076032,3.01092 44.076975,3.010872 44.078198,3.010917 44.078746,3.010405 44.079044,3.010645 44.079317,3.010699 44.079562,3.010325 44.080228,3.010564 44.080746,3.011223 44.081227,3.011503 44.081372,3.011475 44.082492,3.011739 44.082721,3.012055 44.083316,3.012409 44.084038,3.013635 44.083812,3.015417 44.083764,3.016062 44.083835,3.017206 44.08396,3.0192 44.084469,3.021916 44.08526,3.022957 44.085596,3.024484 44.086153,3.026148 44.086769,3.027328 44.087383,3.027861 44.087781,3.028895 44.089123,3.029775 44.090178,3.031084 44.091638,3.031638 44.092134,3.032565 44.092532,3.033632 44.092819,3.034198 44.093095,3.034619 44.093368,3.035568 44.093522,3.036096 44.093504,3.036651 44.093409,3.037073 44.093306,3.037371 44.09321,3.038235 44.092779,3.038471 44.092615,3.039229 44.091881,3.039588 44.091633,3.039915 44.091482,3.040588 44.091245,3.041225 44.090984,3.041774 44.0908,3.042199 44.090694,3.042716 44.09068,3.0433 44.090697,3.043986 44.090801,3.045082 44.091011,3.046002 44.091306,3.046811 44.091502,3.047505 44.091588,3.048029 44.091585,3.048908 44.091472,3.049186 44.091386,3.049504 44.091177,3.049733 44.090888,3.049857 44.090621,3.049897 44.090176,3.049788 44.089733,3.049635 44.089367,3.049501 44.088903,3.049524 44.088511,3.049723 44.088035,3.05008 44.087497,3.050523 44.086925,3.05098 44.086507,3.05154 44.086172,3.052134 44.085948,3.052913 44.085763,3.053545 44.085686,3.054086 44.085687,3.054743 44.085756,3.055265 44.085841,3.055902 44.085973,3.056785 44.08617,3.057666 44.086379,3.058536 44.086599,3.05946 44.086865,3.059902 44.087027,3.060355 44.087292,3.060486 44.087398,3.06083 44.087746,3.061581 44.088609,3.061952 44.088995,3.062326 44.089465,3.062469 44.089593,3.062662 44.089701,3.063245 44.089905,3.063987 44.090116,3.064649 44.09027,3.065086 44.090353,3.065879 44.090417,3.067434 44.090563,3.067723 44.090017,3.067912 44.089518,3.068354 44.089169,3.068441 44.089026,3.068495 44.088831,3.068509 44.088639,3.068476 44.088516,3.068608 44.087924,3.068791 44.087608,3.06885 44.087338,3.069031 44.086932,3.069473 44.0863,3.069965 44.085802,3.070337 44.08562,3.070649 44.085618,3.072652 44.086188,3.072919 44.085398,3.073686 44.085445,3.074441 44.08551,3.077757 44.0852,3.078276 44.085275,3.07876 44.085372,3.0792 44.085315,3.079115 44.083447,3.079345 44.081953,3.07965 44.081456,3.080876 44.07983,3.081875 44.079958,3.083099 44.078106,3.085942 44.075209,3.086311 44.074847,3.087356 44.073671,3.089135 44.071492,3.089439 44.070905,3.08962 44.070499,3.089678 44.070139,3.08992 44.069553,3.090039 44.069103,3.090342 44.068516,3.090466 44.068336,3.091073 44.067163,3.091501 44.066575,3.09192 44.066145,3.092234 44.065906,3.093546 44.065091,3.093955 44.06479,3.094632 44.064216,3.095013 44.063931,3.095308 44.063784,3.095614 44.063689,3.095787 44.063262,3.096057 44.062884,3.09675 44.061984,3.09697 44.061671,3.097263 44.061176,3.097663 44.060403,3.097886 44.060008,3.098749 44.058359,3.098889 44.058048,3.101797 44.05602,3.102415 44.055656,3.103158 44.055336,3.104566 44.053393,3.105116 44.05258,3.105102 44.051501,3.104897 44.050189,3.104828 44.049695,3.104693 44.048887,3.104423 44.046506,3.103914 44.044721,3.103965 44.044531,3.103998 44.044162)))"
  "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(((3.103998 44.044162,3.100957 44.044598,3.098404 44.044786,3.097906 44.044789,3.09703 44.044527,3.095091 44.044045,3.095087 44.043731,3.095207 44.04346,3.095505 44.042379,3.095864 44.041252,3.095983 44.040802,3.095976 44.040352,3.09535 44.040177,3.092665 44.039747,3.092663 44.039612,3.093151 44.038799,3.0937 44.037842,3.094005 44.03739,3.094375 44.037073,3.096357 44.036205,3.096039 44.035713,3.095472 44.035268,3.094342 44.034646,3.091827 44.03291,3.0912 44.032644,3.089949 44.032338,3.087949 44.031804,3.087134 44.031405,3.085381 44.030833,3.084446 44.030749,3.083822 44.030753,3.082878 44.03094,3.078592 44.032002,3.078647 44.033836,3.078704 44.034455,3.079406 44.038764,3.07899 44.038662,3.076457 44.038151,3.075549 44.038596,3.074306 44.039069,3.07259 44.041089,3.071915 44.041903,3.07118 44.042896,3.070996 44.043078,3.070813 44.043348,3.070444 44.043756,3.070319 44.043756,3.069507 44.043537,3.067237 44.042563,3.06443 44.04214,3.06321 44.04198,3.062473 44.042168,3.06101 44.042715,3.060847 44.042898,3.060575 44.043064,3.060183 44.043249,3.059749 44.043372,3.059536 44.043387,3.058782 44.04335,3.058216 44.043304,3.056477 44.043046,3.056171 44.043417,3.055205 44.044618,3.055205 44.04612,3.054707 44.046972,3.054329 44.047341,3.05398 44.047627,3.053848 44.047762,3.053877 44.047965,3.054125 44.048227,3.054227 44.048514,3.054252 44.048963,3.054242 44.049152,3.05412 44.049355,3.053959 44.049496,3.053855 44.049754,3.053893 44.049946,3.053563 44.050632,3.05004 44.049863,3.044086 44.048374,3.043578 44.048756,3.037957 44.051477,3.036346 44.052296,3.035428 44.05374,3.035062 44.054372,3.034107 44.056176,3.032144 44.058155,3.032223 44.058559,3.032842 44.059484,3.031542 44.059929,3.030262 44.060443,3.028968 44.061377,3.028366 44.06155,3.028017 44.061497,3.026988 44.061491,3.02674 44.061718,3.025674 44.061761,3.025343 44.061795,3.025049 44.061906,3.024708 44.061994,3.024088 44.062106,3.023747 44.062045,3.023092 44.062018,3.022709 44.062049,3.02254 44.062106,3.022324 44.062233,3.022122 44.062315,3.021468 44.062689,3.021346 44.062895,3.021178 44.063057,3.02073 44.063282,3.020506 44.06347,3.020148 44.064151,3.019949 44.064694,3.019912 44.065396,3.019872 44.065535,3.019927 44.065716,3.019874 44.066054,3.019896 44.066528,3.019845 44.066633,3.019933 44.066826,3.019922 44.067153,3.019819 44.067546,3.019907 44.067764,3.019506 44.068592,3.016594 44.071594,3.016541 44.071619,3.016367 44.071871,3.016133 44.071843,3.016029 44.071923,3.015798 44.072021,3.013366 44.073122,3.012986 44.073126,3.012848 44.073175,3.011851 44.073342,3.011479 44.073449,3.010135 44.073638,3.009371 44.073751,3.009642 44.074331,3.009769 44.074555,3.010639 44.074503,3.010847 44.076032,3.01092 44.076975,3.010872 44.078198,3.010917 44.078746,3.010405 44.079044,3.010645 44.079317,3.010699 44.079562,3.010325 44.080228,3.010564 44.080746,3.011223 44.081227,3.011503 44.081372,3.011475 44.082492,3.011739 44.082721,3.012055 44.083316,3.012409 44.084038,3.013635 44.083812,3.015417 44.083764,3.016062 44.083835,3.017206 44.08396,3.0192 44.084469,3.021916 44.08526,3.022957 44.085596,3.024484 44.086153,3.026148 44.086769,3.027328 44.087383,3.027861 44.087781,3.028895 44.089123,3.029775 44.090178,3.031084 44.091638,3.031638 44.092134,3.032565 44.092532,3.033632 44.092819,3.034198 44.093095,3.034619 44.093368,3.035568 44.093522,3.036096 44.093504,3.036651 44.093409,3.037073 44.093306,3.037371 44.09321,3.038235 44.092779,3.038471 44.092615,3.039229 44.091881,3.039588 44.091633,3.039915 44.091482,3.040588 44.091245,3.041225 44.090984,3.041774 44.0908,3.042199 44.090694,3.042716 44.09068,3.0433 44.090697,3.043986 44.090801,3.045082 44.091011,3.046002 44.091306,3.046811 44.091502,3.047505 44.091588,3.048029 44.091585,3.048908 44.091472,3.049186 44.091386,3.049504 44.091177,3.049733 44.090888,3.049857 44.090621,3.049897 44.090176,3.049788 44.089733,3.049635 44.089367,3.049501 44.088903,3.049524 44.088511,3.049723 44.088035,3.05008 44.087497,3.050523 44.086925,3.05098 44.086507,3.05154 44.086172,3.052134 44.085948,3.052913 44.085763,3.053545 44.085686,3.054086 44.085687,3.054743 44.085756,3.055265 44.085841,3.055902 44.085973,3.056785 44.08617,3.057666 44.086379,3.058536 44.086599,3.05946 44.086865,3.059902 44.087027,3.060355 44.087292,3.060486 44.087398,3.06083 44.087746,3.061581 44.088609,3.061952 44.088995,3.062326 44.089465,3.062469 44.089593,3.062662 44.089701,3.063245 44.089905,3.063987 44.090116,3.064649 44.09027,3.065086 44.090353,3.065879 44.090417,3.067434 44.090563,3.067723 44.090017,3.067912 44.089518,3.068354 44.089169,3.068441 44.089026,3.068495 44.088831,3.068509 44.088639,3.068476 44.088516,3.068608 44.087924,3.068791 44.087608,3.06885 44.087338,3.069031 44.086932,3.069473 44.0863,3.069965 44.085802,3.070337 44.08562,3.070649 44.085618,3.072652 44.086188,3.072919 44.085398,3.073686 44.085445,3.074441 44.08551,3.077757 44.0852,3.078276 44.085275,3.07876 44.085372,3.0792 44.085315,3.079115 44.083447,3.079345 44.081953,3.07965 44.081456,3.080876 44.07983,3.081875 44.079958,3.083099 44.078106,3.085942 44.075209,3.086311 44.074847,3.087356 44.073671,3.089135 44.071492,3.089439 44.070905,3.08962 44.070499,3.089678 44.070139,3.08992 44.069553,3.090039 44.069103,3.090342 44.068516,3.090466 44.068336,3.091073 44.067163,3.091501 44.066575,3.09192 44.066145,3.092234 44.065906,3.093546 44.065091,3.093955 44.06479,3.094632 44.064216,3.095013 44.063931,3.095308 44.063784,3.095614 44.063689,3.095787 44.063262,3.096057 44.062884,3.09675 44.061984,3.09697 44.061671,3.097263 44.061176,3.097663 44.060403,3.097886 44.060008,3.098749 44.058359,3.098889 44.058048,3.101797 44.05602,3.102415 44.055656,3.103158 44.055336,3.104566 44.053393,3.105116 44.05258,3.105102 44.051501,3.104897 44.050189,3.104828 44.049695,3.104693 44.048887,3.104423 44.046506,3.103914 44.044721,3.103965 44.044531,3.103998 44.044162)))', 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)

3.53 ms
(0.44%)

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.99 ms
(0.37%)

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.97 ms
(0.37%)

SELECT COUNT(*) FROM osm_object_meta WHERE osm_type = 'relation'
                  AND geom IS NOT NULL
                  AND (tags->'admin_level') = '8'
                  AND (tags->'boundary') = 'administrative'
                  AND (tags->'ref:INSEE') IS NOT NULL

Parameters:

[]

View formatted query View runnable query Explain query View query backtrace

SELECT
  COUNT(*)
FROM
  osm_object_meta
WHERE
  osm_type = 'relation'
  AND geom IS NOT NULL
  AND (tags -> 'admin_level') = '8'
  AND (tags -> 'boundary') = 'administrative'
  AND (tags -> 'ref:INSEE') IS NOT NULL

SELECT COUNT(*) FROM osm_object_meta WHERE osm_type = 'relation'
                  AND geom IS NOT NULL
                  AND (tags->'admin_level') = '8'
                  AND (tags->'boundary') = 'administrative'
                  AND (tags->'ref:INSEE') IS NOT NULL;

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

2.97 ms
(0.37%)

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)

1.90 ms
(0.24%)

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(((3.103998 44.044162,3.100957 44.044598,3.098404 44.044786,3.097906 44.044789,3.09703 44.044527,3.095091 44.044045,3.095087 44.043731,3.095207 44.04346,3.095505 44.042379,3.095864 44.041252,3.095983 44.040802,3.095976 44.040352,3.09535 44.040177,3.092665 44.039747,3.092663 44.039612,3.093151 44.038799,3.0937 44.037842,3.094005 44.03739,3.094375 44.037073,3.096357 44.036205,3.096039 44.035713,3.095472 44.035268,3.094342 44.034646,3.091827 44.03291,3.0912 44.032644,3.089949 44.032338,3.087949 44.031804,3.087134 44.031405,3.085381 44.030833,3.084446 44.030749,3.083822 44.030753,3.082878 44.03094,3.078592 44.032002,3.078647 44.033836,3.078704 44.034455,3.079406 44.038764,3.07899 44.038662,3.076457 44.038151,3.075549 44.038596,3.074306 44.039069,3.07259 44.041089,3.071915 44.041903,3.07118 44.042896,3.070996 44.043078,3.070813 44.043348,3.070444 44.043756,3.070319 44.043756,3.069507 44.043537,3.067237 44.042563,3.06443 44.04214,3.06321 44.04198,3.062473 44.042168,3.06101 44.042715,3.060847 44.042898,3.060575 44.043064,3.060183 44.043249,3.059749 44.043372,3.059536 44.043387,3.058782 44.04335,3.058216 44.043304,3.056477 44.043046,3.056171 44.043417,3.055205 44.044618,3.055205 44.04612,3.054707 44.046972,3.054329 44.047341,3.05398 44.047627,3.053848 44.047762,3.053877 44.047965,3.054125 44.048227,3.054227 44.048514,3.054252 44.048963,3.054242 44.049152,3.05412 44.049355,3.053959 44.049496,3.053855 44.049754,3.053893 44.049946,3.053563 44.050632,3.05004 44.049863,3.044086 44.048374,3.043578 44.048756,3.037957 44.051477,3.036346 44.052296,3.035428 44.05374,3.035062 44.054372,3.034107 44.056176,3.032144 44.058155,3.032223 44.058559,3.032842 44.059484,3.031542 44.059929,3.030262 44.060443,3.028968 44.061377,3.028366 44.06155,3.028017 44.061497,3.026988 44.061491,3.02674 44.061718,3.025674 44.061761,3.025343 44.061795,3.025049 44.061906,3.024708 44.061994,3.024088 44.062106,3.023747 44.062045,3.023092 44.062018,3.022709 44.062049,3.02254 44.062106,3.022324 44.062233,3.022122 44.062315,3.021468 44.062689,3.021346 44.062895,3.021178 44.063057,3.02073 44.063282,3.020506 44.06347,3.020148 44.064151,3.019949 44.064694,3.019912 44.065396,3.019872 44.065535,3.019927 44.065716,3.019874 44.066054,3.019896 44.066528,3.019845 44.066633,3.019933 44.066826,3.019922 44.067153,3.019819 44.067546,3.019907 44.067764,3.019506 44.068592,3.016594 44.071594,3.016541 44.071619,3.016367 44.071871,3.016133 44.071843,3.016029 44.071923,3.015798 44.072021,3.013366 44.073122,3.012986 44.073126,3.012848 44.073175,3.011851 44.073342,3.011479 44.073449,3.010135 44.073638,3.009371 44.073751,3.009642 44.074331,3.009769 44.074555,3.010639 44.074503,3.010847 44.076032,3.01092 44.076975,3.010872 44.078198,3.010917 44.078746,3.010405 44.079044,3.010645 44.079317,3.010699 44.079562,3.010325 44.080228,3.010564 44.080746,3.011223 44.081227,3.011503 44.081372,3.011475 44.082492,3.011739 44.082721,3.012055 44.083316,3.012409 44.084038,3.013635 44.083812,3.015417 44.083764,3.016062 44.083835,3.017206 44.08396,3.0192 44.084469,3.021916 44.08526,3.022957 44.085596,3.024484 44.086153,3.026148 44.086769,3.027328 44.087383,3.027861 44.087781,3.028895 44.089123,3.029775 44.090178,3.031084 44.091638,3.031638 44.092134,3.032565 44.092532,3.033632 44.092819,3.034198 44.093095,3.034619 44.093368,3.035568 44.093522,3.036096 44.093504,3.036651 44.093409,3.037073 44.093306,3.037371 44.09321,3.038235 44.092779,3.038471 44.092615,3.039229 44.091881,3.039588 44.091633,3.039915 44.091482,3.040588 44.091245,3.041225 44.090984,3.041774 44.0908,3.042199 44.090694,3.042716 44.09068,3.0433 44.090697,3.043986 44.090801,3.045082 44.091011,3.046002 44.091306,3.046811 44.091502,3.047505 44.091588,3.048029 44.091585,3.048908 44.091472,3.049186 44.091386,3.049504 44.091177,3.049733 44.090888,3.049857 44.090621,3.049897 44.090176,3.049788 44.089733,3.049635 44.089367,3.049501 44.088903,3.049524 44.088511,3.049723 44.088035,3.05008 44.087497,3.050523 44.086925,3.05098 44.086507,3.05154 44.086172,3.052134 44.085948,3.052913 44.085763,3.053545 44.085686,3.054086 44.085687,3.054743 44.085756,3.055265 44.085841,3.055902 44.085973,3.056785 44.08617,3.057666 44.086379,3.058536 44.086599,3.05946 44.086865,3.059902 44.087027,3.060355 44.087292,3.060486 44.087398,3.06083 44.087746,3.061581 44.088609,3.061952 44.088995,3.062326 44.089465,3.062469 44.089593,3.062662 44.089701,3.063245 44.089905,3.063987 44.090116,3.064649 44.09027,3.065086 44.090353,3.065879 44.090417,3.067434 44.090563,3.067723 44.090017,3.067912 44.089518,3.068354 44.089169,3.068441 44.089026,3.068495 44.088831,3.068509 44.088639,3.068476 44.088516,3.068608 44.087924,3.068791 44.087608,3.06885 44.087338,3.069031 44.086932,3.069473 44.0863,3.069965 44.085802,3.070337 44.08562,3.070649 44.085618,3.072652 44.086188,3.072919 44.085398,3.073686 44.085445,3.074441 44.08551,3.077757 44.0852,3.078276 44.085275,3.07876 44.085372,3.0792 44.085315,3.079115 44.083447,3.079345 44.081953,3.07965 44.081456,3.080876 44.07983,3.081875 44.079958,3.083099 44.078106,3.085942 44.075209,3.086311 44.074847,3.087356 44.073671,3.089135 44.071492,3.089439 44.070905,3.08962 44.070499,3.089678 44.070139,3.08992 44.069553,3.090039 44.069103,3.090342 44.068516,3.090466 44.068336,3.091073 44.067163,3.091501 44.066575,3.09192 44.066145,3.092234 44.065906,3.093546 44.065091,3.093955 44.06479,3.094632 44.064216,3.095013 44.063931,3.095308 44.063784,3.095614 44.063689,3.095787 44.063262,3.096057 44.062884,3.09675 44.061984,3.09697 44.061671,3.097263 44.061176,3.097663 44.060403,3.097886 44.060008,3.098749 44.058359,3.098889 44.058048,3.101797 44.05602,3.102415 44.055656,3.103158 44.055336,3.104566 44.053393,3.105116 44.05258,3.105102 44.051501,3.104897 44.050189,3.104828 44.049695,3.104693 44.048887,3.104423 44.046506,3.103914 44.044721,3.103965 44.044531,3.103998 44.044162)))"
  "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(((3.103998 44.044162,3.100957 44.044598,3.098404 44.044786,3.097906 44.044789,3.09703 44.044527,3.095091 44.044045,3.095087 44.043731,3.095207 44.04346,3.095505 44.042379,3.095864 44.041252,3.095983 44.040802,3.095976 44.040352,3.09535 44.040177,3.092665 44.039747,3.092663 44.039612,3.093151 44.038799,3.0937 44.037842,3.094005 44.03739,3.094375 44.037073,3.096357 44.036205,3.096039 44.035713,3.095472 44.035268,3.094342 44.034646,3.091827 44.03291,3.0912 44.032644,3.089949 44.032338,3.087949 44.031804,3.087134 44.031405,3.085381 44.030833,3.084446 44.030749,3.083822 44.030753,3.082878 44.03094,3.078592 44.032002,3.078647 44.033836,3.078704 44.034455,3.079406 44.038764,3.07899 44.038662,3.076457 44.038151,3.075549 44.038596,3.074306 44.039069,3.07259 44.041089,3.071915 44.041903,3.07118 44.042896,3.070996 44.043078,3.070813 44.043348,3.070444 44.043756,3.070319 44.043756,3.069507 44.043537,3.067237 44.042563,3.06443 44.04214,3.06321 44.04198,3.062473 44.042168,3.06101 44.042715,3.060847 44.042898,3.060575 44.043064,3.060183 44.043249,3.059749 44.043372,3.059536 44.043387,3.058782 44.04335,3.058216 44.043304,3.056477 44.043046,3.056171 44.043417,3.055205 44.044618,3.055205 44.04612,3.054707 44.046972,3.054329 44.047341,3.05398 44.047627,3.053848 44.047762,3.053877 44.047965,3.054125 44.048227,3.054227 44.048514,3.054252 44.048963,3.054242 44.049152,3.05412 44.049355,3.053959 44.049496,3.053855 44.049754,3.053893 44.049946,3.053563 44.050632,3.05004 44.049863,3.044086 44.048374,3.043578 44.048756,3.037957 44.051477,3.036346 44.052296,3.035428 44.05374,3.035062 44.054372,3.034107 44.056176,3.032144 44.058155,3.032223 44.058559,3.032842 44.059484,3.031542 44.059929,3.030262 44.060443,3.028968 44.061377,3.028366 44.06155,3.028017 44.061497,3.026988 44.061491,3.02674 44.061718,3.025674 44.061761,3.025343 44.061795,3.025049 44.061906,3.024708 44.061994,3.024088 44.062106,3.023747 44.062045,3.023092 44.062018,3.022709 44.062049,3.02254 44.062106,3.022324 44.062233,3.022122 44.062315,3.021468 44.062689,3.021346 44.062895,3.021178 44.063057,3.02073 44.063282,3.020506 44.06347,3.020148 44.064151,3.019949 44.064694,3.019912 44.065396,3.019872 44.065535,3.019927 44.065716,3.019874 44.066054,3.019896 44.066528,3.019845 44.066633,3.019933 44.066826,3.019922 44.067153,3.019819 44.067546,3.019907 44.067764,3.019506 44.068592,3.016594 44.071594,3.016541 44.071619,3.016367 44.071871,3.016133 44.071843,3.016029 44.071923,3.015798 44.072021,3.013366 44.073122,3.012986 44.073126,3.012848 44.073175,3.011851 44.073342,3.011479 44.073449,3.010135 44.073638,3.009371 44.073751,3.009642 44.074331,3.009769 44.074555,3.010639 44.074503,3.010847 44.076032,3.01092 44.076975,3.010872 44.078198,3.010917 44.078746,3.010405 44.079044,3.010645 44.079317,3.010699 44.079562,3.010325 44.080228,3.010564 44.080746,3.011223 44.081227,3.011503 44.081372,3.011475 44.082492,3.011739 44.082721,3.012055 44.083316,3.012409 44.084038,3.013635 44.083812,3.015417 44.083764,3.016062 44.083835,3.017206 44.08396,3.0192 44.084469,3.021916 44.08526,3.022957 44.085596,3.024484 44.086153,3.026148 44.086769,3.027328 44.087383,3.027861 44.087781,3.028895 44.089123,3.029775 44.090178,3.031084 44.091638,3.031638 44.092134,3.032565 44.092532,3.033632 44.092819,3.034198 44.093095,3.034619 44.093368,3.035568 44.093522,3.036096 44.093504,3.036651 44.093409,3.037073 44.093306,3.037371 44.09321,3.038235 44.092779,3.038471 44.092615,3.039229 44.091881,3.039588 44.091633,3.039915 44.091482,3.040588 44.091245,3.041225 44.090984,3.041774 44.0908,3.042199 44.090694,3.042716 44.09068,3.0433 44.090697,3.043986 44.090801,3.045082 44.091011,3.046002 44.091306,3.046811 44.091502,3.047505 44.091588,3.048029 44.091585,3.048908 44.091472,3.049186 44.091386,3.049504 44.091177,3.049733 44.090888,3.049857 44.090621,3.049897 44.090176,3.049788 44.089733,3.049635 44.089367,3.049501 44.088903,3.049524 44.088511,3.049723 44.088035,3.05008 44.087497,3.050523 44.086925,3.05098 44.086507,3.05154 44.086172,3.052134 44.085948,3.052913 44.085763,3.053545 44.085686,3.054086 44.085687,3.054743 44.085756,3.055265 44.085841,3.055902 44.085973,3.056785 44.08617,3.057666 44.086379,3.058536 44.086599,3.05946 44.086865,3.059902 44.087027,3.060355 44.087292,3.060486 44.087398,3.06083 44.087746,3.061581 44.088609,3.061952 44.088995,3.062326 44.089465,3.062469 44.089593,3.062662 44.089701,3.063245 44.089905,3.063987 44.090116,3.064649 44.09027,3.065086 44.090353,3.065879 44.090417,3.067434 44.090563,3.067723 44.090017,3.067912 44.089518,3.068354 44.089169,3.068441 44.089026,3.068495 44.088831,3.068509 44.088639,3.068476 44.088516,3.068608 44.087924,3.068791 44.087608,3.06885 44.087338,3.069031 44.086932,3.069473 44.0863,3.069965 44.085802,3.070337 44.08562,3.070649 44.085618,3.072652 44.086188,3.072919 44.085398,3.073686 44.085445,3.074441 44.08551,3.077757 44.0852,3.078276 44.085275,3.07876 44.085372,3.0792 44.085315,3.079115 44.083447,3.079345 44.081953,3.07965 44.081456,3.080876 44.07983,3.081875 44.079958,3.083099 44.078106,3.085942 44.075209,3.086311 44.074847,3.087356 44.073671,3.089135 44.071492,3.089439 44.070905,3.08962 44.070499,3.089678 44.070139,3.08992 44.069553,3.090039 44.069103,3.090342 44.068516,3.090466 44.068336,3.091073 44.067163,3.091501 44.066575,3.09192 44.066145,3.092234 44.065906,3.093546 44.065091,3.093955 44.06479,3.094632 44.064216,3.095013 44.063931,3.095308 44.063784,3.095614 44.063689,3.095787 44.063262,3.096057 44.062884,3.09675 44.061984,3.09697 44.061671,3.097263 44.061176,3.097663 44.060403,3.097886 44.060008,3.098749 44.058359,3.098889 44.058048,3.101797 44.05602,3.102415 44.055656,3.103158 44.055336,3.104566 44.053393,3.105116 44.05258,3.105102 44.051501,3.104897 44.050189,3.104828 44.049695,3.104693 44.048887,3.104423 44.046506,3.103914 44.044721,3.103965 44.044531,3.103998 44.044162)))', 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.48 ms
(0.18%)

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)

1.24 ms
(0.15%)

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.84 ms
(0.10%)

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(((3.103998 44.044162,3.100957 44.044598,3.098404 44.044786,3.097906 44.044789,3.09703 44.044527,3.095091 44.044045,3.095087 44.043731,3.095207 44.04346,3.095505 44.042379,3.095864 44.041252,3.095983 44.040802,3.095976 44.040352,3.09535 44.040177,3.092665 44.039747,3.092663 44.039612,3.093151 44.038799,3.0937 44.037842,3.094005 44.03739,3.094375 44.037073,3.096357 44.036205,3.096039 44.035713,3.095472 44.035268,3.094342 44.034646,3.091827 44.03291,3.0912 44.032644,3.089949 44.032338,3.087949 44.031804,3.087134 44.031405,3.085381 44.030833,3.084446 44.030749,3.083822 44.030753,3.082878 44.03094,3.078592 44.032002,3.078647 44.033836,3.078704 44.034455,3.079406 44.038764,3.07899 44.038662,3.076457 44.038151,3.075549 44.038596,3.074306 44.039069,3.07259 44.041089,3.071915 44.041903,3.07118 44.042896,3.070996 44.043078,3.070813 44.043348,3.070444 44.043756,3.070319 44.043756,3.069507 44.043537,3.067237 44.042563,3.06443 44.04214,3.06321 44.04198,3.062473 44.042168,3.06101 44.042715,3.060847 44.042898,3.060575 44.043064,3.060183 44.043249,3.059749 44.043372,3.059536 44.043387,3.058782 44.04335,3.058216 44.043304,3.056477 44.043046,3.056171 44.043417,3.055205 44.044618,3.055205 44.04612,3.054707 44.046972,3.054329 44.047341,3.05398 44.047627,3.053848 44.047762,3.053877 44.047965,3.054125 44.048227,3.054227 44.048514,3.054252 44.048963,3.054242 44.049152,3.05412 44.049355,3.053959 44.049496,3.053855 44.049754,3.053893 44.049946,3.053563 44.050632,3.05004 44.049863,3.044086 44.048374,3.043578 44.048756,3.037957 44.051477,3.036346 44.052296,3.035428 44.05374,3.035062 44.054372,3.034107 44.056176,3.032144 44.058155,3.032223 44.058559,3.032842 44.059484,3.031542 44.059929,3.030262 44.060443,3.028968 44.061377,3.028366 44.06155,3.028017 44.061497,3.026988 44.061491,3.02674 44.061718,3.025674 44.061761,3.025343 44.061795,3.025049 44.061906,3.024708 44.061994,3.024088 44.062106,3.023747 44.062045,3.023092 44.062018,3.022709 44.062049,3.02254 44.062106,3.022324 44.062233,3.022122 44.062315,3.021468 44.062689,3.021346 44.062895,3.021178 44.063057,3.02073 44.063282,3.020506 44.06347,3.020148 44.064151,3.019949 44.064694,3.019912 44.065396,3.019872 44.065535,3.019927 44.065716,3.019874 44.066054,3.019896 44.066528,3.019845 44.066633,3.019933 44.066826,3.019922 44.067153,3.019819 44.067546,3.019907 44.067764,3.019506 44.068592,3.016594 44.071594,3.016541 44.071619,3.016367 44.071871,3.016133 44.071843,3.016029 44.071923,3.015798 44.072021,3.013366 44.073122,3.012986 44.073126,3.012848 44.073175,3.011851 44.073342,3.011479 44.073449,3.010135 44.073638,3.009371 44.073751,3.009642 44.074331,3.009769 44.074555,3.010639 44.074503,3.010847 44.076032,3.01092 44.076975,3.010872 44.078198,3.010917 44.078746,3.010405 44.079044,3.010645 44.079317,3.010699 44.079562,3.010325 44.080228,3.010564 44.080746,3.011223 44.081227,3.011503 44.081372,3.011475 44.082492,3.011739 44.082721,3.012055 44.083316,3.012409 44.084038,3.013635 44.083812,3.015417 44.083764,3.016062 44.083835,3.017206 44.08396,3.0192 44.084469,3.021916 44.08526,3.022957 44.085596,3.024484 44.086153,3.026148 44.086769,3.027328 44.087383,3.027861 44.087781,3.028895 44.089123,3.029775 44.090178,3.031084 44.091638,3.031638 44.092134,3.032565 44.092532,3.033632 44.092819,3.034198 44.093095,3.034619 44.093368,3.035568 44.093522,3.036096 44.093504,3.036651 44.093409,3.037073 44.093306,3.037371 44.09321,3.038235 44.092779,3.038471 44.092615,3.039229 44.091881,3.039588 44.091633,3.039915 44.091482,3.040588 44.091245,3.041225 44.090984,3.041774 44.0908,3.042199 44.090694,3.042716 44.09068,3.0433 44.090697,3.043986 44.090801,3.045082 44.091011,3.046002 44.091306,3.046811 44.091502,3.047505 44.091588,3.048029 44.091585,3.048908 44.091472,3.049186 44.091386,3.049504 44.091177,3.049733 44.090888,3.049857 44.090621,3.049897 44.090176,3.049788 44.089733,3.049635 44.089367,3.049501 44.088903,3.049524 44.088511,3.049723 44.088035,3.05008 44.087497,3.050523 44.086925,3.05098 44.086507,3.05154 44.086172,3.052134 44.085948,3.052913 44.085763,3.053545 44.085686,3.054086 44.085687,3.054743 44.085756,3.055265 44.085841,3.055902 44.085973,3.056785 44.08617,3.057666 44.086379,3.058536 44.086599,3.05946 44.086865,3.059902 44.087027,3.060355 44.087292,3.060486 44.087398,3.06083 44.087746,3.061581 44.088609,3.061952 44.088995,3.062326 44.089465,3.062469 44.089593,3.062662 44.089701,3.063245 44.089905,3.063987 44.090116,3.064649 44.09027,3.065086 44.090353,3.065879 44.090417,3.067434 44.090563,3.067723 44.090017,3.067912 44.089518,3.068354 44.089169,3.068441 44.089026,3.068495 44.088831,3.068509 44.088639,3.068476 44.088516,3.068608 44.087924,3.068791 44.087608,3.06885 44.087338,3.069031 44.086932,3.069473 44.0863,3.069965 44.085802,3.070337 44.08562,3.070649 44.085618,3.072652 44.086188,3.072919 44.085398,3.073686 44.085445,3.074441 44.08551,3.077757 44.0852,3.078276 44.085275,3.07876 44.085372,3.0792 44.085315,3.079115 44.083447,3.079345 44.081953,3.07965 44.081456,3.080876 44.07983,3.081875 44.079958,3.083099 44.078106,3.085942 44.075209,3.086311 44.074847,3.087356 44.073671,3.089135 44.071492,3.089439 44.070905,3.08962 44.070499,3.089678 44.070139,3.08992 44.069553,3.090039 44.069103,3.090342 44.068516,3.090466 44.068336,3.091073 44.067163,3.091501 44.066575,3.09192 44.066145,3.092234 44.065906,3.093546 44.065091,3.093955 44.06479,3.094632 44.064216,3.095013 44.063931,3.095308 44.063784,3.095614 44.063689,3.095787 44.063262,3.096057 44.062884,3.09675 44.061984,3.09697 44.061671,3.097263 44.061176,3.097663 44.060403,3.097886 44.060008,3.098749 44.058359,3.098889 44.058048,3.101797 44.05602,3.102415 44.055656,3.103158 44.055336,3.104566 44.053393,3.105116 44.05258,3.105102 44.051501,3.104897 44.050189,3.104828 44.049695,3.104693 44.048887,3.104423 44.046506,3.103914 44.044721,3.103965 44.044531,3.103998 44.044162)))"
]

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(((3.103998 44.044162,3.100957 44.044598,3.098404 44.044786,3.097906 44.044789,3.09703 44.044527,3.095091 44.044045,3.095087 44.043731,3.095207 44.04346,3.095505 44.042379,3.095864 44.041252,3.095983 44.040802,3.095976 44.040352,3.09535 44.040177,3.092665 44.039747,3.092663 44.039612,3.093151 44.038799,3.0937 44.037842,3.094005 44.03739,3.094375 44.037073,3.096357 44.036205,3.096039 44.035713,3.095472 44.035268,3.094342 44.034646,3.091827 44.03291,3.0912 44.032644,3.089949 44.032338,3.087949 44.031804,3.087134 44.031405,3.085381 44.030833,3.084446 44.030749,3.083822 44.030753,3.082878 44.03094,3.078592 44.032002,3.078647 44.033836,3.078704 44.034455,3.079406 44.038764,3.07899 44.038662,3.076457 44.038151,3.075549 44.038596,3.074306 44.039069,3.07259 44.041089,3.071915 44.041903,3.07118 44.042896,3.070996 44.043078,3.070813 44.043348,3.070444 44.043756,3.070319 44.043756,3.069507 44.043537,3.067237 44.042563,3.06443 44.04214,3.06321 44.04198,3.062473 44.042168,3.06101 44.042715,3.060847 44.042898,3.060575 44.043064,3.060183 44.043249,3.059749 44.043372,3.059536 44.043387,3.058782 44.04335,3.058216 44.043304,3.056477 44.043046,3.056171 44.043417,3.055205 44.044618,3.055205 44.04612,3.054707 44.046972,3.054329 44.047341,3.05398 44.047627,3.053848 44.047762,3.053877 44.047965,3.054125 44.048227,3.054227 44.048514,3.054252 44.048963,3.054242 44.049152,3.05412 44.049355,3.053959 44.049496,3.053855 44.049754,3.053893 44.049946,3.053563 44.050632,3.05004 44.049863,3.044086 44.048374,3.043578 44.048756,3.037957 44.051477,3.036346 44.052296,3.035428 44.05374,3.035062 44.054372,3.034107 44.056176,3.032144 44.058155,3.032223 44.058559,3.032842 44.059484,3.031542 44.059929,3.030262 44.060443,3.028968 44.061377,3.028366 44.06155,3.028017 44.061497,3.026988 44.061491,3.02674 44.061718,3.025674 44.061761,3.025343 44.061795,3.025049 44.061906,3.024708 44.061994,3.024088 44.062106,3.023747 44.062045,3.023092 44.062018,3.022709 44.062049,3.02254 44.062106,3.022324 44.062233,3.022122 44.062315,3.021468 44.062689,3.021346 44.062895,3.021178 44.063057,3.02073 44.063282,3.020506 44.06347,3.020148 44.064151,3.019949 44.064694,3.019912 44.065396,3.019872 44.065535,3.019927 44.065716,3.019874 44.066054,3.019896 44.066528,3.019845 44.066633,3.019933 44.066826,3.019922 44.067153,3.019819 44.067546,3.019907 44.067764,3.019506 44.068592,3.016594 44.071594,3.016541 44.071619,3.016367 44.071871,3.016133 44.071843,3.016029 44.071923,3.015798 44.072021,3.013366 44.073122,3.012986 44.073126,3.012848 44.073175,3.011851 44.073342,3.011479 44.073449,3.010135 44.073638,3.009371 44.073751,3.009642 44.074331,3.009769 44.074555,3.010639 44.074503,3.010847 44.076032,3.01092 44.076975,3.010872 44.078198,3.010917 44.078746,3.010405 44.079044,3.010645 44.079317,3.010699 44.079562,3.010325 44.080228,3.010564 44.080746,3.011223 44.081227,3.011503 44.081372,3.011475 44.082492,3.011739 44.082721,3.012055 44.083316,3.012409 44.084038,3.013635 44.083812,3.015417 44.083764,3.016062 44.083835,3.017206 44.08396,3.0192 44.084469,3.021916 44.08526,3.022957 44.085596,3.024484 44.086153,3.026148 44.086769,3.027328 44.087383,3.027861 44.087781,3.028895 44.089123,3.029775 44.090178,3.031084 44.091638,3.031638 44.092134,3.032565 44.092532,3.033632 44.092819,3.034198 44.093095,3.034619 44.093368,3.035568 44.093522,3.036096 44.093504,3.036651 44.093409,3.037073 44.093306,3.037371 44.09321,3.038235 44.092779,3.038471 44.092615,3.039229 44.091881,3.039588 44.091633,3.039915 44.091482,3.040588 44.091245,3.041225 44.090984,3.041774 44.0908,3.042199 44.090694,3.042716 44.09068,3.0433 44.090697,3.043986 44.090801,3.045082 44.091011,3.046002 44.091306,3.046811 44.091502,3.047505 44.091588,3.048029 44.091585,3.048908 44.091472,3.049186 44.091386,3.049504 44.091177,3.049733 44.090888,3.049857 44.090621,3.049897 44.090176,3.049788 44.089733,3.049635 44.089367,3.049501 44.088903,3.049524 44.088511,3.049723 44.088035,3.05008 44.087497,3.050523 44.086925,3.05098 44.086507,3.05154 44.086172,3.052134 44.085948,3.052913 44.085763,3.053545 44.085686,3.054086 44.085687,3.054743 44.085756,3.055265 44.085841,3.055902 44.085973,3.056785 44.08617,3.057666 44.086379,3.058536 44.086599,3.05946 44.086865,3.059902 44.087027,3.060355 44.087292,3.060486 44.087398,3.06083 44.087746,3.061581 44.088609,3.061952 44.088995,3.062326 44.089465,3.062469 44.089593,3.062662 44.089701,3.063245 44.089905,3.063987 44.090116,3.064649 44.09027,3.065086 44.090353,3.065879 44.090417,3.067434 44.090563,3.067723 44.090017,3.067912 44.089518,3.068354 44.089169,3.068441 44.089026,3.068495 44.088831,3.068509 44.088639,3.068476 44.088516,3.068608 44.087924,3.068791 44.087608,3.06885 44.087338,3.069031 44.086932,3.069473 44.0863,3.069965 44.085802,3.070337 44.08562,3.070649 44.085618,3.072652 44.086188,3.072919 44.085398,3.073686 44.085445,3.074441 44.08551,3.077757 44.0852,3.078276 44.085275,3.07876 44.085372,3.0792 44.085315,3.079115 44.083447,3.079345 44.081953,3.07965 44.081456,3.080876 44.07983,3.081875 44.079958,3.083099 44.078106,3.085942 44.075209,3.086311 44.074847,3.087356 44.073671,3.089135 44.071492,3.089439 44.070905,3.08962 44.070499,3.089678 44.070139,3.08992 44.069553,3.090039 44.069103,3.090342 44.068516,3.090466 44.068336,3.091073 44.067163,3.091501 44.066575,3.09192 44.066145,3.092234 44.065906,3.093546 44.065091,3.093955 44.06479,3.094632 44.064216,3.095013 44.063931,3.095308 44.063784,3.095614 44.063689,3.095787 44.063262,3.096057 44.062884,3.09675 44.061984,3.09697 44.061671,3.097263 44.061176,3.097663 44.060403,3.097886 44.060008,3.098749 44.058359,3.098889 44.058048,3.101797 44.05602,3.102415 44.055656,3.103158 44.055336,3.104566 44.053393,3.105116 44.05258,3.105102 44.051501,3.104897 44.050189,3.104828 44.049695,3.104693 44.048887,3.104423 44.046506,3.103914 44.044721,3.103965 44.044531,3.103998 44.044162)))', 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.76 ms
(0.09%)

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(((3.103998 44.044162,3.100957 44.044598,3.098404 44.044786,3.097906 44.044789,3.09703 44.044527,3.095091 44.044045,3.095087 44.043731,3.095207 44.04346,3.095505 44.042379,3.095864 44.041252,3.095983 44.040802,3.095976 44.040352,3.09535 44.040177,3.092665 44.039747,3.092663 44.039612,3.093151 44.038799,3.0937 44.037842,3.094005 44.03739,3.094375 44.037073,3.096357 44.036205,3.096039 44.035713,3.095472 44.035268,3.094342 44.034646,3.091827 44.03291,3.0912 44.032644,3.089949 44.032338,3.087949 44.031804,3.087134 44.031405,3.085381 44.030833,3.084446 44.030749,3.083822 44.030753,3.082878 44.03094,3.078592 44.032002,3.078647 44.033836,3.078704 44.034455,3.079406 44.038764,3.07899 44.038662,3.076457 44.038151,3.075549 44.038596,3.074306 44.039069,3.07259 44.041089,3.071915 44.041903,3.07118 44.042896,3.070996 44.043078,3.070813 44.043348,3.070444 44.043756,3.070319 44.043756,3.069507 44.043537,3.067237 44.042563,3.06443 44.04214,3.06321 44.04198,3.062473 44.042168,3.06101 44.042715,3.060847 44.042898,3.060575 44.043064,3.060183 44.043249,3.059749 44.043372,3.059536 44.043387,3.058782 44.04335,3.058216 44.043304,3.056477 44.043046,3.056171 44.043417,3.055205 44.044618,3.055205 44.04612,3.054707 44.046972,3.054329 44.047341,3.05398 44.047627,3.053848 44.047762,3.053877 44.047965,3.054125 44.048227,3.054227 44.048514,3.054252 44.048963,3.054242 44.049152,3.05412 44.049355,3.053959 44.049496,3.053855 44.049754,3.053893 44.049946,3.053563 44.050632,3.05004 44.049863,3.044086 44.048374,3.043578 44.048756,3.037957 44.051477,3.036346 44.052296,3.035428 44.05374,3.035062 44.054372,3.034107 44.056176,3.032144 44.058155,3.032223 44.058559,3.032842 44.059484,3.031542 44.059929,3.030262 44.060443,3.028968 44.061377,3.028366 44.06155,3.028017 44.061497,3.026988 44.061491,3.02674 44.061718,3.025674 44.061761,3.025343 44.061795,3.025049 44.061906,3.024708 44.061994,3.024088 44.062106,3.023747 44.062045,3.023092 44.062018,3.022709 44.062049,3.02254 44.062106,3.022324 44.062233,3.022122 44.062315,3.021468 44.062689,3.021346 44.062895,3.021178 44.063057,3.02073 44.063282,3.020506 44.06347,3.020148 44.064151,3.019949 44.064694,3.019912 44.065396,3.019872 44.065535,3.019927 44.065716,3.019874 44.066054,3.019896 44.066528,3.019845 44.066633,3.019933 44.066826,3.019922 44.067153,3.019819 44.067546,3.019907 44.067764,3.019506 44.068592,3.016594 44.071594,3.016541 44.071619,3.016367 44.071871,3.016133 44.071843,3.016029 44.071923,3.015798 44.072021,3.013366 44.073122,3.012986 44.073126,3.012848 44.073175,3.011851 44.073342,3.011479 44.073449,3.010135 44.073638,3.009371 44.073751,3.009642 44.074331,3.009769 44.074555,3.010639 44.074503,3.010847 44.076032,3.01092 44.076975,3.010872 44.078198,3.010917 44.078746,3.010405 44.079044,3.010645 44.079317,3.010699 44.079562,3.010325 44.080228,3.010564 44.080746,3.011223 44.081227,3.011503 44.081372,3.011475 44.082492,3.011739 44.082721,3.012055 44.083316,3.012409 44.084038,3.013635 44.083812,3.015417 44.083764,3.016062 44.083835,3.017206 44.08396,3.0192 44.084469,3.021916 44.08526,3.022957 44.085596,3.024484 44.086153,3.026148 44.086769,3.027328 44.087383,3.027861 44.087781,3.028895 44.089123,3.029775 44.090178,3.031084 44.091638,3.031638 44.092134,3.032565 44.092532,3.033632 44.092819,3.034198 44.093095,3.034619 44.093368,3.035568 44.093522,3.036096 44.093504,3.036651 44.093409,3.037073 44.093306,3.037371 44.09321,3.038235 44.092779,3.038471 44.092615,3.039229 44.091881,3.039588 44.091633,3.039915 44.091482,3.040588 44.091245,3.041225 44.090984,3.041774 44.0908,3.042199 44.090694,3.042716 44.09068,3.0433 44.090697,3.043986 44.090801,3.045082 44.091011,3.046002 44.091306,3.046811 44.091502,3.047505 44.091588,3.048029 44.091585,3.048908 44.091472,3.049186 44.091386,3.049504 44.091177,3.049733 44.090888,3.049857 44.090621,3.049897 44.090176,3.049788 44.089733,3.049635 44.089367,3.049501 44.088903,3.049524 44.088511,3.049723 44.088035,3.05008 44.087497,3.050523 44.086925,3.05098 44.086507,3.05154 44.086172,3.052134 44.085948,3.052913 44.085763,3.053545 44.085686,3.054086 44.085687,3.054743 44.085756,3.055265 44.085841,3.055902 44.085973,3.056785 44.08617,3.057666 44.086379,3.058536 44.086599,3.05946 44.086865,3.059902 44.087027,3.060355 44.087292,3.060486 44.087398,3.06083 44.087746,3.061581 44.088609,3.061952 44.088995,3.062326 44.089465,3.062469 44.089593,3.062662 44.089701,3.063245 44.089905,3.063987 44.090116,3.064649 44.09027,3.065086 44.090353,3.065879 44.090417,3.067434 44.090563,3.067723 44.090017,3.067912 44.089518,3.068354 44.089169,3.068441 44.089026,3.068495 44.088831,3.068509 44.088639,3.068476 44.088516,3.068608 44.087924,3.068791 44.087608,3.06885 44.087338,3.069031 44.086932,3.069473 44.0863,3.069965 44.085802,3.070337 44.08562,3.070649 44.085618,3.072652 44.086188,3.072919 44.085398,3.073686 44.085445,3.074441 44.08551,3.077757 44.0852,3.078276 44.085275,3.07876 44.085372,3.0792 44.085315,3.079115 44.083447,3.079345 44.081953,3.07965 44.081456,3.080876 44.07983,3.081875 44.079958,3.083099 44.078106,3.085942 44.075209,3.086311 44.074847,3.087356 44.073671,3.089135 44.071492,3.089439 44.070905,3.08962 44.070499,3.089678 44.070139,3.08992 44.069553,3.090039 44.069103,3.090342 44.068516,3.090466 44.068336,3.091073 44.067163,3.091501 44.066575,3.09192 44.066145,3.092234 44.065906,3.093546 44.065091,3.093955 44.06479,3.094632 44.064216,3.095013 44.063931,3.095308 44.063784,3.095614 44.063689,3.095787 44.063262,3.096057 44.062884,3.09675 44.061984,3.09697 44.061671,3.097263 44.061176,3.097663 44.060403,3.097886 44.060008,3.098749 44.058359,3.098889 44.058048,3.101797 44.05602,3.102415 44.055656,3.103158 44.055336,3.104566 44.053393,3.105116 44.05258,3.105102 44.051501,3.104897 44.050189,3.104828 44.049695,3.104693 44.048887,3.104423 44.046506,3.103914 44.044721,3.103965 44.044531,3.103998 44.044162)))"
]

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(((3.103998 44.044162,3.100957 44.044598,3.098404 44.044786,3.097906 44.044789,3.09703 44.044527,3.095091 44.044045,3.095087 44.043731,3.095207 44.04346,3.095505 44.042379,3.095864 44.041252,3.095983 44.040802,3.095976 44.040352,3.09535 44.040177,3.092665 44.039747,3.092663 44.039612,3.093151 44.038799,3.0937 44.037842,3.094005 44.03739,3.094375 44.037073,3.096357 44.036205,3.096039 44.035713,3.095472 44.035268,3.094342 44.034646,3.091827 44.03291,3.0912 44.032644,3.089949 44.032338,3.087949 44.031804,3.087134 44.031405,3.085381 44.030833,3.084446 44.030749,3.083822 44.030753,3.082878 44.03094,3.078592 44.032002,3.078647 44.033836,3.078704 44.034455,3.079406 44.038764,3.07899 44.038662,3.076457 44.038151,3.075549 44.038596,3.074306 44.039069,3.07259 44.041089,3.071915 44.041903,3.07118 44.042896,3.070996 44.043078,3.070813 44.043348,3.070444 44.043756,3.070319 44.043756,3.069507 44.043537,3.067237 44.042563,3.06443 44.04214,3.06321 44.04198,3.062473 44.042168,3.06101 44.042715,3.060847 44.042898,3.060575 44.043064,3.060183 44.043249,3.059749 44.043372,3.059536 44.043387,3.058782 44.04335,3.058216 44.043304,3.056477 44.043046,3.056171 44.043417,3.055205 44.044618,3.055205 44.04612,3.054707 44.046972,3.054329 44.047341,3.05398 44.047627,3.053848 44.047762,3.053877 44.047965,3.054125 44.048227,3.054227 44.048514,3.054252 44.048963,3.054242 44.049152,3.05412 44.049355,3.053959 44.049496,3.053855 44.049754,3.053893 44.049946,3.053563 44.050632,3.05004 44.049863,3.044086 44.048374,3.043578 44.048756,3.037957 44.051477,3.036346 44.052296,3.035428 44.05374,3.035062 44.054372,3.034107 44.056176,3.032144 44.058155,3.032223 44.058559,3.032842 44.059484,3.031542 44.059929,3.030262 44.060443,3.028968 44.061377,3.028366 44.06155,3.028017 44.061497,3.026988 44.061491,3.02674 44.061718,3.025674 44.061761,3.025343 44.061795,3.025049 44.061906,3.024708 44.061994,3.024088 44.062106,3.023747 44.062045,3.023092 44.062018,3.022709 44.062049,3.02254 44.062106,3.022324 44.062233,3.022122 44.062315,3.021468 44.062689,3.021346 44.062895,3.021178 44.063057,3.02073 44.063282,3.020506 44.06347,3.020148 44.064151,3.019949 44.064694,3.019912 44.065396,3.019872 44.065535,3.019927 44.065716,3.019874 44.066054,3.019896 44.066528,3.019845 44.066633,3.019933 44.066826,3.019922 44.067153,3.019819 44.067546,3.019907 44.067764,3.019506 44.068592,3.016594 44.071594,3.016541 44.071619,3.016367 44.071871,3.016133 44.071843,3.016029 44.071923,3.015798 44.072021,3.013366 44.073122,3.012986 44.073126,3.012848 44.073175,3.011851 44.073342,3.011479 44.073449,3.010135 44.073638,3.009371 44.073751,3.009642 44.074331,3.009769 44.074555,3.010639 44.074503,3.010847 44.076032,3.01092 44.076975,3.010872 44.078198,3.010917 44.078746,3.010405 44.079044,3.010645 44.079317,3.010699 44.079562,3.010325 44.080228,3.010564 44.080746,3.011223 44.081227,3.011503 44.081372,3.011475 44.082492,3.011739 44.082721,3.012055 44.083316,3.012409 44.084038,3.013635 44.083812,3.015417 44.083764,3.016062 44.083835,3.017206 44.08396,3.0192 44.084469,3.021916 44.08526,3.022957 44.085596,3.024484 44.086153,3.026148 44.086769,3.027328 44.087383,3.027861 44.087781,3.028895 44.089123,3.029775 44.090178,3.031084 44.091638,3.031638 44.092134,3.032565 44.092532,3.033632 44.092819,3.034198 44.093095,3.034619 44.093368,3.035568 44.093522,3.036096 44.093504,3.036651 44.093409,3.037073 44.093306,3.037371 44.09321,3.038235 44.092779,3.038471 44.092615,3.039229 44.091881,3.039588 44.091633,3.039915 44.091482,3.040588 44.091245,3.041225 44.090984,3.041774 44.0908,3.042199 44.090694,3.042716 44.09068,3.0433 44.090697,3.043986 44.090801,3.045082 44.091011,3.046002 44.091306,3.046811 44.091502,3.047505 44.091588,3.048029 44.091585,3.048908 44.091472,3.049186 44.091386,3.049504 44.091177,3.049733 44.090888,3.049857 44.090621,3.049897 44.090176,3.049788 44.089733,3.049635 44.089367,3.049501 44.088903,3.049524 44.088511,3.049723 44.088035,3.05008 44.087497,3.050523 44.086925,3.05098 44.086507,3.05154 44.086172,3.052134 44.085948,3.052913 44.085763,3.053545 44.085686,3.054086 44.085687,3.054743 44.085756,3.055265 44.085841,3.055902 44.085973,3.056785 44.08617,3.057666 44.086379,3.058536 44.086599,3.05946 44.086865,3.059902 44.087027,3.060355 44.087292,3.060486 44.087398,3.06083 44.087746,3.061581 44.088609,3.061952 44.088995,3.062326 44.089465,3.062469 44.089593,3.062662 44.089701,3.063245 44.089905,3.063987 44.090116,3.064649 44.09027,3.065086 44.090353,3.065879 44.090417,3.067434 44.090563,3.067723 44.090017,3.067912 44.089518,3.068354 44.089169,3.068441 44.089026,3.068495 44.088831,3.068509 44.088639,3.068476 44.088516,3.068608 44.087924,3.068791 44.087608,3.06885 44.087338,3.069031 44.086932,3.069473 44.0863,3.069965 44.085802,3.070337 44.08562,3.070649 44.085618,3.072652 44.086188,3.072919 44.085398,3.073686 44.085445,3.074441 44.08551,3.077757 44.0852,3.078276 44.085275,3.07876 44.085372,3.0792 44.085315,3.079115 44.083447,3.079345 44.081953,3.07965 44.081456,3.080876 44.07983,3.081875 44.079958,3.083099 44.078106,3.085942 44.075209,3.086311 44.074847,3.087356 44.073671,3.089135 44.071492,3.089439 44.070905,3.08962 44.070499,3.089678 44.070139,3.08992 44.069553,3.090039 44.069103,3.090342 44.068516,3.090466 44.068336,3.091073 44.067163,3.091501 44.066575,3.09192 44.066145,3.092234 44.065906,3.093546 44.065091,3.093955 44.06479,3.094632 44.064216,3.095013 44.063931,3.095308 44.063784,3.095614 44.063689,3.095787 44.063262,3.096057 44.062884,3.09675 44.061984,3.09697 44.061671,3.097263 44.061176,3.097663 44.060403,3.097886 44.060008,3.098749 44.058359,3.098889 44.058048,3.101797 44.05602,3.102415 44.055656,3.103158 44.055336,3.104566 44.053393,3.105116 44.05258,3.105102 44.051501,3.104897 44.050189,3.104828 44.049695,3.104693 44.048887,3.104423 44.046506,3.103914 44.044721,3.103965 44.044531,3.103998 44.044162)))', 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.74 ms
(0.09%)

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(((3.103998 44.044162,3.100957 44.044598,3.098404 44.044786,3.097906 44.044789,3.09703 44.044527,3.095091 44.044045,3.095087 44.043731,3.095207 44.04346,3.095505 44.042379,3.095864 44.041252,3.095983 44.040802,3.095976 44.040352,3.09535 44.040177,3.092665 44.039747,3.092663 44.039612,3.093151 44.038799,3.0937 44.037842,3.094005 44.03739,3.094375 44.037073,3.096357 44.036205,3.096039 44.035713,3.095472 44.035268,3.094342 44.034646,3.091827 44.03291,3.0912 44.032644,3.089949 44.032338,3.087949 44.031804,3.087134 44.031405,3.085381 44.030833,3.084446 44.030749,3.083822 44.030753,3.082878 44.03094,3.078592 44.032002,3.078647 44.033836,3.078704 44.034455,3.079406 44.038764,3.07899 44.038662,3.076457 44.038151,3.075549 44.038596,3.074306 44.039069,3.07259 44.041089,3.071915 44.041903,3.07118 44.042896,3.070996 44.043078,3.070813 44.043348,3.070444 44.043756,3.070319 44.043756,3.069507 44.043537,3.067237 44.042563,3.06443 44.04214,3.06321 44.04198,3.062473 44.042168,3.06101 44.042715,3.060847 44.042898,3.060575 44.043064,3.060183 44.043249,3.059749 44.043372,3.059536 44.043387,3.058782 44.04335,3.058216 44.043304,3.056477 44.043046,3.056171 44.043417,3.055205 44.044618,3.055205 44.04612,3.054707 44.046972,3.054329 44.047341,3.05398 44.047627,3.053848 44.047762,3.053877 44.047965,3.054125 44.048227,3.054227 44.048514,3.054252 44.048963,3.054242 44.049152,3.05412 44.049355,3.053959 44.049496,3.053855 44.049754,3.053893 44.049946,3.053563 44.050632,3.05004 44.049863,3.044086 44.048374,3.043578 44.048756,3.037957 44.051477,3.036346 44.052296,3.035428 44.05374,3.035062 44.054372,3.034107 44.056176,3.032144 44.058155,3.032223 44.058559,3.032842 44.059484,3.031542 44.059929,3.030262 44.060443,3.028968 44.061377,3.028366 44.06155,3.028017 44.061497,3.026988 44.061491,3.02674 44.061718,3.025674 44.061761,3.025343 44.061795,3.025049 44.061906,3.024708 44.061994,3.024088 44.062106,3.023747 44.062045,3.023092 44.062018,3.022709 44.062049,3.02254 44.062106,3.022324 44.062233,3.022122 44.062315,3.021468 44.062689,3.021346 44.062895,3.021178 44.063057,3.02073 44.063282,3.020506 44.06347,3.020148 44.064151,3.019949 44.064694,3.019912 44.065396,3.019872 44.065535,3.019927 44.065716,3.019874 44.066054,3.019896 44.066528,3.019845 44.066633,3.019933 44.066826,3.019922 44.067153,3.019819 44.067546,3.019907 44.067764,3.019506 44.068592,3.016594 44.071594,3.016541 44.071619,3.016367 44.071871,3.016133 44.071843,3.016029 44.071923,3.015798 44.072021,3.013366 44.073122,3.012986 44.073126,3.012848 44.073175,3.011851 44.073342,3.011479 44.073449,3.010135 44.073638,3.009371 44.073751,3.009642 44.074331,3.009769 44.074555,3.010639 44.074503,3.010847 44.076032,3.01092 44.076975,3.010872 44.078198,3.010917 44.078746,3.010405 44.079044,3.010645 44.079317,3.010699 44.079562,3.010325 44.080228,3.010564 44.080746,3.011223 44.081227,3.011503 44.081372,3.011475 44.082492,3.011739 44.082721,3.012055 44.083316,3.012409 44.084038,3.013635 44.083812,3.015417 44.083764,3.016062 44.083835,3.017206 44.08396,3.0192 44.084469,3.021916 44.08526,3.022957 44.085596,3.024484 44.086153,3.026148 44.086769,3.027328 44.087383,3.027861 44.087781,3.028895 44.089123,3.029775 44.090178,3.031084 44.091638,3.031638 44.092134,3.032565 44.092532,3.033632 44.092819,3.034198 44.093095,3.034619 44.093368,3.035568 44.093522,3.036096 44.093504,3.036651 44.093409,3.037073 44.093306,3.037371 44.09321,3.038235 44.092779,3.038471 44.092615,3.039229 44.091881,3.039588 44.091633,3.039915 44.091482,3.040588 44.091245,3.041225 44.090984,3.041774 44.0908,3.042199 44.090694,3.042716 44.09068,3.0433 44.090697,3.043986 44.090801,3.045082 44.091011,3.046002 44.091306,3.046811 44.091502,3.047505 44.091588,3.048029 44.091585,3.048908 44.091472,3.049186 44.091386,3.049504 44.091177,3.049733 44.090888,3.049857 44.090621,3.049897 44.090176,3.049788 44.089733,3.049635 44.089367,3.049501 44.088903,3.049524 44.088511,3.049723 44.088035,3.05008 44.087497,3.050523 44.086925,3.05098 44.086507,3.05154 44.086172,3.052134 44.085948,3.052913 44.085763,3.053545 44.085686,3.054086 44.085687,3.054743 44.085756,3.055265 44.085841,3.055902 44.085973,3.056785 44.08617,3.057666 44.086379,3.058536 44.086599,3.05946 44.086865,3.059902 44.087027,3.060355 44.087292,3.060486 44.087398,3.06083 44.087746,3.061581 44.088609,3.061952 44.088995,3.062326 44.089465,3.062469 44.089593,3.062662 44.089701,3.063245 44.089905,3.063987 44.090116,3.064649 44.09027,3.065086 44.090353,3.065879 44.090417,3.067434 44.090563,3.067723 44.090017,3.067912 44.089518,3.068354 44.089169,3.068441 44.089026,3.068495 44.088831,3.068509 44.088639,3.068476 44.088516,3.068608 44.087924,3.068791 44.087608,3.06885 44.087338,3.069031 44.086932,3.069473 44.0863,3.069965 44.085802,3.070337 44.08562,3.070649 44.085618,3.072652 44.086188,3.072919 44.085398,3.073686 44.085445,3.074441 44.08551,3.077757 44.0852,3.078276 44.085275,3.07876 44.085372,3.0792 44.085315,3.079115 44.083447,3.079345 44.081953,3.07965 44.081456,3.080876 44.07983,3.081875 44.079958,3.083099 44.078106,3.085942 44.075209,3.086311 44.074847,3.087356 44.073671,3.089135 44.071492,3.089439 44.070905,3.08962 44.070499,3.089678 44.070139,3.08992 44.069553,3.090039 44.069103,3.090342 44.068516,3.090466 44.068336,3.091073 44.067163,3.091501 44.066575,3.09192 44.066145,3.092234 44.065906,3.093546 44.065091,3.093955 44.06479,3.094632 44.064216,3.095013 44.063931,3.095308 44.063784,3.095614 44.063689,3.095787 44.063262,3.096057 44.062884,3.09675 44.061984,3.09697 44.061671,3.097263 44.061176,3.097663 44.060403,3.097886 44.060008,3.098749 44.058359,3.098889 44.058048,3.101797 44.05602,3.102415 44.055656,3.103158 44.055336,3.104566 44.053393,3.105116 44.05258,3.105102 44.051501,3.104897 44.050189,3.104828 44.049695,3.104693 44.048887,3.104423 44.046506,3.103914 44.044721,3.103965 44.044531,3.103998 44.044162)))"
]

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(((3.103998 44.044162,3.100957 44.044598,3.098404 44.044786,3.097906 44.044789,3.09703 44.044527,3.095091 44.044045,3.095087 44.043731,3.095207 44.04346,3.095505 44.042379,3.095864 44.041252,3.095983 44.040802,3.095976 44.040352,3.09535 44.040177,3.092665 44.039747,3.092663 44.039612,3.093151 44.038799,3.0937 44.037842,3.094005 44.03739,3.094375 44.037073,3.096357 44.036205,3.096039 44.035713,3.095472 44.035268,3.094342 44.034646,3.091827 44.03291,3.0912 44.032644,3.089949 44.032338,3.087949 44.031804,3.087134 44.031405,3.085381 44.030833,3.084446 44.030749,3.083822 44.030753,3.082878 44.03094,3.078592 44.032002,3.078647 44.033836,3.078704 44.034455,3.079406 44.038764,3.07899 44.038662,3.076457 44.038151,3.075549 44.038596,3.074306 44.039069,3.07259 44.041089,3.071915 44.041903,3.07118 44.042896,3.070996 44.043078,3.070813 44.043348,3.070444 44.043756,3.070319 44.043756,3.069507 44.043537,3.067237 44.042563,3.06443 44.04214,3.06321 44.04198,3.062473 44.042168,3.06101 44.042715,3.060847 44.042898,3.060575 44.043064,3.060183 44.043249,3.059749 44.043372,3.059536 44.043387,3.058782 44.04335,3.058216 44.043304,3.056477 44.043046,3.056171 44.043417,3.055205 44.044618,3.055205 44.04612,3.054707 44.046972,3.054329 44.047341,3.05398 44.047627,3.053848 44.047762,3.053877 44.047965,3.054125 44.048227,3.054227 44.048514,3.054252 44.048963,3.054242 44.049152,3.05412 44.049355,3.053959 44.049496,3.053855 44.049754,3.053893 44.049946,3.053563 44.050632,3.05004 44.049863,3.044086 44.048374,3.043578 44.048756,3.037957 44.051477,3.036346 44.052296,3.035428 44.05374,3.035062 44.054372,3.034107 44.056176,3.032144 44.058155,3.032223 44.058559,3.032842 44.059484,3.031542 44.059929,3.030262 44.060443,3.028968 44.061377,3.028366 44.06155,3.028017 44.061497,3.026988 44.061491,3.02674 44.061718,3.025674 44.061761,3.025343 44.061795,3.025049 44.061906,3.024708 44.061994,3.024088 44.062106,3.023747 44.062045,3.023092 44.062018,3.022709 44.062049,3.02254 44.062106,3.022324 44.062233,3.022122 44.062315,3.021468 44.062689,3.021346 44.062895,3.021178 44.063057,3.02073 44.063282,3.020506 44.06347,3.020148 44.064151,3.019949 44.064694,3.019912 44.065396,3.019872 44.065535,3.019927 44.065716,3.019874 44.066054,3.019896 44.066528,3.019845 44.066633,3.019933 44.066826,3.019922 44.067153,3.019819 44.067546,3.019907 44.067764,3.019506 44.068592,3.016594 44.071594,3.016541 44.071619,3.016367 44.071871,3.016133 44.071843,3.016029 44.071923,3.015798 44.072021,3.013366 44.073122,3.012986 44.073126,3.012848 44.073175,3.011851 44.073342,3.011479 44.073449,3.010135 44.073638,3.009371 44.073751,3.009642 44.074331,3.009769 44.074555,3.010639 44.074503,3.010847 44.076032,3.01092 44.076975,3.010872 44.078198,3.010917 44.078746,3.010405 44.079044,3.010645 44.079317,3.010699 44.079562,3.010325 44.080228,3.010564 44.080746,3.011223 44.081227,3.011503 44.081372,3.011475 44.082492,3.011739 44.082721,3.012055 44.083316,3.012409 44.084038,3.013635 44.083812,3.015417 44.083764,3.016062 44.083835,3.017206 44.08396,3.0192 44.084469,3.021916 44.08526,3.022957 44.085596,3.024484 44.086153,3.026148 44.086769,3.027328 44.087383,3.027861 44.087781,3.028895 44.089123,3.029775 44.090178,3.031084 44.091638,3.031638 44.092134,3.032565 44.092532,3.033632 44.092819,3.034198 44.093095,3.034619 44.093368,3.035568 44.093522,3.036096 44.093504,3.036651 44.093409,3.037073 44.093306,3.037371 44.09321,3.038235 44.092779,3.038471 44.092615,3.039229 44.091881,3.039588 44.091633,3.039915 44.091482,3.040588 44.091245,3.041225 44.090984,3.041774 44.0908,3.042199 44.090694,3.042716 44.09068,3.0433 44.090697,3.043986 44.090801,3.045082 44.091011,3.046002 44.091306,3.046811 44.091502,3.047505 44.091588,3.048029 44.091585,3.048908 44.091472,3.049186 44.091386,3.049504 44.091177,3.049733 44.090888,3.049857 44.090621,3.049897 44.090176,3.049788 44.089733,3.049635 44.089367,3.049501 44.088903,3.049524 44.088511,3.049723 44.088035,3.05008 44.087497,3.050523 44.086925,3.05098 44.086507,3.05154 44.086172,3.052134 44.085948,3.052913 44.085763,3.053545 44.085686,3.054086 44.085687,3.054743 44.085756,3.055265 44.085841,3.055902 44.085973,3.056785 44.08617,3.057666 44.086379,3.058536 44.086599,3.05946 44.086865,3.059902 44.087027,3.060355 44.087292,3.060486 44.087398,3.06083 44.087746,3.061581 44.088609,3.061952 44.088995,3.062326 44.089465,3.062469 44.089593,3.062662 44.089701,3.063245 44.089905,3.063987 44.090116,3.064649 44.09027,3.065086 44.090353,3.065879 44.090417,3.067434 44.090563,3.067723 44.090017,3.067912 44.089518,3.068354 44.089169,3.068441 44.089026,3.068495 44.088831,3.068509 44.088639,3.068476 44.088516,3.068608 44.087924,3.068791 44.087608,3.06885 44.087338,3.069031 44.086932,3.069473 44.0863,3.069965 44.085802,3.070337 44.08562,3.070649 44.085618,3.072652 44.086188,3.072919 44.085398,3.073686 44.085445,3.074441 44.08551,3.077757 44.0852,3.078276 44.085275,3.07876 44.085372,3.0792 44.085315,3.079115 44.083447,3.079345 44.081953,3.07965 44.081456,3.080876 44.07983,3.081875 44.079958,3.083099 44.078106,3.085942 44.075209,3.086311 44.074847,3.087356 44.073671,3.089135 44.071492,3.089439 44.070905,3.08962 44.070499,3.089678 44.070139,3.08992 44.069553,3.090039 44.069103,3.090342 44.068516,3.090466 44.068336,3.091073 44.067163,3.091501 44.066575,3.09192 44.066145,3.092234 44.065906,3.093546 44.065091,3.093955 44.06479,3.094632 44.064216,3.095013 44.063931,3.095308 44.063784,3.095614 44.063689,3.095787 44.063262,3.096057 44.062884,3.09675 44.061984,3.09697 44.061671,3.097263 44.061176,3.097663 44.060403,3.097886 44.060008,3.098749 44.058359,3.098889 44.058048,3.101797 44.05602,3.102415 44.055656,3.103158 44.055336,3.104566 44.053393,3.105116 44.05258,3.105102 44.051501,3.104897 44.050189,3.104828 44.049695,3.104693 44.048887,3.104423 44.046506,3.103914 44.044721,3.103965 44.044531,3.103998 44.044162)))', 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.44 ms
(0.05%)

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:

[
  "12084"
]

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'12084'') = ?
      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.43 ms
(0.05%)

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

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/12084

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