I am running on Windows 8.1 SpatiaLite_gui 1.8.0, SpatiaLite version 4.2.0 and SQLite 3.8.5.

I need to interrogate one single spatial table whith those attributes:

ID | val | val_1 | val_2 | Geom

and I'd like to get all the IDs with the same values (val, val_1, val_2) of the starter ID.

My idea was at the first place to create a view like:



FROM table

WHERE id = 1234

And then to select all the values from "table" with the same values (val, val_1, val_2) in the view.

Is there a way to get the same result with a single query or sub-query?

  • Subquery would be preferred. See this post for why. However, it's not clear to me what you are trying to do. Are you looking for the same values across all three "val" columns?
    – Scro
    Commented Nov 6, 2014 at 12:59
  • Thank you for your reply. No, what I am trying to do is to get the same "val" as the starter ID. For example, ID=8, val=0, val_1=1, val_2=2. I want to get all the IDs that have 0, 1, 2 as vals.
    – marco89
    Commented Nov 6, 2014 at 13:15
  • Is a column with a list of duplicate value IDs acceptable?
    – Scro
    Commented Nov 6, 2014 at 13:21

1 Answer 1


This is almost not a GIS question, but you will need to aggregate your geometries. Right? Thought so. The following query does not use a subquery as I thought would be required earlier. You will simply GROUP BY the value columns. All columns not being grouped will need to be aggregated, and that's where the SpatiaLite function GUnion comes in. Note that this view will need to be added to the spatial meta tables with "ROWID" as the primary key in order to be generally available to other GIS software.

CREATE VIEW duplicate_values AS
  min(id) "ROWID",  -- a suitable primary key
  group_concat(id, ",") "duplicate_id_list", -- a list... Second argument is the delimiter
  GUnion("Geom") "Geom"
  val, val_1, val_2;

Note that the resulting "Geom" column will be a multi, i.e. if this is a point geometry it will now be a MultiPoint one.

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.