Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers 2

up vote 3 down vote accepted

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/

share|improve this answer
    
Great! Thank you –  f.ashouri Dec 1 '12 at 1:04
add comment

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).

share|improve this answer
    
Thank you. I tried but I'm afraid transformation is not the problem. –  f.ashouri Dec 1 '12 at 0:38
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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