[postgis-users] How to SELECT one day's scene (Landsat 8 scenes)

[Rémi Cura]

It is a little bit hard to understand what you mean..
As a summary :
_you have lot's of rasters.
_Each one as a position (and a date associated?).

You need to get a raster based on its position (where st_intersects(..))),
but it is not sufficient and you would like too also filter by date.

You can do so easily by adding a condition  in the WHERE, like
AND my_date < .... AND my_date > ...
(or using time_range
http://www.postgresql.org/docs/9.2/static/rangetypes.html , if postgres
version >= 9.2)

Now about making a table per day, it is entirely another question.
logically wise, you could get the strict equivalent by adding a column on
your raster table with the day of acquisition (the real date would be
better).

Now deciding to split or not your big raster table into several smaller is
more about performance, and should be carefully designed (
http://www.postgresql.org/docs/9.3/static/ddl-partitioning.html), based on
row number, index size, your memory size, how the table will grow, what
kind of usage you make of it, and so on.


Cheers,
Rémi-C



2014-03-19 8:28 GMT+01:00 Jackie Nguyen <justaniceguy.nguyen at gmail.com>:

> Hi PostGIS Users,
>
> I have a huge dataset of raster in PostGIS. Each scene is divided into
> tiles (tile-size 500x500). I was trying to SELECT rast FROM landsat8
> WHERE ST_Intersects(rast, polygon). It runs very slow, because it returns
> longitudinal scenes of raster. Where I just want one day's scene.
> But the problem is if I query with fixed date, adjacent scene with
> different date won't be selected. That's not true.
> So I think about some solutions:
>
>    - Because Landsat 8 re-visiting every 16 days, so I will insert every
>    16 days into different table. So I can query from raster that cover all the
>    earth without duplicate.
>    - I'm not sure about ST_Within(), ST_DWithin() then SELECT rast that
>    doesn't overlap other >50%
>
> What would you do in this situation?
>
> --
> ----
> Jackie
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140319/0a9d8648/attachment.html>