2

I have a global dataset of ecoregion boundaires. There are ~809 unique polygons in this dataset. I've imported it into PostGIS with SRS of 4326 and built a spatial index using the function below

CREATE INDEX ecoregionsindex ON ecoregions USING gist(geom);

Querying with a point takes ~150ms to run

SELECT id FROM "ecoregions"
WHERE ST_Within (ST_PointFromText('POINT(-111 45)', 4326), geom);

Buffering ~20,000m around this point using the ST_DWithin() method takes about 15,000ms to run.

SELECT id
FROM "ecoregions"
WHERE ST_DWithin(ecoregions.geom, ST_MakePoint(-111,45)::geography, 
20000);

Is there a way to optimize my data or the query above to run more quickly?

  • If you cast on the search, you have to cast on the index. – Vince Nov 18 at 16:29
  • Are your polygons in geometry or geography data type? – DPSSpatial Nov 18 at 16:52
  • @DPSSpatial - geometry – jotamon Nov 18 at 17:00
  • 1
    @jotamon well it looks like you've found a solution to cast your geometry as geography in your spatial index.... I'm guessing the data may cover the entire globe? If so, you might want to store your data as geography rather than geometry so you don't have to cast your data in the index (is that OK to do that even though you can - I don't know) – DPSSpatial Nov 18 at 18:11
5

You need to construct your index the way you are gonna use it. If you cast on your call, you need to cast the same way on your index (here you need to create your index on the geography version of your point):

CREATE INDEX ecoregionsindex_geog ON ecoregions USING gist(CAST(geom AS geography));

Note that the :: shortcut don't work for indexes. Don't forget to analyze after:

ANALYZE ecoregions;

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