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);
  • And what do you get: ST_DWithin((p.pt)::geography, ST_MakePoint(-112.779861,52.193760)::geography, 1000). – Cyril Mikhalchenko Mar 6 at 6:29
  • Tried this as well. Same Error. – pranshuagarwal Mar 6 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... – Cyril Mikhalchenko Mar 6 at 7:03
  • And don't forget to accept your answer, as he's solved your question... – Cyril Mikhalchenko Mar 6 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);
| improve this answer | |
  • 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 at 8:32
  • Indeed, all features should have the correct SRID encoded in the geometry itself, not applied during extract. – Vince Mar 6 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.