Hi, I have to find all street intersections, I wrote below code but it returns duplicate rows. Does anyone have an idea why or khnows how to correct it!! Thank you for your help.
DROP TABLE IF EXISTS toto;
CREATE TABLE toto(
gid serial primary key,
nom_voie1 varchar(50),
nom_voie2 varchar(50),
geom_inter geometry(Geometry,4326)
);
CREATE INDEX ON toto using gist (geom_inter);
INSERT INTO toto(nom_voie1, nom_voie2, geom_inter)
SELECT a.nom_voie, b.nom_voie, ST_Intersection(a.geom, b.geom)
FROM reseau_routier AS a , reseau_routier AS b
WHERE ST_Intersects(a.geom,b.geom)
AND ST_Touches(a.geom, b.geom)
AND a.gid < b.gid
AND a.nom_voie <> b.nom_voie;
You are getting duplicated because of the JOIN
on reseau_routier
.
You can remove duplicated with:
INSERT INTO toto(nom_voie1, nom_voie2, geom_inter)
SELECT a.nom_voie, b.nom_voie, ST_Intersection(a.geom, b.geom)
FROM reseau_routier AS a , reseau_routier AS b
WHERE ST_Intersects(a.geom,b.geom)
AND ST_Touches(a.geom, b.geom)
AND a.gid < b.gid
AND a.nom_voie <> b.nom_voie
GROUP BY a.nom_voie, b.nom_voie, ST_Intersection(a.geom, b.geom);
But these duplicated are the sign of a malformed query. With a full data sample it would be easier to give you the perfect query.