4

What is the suggested way to structure a table in a MYSQL Database containing Geo Spatial data. As a POC i am working to insert the data as in the image as below into a database table.

database

This below is the snapshot of the table creation of Phpmyadmin table creation

phpmyadmintable

looking for suggestions to create table for geospatial data as in screenshots.

EDIT: I am working on XAMPP V1.8.3 Windows8 running MYSQL version 5.6.16. Created Table geomduplicate and columns and inserted data as in screenshot with the below 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 was successfully inserted in mysql database.

Glorfindel
  • 21,988
  • 13
  • 81
  • 109
user3592479
  • 695
  • 3
  • 13
  • 26
  • Do you need any more information? Is there anything not covered in my answer? – John Powell Aug 18 '14 at 12:00
  • possible duplicate of [What is the ideal data type to use when storing latitude / longitudes in a MySQL database?](http://stackoverflow.com/questions/159255/what-is-the-ideal-data-type-to-use-when-storing-latitude-longitudes-in-a-mysql) – Gajus Jun 15 '15 at 19:33

2 Answers2

15

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.

Imran Zahoor
  • 2,521
  • 1
  • 28
  • 38
John Powell
  • 12,253
  • 6
  • 59
  • 67
  • [MYSQL Table Structure](http://postimg.org/image/u6vavmi0d/) . i have converted my existing mysql latitude,longitude columns to points data type. Is this how the stucture looks like once converted, attached image in link? – user3592479 Aug 23 '14 at 10:13
  • I am not sure as I don't use phpAdmin. Try using a spatial function on it, like buffer or try creating a spatial index. If that works, you have done it correctly – John Powell Aug 23 '14 at 15:12
  • I tried with code as create spatial index ix_spatial_mytable_pt ON geomduplicate1(pt); but failed Error " The used table type doesn't support SPATIAL indexes ", Can you suggest any reference / sample code to perform spatial functions on it or creating spatial index – user3592479 Aug 23 '14 at 15:57
  • You are using MyISAM? – John Powell Aug 23 '14 at 20:33
0

Since version 5.6 MySQL's innodb engine supports spatial data.

Refer the following 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

codejunkie
  • 908
  • 2
  • 21
  • 34
  • It should be noted however, that while InnoDB supported spatial data column types, it did not support indexing those columns until 5.7. So for those that run across this post in the near future, make sure you are using version 5.7 – jacurtis Aug 30 '17 at 21:53