8768 sujets

Développement web côté serveur, CMS

[Résolu] [Postgres] Récupérer des données proches d’une coordonnée géo

Bonjour a tous. Je sais que le titre est un peu vide donc je vais directement expliquer mon problème

je travail actuellement sur une application d'annonces immobilières et on voudrais qu'a partir d'une coordonnée donnée (lon/lat recuperée via google map) on retrouver les annonces postées dans un rayon donné (20 mètre par exemple)

je sais qu'avec elasticsearch c'est faisable, j'avais vu ça un jour dans leur documentation mais bon, le projet est fait avec une base de données relationnelle et la migration n'est pas prête à s'opérée pour le moment.

le backend est fait en nodejs (express) et a postgresql comme SGDB (j'utilise Sequelize comme ORM)

j'ai ce schema relationnel:

Annonce( --id_annonce, titre, description, prix)
Adresse(--id_adresse, #id_annonce, pays, ville, lon, lat)

Je voudrais que si l'utilisateur entre une position (X, Y) je retrouve les annonces situées dans un perimetres proches de cette position

Merci pour votre aide
Modérateur
Bonjour,

Apparemment, les longitudes et les latitudes sont déjà dans ta base.

Un select SQL suffit pour aller récupérer la liste des adresses concernées.

Où est la difficulté pour toi ?

Amicalement,
parsimonhi a écrit :
Bonjour,

Apparemment, les longitudes et les latitudes sont déjà dans ta base.

Un select SQL suffit pour aller récupérer la liste des adresses concernées.

Où est la difficulté pour toi ?

Amicalement,


Les coordonnées sont dans la base c'est vrai mais il ne s'agit pas de recuperer les enregistrements ayant les coordonnées entrées (genre SELECT * .... WHERE lon = $lon AND lat = $lat) mais plutot les enregistrements situées dans un perimètre de 20 mètre à la ronde des coordonnées entrées

Il s'agit ici de trouver les annonces dont le couple Lat/Lon est dans à plus ou moins X mètres qu’un couple Lat/Lon donné
Bonjour,

Sous postgre, tu as l'extension postgis qui te permet de faire des requêtes géographiques.
Ensuite, tu as la fonction st_distance qui te renvoie la distance entre 2 géométries ... des points par exemple.

Voilà.
J'ai trouvé une solution qui semble marchée.


const post = req.body, 
	filter = post.filter, 
	coordinates = filter.coordinates;
let where_adresse = {}, 
	where = {publish : true};
const distance = 1000;


if (coordinates && coordinates instanceof Array) {
	const lon = parseFloat(coordinates[0]), lat = parseFloat(coordinates[1]);

	where_adresse = Sequelize.where(
		Sequelize.literal(`6371 * acos(cos(radians(${lat})) * cos(radians(lat)) * cos(radians(${lon}) - radians(lon)) + sin(radians(${lat})) * sin(radians(lat)))`),
		'<=',
		distance,
	);
}

let annonces = await db.Annonce.findAll({
	where,
	include: [
		{model: db.Caracteristique, as: 'caracteristiques'},
		{model: db.Photo, as: 'photos'},
		{model: db.Utilisateur, as: 'utilisateur'},
		{model: db.Adresse, as: 'adresse', where: where_adresse},
	]
});


https://stackify.dev/396952-geospatial-distance-calculator-using-sequelize-mysql
Meilleure solution
Modérateur
Bonjour,

Bah, c'est juste une formule à mettre dans le select. Il faut par exemple vérifier la condition suivante pour trouver un point à l'intérieur d'un cercle de diamètre r (où lon1 et lat1, c'est ce qu'il y a dans ta base de données, et lon2 et lat2, c'est la position à partir de laquelle on fait la recherche) :
(lon1-lon2)*(lon1-lon2) + (lat1-lat2)*(lat1-lat2) > r * r

Tu peux aussi faire des comparaisons plus simple en cherchant dans un carré où chaque côté a une longueur d :
(ABS(lon1-lon2) < d) AND (ABS(lat1-lat2) < d)

Pour écrire le sélect de manière détailler, il faudrait savoir de quel type sont les champs lon et lat (est-ce que ce sont des float ?). Il faut convertir les nombres en unité comparable. Si tu te contentes d'une comparaison approximative, tu peux dire que (très) approximativement, 1° de longitude ou latitude = 100 km (mais en fait, ça dépend où on se trouve sur le globe terrestre : plus on va vers le nord, plus cette distance est courte et plus on va vers le sud, plus cette distance est longue). Si tu veux un calcul exact, la formule est sensiblement plus complexe.

EDIT : nos réponses se sont croisées. Je vois que tu as trouvé la formule complexe. Tout baigne donc ! Smiley cligne

Amicalement,
Modifié par parsimonhi (07 Feb 2022 - 16:09)
loicbcn a écrit :
Bonjour,

Sous postgre, tu as l'extension postgis qui te permet de faire des requêtes géographiques.
Ensuite, tu as la fonction st_distance qui te renvoie la distance entre 2 géométries ... des points par exemple.

Voilà.


Merci pour ta reponse.

Je n'ai pas postgis, c'est vrai que ça aurai été plus facile avec ça étant donné qu'il gère les données spatiales mais bon... je fais avec les moyens de bord Smiley biggrin

j'ai néanmoins vu un article pour çà que j'ai pu adapté à mon code et ça semble marché jusqu'ici Smiley smile

https://stackify.dev/396952-geospatial-distance-calculator-using-sequelize-mysql

j'ai utilisé la solution 2
Tant mieux si ça fonctionne, avec peu de lignes en plus ... Bravo.

Quant à postgis, c'est gratuit, mais cela dépend de l'environnement de travail.

Bonne continuation.
Sujet clos
Aller à