I created a GIS database using an OSM datafile for a single city (from Geofabrik) and imported it into PostGIS using osm2pgsql. I have around 180K rows in osm_planet_line database, a manageable sie. Now I am trying to query all intersections in this city. This query
SELECT st_asText(st_transform(st_intersection(p1.way,p2.way), 4326)) FROM planet_osm_line as p1 inner join planet_osm_line as p2 on st_intersects(p1.way,p2.way) where 1=1 and p1.osm_id <> p2.osm_id and p1.name <> p2.name
is taking very long (I waited 30 mins then stopped the query). I know the query works in general, because if I add the following
and st_dwithin(st_transform(p1.way, 4326),st_geomfromtext('POINT(13.4270146842 52.5408961579)',4326)::geography,1000) and st_dwithin(st_transform(p2.way, 4326),st_geomfromtext('POINT(13.4270146842 52.5408961579)',4326)::geography,1000)
which only gives me intersections within a kilometer of a certain region, then after 4-5 mins I get a result, and they seem to be correct.
Is there a faster way of retrieving all intersections, using a different join perhaps, an additional filter, etc?