Find and select duplicate values in your vector attribute table

Suppose you have a vector layer in your GRASS GIS database and you want to find all duplicate values in one of the columns of the attribute table of that layer?

If you are using SQLite or PostgreSQL as the database back-end (and you should), this is easy using a SQLite statement. Suppose you have the table sp1th and the column value with duplicate values:

SELECT value, COUNT(value) AS dbl
FROM sp1th
GROUP BY value
HAVING (COUNT(value) > 1)

The results shows you for each duplicate value how many times it is found in your table. If you want to select all the records that are double instead of the counts, you can use:

SELECT *
FROM sp1th
WHERE value IN (
SELECT value
FROM sp1th
GROUP BY value
HAVING (COUNT(value ) > 1)
)

You can do this in your favorite SQLite or PostgreSQL manager. Alternatively, you can run this in GRASS GIS using the v.db.select function:

v.db.select -c map=sp1th@ConsStat layer=1 columns=cat where='cat IN ( SELECT cat FROM sp1th GROUP BY cat HAVING (COUNT(cat) > 1))'

Or, if you want to get all the points with duplicate values in the attribute value as a new point layer:

v.extract input=sp1th@ConsStat layer=1 where='value IN (SELECT value FROM sp1th GROUP BY value HAVING(COUNT(value)>1))' output=test

With thanks to this blog from which I got most of this.

About these ads

About pvanb

I am a tropical forest ecologist with a focus on spatial and temporal patterns and processes at population and ecosystem level. I am furthermore very interested in issues related to conservation and sustainable use of biodiversity and natural resources under current and future climates. I have worked in the Middle East (Syria and Lebanon) and South America (Brazil) and in Eastern Africa (Kenya).
This entry was posted in Data handling, GRASS GIS, SQLite and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s