sql: support location queries (geospatial indexing)

[vivekmenezes]

http://postgis.net/ is an example of a Postgres extension that supports location queries.

If you would like cockroachdb to support geospatial indexes for your project please do vote for this feature. We would also appreciate specific details on how you might use this feature so we can support you within the scope we define for this feature.

Thanks!

[awoods187]

Community request: what are some use cases that you want to use geospatial indexing for?

[grantcarthew]

Quote:

A common query used with spatial data is the Nearest Neighbor query. Nearest Neighbor queries are used to find the closest spatial objects to a specific spatial object. For example a store locator for a Web site often must find the closest store locations to a customer location.

Source: https://docs.microsoft.com/en-us/sql/relational-databases/spatial/query-spatial-data-for-nearest-neighbor

I have a Postgres database using PostGIS which contains address details of multiple locations. The users can search for the nearest location.

[johanneswuerbach]

The SaaS provider I work for provides the following query capabilities currently backed by postgis: finding points within a bounding box, within a radius around a given point or to generally order points by distance to known point x

So far those seem to solve most of our customer use cases.

[andressrg]

Most of my use cases involve being able to calculate distance between 2 GPS points.

[SpatialJ]

Most (spatial) Databases implement the "Simple Feature Access - Part 2: SQL Option" Specification. I think that this would make sense for cockroachdb as well. Maybe partially at first but better than a proprietary solution.

…

On Fri, Mar 23, 2018 at 5:30 PM, Andres Serrano ***@***.***> wrote: Most of my use cases involve being able to calculate distance between 2 GPS points. — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#19313 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAW2ywpzn1MozGD5H1c89s1ikXA2Gygqks5thSMogaJpZM4P8STr> .

[orofarne]

PostGIS functionality is excellent!

[kevinmartinjos]

Would love to see this in CRDB. Would make CRDB more appealing to small personal projects - get some geospatial data, put them in CRDB, run fancy queries (get me all users within 10km radius of this user) against it.

[gpiffault]

One of my use cases is to find segments within a radius of a point (all in GPS coordinates). I think the most useful part would be efficiently finding geometries within a bounding box, everything else can be done relatively easily at the application level. Something along the lines of rbush for example.

[DeoLeung]

will be great to enable using postgis/pgrouting, the rest is up to people how to use the functions

[gsf]

Most of our queries involve ST_Within to filter down to all points or geoms within a specified geom.

[jefferypalmer]

We are greatly interested in a database solution that scales out well (CRDB) and has geospatial support. We run queries that include: reverse geocoding, map tile generation, polygon/geofence search results, and analytics (combining geospatial data with other fields or cubes).

[lastzero]

If you just want to find results that are within a certain max distance / radius, you don't need "geospatial support", some math will do: https://developers.google.com/maps/solutions/store-locator/clothing-store-locator#findnearsql

[jefferypalmer]

Thanks for the suggestion, but this is a non indexable solution that requires a full table scan. This could work for some very limited use cases by converting the lat lon point to 2 decimal columns and index those for a quick 'am I in this box' type of searches but this doesn't get us very close in our application for polygon searches. Any insight you have is helpful so if you come across more solutions, send them over! Thanks.

[lastzero]

@darren1713 It doesn't require a full table scan if you index lat/long and only calculate the actual distance within a bounding box, see https://mariadb.com/kb/en/library/latitudelongitude-indexing/

More precise and polygon searches in very large data sets can be an issue though. Also coordinates near the poles from what I remember. In our case, it was fast enough (< 100ms) for searching the customer database of a large German cable internet provider.

[Samusername]

The requested featureset would be important,
"Simple features".

[moftak-dev]

any update on this?

[moftak-dev]

my requirement is to get all data nearest to a certain point (lat, long). i-e i have list of users with lat long and i want to get all those user nearest to my location (lat, long) any suggestion how we can achieve this in cockroach database?

[antoniomo]

On app logic side, you can use S2 to achieve this.

For example as explained here: https://blog.nobugware.com/post/2016/geo_db_s2_geohash_database/

Another worked out example in Go code: https://gist.github.com/antoniomo/3371e44cbe2f0cc75a525aac0d188cfb

Note that the actual SQL queries are not in those examples, but it's straightforward to represent those after you understand the idea.

[moftak-dev]

@antoniomo but i want to query on database level. do you think i need to look into other database for this ?

[lastzero]

@moftak-dev How fast do you think developers are with releasing this requested feature? Send a pull request, if you need it now 💎

You're welcome copy our evil hack, which uses an estimated bounding box instead of the exact distance:
https://github.com/photoprism/photoprism/blob/develop/internal/photoprism/search.go#L211

It's good enough for many applications, you can test it here: https://demo.photoprism.org/places?q=&lat=52.459677899999996&long=13.321888699999999&dist=20

[moftak-dev]

@lastzero but how can we integrate this with my nodejs app where i want to pull nearest location from database ?

[lastzero]

@lastzero but how can we integrate this with my nodejs app where i want to pull nearest location from database ?

Use this SQL where condition: (lat BETWEEN ? AND ?) AND (long BETWEEN ? AND ?)

Ranges must be estimated / calculated, see our Go source code or google it :)

If you need the distance in km, you need some additional calculations, see the link I posted here earlier. Doesn't take very long, if you use a bounding box to limit results upfront.

[zgalic]

All traditional DBMS (Oracle, MS SQL Server, PostgreSQL, MySQL, etc.) support spatial data types (and operations on them).
Without them CRDB will not be an option for systems/applications that manage spatial data.

[awoods187]

Hi all, I'm a product manager at Cockroach Labs. We are interesting in collecting some anonymized data about what kind of feature support you might need for geospatial indexing. We'd really appreciate it if you could fill out this short 3 minute survey. This information will be critical to promoting support for geospatial into the roadmap. https://www.surveymonkey.com/r/WQTL8BY

[awoods187]

Thank you to everyone who filled out the last survey. We have started work to add support for geospatial indexing! You can follow along on this issue!

In the meantime, we have a quick follow-up survey aimed at understanding performance expectations as well as third-party tooling usage. We'd greatly appreciate any feedback you may have here!

[otan]

Hello friends monitoring this thread.

We're on our way to building Geospatial features. We want to know which builtins are a priority for us to implement - so we're asking you for help!

Here are two labels which group together implemented PostGIS functions. If you see a function you use for your geospatial applications, let us know by reacting on the issue.

• A-geography-builtins (builtins related to geography types)
• A-geometry-builtins (builtins related to geometry types)

Note we are only focusing on 2D functions for now.

(If you feel like taking an issue on, feel free! We're happy to review and accept your contribution. The ones marked E-easy are the ones we think are good introductory ones.)

[otan]

With v20.2 out with spatial support (https://www.cockroachlabs.com/blog/spatial-data), we'll be closing this issue. Missing features can be dealt with in subsequently filed issues!