Today I want to make a small post and talk about another annoying problem: Duplicated Geometries!
How can you identify which geometries are duplicated in your table? In some cases we have duplicated geometries with different attributes, which one is the correct one?
To deal with this, a simple query can help you in this matter.
Concepts to keep:
- PARTITION BY: Is a window function that receives a column, this means it will partition the column in groups with equal values (this is what we want for our “geom” column: duplicates!)
- ROW_NUMBER(): gives a row number, inside a partition, counting from 1. This means that any number greater than 1 is a duplicate.
Here it is:
select * from (
SELECT id, ROW_NUMBER() OVER(PARTITION BY geom ORDER BY id asc) AS Row,
geom FROM ONLY your_schema.your_table
) dups where dups.Row > 1
Now you can check the duplicates and choose which ones you want to keep.
We all know that search for errors in geometries can be quite a journey. One of the errors we need to fix is the presence of spikes in our geometries. One way to determine the location of those spikes is to determine the angles and check if them are smaller than a predefined threshold.
Ok them, one way to tackle this problem is to load your data into a PostGIS layer and use the available ST functions.
In this post I’ll show you guys a SQL query to solve this.
Let’s suppose we have geometries with problems like these here:
To solve this problems we will use the following ST PostGIS functions:
For more information about these functions check this: http://postgis.net/docs/manual-1.3/ch06.html
The following query can show us where the problems are located, let’s use a limit of 10 degrees here to determine the spikes:
WITH result AS (SELECT points.id, points.anchor, (degrees
ST_Azimuth(points.anchor, points.pt1) - ST_Azimuth(points.anchor, points.pt2)
)::decimal + 360) % 360 as angle
ST_PointN(geom, generate_series(1, ST_NPoints(geom)-1)) as pt1,
ST_PointN(geom, generate_series(1, ST_NPoints(geom)-1) % (ST_NPoints(geom)-1)+1) as anchor,
ST_PointN(geom, generate_series(2, ST_NPoints(geom)) % (ST_NPoints(geom)-1)+1) as pt2,
linestrings.id as id
(SELECT id as id, ST_Boundary((ST_Dump(ST_ForceRHR(geom))).geom) as geom
FROM only my schema.mylayer -- enter your layer name here
) AS linestrings WHERE ST_NPoints(linestrings.geom) > 2 ) as points)
select distinct id, anchor, angle from result where (result.angle % 360) < 10 or result.angle > (360.0 - (10 % 360.0)) -- the 10 here is our threshold
With the results from this query we can locate our spikes using the “anchor” value returned in the query. Something like this:
Very nice right?
Someone has given you a logical model with over 500 tables to implement it in PostgreSQL, where to begin? Should I cry first? Should I just start typing create table statements like there is no tomorrow? No! I present you pgModeler!
pgModeler is a modelling open software developed by Raphael Araújo e Silva and it allows you to build a database just using a nice graphical interface and after you finish your design, you can just deploy it and there you go! Your database is ready!
For instance, suppose you have a table called road with an text attribute “name”, geometry column “geom” with type MultiLinestring, with epsg 4326. To implement this table you just have to click around and there you go, you have the following table:
To deploy this, just go to export and choose between .png, sql or direct deploy into postgres. The sql for the table above is:
CREATE TABLE public.road(
id serial NOT NULL,
geom geometry(MULTILINESTRING, 4326) NOT NULL,
CONSTRAINT road_pk PRIMARY KEY (id)
Another great feature of pgModeler is reverse engineering! Just define a database connection and it shows you all tables and relationships.
By the way, I was the guy that was given the huge database to implement and if wasn’t by pgModeler, I think I’d still be crying… =]