Search code examples
postgresqlgoogle-mapspostgis

Postgres, Postgis (ST_DWithin and radius)


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

enter image description here

But in this picture, the store is not included in the radius of the circle, but gets the store from the database

enter image description here

Update:

I use user coordinates like

{
    position: {
            type: "Point",
            coordinates: [
                50.444327898,
                30.505305436
            ]
        }
}

Solution

  • 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;
        `;