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 been working with postgis recently,and in my query if I use ST_GeomFromText it execute faster than running a sub-query to get geom.

I thought ST_GeomFromText will be more expensive but after running many tests every time I got the result faster, my question Is there any explanation behind this? because for me getting the geom directly in sub-query is better than getting geom as text then added as GeomFromText.

Thanks

share|improve this question
    
Can you give an example. What do you mean by using a sub-query to get the geom? Where from? What do you do in that sub-query? Please give the two different queries. And tell what version of PostGIS you are using. –  Nicklas Avén Apr 11 '12 at 15:10
    
@NicklasAvén this is what I mean by sub-query: Select userid,name FROM table1 WHERE ST_Intersects ((select the_geom from table2 where country='somecountry'),position) and the other query by using ST_GeomFromText which is faster : Select userid,name FROM table1 WHERE ST_Intersects (ST_GeomFromText(' text geom ',4326),position) and I'm using postgis 1.5.3 Many thanks –  Sara Apr 14 '12 at 8:52
    
Could it be that there are more than one polygon that has the name 'somecountry' in table one but you have only picked one of them to be represented as text? How much time are we talking about, milliseconds, seconds or minutes? It is hard to tell what the problem is without the actual data. But your "sub-query" query would be more readable like this: "SELECT userid, name FROM table1, table2 where table1.country='somecountry' AND ST_Intersects(table1.position, table2.the_geom);" –  Nicklas Avén Apr 16 '12 at 6:35
add comment

1 Answer

You should run an "explain analyze" on both queries. As with most performance-related questions, this can provide you with the quantitative data you'll need to answer your question. Post here if you find anything interesting.

Based on your question alone, it's totally possible the sub-query approach would take longer. Why do you expect it to be quicker? Yes, the RDBMS doesn't have to parse your WKT string to a geometry type, BUT your sub query represents additional processing. Based on your schema, this could actually be very expensive! Look at the explain output - the query planner could be executing a sequential scan in the sub-query.

share|improve this answer
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.