Telegram bot and PostGIS
On 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 raised. This is the implementation of work with geolocation inside the bot. In this post I will give an example of how a bot can process geolocation information sent by users based on my own experience of implementing the bot aroundus_bot .
The fact is that the Telegram messenger provides quite convenient opportunities for working with geolocation. The user can send his current position, send any other point on the map (for mobile devices, attaching Location), or send the address and location of any place (restaurants, parks, squares ...) using a foursquare bot.
')
What can I do with this information next? There are various options. This can be either the implementation of quests (the bot will send the following points to follow the users, or give any information based on the location of the user), and search for something nearest (toilets, bars, restaurants). For example, the bot implemented by me allows you to send a story that will be tied to a location specified by the user on the map, with the subsequent ability to read all the stories that users sent in a certain neighborhood from any specified point.
Location comes in the form of a point on a map that has latitude and longitude. Intuitively, it is clear what to do with this information - we simply save the coordinates in the database, and then, when the user requests stories from a given neighborhood, we simply find all the stories that fall in this neighborhood.
But latitude and longitude are the geographical coordinates of a point, which 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 to look for history. It is necessary to calculate the neighborhood of the point in spherical coordinates.
I thought for a long time what can be done with this, since I think it is wrong to reinvent the wheel and realize my calculation curves. I wanted to use a ready-made tool that would allow at least to calculate the distance in meters between two geographic points.
In my search I came across a post published on the current site. And I realized that I made an absolutely correct choice of the DBMS that I use to store the information needed by the bot - PostgreSQL. In short, the PostGIS extension, which can be supplied for this DBMS, allows you to work efficiently with geographic coordinates, using the call of special functions in a normal SQL query. You can read more about PostGIS here .
Thus, sampling all the stories from a neighborhood of a point (or finding the closest thing relative to a given point) is reduced to implementing an SQL query, which, using GiST indices to work 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 type of geometry, on which then the index must be hung. In my implementation, for ease of use, I store data both separately for latitude and longitude in double format, and 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:
Where:
ST_Distance , ST_Transform , ST_SetSRID , ST_MakePoint - special functions for working with geographic coordinates,
coordinates - geometry type column
x , y - the latitude and longitude of the point, the neighborhood of which we are interested.
It should be said that I also know about the implementation of this extension for MySQL DBMS, but in the process of searching for information I had a strong opinion that the implementation of the extension for PostgreSQL works better and with fewer errors, although I could be mistaken.
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 PostgreSQL for storing information. All that needs to be done is to save the latitude and longitude data received from the user in a geometry type column, and then you can efficiently work with this information using SQL queries.
The fact is that the Telegram messenger provides quite convenient opportunities for working with geolocation. The user can send his current position, send any other point on the map (for mobile devices, attaching Location), or send the address and location of any place (restaurants, parks, squares ...) using a foursquare bot.
')
What can I do with this information next? There are various options. This can be either the implementation of quests (the bot will send the following points to follow the users, or give any information based on the location of the user), and search for something nearest (toilets, bars, restaurants). For example, the bot implemented by me allows you to send a story that will be tied to a location specified by the user on the map, with the subsequent ability to read all the stories that users sent in a certain neighborhood from any specified point.
Location comes in the form of a point on a map that has latitude and longitude. Intuitively, it is clear what to do with this information - we simply save the coordinates in the database, and then, when the user requests stories from a given neighborhood, we simply find all the stories that fall in this neighborhood.
Problem
But latitude and longitude are the geographical coordinates of a point, which 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 to look for history. It is necessary to calculate the neighborhood of the point in spherical coordinates.
I thought for a long time what can be done with this, since I think it is wrong to reinvent the wheel and realize my calculation curves. I wanted to use a ready-made tool that would allow at least to calculate the distance in meters between two geographic points.
Postgis
In my search I came across a post published on the current site. And I realized that I made an absolutely correct choice of the DBMS that I use to store the information needed by the bot - PostgreSQL. In short, the PostGIS extension, which can be supplied for this DBMS, allows you to work efficiently with geographic coordinates, using the call of special functions in a normal SQL query. You can read more about PostGIS here .
Thus, sampling all the stories from a neighborhood of a point (or finding the closest thing relative to a given point) is reduced to implementing an SQL query, which, using GiST indices to work 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 type of geometry, on which then the index must be hung. In my implementation, for ease of use, I store data both separately for latitude and longitude in double format, and 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:
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 - geometry type column
x , y - the latitude and longitude of the point, the neighborhood of which we are interested.
It should be said that I also know about the implementation of this extension for MySQL DBMS, but in the process of searching for information I had a strong opinion that the implementation of the extension for PostgreSQL works better and with fewer errors, although I could be mistaken.
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 PostgreSQL for storing information. All that needs to be done is to save the latitude and longitude data received from the user in a geometry type column, and then you can efficiently work with this information using SQL queries.
Source: https://habr.com/ru/post/304328/
All Articles