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?

  • 1
    It's a lot of work, won't be instant, but seems slow. Have a spatial index? CREATE INDEX ways_x ON planet_osm_line USING GIST (way) – Paul Ramsey Dec 7 '16 at 13:06
  • I listed indexes on planet_osm_line, I see a "gist (way)" entry, so I guess I already have this index. BTW, I ran the query again, waited until end, it took around 45 mins, generating 500K rows processing 180K rows (self-joined). Ur right, it is a lot of work, my notebook harware is not so great, so maybe that is the answer :) waiting for 45 minutes. – BBSysDyn Dec 7 '16 at 13:30
  • 1
    You can actually halve the run time for free, as you are intersecting every p1.osm_id with every p2.osm_id, which means you will do each pair twice. It is n² in big 0 notation. If you put WHERE p1.osm_id < p2.osm_id it is now n²/2, still techincally n², but twice as quick :-). You can also drop the where 1 = 1. This takes you from 32 billion potential intersections to 16 billion. – John Powell Dec 8 '16 at 13:38
  • Damn.. I did not know 1=1 could be a performance killer. Will try out all these asap. Thanks, – BBSysDyn Dec 9 '16 at 15:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.