I have spatial table in PostgreSQL 9.2 / postgis 2.0 that I have to extract some information from it. But my query is hopelessly slow. How could I make use of spatial index to improve the performance of my query?
Query: The query is to find the temperature value of a point when coordinate of the point in WGS84 system is (30.424, -1.66) and the data is for 2002-01-09.
with B as
(select array_366
from pixelbased3
order by st_distance(((st_GeomFromText('POINT(30.424 -1.66)', 4326))), pix_centroid)
limit 1)
Any other suggestion to improve my query performance is appreciated. I have a column of points (geometry data type and more than 100000 records) and an array of temperatures for each day throughout the year for every point. I'm computing the nearest point in the table to the point in the query and after locating the the corresponding array, I'm computing the corresponding day and temperature. Table pixelbased3 looks like this:
+-----+------------+
| ID |pix_centroid|
+-----+------------+
| 100 |0101000020 |
| 101 |00000B7E93 |
| 102 |49413098D0 |
| 103 |0000B7E930 |
+-----+------------+
pix_centroid is Well Known Text (WKT) and its type is (point) geometry. I couldn't copy the whole WKT since it is too long. Combination of st_distance and st_GeomFromText computes the distance of given point (30.424 -1.66) in WGS84 lat lon system from pix_centroid records. BTW, I have more than 100000 records and I imagine this makes it slow to find the distance from each.