1

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?

Jim Jones
  • 18,404
  • 3
  • 35
  • 44
regShank
  • 215
  • 2
  • 12

2 Answers2

4

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));
JGH
  • 15,928
  • 4
  • 31
  • 48
  • very well put observations! +1 Regarding point 3 I also added a link to another answer (one of many about distances), and I agree: `ST_DWithin` would be a much better choice. cheers – Jim Jones Jul 16 '20 at 13:48
  • @JGH Thanks a lot for the suggestion. How would the query look based on the changes you suggested. I am new to Postgres have a hard time trying to figure this out? – regShank Jul 16 '20 at 16:54
  • 1
    @JGH Thanks for the query! One last question, what is the format in which I should insert lat,long values into Address.location which is a geography field. – regShank Jul 18 '20 at 22:26
1

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.

Jim Jones
  • 18,404
  • 3
  • 35
  • 44
  • Thanks Jim for the suggestion but now I am getting- ERROR: relation "offers" does not exist LINE 2: FROM Offers, Address ^ SQL state: 42P01 Character: 36 – regShank Jul 16 '20 at 11:42
  • @regShank does the table `offers` exist? – Jim Jones Jul 16 '20 at 11:44
  • Sql query I got from pgadmin for the table - CREATE TABLE public."Offers" ( id integer NOT NULL DEFAULT nextval('"Offers_offerId_seq"'::regclass), CONSTRAINT "Offers_pkey" PRIMARY KEY (id), CONSTRAINT "Offers_addressId_fkey" FOREIGN KEY ("addressId") REFERENCES public."Address" (id) MATCH SIMPLE ON DELETE RESTRICT, CONSTRAINT "Offers_vendorId_fkey" FOREIGN KEY ("vendorId") REFERENCES public."Vendors" (id) MATCH SIMPLE ON UPDATE RESTRICT ON DELETE RESTRICT ) – regShank Jul 16 '20 at 11:48
  • @regShank the error message is actually quite clear. It is not finding the table `offers` in your session. Could it be that have multiple sessions to multiple databases and you're mixing the connections? You may also explicitly add the schemas to the tablenames, e.g. `public.offers`. – Jim Jones Jul 16 '20 at 11:52
  • The issue seems to be because of mixed cases and schema name, when I run the query like this it works ; SELECT A.id, B.id FROM "public"."Offers" A, "public"."Address" B WHERE ST_Distance( ST_SetSRID(ST_MakePoint(12.9406589,77.6185572),3857), ST_Transform( B.location::Geometry, 3857)) < 100 AND B.id = A."addressId" – regShank Jul 16 '20 at 11:55
  • is there a way to avoid " by changing any settings in postgre? – regShank Jul 16 '20 at 11:58
  • 1
    You can change your table names to something that does not require quoting using `ALTER TABLE public."Offers" RENAME TO offers;` as long as you don't break any dependencies, where someone else expects the table to be actually named `Offers`. You do not need to quote `public` - it is lowercase already. [PostgreSQL folds unquoted identifiers to lowercase](https://www.postgresql.org/docs/12/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS). – Zegarek Jul 16 '20 at 15:09