belarus-pharmacy.sql

SELECT DISTINCT n.type, n.osm_id,
       n.tags->'name', n.tags->'name:ru', n.tags->'name:be',
       n.tags->'description', n.tags->'description:ru', n.tags->'description:be',
       n.tags->'operator', n.tags->'operator:ru', n.tags->'operator:be',
       n.tags->'addr:street', n.tags->'addr:housenumber',
       n.tags->'addr:street' = a.street AND n.tags->'addr:housenumber' = a.housenumber,
       ct.country, ct.region, ct.subregion, ct.city, a.street, a.housenumber
FROM (
    SELECT CASE  WHEN o.osm_id > 0 THEN 'w' ELSE 'r' END AS type, o.osm_id, o.tags, o.way
    FROM osm_polygon c
    LEFT JOIN osm_polygon o ON ST_Contains(c.way, o.way)
    WHERE c.osm_id = -59065
    AND o.tags->'amenity' = 'pharmacy'

    UNION

    SELECT 'w' AS type, o.osm_id, o.tags, o.way
    FROM osm_polygon c
    LEFT JOIN osm_line o ON ST_Contains(c.way, o.way)
    WHERE c.osm_id = -59065
    AND o.tags->'amenity' = 'pharmacy'

    UNION

    SELECT 'n' AS type, o.osm_id, o.tags, o.way
    FROM osm_polygon c
    LEFT JOIN osm_point o ON ST_Contains(c.way, o.way)
    WHERE c.osm_id = -59065
    AND o.tags->'amenity' = 'pharmacy'
) n
LEFT JOIN (

    SELECT ct.osm_id,
           c.tags->'name' AS country, '' AS region, '' AS subregion, ct.tags->'name' AS city,
           ct.way
    FROM osm_polygon c
    LEFT JOIN osm_polygon ct ON ST_Contains(c.way, ct.way)
    WHERE c.osm_id = -59065
    AND ct.admin_level = '4'
    AND ct.tags->'place' IN ('city', 'town', 'village', 'hamlet', 'isolated_dwelling', 'farm', 'allotments')

    UNION

    SELECT ct.osm_id,
           c.tags->'name' AS country, r.tags->'name' AS region, '' AS subregion, ct.tags->'name' AS city,
           ct.way
    FROM osm_polygon c
    LEFT JOIN osm_polygon r ON ST_Contains(c.way, r.way)
    LEFT JOIN osm_polygon ct ON ST_Contains(r.way, ct.way)
    WHERE c.osm_id = -59065
    AND r.admin_level = '4' AND ct.admin_level = '6'
    AND ct.tags->'place' IN ('city', 'town', 'village', 'hamlet', 'isolated_dwelling', 'farm', 'allotments')

    UNION

    SELECT ct.osm_id,
           c.tags->'name' AS country, r.tags->'name' AS region, s.tags->'name' AS subregion, ct.tags->'name' AS city,
           ct.way
    FROM osm_polygon c
    LEFT JOIN osm_polygon r ON ST_Contains(c.way, r.way)
    LEFT JOIN osm_polygon s ON ST_Contains(r.way, s.way)
    LEFT JOIN osm_polygon ct ON ST_Contains(s.way, ct.way)
    WHERE c.osm_id = -59065
    AND r.admin_level = '4' AND s.admin_level = '6'
    AND (ct.admin_level IS NULL OR ct.admin_level NOT IN ('4', '6'))
    AND ct.tags->'place' IN ('city', 'town', 'village', 'hamlet', 'isolated_dwelling', 'farm', 'allotments')
) ct
ON ST_Intersects(ct.way, n.way)
LEFT JOIN (
    SELECT a.osm_id,
           a.tags->'addr:street' AS street, a.tags->'addr:housenumber' AS housenumber,
           a.way
    FROM osm_polygon c
    LEFT JOIN osm_polygon a ON ST_Contains(c.way, a.way)
    WHERE c.osm_id = -59065
    AND a.tags ?& ARRAY['addr:street', 'addr:housenumber']

    UNION

    SELECT a.osm_id,
           a.tags->'addr:street' AS street, a.tags->'addr:housenumber' AS housenumber,
           a.way
    FROM osm_polygon c
    LEFT JOIN osm_point a ON ST_Contains(c.way, a.way)
    WHERE c.osm_id = -59065
    AND a.tags ?& ARRAY['addr:street', 'addr:housenumber']
) a
ON ST_Intersects(a.way, n.way)
ORDER BY ct.country, ct.region, ct.subregion, ct.city, a.street, a.housenumber,
         n.type, n.tags->'operator', n.tags->'operator:ru', n.tags->'operator:be',
         n.tags->'name', n.tags->'name:ru', n.tags->'name:be', n.osm_id