3

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

Improve this question
3
  • 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
    Commented Mar 15, 2014 at 14:08
  • I mean 2 tables, sorry
    – user28088
    Commented Mar 15, 2014 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
    Commented Apr 20, 2014 at 17:18

1 Answer 1

Reset to default
3

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.

Improve this answer
2
  • 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
    Commented Mar 15, 2014 at 14:49
  • 2
    @user28088: Your problem is discussed in this link: trac.osgeo.org/postgis/ticket/2543. Could be helpful.
    – Stefan
    Commented Mar 15, 2014 at 15:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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