[postgis-users] ST_Intersects differing results

[Timothy Asquith]

Thanks Paul,

You’re absolutely right that the intersect query should always have returned touching polygons. I’m not sure how we were ever getting the desired results. That’s what I’m looking into now.

Interestingly, the critical difference seems to be the use of binary geometry.

The land parcel geometry is passed into the URL, and used as the basis for the intersect query:

http://my-geoserver.example.com/geoserver/workspace/wfs?service=WFS&version=2.0.0&request=GetFeature&typeName=workspace:zones&outputFormat=json&cql_filter=INTERSECTS(geom,POLYGON((-37.79397716231438%20145.0752696285534,-37.79395266253229%20145.07506684589112,-37.79391636418757%20145.07507518332602,-37.79354819089359%20145.07515979038527,-37.79358130702303%20145.0753488489579,-37.79397716231438%20145.0752696285534)))

Looking at my Postgres query logs, this generates the following query:

SELECT
	"feature_id",
	"scheme_code",
	"zone_status",
	"zone_code",
	"updated_at",
	encode(ST_AsEWKB("geom"),'base64') as "geom"
FROM
	"public"."planning_zones"
WHERE
	"geom" && ST_GeomFromWKB('\x00000000030000000100000006406222689bda209bc042e5a10b2cf4a040622266f29603b8c042e5a03da812fc406222670412240cc042e59f0d29f8f040622267b58135bac042e592fcb3d80e4062226941fd4218c042e594128030c0406222689bda209bc042e5a10b2cf4a0', 4326)
	AND ST_Intersects("geom", ST_GeomFromWKB('\x00000000030000000100000006406222689bda209bc042e5a10b2cf4a040622266f29603b8c042e5a03da812fc406222670412240cc042e59f0d29f8f040622267b58135bac042e592fcb3d80e4062226941fd4218c042e594128030c0406222689bda209bc042e5a10b2cf4a0', 4326)) LIMIT 1000000;

This query provides the current behaviour - returning a single zone polygon that intersects with the parcel polygon.

However, if I take that same polygon string, and run the query using ST_AsText:

SELECT
	"feature_id",
	"scheme_code",
	"zone_status",
	"zone_code",
	"updated_at",
	encode(ST_AsEWKB("geom"),'base64') as "geom"
FROM
	"public"."planning_zones"
WHERE
	"geom" && ST_GeomFromText('POLYGON((-37.79397716231438 145.0752696285534,-37.79395266253229 145.07506684589112,-37.79391636418757 145.07507518332602,-37.79354819089359 145.07515979038527,-37.79358130702303 145.0753488489579,-37.79397716231438 145.0752696285534))', 4326)
	AND ST_Intersects("geom", (ST_GeomFromText('POLYGON((-37.79397716231438 145.0752696285534,-37.79395266253229 145.07506684589112,-37.79391636418757 145.07507518332602,-37.79354819089359 145.07515979038527,-37.79358130702303 145.0753488489579,-37.79397716231438 145.0752696285534))', 4326))) LIMIT 1000000;

Then I get zero results.

The parcel geometry in the URL comes from the same database. If I rewrite this query to use a spatial join, as detailed in my previous email, I get 3 intersecting zone polygons, which sounds like the correct behaviour.

Is there a tolerance / precision difference between text and binary geometry?

I’m now looking for workarounds or solutions - stop me if I’m heading in the wrong direction:

- workaround: take the zone polygon with the largest intersecting area
- workaround: filter out polygons below an arbitrary intersection area (i.e. anything with <1m2 area isn’t useful)
- solution: snap my zone polygons to my parcel polygon, and filter out results that only share a border
    - I’m not experienced in doing this
    - I’m experimenting with ST_Snap and filtering out ST_Touches, but it still returning the same 3 results 

Thanks for all the help

▬▬▬ι═══════════ﺤ
Timothy Asquith // Red Ronin
tim at redronin.io
www.redronin.io

On 25 May 2016 at 5:00:52 AM, postgis-users-request at lists.osgeo.org (postgis-users-request at lists.osgeo.org) wrote:

Re: ST_Intersects differing results
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20160525/3eda5a6a/attachment.html>