I have a mysql database table that will store locations for buildings and events and a few other things. All locations are stored in one table, and linked to buildings, events etc through their own many to many table. That way I can just display dots on a map, and also allow filtering etc.
我有一个mysql数据库表，它将存储建筑物、事件和其他一些东西的位置。所有位置都存储在一个表中，并通过它们自己的多个表与建筑物、事件等联系在一起。这样我就可以在地图上显示点，也可以过滤。
However the problem comes with some things having a single location so 1 lat,long but some like a track has a number of lat long positions, and something like a large stadium might have a polygon over it. These are also stored as a list of lat,longs with the first and last being the same.
但是问题是有些东西只有一个位置，所以1点长，但是有些像跑道有很多点长，像一个大体育场可能有一个多边形。它们也被存储为一个lat列表，其中第一个和最后一个是相同的。
Im wondering how I should store this in the mysql db though. Originally I just had a column for lat, long and id for the lookup table. Should I have ANOTHER lookup table for the co-ordinates or serialise the data before putting it into the DB in some way or should I just store the whole string in one field lat1,long1 lat1,long1;lat2,long2;lat1,long1
我想知道我该如何将它存储在mysql数据库中。最初，我只有一个列用于lat, long和id用于查找表。在以某种方式将数据放入DB之前，我应该有另一个坐标查找表，还是应该将整个字符串存储在一个字段lat1、long1 lat1、long1、long1;lat2、long2、lat1、long1
Any suggestions?
有什么建议吗?
2 个解决方案
#1
3
I wouldn't de-normalize the data from the start, by pushing a whole "serialized" polygon into a single field.
我不会从一开始就通过将整个“序列化”多边形推到一个单独的字段来降低数据的规范化。
Rather, I'd have a Polygons table (with polygon ID and possibly auxiliary per-polygon info, such as whether it's an actual closed polygon or just a polyline -- though that might alternatively be represented in the following table by having the last point equal to the first one for a certain polygon), and a PointsInPolygon table (with coordinates of the point, polygon ID foreign key, vertex number within polygon -- the latter two jointly being unique).
表(相反,我有一个多边形与多边形ID和可能辅助per-polygon信息,如是否它是一个实际的封闭多边形或多段线——尽管可能或者代表在下表中,最后一点等于第一个某个多边形),和一个PointsInPolygon表(的坐标点,多边形ID外键,在多边形顶点数,后两个共同是独一无二的)。
Normalization will make (as usual) your life much simpler for ad-hoc queries (including in this case "polygons near X", point in polygon, etc). Again as usual, you can later add redundant denormalized values if and when you determine that some specific query really needs to get optimized (at some cost to table updates, integrity checks, etc). Geodata are not all that different from other kinds in this regard.
规范化(像往常一样)将使您的查询变得更简单(包括在本例中“X附近的多边形”、多边形中的点等)。与往常一样，如果您确定某些特定查询确实需要进行优化(以表更新、完整性检查等成本为代价)，您可以稍后添加冗余的非规范化值。在这方面，地理数据与其他类型的数据并没有什么不同。
#2
1
Since you're not doing any lookups on the locations, and you're using (I'm assuming) Google Maps API, the simplest solution would probably be to encode a list of lat/lon as JSON and store in a varchar column.
由于您没有对位置进行任何查找，并且使用(我假设)谷歌映射API，最简单的解决方案可能是将lat/lon列表编码为JSON并存储在varchar列中。
You can just output the JSON straight from the database for your Google Maps API code to use. I would suggest you to use some simple JSON structure like so: ["point",1.23456,2.34567] or ["line",1.23456,2.34567,3.45678,4.56789] and so on.
您可以直接从数据库输出JSON，以便使用谷歌映射API代码。我建议您使用一些简单的JSON结构，如:["point"、1.23456、2.34567]或["line"、1.23456、2.34567、3.45678、4.56789等。
#1
3
I wouldn't de-normalize the data from the start, by pushing a whole "serialized" polygon into a single field.
我不会从一开始就通过将整个“序列化”多边形推到一个单独的字段来降低数据的规范化。
Rather, I'd have a Polygons table (with polygon ID and possibly auxiliary per-polygon info, such as whether it's an actual closed polygon or just a polyline -- though that might alternatively be represented in the following table by having the last point equal to the first one for a certain polygon), and a PointsInPolygon table (with coordinates of the point, polygon ID foreign key, vertex number within polygon -- the latter two jointly being unique).
表(相反,我有一个多边形与多边形ID和可能辅助per-polygon信息,如是否它是一个实际的封闭多边形或多段线——尽管可能或者代表在下表中,最后一点等于第一个某个多边形),和一个PointsInPolygon表(的坐标点,多边形ID外键,在多边形顶点数,后两个共同是独一无二的)。
Normalization will make (as usual) your life much simpler for ad-hoc queries (including in this case "polygons near X", point in polygon, etc). Again as usual, you can later add redundant denormalized values if and when you determine that some specific query really needs to get optimized (at some cost to table updates, integrity checks, etc). Geodata are not all that different from other kinds in this regard.
规范化(像往常一样)将使您的查询变得更简单(包括在本例中“X附近的多边形”、多边形中的点等)。与往常一样，如果您确定某些特定查询确实需要进行优化(以表更新、完整性检查等成本为代价)，您可以稍后添加冗余的非规范化值。在这方面，地理数据与其他类型的数据并没有什么不同。
#2
1
Since you're not doing any lookups on the locations, and you're using (I'm assuming) Google Maps API, the simplest solution would probably be to encode a list of lat/lon as JSON and store in a varchar column.
由于您没有对位置进行任何查找，并且使用(我假设)谷歌映射API，最简单的解决方案可能是将lat/lon列表编码为JSON并存储在varchar列中。
You can just output the JSON straight from the database for your Google Maps API code to use. I would suggest you to use some simple JSON structure like so: ["point",1.23456,2.34567] or ["line",1.23456,2.34567,3.45678,4.56789] and so on.
您可以直接从数据库输出JSON，以便使用谷歌映射API代码。我建议您使用一些简单的JSON结构，如:["point"、1.23456、2.34567]或["line"、1.23456、2.34567、3.45678、4.56789等。