I have two shapefiles - test7 and MD_Overlay

test7 has about 600,000 records with over one hundred attributes (about ~2GB) , so performing spatial joins in QGIS is very inefficient. I would like to find out the equivalent SQL command of QGIS Join attributes by location in PostGIS. My aim is to add the columns of MD_Overlay to test7 and to keep both matching and non-matching records.

This would be a polygons in polygons operation. (So a polygon in test7 would have to be fully within a polygon in MD_Overlay in order to be joined)

I have done much research but cannot find a solution. Any advice?

enter image description here


There are a few ways to do this, here's one:

SELECT t.*, m.*
FROM test7 AS t
ON ST_Within(t.geom, m.geom)

LEFT JOIN: Return all rows from the left table, and the matched rows from the right table. The m.* columns will be null when there is no spatial intersection. You should ensure that both your geometry columns have spatial indexes.

  • Make sure you have GIST indexes on t.geom – Evan Carroll Nov 30 '16 at 8:59
  • no join occurred . I get ERROR: GEOSContains: TopologyException: side location conflict at 2430764.1748952293 7027762.7618717942 is there any way to ignore errors and continue? – the_darkside Nov 30 '16 at 9:53
  • Try adding a condition: WHERE ST_IsValid(t.geom) AND ST_IsValid(m.geom). Note that this will exclude records with invalid geometry from the result entirely. postgis.net/docs/ST_IsValid.html – Richard Law Nov 30 '16 at 10:13
  • It is possible that you input geometries are not all valid? Have a look at ST_IsValid (to check) and ST_MakeValid to fix, and try again. There is no way to avoid the GEOSContains error otherwise. OK, Richard beat me to it. I still think it might be worth running ST_MakeValid rather than excluding invalid geometries. – John Powell Nov 30 '16 at 10:13
  • I do, too, but OP asked about just ignoring them. With a name like test7 I'm just assuming the result isn't too important... otherwise it's important to invest a little bit of time to do things correctly. – Richard Law Nov 30 '16 at 10:31

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.