I’ve tried various queries, they all take 4~5 seconds just for one specific latitude+longitude position.
SELECT k,v FROM way_tags WHERE way_id IN (
SELECT way_id FROM way_nodes
JOIN nodes ON way_nodes.node_id = nodes.node_id WHERE
latitude >= 547309125
and latitude <= 547309125
and longitude >= 252397012
and longitude <= 252397012
)
SELECT * FROM way_tags WHERE way_id IN (
SELECT way_id FROM way_nodes WHERE node_id IN (
SELECT node_id FROM nodes WHERE
latitude >= :minLat
and latitude <= :maxLat
and longitude >= :minLng
and longitude <= :maxLng
)
)
SELECT * FROM way_tags
WHERE way_id IN
(SELECT a.way_id FROM way_nodes a
INNER join nodes b
ON a.node_id = b.node_id
AND b.latitude BETWEEN :minLat AND :maxLat
AND b.longitude BETWEEN :minLng AND :maxLng)
SELECT wt.*
FROM way_tags wt
JOIN way_nodes wn ON wt.way_id = wn.way_id
JOIN nodes n ON wn.node_id = n.node_id
WHERE latitude >= :minLat
AND latitude <= :maxLat
AND longitude >= :minLng
AND longitude <= :maxLng
Server details:
I’m using InnoDB on MySQL, so perhaps PostreSQL could give better performance here?
Edit: I guess this is wrong section to post at, sorry