1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
| WITH TT (coo_trk_id, coo_time, coo_length, distance, R) AS (
SELECT coo_trk_id, coo_time, coo_length, (ST_Distance(
geography(st_transform(coo_2d,4326)),
ST_GeographyFromText('POINT(3.075206 50.369314)'))), ROW_NUMBER() OVER(PARTITION BY coo_trk_id ORDER BY (ST_Distance(
geography(st_transform(coo_2d,4326)),
ST_GeographyFromText('POINT(3.075206 50.369314)'))) asc)
FROM coordinates, track
WHERE coo_trk_id=trk_id AND trk_status IN (0,4) AND trk_veh_id=1
and st_contains(GeomFromText('POLYGON((2.79271955988 50.1891338198, 3.35769244012 50.1891338198,
3.35769244012 50.5494941802, 2.79271955988 50.5494941802, 2.79271955988 50.1891338198))',4326), coo_2d)
)
SELECT coo_trk_id, coo_time, coo_length, distance
FROM TT
WHERE R = 1
order by distance; |
Partager