Finding objects intersecting with polygons when buffered in meters using PostGIS

Question

I'm using the following code to get to know, for each approximate neighbourhood, the name of all hotels inside.

The neighbourhood has no precise boundaries, but it's rather represented by a buffer around each neighbourhood which is just a point data.

Both neighbourhood and points of interest (it.poi.accomodation) are in EPSG:4326

The following code is working, but it's calculating the radius of the buffer in grades. I would need to set meters instead. I read about st_transform but i'm not at ease with it. I envision replacing a.wkb_geometry of it_neighbourhood by ST_DWithin(ST_Transform(a.wkb_geometry, 4326) but I don't believe it should work.

WITH crossing AS(
SELECT a.name, a.id, substr(string_agg(
COALESCE(b.name,'') || '§' || b.id || '§' || COALESCE(b.tourism, '') || '§' || COALESCE(b.tourism_1, '') || '§' || COALESCE(b.leisure, ''), ','),1,25490)
as pgaccomodation FROM public."it_neighbourhood" a
INNER JOIN public."it.poi.accomodation" b
ON ST_DWithin(b.wkb_geometry, a.wkb_geometry, 0.006)
GROUP BY a.name, a.id)
UPDATE public."it_neighbourhood" tpb
SET pgaccomodation = c.accomodation
FROM crossing c WHERE tpb.id = c.id";

i'm trying

WITH crossing AS(
SELECT a.name, a.id, substr(string_agg(COALESCE(b.name, '') || '§' || b.id || '§' || COALESCE(b.amenity, '') || '§' || COALESCE(b.amenity_1, ''), ','),1,25490) 
as pgaccomodation FROM public."it_neighbourhood" a
INNER JOIN public."it.poi.accomodation" b
ON ST_DWithin((ST_Transform(b.wkb_geometry,4326),3857), (ST_Transform(a.wkb_geometry,4326),3857), 1) 
GROUP BY a.name, a.id) 
UPDATE public."it_neighbourhood" tpb
SET pgaccomodation = c.pgaccomodation
FROM crossing c
WHERE tpb.id = c.id

with an error:

ERROR: syntax error at or near "," LINE 1: ...metry,4326),3857), (ST_Transform(a.wkb_geometry,4326),3857),.

the mark "^" indicating the error being on 4326.

• JGH: ... but don't use 3857 for any distance measurement, this projection highly distorts distances as you move away from the equator
• JGH: for the st_transform attempt, it seems you would have to set the CRS first, then transform. As it is you are attempting to transform the point to 4326, and then you are creating a tuple with the point and the number 3857, which is invalid for st_dwithin. It would like ST_Transform(st_setsrid(a.wkb_geometry,4326),3857) (but don't do it, see previous comment :)

Answer 1

You can use the version of ST_Dwithin that takes two geography, so the distance is expressed in meters.

ST_DWithin(b.wkb_geometry::geography, a.wkb_geometry::geography, 5000)

To be efficient, you would also have to create an index on the cast

create index it_neighbourhood__geogidx on public."it_neighbourhood" USING gist((wkb_geometry::geography));
create index it_poi_accomadation__geogidx on public."it.poi.accomodation" USING gist((wkb_geometry::geography));

• Vincent Dc: thank you for answering quickly! i'm trying all this but the index should'nt have the same index name, maybe ? i'm getting this error on creating the second index: "ERROR: relation "geogidx" already exists"
• JGH: yes, that's right, index names must be unique
• Vincent Dc: thanks. the code looks to be working, but I've been receptive to your advice that Pseudo mercator, EPSG:3857 is distorting too much the data the more we're heading to the north (it's very much sensible when looking Google maps for Greenland, where Greenland is looking bigger than it might really be, comparing to Africa, maybe) so how could i set data to another CRS, for example for France: EPSG:2154 , called Lambert93 RGF93?
• Vincent Dc: Maybe this is ok, but it says I have "no space left on device"... WITH croisement AS( SELECT a.name, a.id, substr(string_agg(COALESCE(b.name, '') || '§' || b.id || '§' || COALESCE(b.amenity, '') || '§' || COALESCE(b.amenity_1, ''), ','),1,25490) as pgbarrest FROM public."fr_quartier" a INNER JOIN public."fr.poi.barrest" b ON ST_DWithin(ST_Transform(st_setsrid(a.wkb_geometry,4326),2154), ST_Transform(st_setsrid(a.wkb_geometry,4326),2154), 50) GROUP BY a.name, a.id) UPDATE public."fr_quartier" tpb SET pgbarrest = c.pgbarrest FROM croisement c WHERE tpb.id = c.id
• JGH: :-) it's time to get a bigger hard drive then