Advertisement
Guest User

Untitled

a guest
Jul 19th, 2019
115
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. select * from (
  2.     select
  3.         st_setsrid(st_makepoint(ee.startLng, ee.starLat), 4326) as geomstart,
  4.         st_setsrid(st_makepoint(ee.endLng, ee.endLat), 4326) as geomend
  5.     from evt_situations ee
  6. ) e
  7. inner join (
  8.     select
  9.         (st_dump(st_linemerge(st_locatebetween(geom,6.1199,59.5117)))).geom
  10.         , route
  11.         from  (
  12.             select
  13.                 st_makeline(
  14.                     st_setsrid(
  15.                         st_makepointm(rp.lng,rp.lat,rp.m)
  16.                         ,4326
  17.                     )
  18.                     order by rp.m
  19.                 ) as geom
  20.                 , max(lr.route) as route
  21.                 , max(lr.id) as routeid
  22.             from rpoints rp
  23.             inner join routes lr
  24.             on lr.id = rp.route_id
  25.             where lr.id = 9
  26.         ) m
  27. ) l
  28. on st_dwithin(e.geomstart,l.geom, 0.00001) or ST_DWITHIN(e.geomend, l.geom, 0.00001)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement