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?
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users
