4

I have point geometries in a PostGIS table. I want to select only the distinct geometries from this table. If a geometrie is duplicated, it should select the first appearance in the table.

I tried to following query:

SELECT DISTINCT ON(wkb_geometry), ogc_fid from points;

But I get the error:

ERROR:  syntax error at or near ","

What is wrong with the query?

Improve this question
3
  • A note: This will compare the bounding box, not the actual geometry. So, two geometries that share the exactly same bounding box will be regarded as the same, and only 1 of them will be returned. It is much safer (and faster) to use id if you have.
    – Nicklas Avén
    Mar 19, 2015 at 9:50
  • And if you want to be sure to get the first occurrence in the table, you have to sort on something giving the right order. I don't think that the row order is guaranteed.
    – Nicklas Avén
    Mar 19, 2015 at 9:51
  • Thanks for sharing. However only their geometry is unique. In my case it is only point features. So the bounding box should be fine.
    – ustroetz
    Mar 19, 2015 at 9:52

1 Answer 1

Reset to default
5

SELECT DISTINCT ON(wkb_geometry) wkb_geometry, ogc_fid from points;

Improve this answer
1
  • 4
    This will return geometries with distinct bounding boxes, but will consider two geometries equal if they have the same bounding box. If you want distinct geometries, use SELECT DISTINCT ON (ST_AsBinary(wkb_geometry)) wkb_geometry, ogc_fid FROM points.
    – dbaston
    Jun 7, 2016 at 17:56

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.