I have a request that looks like this
const query = `SELECT *, ST_AsGeoJSON(position)::json as position
FROM shops
WHERE ST_DWithin(
ST_SetSRID(ST_MakePoint(${_user.position.coordinates[0]}, ${
_user.position.coordinates[1]
}), 4326)::geography,
position,
${user.radius * 1000}
);`;
And there is a store on the map (which is exactly within the radius of the circle on Google maps). However, when updating the user's position, I don't get the nearest store within that radius. (But if you get a little closer, the store appears)
I store position data as
@Column({
type: 'geography',
spatialFeatureType: 'Point',
srid: 4326,
nullable: true
})
position: Point;
I've tried to use this
const query = `SELECT *, ST_AsGeoJSON(position)::json as position
FROM shops
WHERE ST_DWithin(ST_MakePoint(${_user.position.coordinates[0]}, ${
_user.position.coordinates[1]
})::geography, ST_SetSRID(position::geometry, 4326)::geography, ${
_user.radius * 1000
});`;
But it works as code above
I understand that there are some changes in the geography and geometry of the circle, but it is unlikely that this can affect a circle with a radius of one kilometer
the picture shows that this is the maximum distance at which I can get this store
But in this picture, the store is not included in the radius of the circle, but gets the store from the database
Update:
I use user coordinates like
{
position: {
type: "Point",
coordinates: [
50.444327898,
30.505305436
]
}
}
I finally solved this problem after a while. I started using ST_Distance to get the nearest stores. I actually made a mistake when I swapped the lattitude and longitude of both the user and the store. So now the solution looks like this
const query = `
SELECT *,
ST_AsGeoJSON(position)::json as position,
ST_Distance(
ST_SetSRID(ST_MakePoint(${_user.position.coordinates[1]}, ${
_user.position.coordinates[0]
}), 4326)::geography,
ST_MakePoint(ST_Y(position::geometry), ST_X(position::geometry))
) as distanceToUser
FROM shops
WHERE ST_Distance(
ST_MakePoint(ST_Y(position::geometry), ST_X(position::geometry)),
ST_SetSRID(ST_MakePoint(${_user.position.coordinates[1]}, ${
_user.position.coordinates[0]
}), 4326)::geography
) <= ${_user.radius * 1000}
ORDER BY distanceToUser;
`;