ST_DWithin query throwing 42601 error?

Question

I am trying to get from a particular point more informations about the surroundings (which are also points). So I wrote this:

SELECT *
   FROM osm_point
   WHERE ST_DWithin(way,(ST_SetSRID(ST_MakePoint(12.2385578688105, 51.8435615931295), 4326, 1000);

But there is an error

ERROR: FEHLER: LINE 3: ...T_MakePoint(12.2385578688105, 51.8435615931295), 4326, 1000) ^ SQL state: 42601 Character: 133

What is wrong?

Missing closing paren for ST_SetSRID and a missing ::geography cast after the missing paren. way is also missing a geography cast. — Vince, Sep 12 '18 at 16:37
And the ::geography in connection with my table? like osm_point.way (way is my geometry) — Ayron, Sep 12 '18 at 16:42
If you don't cast both geometry values to geography, ST_DWithin will return all rows within 1000 Cartesian degrees — Vince, Sep 12 '18 at 16:58
In order to leverage a GIST index you need to create one on "Geography(way)". see also https://gis.stackexchange.com/questions/247113/how-to-properly-set-up-indexes-for-postgis-distance-queries/247131#247131 — Vince, Sep 12 '18 at 17:08
ohh now i understand nothing anymore ... maybe i have to read a bit more about spatial querys to continue :) — Ayron, Sep 12 '18 at 17:17

score 5 · Accepted Answer · edited Nov 23 '20 at 20:48

The SQL error 42601 is a syntax error (see here). Basically, you had an extra opening parenthesis before ST_SetSRID and also a closing missing parenthesis for the same function.

If data type of osm_point.way column is geometry, one needs to cast it to geography in order to ST_DWithin evaluate to geodetic distances (instead of planar distances) and in meters (if units of spatial reference aren't already in meters). The same cast to geography is valid for ST_MakePoint which output is of type geometry and the units of applied CRS 4326 is degrees, but you want meters.

So, use the following query:

SELECT *
FROM osm_point
WHERE ST_DWithin(way::geography, ST_SetSRID(ST_MakePoint(12.2385578688105, 51.8435615931295), 4326)::geography, 1000);

When using ST_DWithin one needs to compare data which have the same SRID. So, if column osm_point.way has a different SRID (you said EPSG:3857) from EPSG:4326 (the SRID you used to define the coordinates with ST_MakePoint), one needs to transform coordinates first. For that use ST_Transform. See:

SELECT * 
FROM osm_point 
WHERE ST_DWithin(ST_Transform(way, 4326)::geography, ST_SetSRID(ST_MakePoint(12.2385578688105, 51.8435615931295), 4326)::geography, 1000);

About using indexes to speed up your queries, as suggested by @Vince, take a look in:

Setting up indexes for PostGIS distance queries.

ST_DWithin query throwing 42601 error?

1 Answers1

Linked

Related