[Mapserver-users] PostGIS subselect & where'd the data go?

[Jan Hartmann]

You are perfectly right: PostGIS returns all fields from a table query, 
which is convenient but can be dangerous if the table has many fields. I 
don't know what happens with blobs. Anyway, for myself  I only request 
the actual data I need for the map, either via a subselect (when I 
really need something very adaptive), or by creating a view in 
PostgreSQL, which is much more efficient for static queries.

Jan

Charlton Purvis wrote:

>Thanks, Jan.  I understand what you're saying, but by the same
>reasoning, why DOES
>  DATA "the_geom from se_bethy"
>return all the fields?  After all, the_geom is just a column.
>
>I'm glad that you brought my attention to that detail, too, since I
>might only be interested in returning only a subset of those columns.
>What if for some reason I had 10 columns as part of my table where 3
>were blobs w/ a large amount of binary data, and I was only interested
>in the other 7 smaller integer columns?  If I were to leave the
>"the_geom from se_bathy" alone, I sure would spend a lot of time
>shipping data to MapServer that I would not need.
>
>I guess my question is:  why isn't it only one way or the other instead
>of like it seems to be now, something in the middle?
>
>-----Original Message-----
>From: Jan Hartmann [mailto:jhart at frw.uva.nl] 
>Sent: Friday, April 25, 2003 10:56 AM
>To: mapserver-users at lists.gis.umn.edu
>Subject: Re: [Mapserver-users] PostGIS subselect & where'd the data go?
>
>Charlton,
>
>When you put an SQL request to PostGIS  using a table name, all fields 
>in that table are automatically returned to MapServer, alongside the 
>geometry field. That's why you see "dataset", "source", etc in the 
>result, although you did not specify them in the DATA statement. In a 
>subselect however, you have to request *every* field you need (including
>
>oid).  There is no default set of fields in a subselect.
>
>Jan
>
>Charlton Purvis wrote:
>
>  
>
>>So here are the two cases that should have the same results but don't.
>>
>>// CONTROL (no subselecting)
>>  $layerObj->set("data","the_geom from se_bathy");
>>
>>// The TEST case is when I run my .php w/ this, instead:
>>  $layerObj->set("data","the_geom from "
>>  . "(select oid, the_geom FROM se_bathy where 1=1) "
>>  . "AS foo USING UNIQUE oid USING SRID=-1");
>> 
>>
>>    
>>
>...
>...
>
>  
>
>>CONTROL results
>># results = 739 from bathy_postgis
>>Data fields: | gid | dataset | source | res | depth_ft | depth_m |
>>depth_fa |
>>
>>TEST results
>># results = 739 from bathy_postgis
>>Data fields: | oid |
>> 
>>
>>    
>>
>Jan Hartmann
>Department of Geography
>University of Amsterdam
>jhart at frw.uva.nl
>
>_______________________________________________
>Mapserver-users mailing list
>Mapserver-users at lists.gis.umn.edu
>http://lists.gis.umn.edu/mailman/listinfo/mapserver-users
>
>  
>