32 lines
452 B
SQL
32 lines
452 B
SQL
--- find a stop near *pos*
|
|
SELECT
|
|
id,
|
|
description,
|
|
name,
|
|
ST_AsBinary(ST_Transform(location, 4326)) as location,
|
|
ST_Distance(
|
|
stops.location,
|
|
ST_Transform(
|
|
ST_GeomFromWkb(:searchgeom, 4326),
|
|
25832
|
|
)
|
|
) as dist
|
|
FROM stops
|
|
WHERE
|
|
dist < 500
|
|
AND stops.ROWID IN (
|
|
SELECT ROWID
|
|
FROM SpatialIndex
|
|
WHERE
|
|
f_table_name ='stops'
|
|
AND search_frame = ST_Buffer(
|
|
ST_Transform(
|
|
ST_GeomFromWkb(:searchgeom, 4326),
|
|
25832
|
|
),
|
|
500
|
|
)
|
|
)
|
|
ORDER BY
|
|
dist
|