I'm having problems identifying intersections on a layer of linework, the lines are representative of contacts and faults (geological rock formations). I am trying to select where there are connected line segments of faults and contacts because it is geologically unlikely for a fault to end and a contact to begin immediately after it (thus might be an error in the layer). So I only need to select intersections where there are 2 lines intersecting.
Here is the query I have so far I stole most of it from this thread:
CREATE TABLE contacts_st_touching_faults as SELECT ST_Intersection(a.the_geom, b.the_geom), Count(Distinct a.gid) = 2 FROM intersect_linejoin_geolines as a, intersect_linejoin_geolines as b WHERE ST_Touches(a.the_geom, b.the_geom) AND a.f_class = 'Contact' AND b.f_class = 'Fault' AND a.gid != b.gid GROUP BY ST_Intersection(a.the_geom, b.the_geom)
When I run this and populate the geometry, I get coordinates with more than 2 intersections (T intersections), but I do only get intersections where contacts and faults are intersecting which is what I want. However, I only want it where there is an intersection between two lines. I'm pretty sure this means leaving Count = 2, I've tried Count = 1 and that didn't work either.
Thanks for the help!!