Telegram bot PostGIS and
About implementing bots for the Messager Telegram on the website it was already quite a lot of posts. But there is one topic that, in my opinion, has not been affected. It is the realization of work with location inside the bot. In this post I will give an example of how to handle the bot information about the location sent by the user, based on his own experience of the implementation of the bot aroundus_bot.
the fact that Messager Telegram provides a rather convenient way for working with geolocation. The user can send their current position to send any other point on the map (for mobile devices, the attachment Location), or to send the address and the location of any place (restaurants, parks, squares...) using the bot foursquare.
What can you do with this information next? There are different options. This can be the implementation of quests (bot will send the next point where you need to follow the users, or gives any information based on user location), and search anything the closest (toilets, bars, restaurants). For example, I implemented the bot allows you to send a story that is bound to the user-specified location on the map, with the subsequent opportunity to consider all the stories that were sent by users, in some neighborhood of any given point.
The location comes in the form of points on the map with latitude and longitude. Intuitively, what to do with this information — we just save the coordinates in a database, and then, when the user requests history of a specified neighborhood, just find all the stories that fall into this realm.
But latitude and longitude are geographic coordinates that define the location of the object on the earth's surface. And as you know, the earth is an ellipsoid. And there is no way just to add to the coordinates of, say, 100 meters, and to say that this is the edge of the area in which you need to look for history. It is necessary to compute the neighborhood of a point in spherical coordinates.
I have long thought that this can be done so as to reinvent the wheel and implement their curves calculation is improper. I wanted to use a ready tool, which would allow at least to calculate the distance in meters between two geographic points.
In my search, I came across a post published on this website. And realized that made absolutely the right choice of DBMS, which is used to store information necessary for the bot — PostgreSQL. Briefly, the PostGIS extension, which can be supplied for this database allows you to work efficiently with geographic coordinates using special functions in ordinary SQL query. Read more about PostGIS can be found at the link.
Thus, the sample of all stories from the neighbourhood of any point (or search for the nearest anything relative to a given point) is to implement the SQL query that through the use of GiST indexes for coordinates, will be very quick. But it should be noted that for effective use of the extension, the location data must be stored in a column with a special geometry, which then need to hang the index. In its implementation for ease of use I store the data as separate latitude and longitude values in double format and column format to a geometry.
Example of a sql query to search something in a specified neighborhood (e.g., within 100 meters) can be:
the
where:
coordinates — a column of type geometry
x, y is the latitude and longitude of the point, the neighborhood where we are interested in.
I should say that I also know about the implementation of this extension for MySQL, but in the process of searching information I got the strong opinion that the implementation of the extension for PostgreSQL works better and with fewer errors, although I could be wrong.
If there is a need to implement bot for Telegram, which should work with geographic coordinates, then a good way would be to use PostgreSQL database for storing information. All you need to do is to keep the received from the user, the latitude and longitude in a column of type geometry, and then you can effectively work with this information, in terms of SQL queries.
Article based on information from habrahabr.ru
the fact that Messager Telegram provides a rather convenient way for working with geolocation. The user can send their current position to send any other point on the map (for mobile devices, the attachment Location), or to send the address and the location of any place (restaurants, parks, squares...) using the bot foursquare.
What can you do with this information next? There are different options. This can be the implementation of quests (bot will send the next point where you need to follow the users, or gives any information based on user location), and search anything the closest (toilets, bars, restaurants). For example, I implemented the bot allows you to send a story that is bound to the user-specified location on the map, with the subsequent opportunity to consider all the stories that were sent by users, in some neighborhood of any given point.
The location comes in the form of points on the map with latitude and longitude. Intuitively, what to do with this information — we just save the coordinates in a database, and then, when the user requests history of a specified neighborhood, just find all the stories that fall into this realm.
Problem
But latitude and longitude are geographic coordinates that define the location of the object on the earth's surface. And as you know, the earth is an ellipsoid. And there is no way just to add to the coordinates of, say, 100 meters, and to say that this is the edge of the area in which you need to look for history. It is necessary to compute the neighborhood of a point in spherical coordinates.
I have long thought that this can be done so as to reinvent the wheel and implement their curves calculation is improper. I wanted to use a ready tool, which 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 this website. And realized that made absolutely the right choice of DBMS, which is used to store information necessary for the bot — PostgreSQL. Briefly, the PostGIS extension, which can be supplied for this database allows you to work efficiently with geographic coordinates using special functions in ordinary SQL query. Read more about PostGIS can be found at the link.
Thus, the sample of all stories from the neighbourhood of any point (or search for the nearest anything relative to a given point) is to implement the SQL query that through the use of GiST indexes for coordinates, will be very quick. But it should be noted that for effective use of the extension, the location data must be stored in a column with a special geometry, which then need to hang the index. In its implementation for ease of use I store the data as separate latitude and longitude values in double format and column format to a geometry.
Example of a sql query to search something in a specified neighborhood (e.g., within 100 meters) can be:
the
ST_Distance(ST_Transform(coordinates, 26986), ST_Transform(ST_SetSRID(ST_MakePoint(x, y), 4326), 26986)) < 100
where:
coordinates — a column of type geometry
x, y is the latitude and longitude of the point, the neighborhood where we are interested in.
I should say that I also know about the implementation of this extension for MySQL, but in the process of searching information I got the strong opinion that the implementation of the extension for PostgreSQL works better and with fewer errors, although I could be wrong.
Output
If there is a need to implement bot for Telegram, which should work with geographic coordinates, then a good way would be to use PostgreSQL database for storing information. All you need to do is to keep the received from the user, the latitude and longitude in a column of type geometry, and then you can effectively work with this information, in terms of SQL queries.
Комментарии
Отправить комментарий