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?

Improve this question

1 Answer 1

Reset to default
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);
Improve this answer
3
  • Thanks, unfortunately this still gives me the error: GEOSContains: TopologyException: side location conflict at # #. This can occur if the input geometry is invalid. Commented Jan 17, 2024 at 16:30
  • try removing the st_curveToLine which has nothing to do with points or polygons Commented Jan 17, 2024 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); ```` Commented Jan 18, 2024 at 16:55

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.