2

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?

6
  • 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
  • Something like As wgs84long_lat for SetSRID?
    – Ayron
    Sep 12 '18 at 16:41
  • And the ::geography in connection with my table? like osm_point.way (way is my geometry)
    – Ayron
    Sep 12 '18 at 16:42
  • 1
    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 gis.stackexchange.com/questions/247113/…
    – Vince
    Sep 12 '18 at 17:08
5

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.