2

I am totally new in the world of GIS but as a student surveyor I am working on a project using postgreSQL and PostGIS.

I have one database with all the roads of my country (lines), and one database with a lot of area's (polygons). And for every polygon, I need to find all the roads (lines) that intersect with it (contain in partially or fully).

I do know the ST_Intersects function, but is this the best function to use ? right now I am trying to make a loop function where I loop all the roads to see if they intersect, but there must be a quicker way..

If anybody could get me started, that would be great!

Thanks

  • 1
    Do you really mean two databases or do you mean two tables in a single database? There generally isn't ever any quick way to do a "find every for each" query. – Vince Mar 15 '14 at 14:08
  • I mean 2 tables, sorry – user28088 Mar 15 '14 at 14:46
  • Do you use OSM data? In my case I had to transform the OSM data geometries to the same projection like my other geometries are stored in the database. – Stefan Apr 20 '14 at 17:18
2

Leave the looping for the database. This is SQL.

SELECT polygons.id, lines.id 
FROM polygons, lines 
WHERE ST_Intersects(lines.geom,polygons.geom);

Put spatial indexes on the geometry columns.

  • I did put spatial indexes on both my tables (USING gist(..) ), and then did this query; SELECT wvb_gid, wbn_gid FROM wvb, wbn WHERE ST_Intersects(wvb_geom,wbn_geom); But I always get this error; ERROR: invalid join selectivity: 187087368.000000 ********** Error ********** ERROR: invalid join selectivity: 187087368.000000 SQL state: XX000 I am google'ing it but I have no idea what this means – user28088 Mar 15 '14 at 14:49
  • 2
    @user28088: Your problem is discussed in this link: trac.osgeo.org/postgis/ticket/2543. Could be helpful. – Stefan Mar 15 '14 at 15:14

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.