Recommended data type for GPS coordinates?

I see that postGIS is currently not supported in CockroachDB. What is recommended data type for storing latitude and longitude (GPS coordinates) in CockroachDB?

Hey @hinewwiner

Currently, CockroachDB does not support postGIS, and there is an issue tracking this located on our GitHub at issue #19313. We are always interested in more use cases of this feature, so if you care to update that issue with your use case, that would be great!

If you are trying to simply store latitudes and longitudes, I think a STRING data type could be something you can try. Let me know if there is something more specific you are trying to do with the latitudes and longitudes, if possible.

Cheers,
Ricardo

Just to echo Ricardo, I’m a PM at Cockroach Labs and would learn to love more about your current use case. Why do you need this information? How do you use it today? What database and extension do you use?

Thanks for prompt replies. I really like how this community is helpful.

My startup is location based social platform so we will be calculating distances between two points lot. (For example, I could be querying for :: Given my lat,long coordinate (x,y), give me all registered points within 5km radius.)

I have personally not used GPS coordinates in database so I was looking for some advice on the data type that many recommends.

I thought using FLOAT as data type for coordinates make more sense. Is there any reason you prefer STRING data type?

Hello @hinewwiner

Thanks for sharing some more details of your use case. This is great information that we will share with the team for further evaluation.

I had suggested STRING type based on the idea that you were strictly storing Latitudes and Longitudes, and weren’t actually using them to compute anything. Given the new information, I would say using a FLOAT may be a better option, however computing the distances based off the data would have to be done on the app side.

Let me know if there are any other concerns or questions.

Cheers,
Ricardo

Do you have a plan for doing that efficiently?

Seems to me that if you have a billion points spread over the world and you need to find all the points within 5km radius of a given point then: for each such query you would have to read all 1 billion points and calculate the distance to the given point. Clearly not a workable solution. There is no speedy index and you can use.

You can use an index and avoid reading all values. Here’s an outline to an approach and some sample implementations: http://janmatuschek.de/LatitudeLongitudeBoundingCoordinates#SQLQueries

Note that the queries here haven’t been tested in CRDB, but I don’t see any reason why they would not work off the bat. You’ll end up with some transaction contention in high volume areas, but you could reduce that by using follower reads, assuming it’s acceptable to read data 48s in the past.

Yes, that is the obvious first approach.

I was wondering how speedy that is if one had millions of points to sift through.

I have long since pondered experimenting with a Hilbert 2d to 1d transform so that only one index is required. Sadly my only use case for that disappeared so I never did the experiment.