[OSM-dev] C++ implementation of the API

[Steven te Brinke]

The second query uses a join only to select the correct values, not 
because you really want to join the tables. I think that you can achieve 
the same by a query like this:

SELECT *
FROM current_war_tags
WHERE id IN (
    SELECT *
    FROM current_ways
    WHERE tile IN (...)
       AND latitude BETWEEN ... AND ...
       AND longitude BETWEEN ... AND ...
)
ORDER BY id;

However, I do not know how the db handles subqueries. Thus, I am not 
sure if the performance of this query is better. But it seems to be a 
more logical way than using a join. (I am not very sure if using 
subqueries works at all, because I know some dbs have problems with that.)
It might be worth trying.

Steven


Tom Hughes schreef:
> You will get some duplication, yes. How much that matters will be
> down to profiling as you say.
>
> The other option is to run two queries in parallel:
>
>   SELECT *
>   FROM current_ways
>   WHERE tile IN (...)
>     AND latitude BETWEEN ... AND ... 
>     AND longitude BETWEEN ... AND ... 
>   ORDER BY id;
>
> and:
>
>   SELECT cwt.*
>   FROM current_way_tags cwt
>   INNER JOIN current_ways cw ON cwt.id = cw.id
>   WHERE cw.tile IN (...)
>     AND cw.latitude BETWEEN ... AND ... 
>     AND cw.longitude BETWEEN ... AND ... 
>   ORDER BY cwt.id
>
> then read both result sets at the same time and match them up.
>
> Tom
>