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

[Charlton Purvis]

Sorry this is a lengthy post.  I worked to keep the length down, but if
you're interested in PostGIS-ing (or RDBMS-ing, for that matter) and
subselecting, lend me your eyes.

Problem:  Once I add a WHERE clause (a subselect) to my PostGIS layer, I
lose access to the data and only get the index (oid) back.

Jose, this might be in line w/ your question from yesterday (Subject:
[Mapserver-users] QuerybyPoint no result (Help) PHP/Mapscript), or it
might not!

---- from Jose
I have a data base in Mysql in which it contains ID, cor_x, cor_y and
other
4 fields. The points in the map already visualize, but when wanting to
do
query does not bring any result to me.
----

Here's the deal.  I've got a PostGIS bathymetry line table.  Indexed,
etc.

======================
  LAYER
    CONNECTION "user=postgres dbname=myGISdb host=localhost"
    CONNECTIONTYPE POSTGIS
    NAME "bathy_postgis"
    STATUS ON
    TYPE LINE
    TEMPLATE "dummy.html"
    METADATA
      "DESCRIPTION"   "Bathyemetry"
      "RESULT_FIELDS" "DATASET SOURCE RES DEPTH_FT DEPTH_M DEPTH_FA"
    END
    CLASS
      COLOR 255 0 0
    END
  END
======================

Notice that there is no DATA definition.  That's not a problem because I
set that in my .php.  Well, at least I don't *think* that is a problem.

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");

So, in theory, they should produce the same results.  From the CONTROL
group, I get all my columns returned.  From the TEST, I just get the
index.  How do I know?  I use this code to give me back some info:

======================
//$rect_extents has already been set
 $layerObj->queryByRect($rect_extents);

// how many rows returned?
 echo "# results = " . $layerObj->getNumResults()
       . " from " . $layerObj->name . "<br>";
 
// let's just look at the first row
 $layerObj->open();
 $oRes = $layerObj->getResult(0);
 $oShape = $layerObj->getShape(-1,$oRes->shapeindex);

// pull out the column names & print 'em
 $ak = array_keys($oShape->values);
 echo "Data fields:  | ";
 foreach ($ak as $val) {
   echo $val . " | ";
 }
======================

And the results . . . 

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 |

Interesting, eh?  Something is getting masked when I do my subselecting
and aliasing.  What can I do to get back to my data?

Why do I care?  Why not use a FILTER?  I will if I have to, but my
middle initials are RDBMS, and if I can trim the data down at the
database level before MapServer has to crunch it, I'm happy.  Small
datasets?  No problem.  Large datasets?  Problem.

Thanks for your time.  MapServer rocks.

Charlton

 
 
 
Charlton Purvis
(803) 777-8858 : voice
(803) 777-8833 : fax
cpurvis at sc.edu
 
Advanced Solutions Group
Department of Physics and Astronomy
University of South Carolina
Columbia, SC 29208