2

I'm trying to query a list of polygons (geo_parcels) and get all of them, and also spatially add any points or polygons (geo_buildings) that are in or on that polygon. Normally I'd use a query like this:

SELECT DISTINCT geo_parcels.objectid
FROM geo_parcels LEFT JOIN geo_buildings ON st_contains(geo_parcels.geom, geo_buildings.geom)
WHERE (ST_IsValid(ST_MakeValid(ST_CurveToLine(geo_parcels.geom))) OR geo_parcels.geom IS NULL)
AND (ST_IsValid(ST_MakeValid(ST_CurveToLine(geo_buildings.geom))) OR geo_buildings.geom IS NULL);

However, that gave a new to me error of

GEOSContains: TopologyException: side location conflict at # #. This can occur if the input geometry is invalid.

I dug around and got it working by removing the ST_MakeValid, which is surprising, but works.

SELECT DISTINCT geo_parcels.objectid
FROM geo_parcels LEFT JOIN geo_buildings ON st_contains(geo_parcels.geom, geo_buildings.geom)
WHERE (ST_IsValid((ST_CurveToLine(geo_parcels.geom))) OR geo_parcels.geom IS NULL)
AND (ST_IsValid((ST_CurveToLine(geo_buildings.geom))) OR geo_buildings.geom IS NULL);

With this, I get 23146 results. However, If I just query the geo_parcels directly, I get 23396 results.

SELECT DISTINCT geo_parcels.objectid FROM geo_parcels;

I'd like to find a way to not remove any results when I add in the ST_Contains. Is this possible?

1 Answer 1

5

It makes sense, as your are joining on the raw geometry.

If a geometry is invalid but fixable, the where clause (WHERE (ST_IsValid(ST_MakeValid(ST_CurveToLine(geo_parcels.geom)))) wouldn't filter it out but the geometry remains invalid, while when you remove the st_makevalid the where clause filters out invalid geometries.

Also, because you are referencing the right table in the where clause, it is not really a left join anymore. Instead, you will want to move the restricting clause to the join condition.

The query below will keep every geo_parcels, even if its geometry is invalid. But since geo_buildings is not in the part of the select and is not filtering out records, you might as well just omit it..

SELECT DISTINCT geo_parcels.objectid
-- ... and possibly other fields from geo_buildings?
FROM geo_parcels 
 LEFT JOIN geo_buildings 
  ON st_contains(geo_parcels.geom, geo_buildings.geom)
    AND (ST_IsValid(geo_parcels.geom) OR geo_parcels.geom IS NULL)
    AND (ST_IsValid(geo_buildings.geom) OR geo_buildings.geom IS NULL);
3
  • Thanks, unfortunately this still gives me the error: GEOSContains: TopologyException: side location conflict at # #. This can occur if the input geometry is invalid.
    – Andrew T
    Commented Jan 17 at 16:30
  • try removing the st_curveToLine which has nothing to do with points or polygons
    – JGH
    Commented Jan 17 at 16:38
  • Thanks, I tried that and get it still with this (admittedly bad) dataset. Tried this query out of curiosity but it just runs forever and gives a lot of Ring Self-Intersection Notices. ```` SELECT DISTINCT geo_parcels.objectid -- ... and possibly other fields from geo_buildings? FROM geo_parcels LEFT JOIN geo_buildings ON (st_contains(geo_parcels.geom, geo_buildings.geom) OR geo_parcels.geom IS NULL OR geo_buildings.geom IS NULL); ````
    – Andrew T
    Commented Jan 18 at 16:55

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.