2

My query is:

SELECT count(datasetid)
FROM scenes
WHERE acquisitiondate between '2014-06-01' and '2015-06-01'
AND ST_Intersects(ST_GeographyFromText('SRID=4326; POLYGON((' || '10.66437' || ' ' || '49.981172' || ', ' || '15.46437' || ' ' || '49.981172' || ', ' || '15.46437' || ' ' || '54.781172' || ', ' || '10.66437' || ' ' || '54.781172' || ', ' || '10.66437' || ' ' || '49.981172' || '))'), bounds)
GROUP BY datasetid;

datasetid has the data type smallint and acquisitiondate is a timestamp (without time zone).

There is a gist-index on the column bounds. The index won't be used if the column has geometry data type. But if the column has geography data type than the gist index is used instead of a sequential scan.

What is the reason for this?

Maybe this can be helpful: PostGIS Version is 2.1.8 and PostgreSQL 9.4.5.

  • Can you post the EXPLAIN SELECT output stuff of your statement? – huckfinn Feb 24 '16 at 17:39
  • What SRID is defined on the column that you have indexed? As as aside, it isn't necessary to use this hideous string concatenation ||. Look at functions like ST_Envelope and ST_MakePoint, much cleaner – John Powell Feb 24 '16 at 18:36
2

You want both sides of the query to have the same type, for the index to come into play. By turning 'bounds' into geography, you're accomplishing this. You could also do it by using ST_GeomFromText() and turning bounds into a geometry instead, so both sides are geometry.

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.