2

I have a MultiPoint geometry column which has only one point (DB is a live server which cannot be changed now).

I need to find all points within a certain distance from a fixed point I provide to the query.

I tried to extract a point from the MultiPoint and use that as data for the ST_DWithin query but I still get a "Operation on mixed SRID geometries" error.

My query is as below:

SELECT o.id, ST_AsText(o.geom)
FROM table AS o
LEFT JOIN (
    SELECT id, ST_GeometryN(geom, 1) AS pt
    FROM table
) AS p
ON o.id = p.id 
WHERE ST_DWithin(p.pt, ST_MakePoint(-112.779861,52.193760)::geography, 1000);
4
  • And what do you get: ST_DWithin((p.pt)::geography, ST_MakePoint(-112.779861,52.193760)::geography, 1000). Mar 6, 2020 at 6:29
  • Tried this as well. Same Error. Mar 6, 2020 at 6:37
  • In what SRID do you have the original geometry? You need to lead either to the type of geography or to the type of geometry... Mar 6, 2020 at 7:03
  • And don't forget to accept your answer, as he's solved your question... Mar 6, 2020 at 7:10

1 Answer 1

0

I found the solution and was nothing but setting the SRID to the geometry. Since, setting the SRID does not change the geometry itself, this works.

SELECT o.id, ST_AsText(o.geom)
FROM table AS o
LEFT JOIN (
    SELECT id, ST_SetSRID(ST_GeometryN(geom, 1), 4326) AS pt
    FROM table
) AS p
ON o.id = p.id 
WHERE ST_DWithin(p.pt::geography, ST_MakePoint(-112.779861,52.193760)::geography, 1000);
2
  • 1
    ST_Dwithin happily accepts a Multi geometry; if there's only a single Point in your MultiPoint, the results will be the same, plus it can use the spatial index on the column!
    – geozelot
    Mar 6, 2020 at 8:32
  • Indeed, all features should have the correct SRID encoded in the geometry itself, not applied during extract.
    – Vince
    Mar 6, 2020 at 10:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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