Advertisement
Guest User

Untitled

a guest
Aug 20th, 2014
227
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.38 KB | None | 0 0
  1. WITH
  2. Data as
  3. ( SELECT gid,geom FROM schema1.data AS Mp
  4. WHERE Mp.id=14
  5. AND EXISTS (
  6. SELECT 1 FROM schema1.base As Base
  7. WHERE Base."ID"=14
  8. AND st_covers(st_makeenvelope(-180, -90, 180, 90, 4326), Mp.geom)
  9. AND ST_DWithin(Mp.geom::geography, Base.geom::geography, 5000)
  10. )
  11. ),
  12. Line As
  13. ( SELECT * FROM public.planet_osm_line As Line
  14. WHERE EXISTS (
  15. SELECT 1 FROM schema1.base As Base
  16. WHERE Base."ID"=14
  17. AND ST_DWithin(Line.way::geography, Base.geom::geography, 5000)
  18. AND Line.highway='motorway'
  19. )
  20. )
  21. SELECT
  22. Data.*
  23. FROM
  24. Data, Line
  25. WHERE
  26. st_covers(st_makeenvelope(-180, -90, 180, 90, 4326), Data.geom)
  27. AND ST_DWithin(Data.geom::geography, Line.way::geography, 20);
  28.  
  29. SELECT
  30. osm_id,
  31. name,
  32. ST_XMin(line.way),
  33. ST_XMax(line.way),
  34. ST_Distance(line.way, st_transform(
  35. st_setsrid(st_makepoint(36.768676, -1.289927), 4326),
  36. 900913)) as distance
  37. FROM
  38. line
  39. WHERE
  40. ST_DWithin(line.way,
  41. st_transform(
  42. st_setsrid(st_makepoint(36.768676, -1.289927), 4326),
  43. 900913),
  44. 5);
  45.  
  46.  
  47. -[ RECORD 1 ]---------------
  48. osm_id | 25060263
  49. name | Gitanga Road
  50. st_xmin | 4093024.51
  51. st_xmax | 4093669.13
  52. distance | 0.993252319680161
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement