Table structure for geospatial data
What is the suggested way to structure a table in a MYSQL database containing geospatial data. As a POC, I am working on inserting data like the picture below into a database table.
Below is a snapshot of phpmyadmin table creation table creation
looking for suggestions for creating a table for geospatial data as in the screenshots.
UPDATE: I am on XAMPP V1.8.3 Windows8 running MYSQL version 5.6.16. Geomduplicate tables and columns and inserted data like in below screenshot sql
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');
and the data has been successfully inserted into the MySQL database.
source to share
Save it as a geometric data type . MySQL supports Geometry (generic) as well as Point, Linestring, and Polygon data types, see creating spatial data types . Longitude or latitude alone cannot be geometry as shown in the screenshot.
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 can use some of the MySQL spatial operator functions like ST_Buffer, ST_Intersects, ST_Distance to further analyze ... Spatial indexes are R-tree-based and will perform much better than two B-tree indexes for nonspatial columns, latitude and longitude — and this performance difference will grow as the size of the table grows.
You can still get latitude and longitude values using the X and Y point functions , so you have nothing to lose by storing your data as points.
If you already have data in two separate latitude / longitude columns, and you want to traverse the route of a geometry / point data type, you can use the Point function to create a Point data type:
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's not too well documented, so I'm not sure the exact version), and before that you had to use concat with the GeomFromText function, see Moving lat / lon text columns in point & # 39; enter a column for more information on this, although note that in Quassnoi's answer the lon and lat are incorrect - it is the dot (lon, lat) and not vice versa, although this is a very common mistake.
NOTE: Until recently, you could only index a spatial column using the MyISAM engine.
UPDATE: In the next release, MySQL 5.7.5 , InnoDB will finally support indexes for spatial data types (rather than just storing spatial types without an index, which is significantly less useful). This means you can have foreign keys, ACID guarantees, spatial indexes - all in one engine, which has been in preparation for a long time.
source to share
Since version 5.6 MySQL innodb engine supports spatial data.
Refer to the links:
http://dev.mysql.com/doc/refman/5.6/en/creating-spatial-columns.html
https://dev.mysql.com/doc/refman/5.5/en/gis-data-formats.html
https://dev.mysql.com/doc/refman/5.5/en/populating-spatial-columns.html
source to share