Skip to content

Instantly share code, notes, and snippets.

@nazt

nazt/postgist_st_distance.sql

Created February 16, 2010 10:12
Show Gist options
  • Save nazt/305430 to your computer and use it in GitHub Desktop.
Save nazt/305430 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
postgist_st_distance.sql
select t_snapbkk(setsrid(st_makepoint(100.583267,13.800741),4326)) ;
select st_makepoint(100.583267,13.800741);
select link_id, (ST_Distance(ST_Transform(setsrid(st_makepoint(100.53816,13.76439),4326),32647),ST_Transform(setsrid(line_geom,4326),32647))) AS distance from bkktraffic_linkdef
select vms.link_id, vms_id,(ST_Distance(ST_Transform(setsrid(st_makepoint(100.53816,13.76439),4326),32647),ST_Transform(setsrid(line_geom,4326),32647))) AS distance from bkktraffic_linkdef as bkk , bkk_traffic_vms2way as vms where vms.link_id=bkk.link_id order by distance
select *,(ST_Distance(ST_Transform(setsrid(st_makepoint(100.53816,13.76439),4326),32647),ST_Transform(setsrid(line_geom,4326),32647))) AS distance from linkdef_vms_data order by distance limit 50
select vms.bkk_link_id, vms_id,(ST_Distance(ST_Transform(setsrid(st_makepoint(100.53816,13.76439),4326),32647),ST_Transform(setsrid(line_geom,4326),32647))) AS distance from bkktraffic_linkdef as bkk , vms_2way_info as vms where vms.bkk_link_id=bkk.link_id order by distance
distinct
select distinct (vms.bkk_link_id), vms_id, (ST_Distance(ST_Transform(setsrid(st_makepoint(100.53816,13.76439),4326),32647),ST_Transform(setsrid(line_geom,4326),32647))) AS distance from bkktraffic_linkdef as bkk JOIN vms_2way_info as vms ON vms.bkk_link_id=bkk.link_id order by distance
#http://postgis.refractions.net/docs/ST_Distance.html
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment