OpenStreetMap Forum

zefyras

Member

Registered: 2015-09-05

Posts: 1

selecting tags from way_tags slow?

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

Last edited by zefyras (2015-09-05 19:46:12)