存储路线的纬度和经度

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 routes for a given key.

我想在数据库中存储一堆由一对纬度和经度系列组成的路由。我希望能够获得给定密钥的所有路由。

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

我的表现在的设置方式如下:

RouteID Order Latitude Longitude

RouteID订单纬度经度

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?

我有另一个名为R的表,它有RouteID,它引用了这个Route表。问题是: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

更新:所以另一个表称为TEMP,它具有以下格式

TempID RouteID UserID AttributeX AttributeY

when I do a SQL:

当我做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?

在我的表中,目前UserID 1有两条路线,但它打印出每个格子两次。我的SQL错了吗?

3 个解决方案

#1

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?

但是不要用一张名为R的桌子来污染这个世界。想想那个跟你走的人,R是什么意思?

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

另一个表应该可以命名为Routes,这个表应该可以命名为RoutePoints,或者只是Point或其他信息。

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

对于SQL,当您想使用INNER JOIN时,您已经使用了CROSS 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.

那可能有用。

#2

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 ).

您最有可能想要使用空间填充曲线或空间索引。我用它来存储邮政编码并用1个独立于缩放级别的操作符查询它们。欢迎您在phpclasses.org(hilbert-curve)下载我的课程。

#3

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.

很大程度上取决于您在存储后要对此信息执行的操作。如果您只想打印出给定路线的地图,您的表格设置就可以了。如果您想知道两条路线是相交还是重叠,您可能应该了解更多有关Geogrpahic信息系统(GIS)的信息。 GIS Stackexchange是一个开始提问的好地方。

#1