3

I am running a very simple boolean selection with postgis st_intersects on two polygon tables:

select distinct on(ms.shape) ms.shape shape,ms.type_ 
    from riparian_corridor.merged_soils ms join riparian_corridor.riparian_open_waters o 
        on st_intersects(ms.shape,o.shape);

This query returns most correct features but there are a handful of instances where the features clearly intersect and it is not selected.

The distance between the edge of that blue feature and red outline polygon is about 2 feet. Should I be using st_snaptogrid? If so, which geometry should I put it on? and should the tolerance be something around 2 feet to pick those features up?

enter image description here

closed as off-topic by Vince, PolyGeo Sep 8 '17 at 23:16

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "This problem cannot or can no longer be reproduced. Changes to the system or to the asker's circumstances may have rendered the question obsolete, or the question does not include a procedure to enable potential answerers to reproduce the same symptoms. Such questions are off-topic as they are unlikely to help future readers, but editing them to include more details can lead to re-opening." – Vince, PolyGeo
If this question can be reworded to fit the rules in the help center, please edit the question.

  • What about: select ms.shape,ms.type_ from riparian_corridor.merged_soils ms where ST_Intersects(ms.shape, (select shape from riparian_corridor.riparian_open_waters o)); – HeikkiVesanto Sep 8 '17 at 13:59
  • 1. ERROR: more than one row returned by a subquery used as an expression ********** Error ********** 2. why would this work better? 3. using st_union on the riparian_open_waters would take forever, its a massive table – ziggy Sep 8 '17 at 14:07
  • Ok, yeah. I'm just thinking perhaps the distinct is removing some of the joins. Try: select ms.shape,ms.type_ from riparian_corridor.merged_soils ms join riparian_corridor.riparian_open_waters o on st_intersects(ms.shape,o.shape); – HeikkiVesanto Sep 8 '17 at 14:11
  • Or simply check if PostGIS really does not find intersection between those geometries or if there is some glitch in your query select st_intersects(ms.shape,o.shape) where ms.id=xx and o.id=yy – user30184 Sep 8 '17 at 14:21
  • 1
    @HeikkiVesanto I think you were correct the distinct on clause was removing some joins, I will post what ended up working for me – ziggy Sep 8 '17 at 15:58
4

You don't want to put shape in a DISTINCT ON clause. It would be the bounding box of the geometry it's distincting.

Use a different field like gid or if you really want to go by geometry do something like DISTINCT ON(ms.shape::text)

  • the merged soils table has many duplicate geometries, I am aware the distinct on uses a bounding box to collapse them but I would still need to distinct them/group them down (want to avoid st_union because these tables are so large). – ziggy Sep 8 '17 at 14:24
  • okay then cast your geometry to text as I mentioned using ms.shape::text will then group by the hex representation of the geometry. You can also use ST_AsBinary(ms.shape) – LR1234567 Sep 10 '17 at 15:03
3

@HeikkiVesanto led me in the right direction with removing the distinct on clause, I believe that was removing some features from my query (still exactly unsure how)

but the query that ended up doing the trick was

select distinct shape from(
    select ms.shape shape 
    from riparian_corridor.merged_soils ms join riparian_corridor.riparian_open_waters o 
        on st_intersects(ms.shape,o.shape))t  ;

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