[postgis-users] Perl Mapscript - queryByPoint to extract Data from POSTGIS table

[Paul Ramsey]

Unfortunately, it is difficult to extract where the problem is
occurring. I had a similar problem with SWIG, wherein it returned
references, but the references pointed nowhere (rather, to an
incomprehensible integer value). Work your way through each portion of
the result and see where it fails and how.

P.

Tom Melhuish wrote:
> 
> I am using Perl Mapscript and have not been able to figure out how to obtain the record number out of POSTGIS when I’m doing a point query in mapserver. I have successfully been able to get the data out of a xxx.dbf file but not a Postgis table.
> =============================================================
> Below is my code to show what I’m trying to do by getting the data out of a xxx.dbf  -shapefile
> 
> $x1 = $CursorCoords[0];
>         $y1 = $CursorCoords[1];
> 
>         $cx = ($session{WindowXmax}-$session{WindowXmin})/($session{ImageWidth}); # calculate cellsize in x and y
>         $cy = ($session{WindowYmax}-$session{WindowYmin})/($session{ImageHeight});
> 
>         $x1 = ($session{WindowXmin} + $cx*$x1); # change x,y from image to map coordinates, offset from UL corner of previous image
>         $y1 = ($session{WindowYmax} - $cy*$y1);
> 
>         my $point = new pointObj();
>                 $point->{x} = $x1;
>                 $point->{y} = $y1;
> 
>                 print "**$x1,$y1 **  Point: $point**<BR>\n";
> 
>                 $map->prepareQuery();
>                 $layer = $map->getLayerByName($QueryLayer) or die('Unable to get layer’);
>                 #$layer->{$QueryLayer} = $mapscript::MS_ON; # if it's not on already
> 
>                 $rv = $layer->queryByPoint($map,$point, $mapscript::MS_SINGLE,0);
>                 $rv == $mapscript::MS_SUCCESS or die("No items found at the point selected for query type $session{SubOption} ");
> 
>                 #then get your results like:
> 
>                 $layer->open($resultcache);
>                 $resultcache = $layer->{resultcache};
>                 @result= $resultcache->{results};
>                 $result = $layer->getResult(0);
> 
>                 $dbfname = "data/parcels";
>                 $shapefile= new shapefileObj ($dbfname, -1) or die('unable to open shapefile $dbfname');
> 
>                 $shprecnum =  $result->{shapeindex};
>                 my $table = new XBase 'data/parcels.dbf';
>                 @querydata = $table->get_record($shprecnum);
> 
>                 print "**Result is:  @querydata, $data[1], $data[6] **<BR>\n";
> 
>                 $img = $map->drawQuery();
> 
>         }
> 
> ##
> ## My results in @querydata is an array with each xx.dbf field value in it for the record I queried by the point.
> 
> =======================================================================
> 
> Now I’m trying to do the same thing with POSTGIS with no success.
> 
> my $point = new pointObj();
>                 $point->{x} = $x1;
>                 $point->{y} = $y1;
> 
>                 print "**$x1,$y1 **  Point: $point**<BR>\n";
> 
>                 $map->prepareQuery();
>                 $layer = $map->getLayerByName($QueryLayer) or die('Unable to get layer’);
>                 #$layer->{$QueryLayer} = $mapscript::MS_ON; # if it's not on already
> 
>                 $rv = $layer->queryByPoint($map,$point, $mapscript::MS_SINGLE,0);
>                 $rv == $mapscript::MS_SUCCESS or die("No items found at the point selected for query type $session{SubOption} ");
> 
>                 #then get your results like:
> 
>                 $layer->open($resultcache);
>                 $resultcache = $layer->{resultcache};
>                 @result= $resultcache->{results};
>                 $result = $layer->getResult(0);
> 
>                 $id=[id];
>                 $tag=[tag];
> 
> $SQLcommand=qq(SELECT tag, ncpin, mapsheet, ownername, addr1, addr2, addr3, city, state, zip, book, page, value, acreage, revalue, market  FROM  jcparcels WHERE tag=$tag);
> 
>                         print "**$SQLcommand**<BR>\n";
> 
>                         $stmt = $dbh->prepare($SQLcommand);
>                         $rc = $stmt->execute();
> 
>                         while (@tname = ($stmt->fetchrow_array())) {
>                                         $valuetag=$tname[0];
>                                         #print "**$extent**<BR>\n";
>                         }
> 
> 
> 
>                 print "**Result is:  $tag, $valuetag,  **<BR>\n";
> 
>                 $img = $map->drawQuery();
>         }
> 
> My Results are :
> 
> SELECT tag, ncpin, mapsheet, ownername, addr1, addr2, addr3, city, state, zip, book, page, value, acreage, revalue, market  FROM  jcparcels WHERE tag=ARRAY(0x860cf34)
> 
> tag=ARRAY(0x860cf34)
> 
> valuetag=
> 
> ======================================================================
> 
> In Perl Mapscript I didn’t see any methods like I did for a shapefile to find the record number in a POSGIS table. There are values put in [id] = ARRAY(0x860cfdc) and [tag] = ARRAY(0x860cf34) but I don’t know how to write a SQL statement or what method to call to convert [id]  to a “gid” value to select the query record.  Can anybody provide code to show me how this is accomplished?
> 
> Thanks
> Tom Melhuish
> 
> --
> Tom Melhuish
> Consultant
> WebInsights
> 703.255.5127
> Email: tmelhuish at webinsights.org
> --
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users

-- 
      __
     /
     | Paul Ramsey
     | Refractions Research
     | Email: pramsey at refractions.net
     | Phone: (250) 885-0632
     \_