there is one thing I do not understand when using the following Query in geometries which are all SRID 4326.
WITH s AS (SELECT * FROM de_sim_points_start WHERE id = 6545)
SELECT e.*, ST_Distance(e.geom, s.geom, true) as distance FROM de_sim_points_end AS e, s
WHERE ST_DWithin(e.geom, s.geom, 10000)
AND e.parent_geometry = s.parent_geometry
ORDER BY distance
Result looks like
ID;parent_geometry;geom;distance
14033;"092730137137";"0101000020E6100000FE6FACF6AA8027409C2C8D9A0C744840";0.245261803286329
14031;"092730137137";"0101000020E6100000CCDBF35375812740825574F5F8734840";0.243900901309088
14029;"092730137137";"0101000020E6100000DCEE415F6A812740F7F2A95305744840";0.243893460499341
14026;"092730137137";"0101000020E61000003510CD8FB781274033F5CA0231744840";0.243011470475221
13535;"092730137137";"0101000020E610000039E0FBE271802740C6906BE8D6754840";0.242827846195593
14008;"092730137137";"0101000020E610000020C337BB18822740C5250EA2F3734840";0.242728103270343
13999;"092730137137";"0101000020E61000002A9807A3668227409825450CE4734840";0.242264477969627
13533;"092730137137";"0101000020E6100000C8AB5D1715812740968D666FCD754840";0.241653901891845
As far as I understand, distance results in SRID 4326 are in meters (I'm totally fine with that). So how can a point be just centimetres away from each other, when in reality they are serval meters away from each other? Image below uses point with id 14033 (orange)
EDIT: New Query with accepted answer looks like this and performs as expected
WITH s AS (SELECT * FROM de_sim_points_start WHERE id = 6545)
SELECT e.*, ST_Distance(e.geom::geography, s.geom::geography) as distance FROM de_sim_points_end AS e, s
WHERE ST_DWithin(e.geom::geography, s.geom::geography, 10000)
AND e.parent_geometry = s.parent_geometry
AND ST_Distance(e.geom::geography, s.geom::geography) > 2000
ORDER BY RANDOM()
LIMIT 1