Telegram bot and PostGIS

About the implementation of bots for the Telegram messenger on the site there were already quite a lot of posts. But there is one topic that, in my opinion, has not yet been addressed. This is an implementation of working with geolocation inside the bot. In this post I will give an example of how the bot can process geolocation information sent by users based on my own experience with the aroundus_bot bot .



The fact is that the Telegram messenger provides quite convenient opportunities for working with geolocation. The user can send his current position, send some other point on the map (for mobile devices, attachment Location), or send the address and location of any place (restaurants, parks, squares ...) using the foursquare bot .



What can be done with this information further? Different options are possible. This can be either the implementation of quests (the bot will send the following points where users need to follow, or give out some information based on the user's location), and search for something nearby (toilets, bars, restaurants). For example, the bot I’ve implemented allows you to send a story that will be tied to a location specified by the user on the map, with the subsequent opportunity to read all the stories that users sent in a certain neighborhood from any specified point.



The location comes in the form of a point on a map having latitude and longitude. Intuitively, what to do with this information - we simply save the coordinate data in a database, and then, when a user requests stories from a given neighborhood, we simply find all the stories that fall into this neighborhood.

Problem


But latitude and longitude are the geographical coordinates of a point that determine the location of an object on the earth's surface. And as you know, the earth has the shape of an ellipsoid. And there is no way to simply add to these coordinates, say, 100 meters, and say that this is the edge of the area in which you want to look for stories. It is necessary to calculate the neighborhood of a point in spherical coordinates.

I thought for a long time what can be done with this, since I think it’s wrong to reinvent the wheel and realize my own calculation curves. I would like to use a ready-made tool that would allow me to at least calculate the distance in meters between two geographical points.

Postgis


In my search, I came across a post published on the current site. And he realized that he had made the absolutely right choice of the DBMS that I use to store the information necessary for the bot - PostgreSQL. Briefly, the PostGIS extension, which can be supplied for this DBMS, allows you to effectively work with geographic coordinates using special functions in a regular SQL query. You can read more about PostGIS here .

Thus, the selection of all stories from the vicinity of a point (or the search for the nearest anything relative to a given point) is reduced to the implementation of an SQL query, which, thanks to the use of GiST indices for working with coordinates, will be performed very quickly. But it should be noted that for the effective use of the extension, the location data must be stored in a column with a special geometry type, on which you then need to hang the index. In my implementation, for ease of use, I store data both separately for latitude and longitude in double format, and a column in geometry format.

An example sql query to search for something in a given neighborhood (for example, within a radius of 100 meters) could be like this:

ST_Distance(ST_Transform(coordinates, 26986), ST_Transform(ST_SetSRID(ST_MakePoint(x, y), 4326), 26986)) < 100

where:

ST_Distance , ST_Transform , ST_SetSRID , ST_MakePoint - special functions for working with geographic coordinates,
coordinates - a column of type geometry
x , y - latitude and longitude of the point, the vicinity of which we are interested in.

It should be said that I also know about the implementation of this extension for the MySQL DBMS, but in the process of searching for information I had the strong opinion that the implementation of the extension for PostgreSQL works better and with fewer errors, although I can be wrong.

Conclusion


If there is a need to implement a bot for Telegram, which should work with geographic coordinates, then a good solution would be to use the PostgreSQL DBMS to store information. All that needs to be done is to save the latitude and longitude data received from the user in a column of type geometry, and then you can effectively work with this information by operating with SQL queries.

Also popular now: