2

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:

CREATE VIEW test_1 AS

SELECT *

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 Nov 6 '14 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 Nov 6 '14 at 13:15
  • Is a column with a list of duplicate value IDs acceptable? – Scro Nov 6 '14 at 13:21
3

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
SELECT
  min(id) "ROWID",  -- a suitable primary key
  group_concat(id, ",") "duplicate_id_list", -- a list... Second argument is the delimiter
  val,
  val_1,
  val_2,
  GUnion("Geom") "Geom"
FROM
  the_table
GROUP BY
  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, privacy policy and cookie policy

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