Postgis disconnecting during some queries

Question

I have two tables one in SRID 4326 (containing MULTIPOLYGON) and the other in SRID 32188 (containing MULTILINESTRING).

I am trying to select all polygons from t1 which are within 100 meters from any lines in t2.

This is the query I am making:

SELECT * FROM table1 AS t1, table2 AS t2 
WHERE ST_DWithin(
        ST_Transform(t1.geom, 3857),
        ST_Transform(t2.geom, 3857),
        100);

Unfortunately, it doesn't work, and pgAdmin4 doesn't give me a useful error. Instead, it disconnects from the DB as soon as I run the query (so it's not a time out situation):

I also tried with two tables in the same SRID, but I still have the problem... What am I doing wrong here?

EDIT: In order to try an narrow down the problem, I took @JGH suggestion and adapted it to test ST_DWITHIN against a point instead of the multilinestrings of table2, and it works fine.

SELECT * FROM table1 t1
WHERE EXISTS (
    SELECT FROM table2 t2
    WHERE ST_DWithin(t1.geom, ST_MakePoint(-73.63,45.52)::geography,100)
);

However, I tested with table2 containing only one straight line (still a multilinestrings geometry though), and the client crashes. Could it bee related to this: https://gis.stackexchange.com/questions/31923/why-is-the-database-connection-lost-on-every-query ?

JGH JGH · Accepted Answer · 2020-02-16T02:27:48

Your client is probably timing out.

As it is, you are doing a cross-join between the two tables, so if a polygon from t1 is within 100m of 10 features in t2, it will be returned 10 times. You likely want distinct polygons from t1. to do so, you would select only on t1 and keep the row when the condition applied on t2 is satisfied.

The second issue - which won't trigger any error unfortunately - is that it is wrong to compute distances using 3857. In Québec the length distortion is about 40%, which is huge. You would have to either use a suitable local projection (like MTM or UTM) for both geometries, or, if not possible, to cast to geography to do the distance computation.

And because you do transform both geometries to a new CRS, no spatial index solely on the geometries can be used and the distance computation will take a long, long time, causing your client to time out.

SELECT *
FROM   table1 t1
WHERE  EXISTS (
   SELECT FROM table2 t2
   WHERE  ST_DWithin(t1.geom::geography, ST_Transform(t2.geom, 4326)::geography, 100)
   );

Don't forget to create a spatial index on the geography (and/or on the transformed geometry)

CREATE INDEX t1_geog ON t1 USING gist(geography(t1.geom);

or

CREATE INDEX t1_32188 ON t1 USING gist(st_transform(geom,32188));

Postgis disconnecting during some queries

1 Answers