Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- WITH
- Data as
- ( SELECT gid,geom FROM schema1.data AS Mp
- WHERE Mp.id=14
- AND EXISTS (
- SELECT 1 FROM schema1.base As Base
- WHERE Base."ID"=14
- AND st_covers(st_makeenvelope(-180, -90, 180, 90, 4326), Mp.geom)
- AND ST_DWithin(Mp.geom::geography, Base.geom::geography, 5000)
- )
- ),
- Line As
- ( SELECT * FROM public.planet_osm_line As Line
- WHERE EXISTS (
- SELECT 1 FROM schema1.base As Base
- WHERE Base."ID"=14
- AND ST_DWithin(Line.way::geography, Base.geom::geography, 5000)
- AND Line.highway='motorway'
- )
- )
- SELECT
- Data.*
- FROM
- Data, Line
- WHERE
- st_covers(st_makeenvelope(-180, -90, 180, 90, 4326), Data.geom)
- AND ST_DWithin(Data.geom::geography, Line.way::geography, 20);
- SELECT
- osm_id,
- name,
- ST_XMin(line.way),
- ST_XMax(line.way),
- ST_Distance(line.way, st_transform(
- st_setsrid(st_makepoint(36.768676, -1.289927), 4326),
- 900913)) as distance
- FROM
- line
- WHERE
- ST_DWithin(line.way,
- st_transform(
- st_setsrid(st_makepoint(36.768676, -1.289927), 4326),
- 900913),
- 5);
- -[ RECORD 1 ]---------------
- osm_id | 25060263
- name | Gitanga Road
- st_xmin | 4093024.51
- st_xmax | 4093669.13
- distance | 0.993252319680161
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement