Spatial index to speed up the query

Question

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.

Answer 1

You're correct that your problem is that in order to sort, then limit to the closest, you have to generate the distance to each of the 100k points, which is extremely time consuming.

Luckily, there's help. First, you want to make sure that your geometry column is indexed, if it's not, then you need to index it, or this won't work.

Then you want to use the <-> operator, which is new in PostGIS 2.x. You only have the CTE portion of query, not the whole thing, but the query would probably look something like:

SELECT array_366
FROM pixelbased3
ORDER BY pix_centroid <-> ST_Transform(
 ST_GeomFromText('POINT(30.424 -1.66)', 4326), 
 35991
 )
LIMIT 10;

You can read Paul Ramsey's blog post on the topic here: http://blog.opengeo.org/2011/09/28/indexed-nearest-neighbour-search-in-postgis/

Answer 2

100000 points isn't that many, but it might help a lot if you remove the st_transform call (i.e. do it once, outside of this query).

• f.ashouri: Thank you. I tried but I'm afraid transformation is not the problem.

Answer 3

Currently there is a method to filter geometries within some distance in PostGIS that uses spatial index:

http://postgis.net/docs/ST_DWithin.html

Answer 4

100.000 points should not make PostgreSQL sweat... :-)

But it looks like you are (maybee) comparing data that are not in the same coordinate system?

If so, this will force PostGIS to project-on-the-fly one data set to fit the projection of the other. Not only is this really slow, but you have little control over this. The distance measurement (and it's unit) that you end up with is depending on the coordinate system, and it's difficult to know which that is.

I strongly recommend that you take the control, and bring all the data into the same coordinate system, before you start using spatial operators like <-> on it.

Please read this To project or not to project? It's short, and I can't write it any better, any way.