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

[Charlton Purvis]

Thanks a bunch, David.  Don't know why I didn't think of that.  Listing
out all the columns I wanted (instead of the asterisk), worked great.

$layerObj->set("data","the_geom from "
   . "(select oid, the_geom, gid, dataset, source, res, depth_ft,
depth_m, depth_fa "
   . " FROM se_bathy where 1=1) "
   . "AS foo USING UNIQUE oid USING SRID=-1");

Thanks.

-----Original Message-----
From: Lowther, David W [mailto:dlowther at ou.edu] 
Sent: Friday, April 25, 2003 10:22 AM
To: Charlton Purvis; mapserver-users at lists.gis.umn.edu
Cc: jose.quintal at cfe.gob.mx
Subject: RE: [Mapserver-users] PostGIS subselect & where'd the data go?

Charlton,

Try adding , * after the_geom to explicitly bring back all the rows in
the
subselect. Like:

    $layerObj->set("data","the_geom from "
    . "(select oid, the_geom, * FROM se_bathy where 1=1) "
    . "AS foo USING UNIQUE oid USING SRID=-1");

David Lowther
Software Engineer
GEO Information Systems
University of Oklahoma
dlowther at ou.edu
(405) 325-3131
http://www.geo.ou.edu




> -----Original Message-----
> From: Charlton Purvis [mailto:cpurvis at asg.sc.edu] 
> Sent: Friday, April 25, 2003 9:14 AM
> To: mapserver-users at lists.gis.umn.edu
> Cc: jose.quintal at cfe.gob.mx
> Subject: [Mapserver-users] PostGIS subselect & where'd the data go?
> 
> 
> 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
> 
> _______________________________________________
> Mapserver-users mailing list
> Mapserver-users at lists.gis.umn.edu 
> http://lists.gis.umn.edu/mailman/listinfo/maps> erver-users
>