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

[Tom Melhuish]

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
--