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 am trying to retrieve all points p that are within polygons c. While building the query, I came across this strange behavior.

Question: Why does query 2 takes so long compared to query 1? Is it also doing a full tablescan of table points even though the WHERE clause is using c.osm_id and not table p? If so how can we prevent this? My final query probably requires both tables in the FROM clause.

Query 1

SELECT *
FROM counties As c
WHERE c.osm_id IN (-1842139,-1933719,-2362362,-1933718,-2362263,-2306361,-1933745,-1942017,-1931997,-1865772,-1933055,-1933746,-2315704)

Query 2

SELECT *
FROM counties As c, points As p
WHERE c.osm_id IN (-1842139,-1933719,-2362362,-1933718,-2362263,-2306361,-1933745,-1942017,-1931997,-1865772,-1933055,-1933746,-2315704)

This is my final query, it seems to take forever (207 sec) to execute... I'm wondering if the slowness of this query is related to the slow speed of the above query 2.

Table p countains 1 million points and table c contains 300 polygons.

SELECT *
FROM counties As c, points As p
WHERE c.osm_id IN (-1842139,-1933719,-2362362,-1933718,-2362263,-2306361,-1933745,-1942017,-1931997,-1865772,-1933055,-1933746,-2315704)
AND ST_Within(
        ST_Transform(ST_SetSRID(ST_Point(p.lng, p.lat), 4326), 2163)
    ,c.geom)

Update

Query using indexed geometry column p.geom and JOIN still takes 144 seconds. Am I expecting an unreasonable increase in speed?

SELECT *
FROM counties As c
JOIN points As p
ON ST_Within(p.geom,c.geom)
WHERE c.osm_id IN (-1842139,-1933719,-2362362,-1933718,-2362263,-2306361,-1933745,-1942017,-1931997,-1865772,-1933055,-1933746,-2315704)

I figured the long query time is due to transferring the geometry data for 17k rows from the Pgsql database to my computer running pgAdmin3. Transferring the ids alone took 480ms.

share|improve this question
    
Ok, deleted my answer since you added more to your question. Q2 is slow because it results in a full cross join, which means many many rows. Your final query is not able to use any index or anything from your points (it doen't have an actual geometry?), so joining a million of those records with your counties doesn't seem that slow! –  René Wolferink Apr 18 '13 at 13:46
    
If you want to know what it's doing add EXPLAIN before the select to get the query plan (but yes it's doing a seq scan). To confirm that it would be aviable to use indexes please try to select the points from p that are within a hardcoded geometry (or geometry pulled from a subquery). –  Jakub Kania Apr 18 '13 at 13:59
    
I've updated table p with an GIST indexed geometry column and the final query now takes 157 secs instead of 207 sec. Updated the question with a query that uses the indexed column and a JOIN but its still pretty slow at 144 secs. –  Nyxynyx Apr 18 '13 at 14:11
    
Slow query time is actually due to transferring the geometry data for 17k rows over the network to my computer. Transferring ids alone was really quick. Thanks everyone! –  Nyxynyx Apr 18 '13 at 14:22

2 Answers 2

up vote 1 down vote accepted

Your problem is caused by the cross joining, you will have to use a correlated subquery to speed it up (assuming that an index can be used).

SELECT * 
FROM points AS p
JOIN 
(
 SELECT c.id, <other c columns>, 
    (SELECT array_agg(p.id) 
     FROM points AS p 
     WHERE ST_Within(ST_Transform(ST_SetSRID(ST_Point(p.lng, p.lat), 4326), 2163)   ,c.geom)) as p_id
 FROM counties c
 WHERE c.osm_id IN   (-1842139,-1933719,-2362362,-1933718,-2362263,-2306361,-1933745,-1942017,-1931997,-1865772,-1933055,-1933746,-2315704)
OFFSET 0) as q
ON p.id<@q.p_id
share|improve this answer
    
Thanks, I've removed the need for the subquery (I think!) by updating table p with a indexed geometry column. –  Nyxynyx Apr 18 '13 at 14:13

I think the second request's answer will give you the whole table "points" whatever the parameters in this where function... This might be very important compared to the first request which only returns entries in the "countries" table whose osm_id is mentionned.

Maybe you should add a clause like ' AND ST_WITHIN(p.the_geom, c.the_geom)"

The request may still be quite long but the answered list shorter ;)

share|improve this answer

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.