2

I would like to know how should i store longitude and latitude values in a mysql database, maybe the questions should be: Decimal vs Point (Spatial Data)

I found this question where the question is almost the same, but what i want to know unlike that question is how will i use the data when using each of the options? I know that when using decimal i'll simple retrieve the columns data and use them, but what when using Point?

Also i would like to know about the benefits of using any of them, and if there's a better way to do it i would like to know it. Also i'd like to know why google used float in this example?

  • What will you do with the lat-lng or Point when you get it? – Rick James Sep 17 '15 at 22:19
  • when i store it i will show the locations in a map, and a todo for me is create routes for a truck, things like that @RickJames – Jonathan Solorzano Sep 17 '15 at 23:35
0

As they stated 6 positions is all you need for Google maps - likely enough to not lose precision with a float. They may have correcting logic and likely don't have any mission critical apps running off standard API access (maybe??). Good answer concerning precision of coordinates here https://gis.stackexchange.com/questions/8650/how-to-measure-the-accuracy-of-latitude-and-longitude.

Decimal is a basic numeric field... your queries will be restricted to operations appropriate to decimals. This might be all you need if you're just storing the data and have an application layer that does any additional logic with the locations. If you're just appending Lat/Long to another table then you may already have all the indexing you need. For example, a Property table might include geo-coords, but it is primarily accessed by Property Name, Property Codes, etc. by users and systems. This should be simpler to read, maintain, and doesn't require any specific query/spatial knowledge.

If you want the benefits of GIS then you should store data in it's spatial specific data types. This will allow geospatial indexing, and direct, DB level manipulation of the coordinates. You can write queries that provide bounding boxes or calculate distances. This may be more efficiently handled at the DB level, especially for set selection. Instead of dumping a lot of unnecessary decimals to an app and expecting a mobile phone to do routing or distance calculations (as if decimals is all it would need!) you can use SQL spatial implementation to answer questions and find locations.

In SQL for spatial data you could create queries like this:

DECLARE @g geography = 'POINT(-121.626 47.8315)';
SELECT TOP(7) SpatialLocation.ToString(), City
FROM Person.Address
ORDER BY SpatialLocation.STDistance(@g);

To do the same with decimal points you would either need an API to feed that into or create your own logic to determine distance between lat/long pairs.

You can still get your spatial data out in the form of a lat/long numeric pair, but if that is all you're going to be using (e.g. feeding into an API) then just store it as a decimal.

DECLARE @g geometry;
SET @g = geometry::Parse('POINT(3 4 7 2.5)');


SELECT @g.STX; --3
SELECT @g.STY; --4
SELECT @g.Z; --7
SELECT @g.M; --2.5

The above code creates a point with the geometry data type and each SELECT returns the requested part of the point definition.

  • Nice answer, could you please make an example about using GIS please? – Jonathan Solorzano Sep 17 '15 at 23:33
  • Added a couple examples... fair warning GIS is a complex subject! – Dave Sep 18 '15 at 17:51
0

To feed into the map app, you need lat and lng as two numbers, not a Point.

Datatype(s)         bytes     Resolution
FLOAT                  8   1.7 m    5.6 ft
DECIMAL(8,6)/(9,6)     9    16cm    1/2 ft

(These resolutions disagree with the link give because I account for both lat and lng.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.