osm_ca_20=# SELECT osm_id, geometry < -> st_geomfromEWKT( 'SRID=900913;POINT(-13145550 4045137)' ) as the_distance FROM osm_new_mainroads ORDER BY geometry < -> st_geomfromEWKT( 'SRID=900913;POINT(-13145550 4045137)' ) limit 1; osm_id | the_distance ----------+------------------ 59339590 | 268.205611425265 (1 row)
UPDATE
distance of all museums less than 30km away from a school, by school. Thanks to the geonames project for the locations.
select
distinct on (s.name, m.name)
s.name as school_name, s.admin2,
m.name as museum_name, m.admin2,
st_distance( s.geom::geography, m.geom::geography )::integer as dist,
rank() over ( partition by (s.name, s.admin2)
order by st_distance( s.geom::geography, m.geom::geography )) as rank
from museum m, school s
where
s.admin2 = 'Alameda County' AND
m.admin1 = 'California' AND
st_dwithin( m.geom::geography, s.geom::geography, 30000 )
ORDER BY s.name, m.name, dist;