What is the best way to store Google Maps points in the database?

Asked

Viewed 338 times

0

I am developing a system that will store some Google Maps paths of registered users and structured my table as below:

----------------------
| trajetos           |
----------------------
| id                 |
| id_usuario         |
| partida_longitude  |
| partida_latitude   |
| chegada_longitude  |
| chegada_latitude   |
----------------------

This journey will be simple and will include a point of departure and a point of arrival. I know that this table will have many records (hundreds of thousands) and right now I need to think about performance when doing a search for some route.

My question is:

This is a common (and perhaps best) structure for storing Google Maps points in a database?

Another option would be to store the address (instead of latitude and longitude), but this would compromise performance when seeking the information?

I also thought of using only the zip code with place number (for accuracy), would it be better than latitude and longitude? Is there any other how to store those "dots" that are efficient?

mysql google-maps
  • 1

    The way the question stands, it depends on "like," or personal opinion. If you can [Dit] and detail the needs of your specific case, what you tried (or thought) and the difficulties encountered in the alternatives, perhaps a more technical answer is possible. When the size is predictable of the paths I usually simply use a BLOB with the coordinates packaged in "binary" even as I would rarely need an isolated point in a path. If you need them individually for queries and/or metadata, or many points on the map can be the case for geospatial tables

  • @Bacco, I made clear in the question on several points: "I need to think about performance", "I would compromise performance", "I want it to be efficient", including I put several alternatives that I thought about doing but I don’t know what’s best in this regard. Performance is not personal taste or opinion. What do you want me to detail? / Yes, I saw a question similar that helped me a lot but I do not know if it is the best alternative and as I am using Laravel, I saw that there is a function "Geometry". Maybe your idea is good.

  • Performance depends on what you will do with the data. Line splitting increases the performance of single-point queries and/or screen-filtered points (such as miscellaneous markers), but worsens the organization of individual paths. Placing in a row only brings an entire path in a single select line, leaving very efficient the return, but hinders the treatment and sectorization of individual points. Each case is a case, and so details give greater chance of answers that actually solve your problem. Remember that your post can be reopened, I think it’s worth [Dit].

1 answer

1

Face there are several ways to structure this but it all depends on the need. I would structure this way:

id id_usuario origem destino

Which would be implicit that the data of the two columns origin and fate are in format latitude and longitude.

I do not recommend using the address or ZIP code, because there are places that may not be in the Post Office database and even less in the Google Maps database. With latitude and longitude you can find anywhere on the planet on the map.

  • Really, the ZIP code and address thing has to be avoided, but still, it all depends on the need.

Browser other questions tagged mysql google-maps

You are not signed in. Login or sign up in order to post.