3

I have a table of devices with a column last_known_coordinates (geography(Point,4326)). Here is an example last_known_coordinates:

0101000020E6100000569C00000D99EDBFE006C60B33F54640

Which is in France. GeoJSON representation:

{"type": "Feature", "geometry": {"type": "Point", "coordinates": [-0.9249329567, 45.9156203]}, "properties": {"date": "2021-08-31T19:46:43Z", "type": "Circle", "radius": 3574, "device_location_observation_id": "612e8724f170cf0001aab253"}}

I'm trying to do a bounding box search using that column. If I use this query:

SELECT "device".* FROM devices AS "device"
WHERE (ST_Intersects("device"."last_known_coordinates", ST_MakeEnvelope(77.1213473925, 33.38830657282016, -56.911855732499994, 67.08154108286482)::geography))

The device (and others which are expected) are not returned.

Here's GeoJSON for that envelope:

{"type":"Polygon","coordinates":[[[77.1213473925,33.3883065728202],[77.1213473925,67.0815410828648],[-56.9118557325,67.0815410828648],[-56.9118557325,33.3883065728202],[77.1213473925,33.3883065728202]]]}

It's clear that France and the device are both 100% within the box.

However, if I do this query with a slightly smaller box:

SELECT "device".* FROM devices AS "device"
WHERE (ST_Intersects("device"."last_known_coordinates", ST_MakeEnvelope(41.349863017500006, 37.56022509361477, -25.666738544999994, 56.37618107342221, 4326)::geography))

The device (and 39 others which are expected) are returned.

I cannot see anything that might be out of order about these bounding boxes. In fact checking that the smaller box is inside of the larger returns true:

SELECT ST_within(
    ST_MakeEnvelope(41.349863017500006, 37.56022509361477, -25.666738544999994, 56.37618107342221),
    ST_MakeEnvelope(77.1213473925, 33.38830657282016, -56.911855732499994, 67.08154108286482)
);

Here is the explain for the "bad" query:

"QUERY PLAN"
"Limit  (cost=8.55..8.56 rows=1 width=4001)"
"  ->  Sort  (cost=8.55..8.56 rows=1 width=4001)"
"        Sort Key: updated_at DESC"
"        ->  Index Scan using device_last_known_coordinates_idx on devices device  (cost=0.28..8.54 rows=1 width=4001)"
"              Index Cond: (last_known_coordinates && '0103000020E61000000100000005000000838FDA27C4475340CF8A9F07B4B14040838FDA27C4475340D40B17F837C55040F9E04AB0B7744CC0D40B17F837C55040F9E04AB0B7744CC0CF8A9F07B4B14040838FDA27C4475340CF8A9F07B4B14040'::geography)"
"              Filter: (_st_distance(last_known_coordinates, '0103000020E61000000100000005000000838FDA27C4475340CF8A9F07B4B14040838FDA27C4475340D40B17F837C55040F9E04AB0B7744CC0D40B17F837C55040F9E04AB0B7744CC0CF8A9F07B4B14040838FDA27C4475340CF8A9F07B4B14040'::geography, '0'::double precision, false) < '1.0000000000000001e-05'::double precision)"

And the "good" query:

"QUERY PLAN"
"Limit  (cost=8.55..8.56 rows=1 width=358)"
"  ->  Sort  (cost=8.55..8.56 rows=1 width=358)"
"        Sort Key: updated_at DESC"
"        ->  Index Scan using device_last_known_coordinates_idx on devices device  (cost=0.28..8.54 rows=1 width=358)"
"              Index Cond: (last_known_coordinates && '0103000020E61000000100000005000000071FB54FC8AC4440ABBCB374B5C74240071FB54FC8AC44407DDC8FB326304C40F2C19560AFAA39C07DDC8FB326304C40F2C19560AFAA39C0ABBCB374B5C74240071FB54FC8AC4440ABBCB374B5C74240'::geography)"
"              Filter: (_st_distance(last_known_coordinates, '0103000020E61000000100000005000000071FB54FC8AC4440ABBCB374B5C74240071FB54FC8AC44407DDC8FB326304C40F2C19560AFAA39C07DDC8FB326304C40F2C19560AFAA39C0ABBCB374B5C74240071FB54FC8AC4440ABBCB374B5C74240'::geography, '0'::double precision, false) < '1.0000000000000001e-05'::double precision)"

As you can see there is an index. Why is ST_Intersect finding the device in one case and not in the other?

2
  • 4
    Aren't geography objects spheroidal, in which case the envelope will follow great ellipse lines? You certainly have two different queries, and the geometry doesn't prove the geography is within.
    – Vince
    Sep 1 at 1:42
  • @Vince You're correct, it looks like ST_Within doesn't take geographies. However if I put both GeoJSON representations of the geographies on a map, the smaller rectangle is contained within the larger rectangle
    – Tony
    Sep 1 at 2:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.