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 '20 at 6:29
  • Tried this as well. Same Error. Mar 6 '20 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 '20 at 7:03
  • And don't forget to accept your answer, as he's solved your question... Mar 6 '20 at 7:10
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 '20 at 8:32
  • Indeed, all features should have the correct SRID encoded in the geometry itself, not applied during extract.
    – Vince
    Mar 6 '20 at 10:25

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.