[postgis-users] Query choces on searching too small area

Arjen Haayman Arjen.Haayman at imagem.nl
Fri Dec 16 05:09:01 PST 2016


Hi,

I've got this database that has photos that have a location and a table with key/value metadata. There are almost 450,000 photos and 5 million rows of metadata.
Searching on metadata alone always works, but when I add a spatial search to it the query freezes if the spatial component is too precise, i.e. if I search at a certain point or on a bounding box that is too small. My workaround now is to always extend to a bounding box with a minimum size.

I cannot find a way how to tackle this. EXPLAIN ANALYZE fails just as miserably as the original query. I've tried all kinds of indexes, but nothing works.
The queries have been running successfully for years but at a certain point in time the database got too big apparently and now this happens.

What I really don't understand why it would fail when the query gets too specific, I mean searching on a point should be easier than searching a large bounding box. It usually is the other way around??

Here's an example query:

SELECT
"id",
"filename",
ST_AsText("geometry") AS "geometry",
ST_AsText("center") AS "center",
"angle"
FROM "photo"
WHERE (ST_Intersects("geometry", st_GeomFromText( 'POINT(4.5063099203616 51.923602970634)', 4326)))
AND ("id" IN (
SELECT  "photoId" FROM "photoMetadata"
WHERE ("photoId" IN (
SELECT DISTINCT "photoId" FROM "photoMetadata"
WHERE ("value" = 'KADASTER') AND ("key" = 'source')))
                                             AND ("photoId" IN (
SELECT DISTINCT "photoId" FROM "photoMetadata"
WHERE (key = 'year' AND ( cast(value as int ) >= 1866 AND cast ( value as int ) <= 1981 ))))))
ORDER BY "filename" LIMIT 36

So this fails. This means that it takes way too long. And a few of these queries running at the same time completely clogs my machine.

When I change it to something like (ST_Intersects("geometry", st_GeomFromText( 'POLYGON((6.1444640338619 52.265808403464,6.1444640338619 52.281808403464,6.1496640338619 52.281808403464,6.1496640338619 52.265808403464,6.1444640338619 52.265808403464))', 4326)))

where the extent of the geometry needs to be large enough.

Does anyone have any clues how to tackle this?




-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20161216/61209eb6/attachment.html>


More information about the postgis-users mailing list