storing latitude and longitude for a route

I want to store a bunch of routes which consists of a pair of latitude and longitude series in a database. I want to be able to get all the 开发者_StackOverflowroutes for a given key.

The way my table is set up now is the following:

RouteID Order Latitude Longitude

but a route can have many sets of latitude and longitude, so it becomes:

RouteID Order Latitude Longitude
1         0       X         Y
1         1       X         Y
1         2       X         Y
1         3       X         Y
1         4       X         Y
2         0       X         Y  
2         1       X         Y  

I have another table called R, which has RouteID and it references to this Route table. The question is: 1. Does this seems reasonable?

1. If not what is a better way of doing this

UPDATE: So the other table is called TEMP and it has the following format

TempID RouteID UserID AttributeX AttributeY

when I do a SQL:

SELECT R.LATITUDE, R.LONGITUDE
FROM TEMP T, ROUTE R
WHERE T.UserID =1
ORDER BY R.ORDER ASC ;

And in my table currently UserID 1 has two routes, but it prints out each lattitude twice. Is my SQL wrong?

---

This looks OK to me.

But don't pollute the world with a table named R. Think about the guy coming after you, what does R mean?

The other table should probably be named Routes and this table should probably be named RoutePoints or just Points or something else informative.

And for the SQL, you have used a CROSS JOIN when you want to use a INNER JOIN:

SELECT R.LATITUDE, R.LONGITUDE
FROM TEMP T INNER JOIN ROUTE R ON T.ROUTEID = R.ROUTEID
WHERE T.UserID =1
ORDER BY R.ORDER ASC

That might work.

---

Most likely you want to use a space-filling-curve or a spatial index. I use it to store postcode and query them with just 1 operator indepedent of the zoom-level. You are welcome to download my class at phpclasses.org ( hilbert-curve ).

---

A lot of it depends on what you want to do with this information after you have stored it. If you just want to print out a map for a given route, your table setups could be ok. If you wanted to know if two routes intersected or overlapped, you should probably learn more about Geogrpahic Information Systems (GIS). GIS Stackexchange is a good place to start asking questions.