Level 9
$table->decimal('long', 10, 7);
$table->decimal('lat', 10, 7);
10 likes
Dec 30, 2015
10
Level 9
What type of column type should I use for storing latitude and longitude?
Looking at this stackexchange article I'm kind of confused how I should go about storing geographic data using mysql and laravel.
Title: What is the ideal data type to use when storing latitude / longitudes in a MySQL database?
I plan to be storing latitude, longitude and countries. What column type should I use in my migrations?
Thank you for the help I may get.
Level 1
when retrieving lat and long these value are convert in string from database. please suggest any solution
1 like
Level 5
@Corbin - a small point is that LONG is an sql reserved word. I expect laravel is quoting it anyway, but I think its good practice to try and avoid reserved words as schema object names where possible. fwiw I usually abbreviate longitude to lng
regards
l.
4 likes
Level 9
@londoh, Thanks for the valuable advice!
1 like
Level 2
Also take in consideration the precision you require. 7 decimals would suffice in most cases but might not be the optimal setup. This is useful when storing large amounts of records.
Decimal Places - Aprox. Distance
1 - 10 kilometers 6.2 miles
2 - 1 kilometer 0.62 miles
3 - 100 meters About 328 feet
4 - 10 meters About 33 feet
5 - 1 meter |About 3 feet
6 - 10 centimeters About 4 inches
7 - 1.0 centimeter About ½ an inch
8 - 1.0 millimeter The width of paperclip wire.
9 - 0.1 millimeter The width of a strand of hair.
10 - 10 microns A speck of pollen.
11 - 1.0 micron A piece of cigarette smoke.
12 - 0.1 micron You're doing virus-level mapping at this point.
13 - 10 nanometers Does it matter how big this is?
14 - 1.0 nanometer Your fingernail grows about this far in one second.
15 - 0.1 nanometer An atom. An atom! What are you mapping?
19 likes
Level 7
@sander0105 is this after the comma or in total?
Level 2
@malfait.robin it's the number of decimals, so after the comma. The above example by @Corbin (10,7) has a 1.0 centimeter accuracy.
Example: Mobile phones have a typical accuracy of 5-8 meters in open field, so 4 (7,4) or 5 (8,5) decimals would be sufficient for storing their lat/lng.
Level 9
@sander0105, 4, or 5 would probably be best. But at least we now all know we could make a Laravel atomic clock using 15!
4 likes
Level 13
In this case I would store it as a string. The precision is not really important and could introduce unwanted side effects. String compares will provide more value as the fields are labels.
Level 2
Latitudes range from -90 to +90 (degrees), so DECIMAL(10, 8) is ok for that, but longitudes range from -180 to +180 (degrees) so you need DECIMAL(11, 8).
In short: lat DECIMAL(10, 8) lng DECIMAL(11, 8)
11 likes
Please or to participate in this conversation.