[postgis-users] postgis and mapserver - queries

[C F]

What does mappostgis.c do (I don't see it in my PostGIS source)?  I'm 
curious, because I'm doing some similar operations using the EXPLAIN VERBOSE 
method.  Without writing a full blown SQL parser, I don't see a better way.  
It seems like the best way would be to hook into the actualy SQL parser 
being used by Postgres... but maybe that's too difficult and that could 
change as well.


>From: David Blasby <dblasby at refractions.net>
>Reply-To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
>To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
>Subject: [postgis-users] postgis and mapserver - queries
>Date: Mon, 09 Jun 2003 10:04:56 -0700
>
>I recently changed mappostgis.c so its a bit wiser about how it determines 
>the columns in a query.  Previously, it did this:
>
>EXPLAIN VERBOSE <query>;
>
>The advantage of this is that it is extreamly quick.  Unfortunately, the 
>EXPLAIN VERBOSE command changes its output with different versions of 
>postgresql.  For example, in 7.1 and 7.2, the EXPLAIN VERBOSE is set as a 
>NOTICE message.  In 7.3 its sent as a query result (and is formated 
>differently).
>
>The new mappostgis.c determines columns like this (<query> is basically the 
>query in your DATA statement):
>
>SELECT * FROM (<query>) as foo LIMIT 0;
>
>Basically, it executes the query asking for zero rows.  In most cases this 
>will return a result (where you can determine the column names and types 
>easily) quickly.  Unfortunately, i noticed that it can actually take a long 
>time to execute.  For example, imagine you have a big table with road data 
>in it:
>
>SELECT * FROM (  SELECT gid, sum(len) FROM roads GROUP BY gid) as foo LIMIT 
>0;
>
>This can take many seconds to compute because postgresql will do the entire 
>GROUP BY (which involves a SORT) before it can start returning results.
>
>I was thinking that you can use the fact you know the unique column 
>attribute ("using unique <column>") and the geometry column's name to speed 
>things up by reducing the query set:
>
>SELECT * FROM (<query>) as foo WHERE <unique column> = -666 AND 
><geom_column> && 'BOX3D(0 0, 0 0)' LIMIT 0;
>
>[Here -666 and the BOX3D are just made up to reduce the query results]
>
>But this seems pretty hacky (and will not always work cut down on the work) 
>- anyone else have ideas?
>
>dave
>
>
>_______________________________________________
>postgis-users mailing list
>postgis-users at postgis.refractions.net
>http://postgis.refractions.net/mailman/listinfo/postgis-users

_________________________________________________________________
Add photos to your messages with MSN 8. Get 2 months FREE*.  
http://join.msn.com/?page=features/featuredemail