SQL Query Optimization

i've noticed this query in potlach and i was wondering if it would be faster without the implicit joins. AFAIK MySQL doesn't build the cartesian product with this kind of join but it could still result in a faster query if explicitly declared how to join

SELECT gps_points.latitude,gps_points.longitude,gpx_files.id AS fileid,UNIX_TIMESTAMP(gps_points.timestamp) AS ts
FROM gpx_files,gps_points,users
WHERE gpx_files.id=gpx_id
AND gpx_files.user_id=users.id
AND token=?
AND (gps_points.longitude BETWEEN ? AND ?)
AND (gps_points.latitude BETWEEN ? AND ?)
ORDER BY fileid,ts

this would result in

SELECT P.latitude,P.longitude,F.id AS fileid,UNIX_TIMESTAMP(P.timestamp) AS ts
FROM gpx_files F
INNER LEFT JOIN gps_points P ON P.gpx_id=F.id
INNER LEFT JOIN users U ON F.user_id=U.id
WHERE
token=?
AND (P.longitude BETWEEN ? AND ?)
AND (P.latitude BETWEEN ? AND ?)
ORDER BY fileid,ts

or if we could leave out the JOINs and just get the gps points without linking it to a specific user/file (if we drop this you obviously can't order by fileid)

if the file/user is relevant but database inconsistencies are to be expected (linking to a user/file which doesn't exist anymore) you could use an OUTER JOIN which would fill those values with NULLs and return all GPS points (instead of retaining them)

can somebody benchmark this on the system?
--blk 11:02, 15 May 2007 (BST)

Change requests, ideas, bug reports

Satelite imagery

The satellite imagery from Google Maps seems to be a lot better than Yahoo! Aerial Maps in many areas. How come Potlach doesn't use it? Is it because of legal issues? Because it wouldn't be much of a problem to integrate Google Maps, there is a Flash API and also a static API. It would definately accelerate the growth of OSM!
--Freelancemapper 18:36, 14 January 2009 (UTC)

different size potlatch editors?

i do most of my osm mapping on a 23" monitor at 1920 x 1200, and potlatch occupies about a sixth of the screen. i would love to have something that could take advantage of the extra screen space (josm is not an option, work firewall breaks it), is anyone out there willing to build different size editors for those with big screens? Myfanwy 23:57, 17 October 2007 (BST)

Idea: Different colors for GPS tracks

Depending on the speed, the track shall be drawn in a different color, e.g.

purple for walking, under 10 km/h

blue for bycicle, 10 to 30 km/h

green for traffic in town, 30 to ~60 km/h

yellow for overland, 60 to 100 km/h

orange for 100 to 120 km/h

red for over 120 km/h

(might be realized in a table or as a rainbow-function)
Reason: When showing other peoples GPS tracks, I can not be sure if I see a track from a car in the car lane or from a bike on the bicycle-lane next to it, so I might incorrectly move the way to the middle of all lanes instead of to in the middle of the car lanes. Showing the speed would help to identify lanes for different vehicles.
--Lulu-Ann 11:52, 3 July 2008 (UTC)

Idea: Layer of GPS track shall switch

When I want to see if my track contains unmapped ways, I often encounter the problem that I can hardly find the lightblue line of my track, because it is covered by the already mapped ways.
The track line shall not be solid, but a dottet line, where one point is shown on layer +6 and the next on layer -6, so that a line appears solid when there is no way and dottet if it is on an object.
--Lulu-Ann 11:52, 3 July 2008 (UTC)

Idea: Show scale in edit mode

Like in the view mode, also in the edit mode the skale with miles and kilometers shall be shown in each zoom level.
--Lulu-Ann 11:52, 3 July 2008 (UTC)