2

I have a table with a lot of geographical objects like countries, cities or points inside the cities.

For instance

# SELECT id, ST_AsText(location) FROM brace_brace WHERE id IN (3, 42);
id |                                         st_astext                                          
----+--------------------------------------------------------------------------------------------
3 | GEOMETRYCOLLECTION(POLYGON((39.95 70.05,40.05 70.05,40.05 69.95,39.95 69.95,39.95 70.05)))
42 | GEOMETRYCOLLECTION(POINT(40.0045845585 70.0036813508))
(2 rows)

Here the id #3 is a fictional city, and the id #42 is a fictional restaurant. Now, it clearly seems that both do indeed intersect

# SELECT ST_Intersects('GEOMETRYCOLLECTION(POLYGON((39.95 70.05,40.05 70.05,40.05 69.95,39.95 69.95,39.95 70.05)))'::geography, 'GEOMETRYCOLLECTION(POINT(40.0045845585 70.0036813508))'::geography);
st_intersects 
---------------
t
(1 row)

Or even with explicit SRID (that is the same being used in the table)

# SELECT ST_Intersects('SRID=4326;GEOMETRYCOLLECTION(POLYGON((39.95 70.05,40.05 70.05,40.05 69.95,39.95 69.95,39.95 70.05)))'::geography, 'SRID=4326;GEOMETRYCOLLECTION(POINT(40.0045845585 70.0036813508))'::geography);
st_intersects 
---------------
t
(1 row)

However, if I look for all items intersecting with my city

# SELECT id, ST_AsText(location) FROM brace_brace WHERE ST_Intersects('GEOMETRYCOLLECTION(POLYGON((39.95 70.05,40.05 70.05,40.05 69.95,39.95 69.95,39.95 70.05)))'::geography, location);
id |                                         st_astext                                          
----+--------------------------------------------------------------------------------------------
3 | GEOMETRYCOLLECTION(POLYGON((39.95 70.05,40.05 70.05,40.05 69.95,39.95 69.95,39.95 70.05)))
(1 row)

It looks like that the restaurant does not come up, and neither do the other 80 features I've put in the city (which are points in geometry collections) nor even the enclosing country (which is a polygon in a geometrycollection).

Interestingly enough, if I make the query the other way around, here is what I get

# SELECT id, ST_AsText(location) FROM brace_brace WHERE ST_Intersects('GEOMETRYCOLLECTION(POINT(40.0045845585 70.0036813508))'::geography, location);
 id |                                         st_astext                                          
----+--------------------------------------------------------------------------------------------
3 | GEOMETRYCOLLECTION(POLYGON((39.95 70.05,40.05 70.05,40.05 69.95,39.95 69.95,39.95 70.05)))
42 | GEOMETRYCOLLECTION(POINT(40.0045845585 70.0036813508))
(2 rows)

It indeed brings up the enclosing city, but does not bring the enclosing state nor the country. For reference, here they are

# SELECT id, ST_AsText(location) FROM brace_brace WHERE id IN (1, 2, 3, 42);
id |                                         st_astext                                          
----+--------------------------------------------------------------------------------------------
1 | GEOMETRYCOLLECTION(POLYGON((-160 70,-20 70,-20 -50,-160 -50,-160 70)))
2 | GEOMETRYCOLLECTION(POLYGON((20 50,160 50,160 -70,20 -70,20 50)))
3 | GEOMETRYCOLLECTION(POLYGON((39.95 70.05,40.05 70.05,40.05 69.95,39.95 69.95,39.95 70.05)))
42 | GEOMETRYCOLLECTION(POINT(40.0045845585 70.0036813508))
(4 rows)

And this is quite disturbing for me I must admit. My data are random and were "generated" on some flat paper initially, then using linear bounding boxes, and I am well aware that since I use geographies some points might not fit in, yet my city is in the middle of the country, and anyway the "manual" query works fine.

I suppose that this has something to see with an index lookup not doing what I expect. Here is the explain of the query.

Here is my create table statement

CREATE TABLE "brace_brace" (
    "id" serial NOT NULL PRIMARY KEY,
    "location" geography(GEOMETRYCOLLECTION,4326)
)
;
CREATE INDEX "brace_brace_location_id" ON "brace_brace" USING GIST ( "location" );

I'm using Archlinux with the latest version of postgresql (9.3.3-1)/postgis (2.1.1-1).

Many thanks!

UPDATE: I've been SET enable_indexscan = false;, but the result is still the same. Not an index issue I guess.

  • try do same using multi or simple type rather than geometrycollection. Have you check that data ST_IsValid or ST_IsSimple ? SEE : postgis.org/docs/ST_Intersects.html , try cast to geometry (srid 4326) and see if anything changes – simplexio Mar 7 '14 at 8:34
  • My polygons are both valid and simple. I am currently refactoring the code to see if it works better with multipolygons/points – Xowap Mar 7 '14 at 12:54
  • It indeed seems to be working much better using a multipolygon field to look points. – Xowap Mar 7 '14 at 17:19
  • However, I wonder if this is a bug or if I was doing something wrong. Anyway, being given the number of functions documented as "do not use on GeometryCollection", I understand that it is a safe choice to abandon them – Xowap Mar 7 '14 at 17:20
  • You may be encountering a bug in geography that is still extant (though there are fixes in the code in the source repository, but it is not in a numbered release yet). – Paul Ramsey Mar 7 '14 at 21:35
3

As seen in the comments, it was a bug.

You can see the bug in the bugtracker.

It was fixed in the 2.1.2 release

However, being caught by that bug outlined a fundamental issue in our design, and made it possible to realize that the app would not be performing well using such a generic type. I would not advise to use GeometryCollections unless you know exactly what you're doing.

Your Answer

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

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