地理空间数据的表结构 [英] Table structure for Geo Spatial Data
问题描述
在包含地理空间数据的MYSQL数据库中构造表的建议方式是什么.作为POC，我正在努力将如下图所示的数据插入数据库表中.
下面是Phpmyadmin表创建表的快照
寻找有关创建地理空间数据表的建议，如屏幕截图所示.
我正在XAMPP V1.8.3 Windows8上运行MYSQL版本5.6.16. 如下面的sql
所示，创建了表geomduplicate和列，并插入了数据CREATE TABLE geomduplicate1(
zip INTEGER(3) NOT NULL PRIMARY KEY,
latitude NUMERIC(9,6),
longitude NUMERIC(10,6),
city VARCHAR(10),
state VARCHAR(2),
county VARCHAR(9)
);
REPLACE INTO geomduplicate1(zip,latitude,longitude,city,state,county) VALUES
(501,40.922326,-72.637078,'Holtsville','NY','Suffolk');
REPLACE INTO geomduplicate1(zip,latitude,longitude,city,state,county) VALUES
(544,40.922326,-72.637078,'Holtsville','NY','Suffolk');
REPLACE INTO geomduplicate1(zip,latitude,longitude,city,state,county) VALUES
(601,18.165273,-66.722583,'Adjuntas','PR','Adjuntas');
REPLACE INTO geomduplicate1(zip,latitude,longitude,city,state,county) VALUES
(602,18.393103,-67.180953,'Aguada','PR','Aguada');
REPLACE INTO geomduplicate1(zip,latitude,longitude,city,state,county) VALUES
(603,18.455913,-67.14578,'Aguadilla','PR','Aguadilla');
REPLACE INTO geomduplicate1(zip,latitude,longitude,city,state,county) VALUES
(604,18.49352,-67.135883,'Aguadilla','PR','Aguadilla');
REPLACE INTO geomduplicate1(zip,latitude,longitude,city,state,county) VALUES
(605,18.465162,-67.141486,'Aguadilla','PR','Aguadilla');
REPLACE INTO geomduplicate1(zip,latitude,longitude,city,state,county) VALUES
(606,18.172947,-66.944111,'Maricao','PR','Maricao');
REPLACE INTO geomduplicate1(zip,latitude,longitude,city,state,county) VALUES
(610,18.288685,-67.139696,'Anasco','PR','Anasco');
并且数据已成功插入mysql数据库中.
以创建空间数据类型.一个单一的经度或纬度值不能单独成为一个几何图形，就像在屏幕快照中一样.
如果走使用几何类型的路线，那么与拥有单独的纬度和经度字段相比，它具有两个优点:您可以添加将lat/lon文本列移动到点"类型列中有关此问题的更多信息，尽管请注意，Quassnoi的答案在lon和lat的方向上是错误的-尽管Point(lon，lat)并非如此，但这是一个很常见的错误.
注意::直到最近，如果使用MyISAM引擎，则只能索引空间列.
编辑:在即将发布的版本中， 解决方案
Store it is as a geometry data type. MySQL supports Geometry (generic), as well as Point, Linestring and Polygon data types, see creating spatial data types. A single longitude or latitude value can not be a geometry on its own, as you have it in your screen shot.
If you go the route of using geometry types, it gives you two advantages over having separate latitude and longitude fields: you can add a spatial index and you will be able to use some of MySQL's spatial operator functions such as ST_Buffer, ST_Intersects, ST_Distance to do further analysis. Spatial indexes are based on R-trees and will perform far better than two B-tree indexes on non spatial columns, latitude and longitude -- and this performance difference will grow as your table size grows.
You can still get the latitude and longitude values back by using the X and Y point functions so you will not lose anything by storing your data as a Point.
If you already have your data in two separate lat/lon columns, and you want to go the geometry/point datatype route, you can use the Point function to create the Point datatype:
alter table mytable add column pt POINT;
update mytable set pt=Point(longitude, latitude);
alter table mytable modify pt POINT NOT NULL;
create spatial index ix_spatial_mytable_pt ON mytable(pt);
Note that the Point function was only introduced in MySQL 5.1.x (it isn't too well documented, so I'm not sure of exact version), and before that you had to use concat with the GeomFromText function, see Moving lat/lon text columns into a 'point' type column for some more information on this, although note that Quassnoi's answer has lon and lat the wrong way round -- it is Point(lon, lat) not the other way, though this is a very common mistake.
NOTE: Until recently, you could only index a spatial column if using the MyISAM engine.
EDIT: In the upcoming release, MySQL 5.7.5, InnoDB will finally support indexes on spatial data types (and not just store spatial types without an index, which is considerably less useful). This means you can have foreign keys, ACID guarantees, spatial indexes all in one engine, which has been a long time in coming.
这篇关于地理空间数据的表结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
