osm_postgis2.sql

SELECT name, osm_id,
   ST_Distance_Sphere(ST_Transform(way::geometry, 4326),ST_SetSRID(ST_MakePoint(47.217507, 56.113372), 4326)) as dist
FROM planet_osm_point WHERE ST_Distance_Sphere(ST_Transform(way::geometry, 4326), ST_SetSRID(ST_MakePoint(47.217507, 56.113372), 4326)) < 1500 limit 10;

SELECT points.name, points.dist, nodes.tags FROM 
(SELECT name, osm_id, dist FROM
(SELECT name, osm_id, ST_Distance_Sphere(ST_Transform(way::geometry, 4326),
 ST_SetSRID(ST_MakePoint(47.24982, 56.131380000000007), 4326)) AS dist
FROM planet_osm_point 
WHERE ST_Within(ST_Transform(way::geometry, 4326),
 ST_MakeEnvelope(47.217506827000101, 56.113372177580452, 47.282133172999899, 56.149387822419577, 4326)) = true
) AS points_dist
WHERE dist < 1000 
) AS points
LEFT JOIN 
(SELECT id, tags FROM planet_osm_nodes) AS nodes
ON points.osm_id = nodes.id;

SELECT points.name, nodes.tags FROM 
(SELECT name, osm_id FROM
(SELECT name, osm_id 
FROM planet_osm_point 
WHERE ST_Within(ST_Transform(way::geometry, 4326), ST_MakeEnvelope(47.217506827000101, 56.113372177580452, 47.282133172999899, 56.149387822419577, 4326)) = true
) AS points_dist
) AS points
LEFT JOIN 
(SELECT id, tags FROM planet_osm_nodes) AS nodes
ON points.osm_id = nodes.id;
--limit 100;


SELECT points.name, points.dist, nodes.tags FROM 
    (SELECT name, osm_id, ST_Distance_Sphere(ST_Transform(way::geometry, 4326), ST_SetSRID(ST_MakePoint(47.24982, 56.131380000000007), 4326)) AS dist 
     FROM planet_osm_point 
     WHERE
        dist < 1500
        AND ST_XMin(ST_Transform(way::geometry, 4326)) >= 47.217506827000101 -- lng min
        AND ST_XMax(ST_Transform(way::geometry, 4326)) <= 47.282133172999899 -- lng max
        AND ST_YMin(ST_Transform(way::geometry, 4326)) >= 56.113372177580452 -- lat min
        AND ST_YMin(ST_Transform(way::geometry, 4326)) <= 56.149387822419577 -- lat max
    )AS points
LEFT JOIN 
(SELECT id, tags 
 FROM planet_osm_nodes) AS nodes
ON points.osm_id = nodes.id
ORDER BY dist


with points as (SELECT name,
            osm_id,
            dist
     FROM
         (SELECT name,
                 osm_id,
                 ST_Distance_Sphere(ST_Transform(way::geometry, 4326), ST_SetSRID(ST_MakePoint(47.24982, 56.131380000000007), 4326)) AS dist
          FROM planet_osm_point
          WHERE ST_XMin(ST_Transform(way::geometry, 4326)) >= 47.217506827000101 -- lng min
AND ST_XMax(ST_Transform(way::geometry, 4326)) <= 47.282133172999899 -- lng max
AND ST_YMin(ST_Transform(way::geometry, 4326)) >= 56.113372177580452 -- lat min
AND ST_YMin(ST_Transform(way::geometry, 4326)) <= 56.149387822419577 -- lat max
) AS points_dist
     WHERE dist < 1500 ),
nodes as (SELECT id,
            tags
     FROM planet_osm_nodes)
SELECT points.name,
       points.dist,
       nodes.tags
FROM points
LEFT JOIN
    nodes ON points.osm_id = nodes.id
LIMIT 100;


select planet_osm_point.osm_id, planet_osm_nodes.id, tags
from planet_osm_point
join planet_osm_nodes 
on planet_osm_point.osm_id = planet_osm_nodes.id
order by way <-> ST_Transform('SRID=4326;POINT(48.330585499999998 41.988205000000001)'::geometry, 3857) 
limit 5;


select tags, ST_AsGeoJSON(st_transform(way, 4326))
from planet_osm_point
join planet_osm_nodes
on planet_osm_point.osm_id = planet_osm_nodes.id
where 
order by way <-> ST_Transform('SRID=4326;POINT(48.330585499999998 41.988205000000001)'::geometry, 3857)
limit 5;