CODEWITHSUNDEEP
sqlpostgresqlpostgis
regShank
regShank

Reputation: 235

POSTGIS Get nearest locations query

I am trying to get all offers located near a user within a distance of 100kms. The user shares the location and I query it in Offers Table - id, name, text, addressId (addressId is the foreign key)

Address Table - id, address, location.

Select Offers.id, Address.id 
from Offers, Address 
where 
  ST_Distance(
    ST_Transform( [12.9406589,77.6185572] ]::Geometry, 3857),
    ST_Transform( Address.location::Geometry, 3857) < 100 
  and Address.id = Offers.addressId

When I run the above query, it's throwing a syntax error:

Postgres syntax error near "]"

How else should I give the coordinates?

Upvotes: 1

Views: 1752

Answers (2)

JGH
JGH

Reputation: 17906

While JimJones answers covers how to create a point, there are several other issues:

  1. st_distance uses the unit of the CRS, which is meters for 3857. You are searching for entries within 100m of the said location
  2. 3857 in a projection used for DISPLAY only. Don't compute distance in this projection, they are highly distorted. Either use a suitable local projection, or use geography instead of geometry, which uses meters by default
  3. Idealy you would have an index on your geometry/geography, and then you can use ST_DWithin instead of ST_Distance, as the former makes uses of the spatial index
  4. If your data is in India rather than the far north, it means you have swapped latitude and longitude when creating the point

The query could become similar to

SELECT Offers.id, Address.id 
FROM Offers 
  JOIN Address ON Address.id = Offers.addressId
WHERE 
 ST_DWithin(ST_MakePoint(77.6185572, 12.9406589)::geography,
    Address.location::Geography, 100000);

with an index on the geography

CREATE INDEX geogidx ON Address USING GIST((location::geography));

Upvotes: 4

Jim Jones
Jim Jones

Reputation: 19653

Have you tried combining ST_MakePoint and ST_SetSRID to create your point?

SELECT Offers.id, Address.id 
FROM Offers, Address 
WHERE 
 ST_Distance(
   ST_SetSRID(ST_MakePoint(12.9406589,77.6185572),3857),
   ST_Transform( Address.location::Geometry, 3857)) < 100 AND 
Address.id = Offers.addressId

Note: The function ST_MakePoint expects the parameters as longitude,latitude, not the other way around.

See also this related answer.

Upvotes: 1

Related Questions