I'm trying to replicate the result of the QGIS line intersections tool in PostGIS.
The result of this tool gives me 133 intersections represented as points as depicted in the map below.
Using the same data I've tried to replicate this in PostGIS using this code
WITH select_trips AS
(
SELECT t.* FROM trips t
WHERE t.id IN (25758,24730,23875,25886,24442,26723)
)
SELECT
a.id,
st_intersection(a.the_geom_webmercator, b.the_geom_webmercator) as buffer
FROM
select_trips a
JOIN
select_trips b ON st_intersects(a.the_geom_webmercator, b.the_geom_webmercator)
My problems are two fold. 1: this only returns 36 rows, 2: when I try to add the layer using the QGIS DB manager, nothing gets added.
From what I understand st_intersection should return the shared geometry of the two lines represented as a point, so why isn't this working?
I know I can run the tool in QGIS and then import to my postgresql DB, but I'd like to be able to run the whole process using PostGIS.
UPDATE: Using this code returns some very strange results a line string that looks somewhat like the trip lines below and 1127 intersection points. (the light blue is the correct intersection points generated with QGIS line intersections tool)
SELECT
ST_Intersection(a.the_geom, b.the_geom),
a.id
FROM
trips as a,
trips as b
WHERE
st_intersects(a.the_geom, b.the_geom)
and a.id in (25758,24730,23875,25886,24442,26723);