[mapserver-users] Query by attributes with PHP/MapScript

[Armin Burger]

Hi all,

I would like to query shapefiles based on their attibutes using
PHP/MapScript, like

street='mainstreet' and city='newtown'

and then get back all records, including for instance the bounding box
of the shape.

There is the function QueryByAttributes, but as I saw in the mail
archives you have to set a filteritem and then a filter on the layer.
That can only use one field as filteritem. I would need 2 or even more
fields used in the query.

I was thinking about using basic PHP functions for dBase.
Unfortunately the dBase support of pure PHP seems to be very rudimentary
and does not allow queries. There is the Pear DB module, but there is no
documentation to what extent dBase files are supported.

Can anybody who was succesful in this area give me hints how to achieve
slightly complex attribute queries with PHP or PHP/MapScript?

An optimal solution in my opinion would be a query interface in
MapScript that mimics the SQL syntax. Though I don't have any idea how
difficult this would be to implement.


Armin

[w***@swoodbridge.com]

Post by Armin Burger
Hi all,
I would like to query shapefiles based on their attibutes using
PHP/MapScript, like
street='mainstreet' and city='newtown'
and then get back all records, including for instance the bounding box
of the shape.

This is basically a geocoding function. I ended up implementing this
in C for the US Tiger data, because if you have a large dataset it
could take a long time to query it and requires a lot of slow disk
reads. As part of this I had to build indexes to make it fast as
there are about 6500 files that might have to be scanned depending on
the query!!!

http://iMapTools.com/demos/?tab=1

Post by Armin Burger
There is the function QueryByAttributes, but as I saw in the mail
archives you have to set a filteritem and then a filter on the layer.
That can only use one field as filteritem. I would need 2 or even more
fields used in the query.
I was thinking about using basic PHP functions for dBase.
Unfortunately the dBase support of pure PHP seems to be very
rudimentary and does not allow queries. There is the Pear DB module,
but there is no documentation to what extent dBase files are
supported.
Can anybody who was succesful in this area give me hints how to
achieve slightly complex attribute queries with PHP or PHP/MapScript?
An optimal solution in my opinion would be a query interface in
MapScript that mimics the SQL syntax. Though I don't have any idea how
difficult this would be to implement.

While there are some simple SQL parsers available on the web, the
real problem becomes a) mapping the SQL actions into all the data
sets that mapserver supports and b) performance of the queries.
Things like shapefiles do not have indexes to make attribute queries
fast, so you end up always doing full table scans, or you have to add
generating indexes, but them you end up building a whole relational
database on top of structures that are not nicely suited for them :(

Why not load your attribute data into a MySQL or all your data into
PostGIS and be done with it. then you can make you SQL queries and
generate appropriate indexes, etc. If you use shapefiles then store
the shapefile name and the index to the record along with the
attribute info so you have a back pointer to the spatial data. I
built the following placename geocoder using Perl to load the data
into MySQL and PHP as the web UI with over 7 million place names:

http://iMapTools.com/geocode.php

Any way, using a database should work well from PHP or Perl and you
can write a script to upload the data into the database.

-Stephen Woodbridge
http://iMapTools.com

Post by Armin Burger
Armin

[Puneet Kishor]

Post by Armin Burger
Hi all,
I would like to query shapefiles based on their attibutes using
PHP/MapScript, like
street='mainstreet' and city='newtown'
and then get back all records, including for instance the bounding box
of the shape.
There is the function QueryByAttributes, but as I saw in the mail
archives you have to set a filteritem and then a filter on the layer.
That can only use one field as filteritem. I would need 2 or even more
fields used in the query.
I was thinking about using basic PHP functions for dBase.
Unfortunately the dBase support of pure PHP seems to be very
rudimentary
and does not allow queries. There is the Pear DB module, but there is
no
documentation to what extent dBase files are supported.

hmmmm... does too. And it is not rudimentary at all... in fact, it is
only limited by the limits of the dbase format (which is a pretty poor
limit).

Here is a dbase function that will query whatever you want --

//{{{ getDbfRowid($dbf, $field, $arVals).
// Get dbf row_ids from a $dbf given an array of $arVals for a field
$field
//
function getDbfRowid($dbf, $field, $arVals) {
$db = dbase_open($dbf, 0); // open the dbase
file
$nr = dbase_numrecords($db); // get number of
records

$arResult = array(); // new array to
hold row_ids

for($i=1; $i <= $nr; $i++) { // loop from 1 to $nr
$temp = dbase_get_record($db, $i); // assign fields to
$ar_temp
foreach($arVals as $val) { // loop thru all the
$gis_keys
if(chop($temp[2]) == "$val") {
// create a hash of val and row_id
$hsResult = array("val" => $val, "row_id" => $i);
array_push($arResult, $hsResult); // now push the
hash into the array
}
}
}

dbase_close($db);
return $arResult; // return the array of hashes
} //}}}

This allows you to, for example, find the rows where field is "street"
and the matches you are looking for are "mainstreet", "divisionstreet",
"highstreet", etc. Use those row_ids as indexes for the shapefile to
zoom to the relevant features.

pk/

[Hankley, Chip]

Armin,

Here's how I do it. This is an example using a zipcode, and a layer called
"ZIP." The key thing here is that a shapefiles records are base 1, whereas
mapscript views them as base 0. So, if you wanted to retrieve the first
record in your shapefile, you would refer to it as 'record 1' in dbase, and
'row 0' in mapscript.

You're right, there is no SQL like syntax. Basically what we are doing here
is VERY inefficient... we are looping through the records in the dbase doc
'til we get a match (also a logic problem in some cases... what if there are
two matches?).

One solution to this, if you want better performance, is to store your
attribute data in a database (MySQL for instance), with one of the fields
being row-id. You can then perform a real SQL query, return the row-id's,
then use the ->getExtent(<row-id>) method in MapScript.

If you don't care about performance, or your recordset is small, you could
always rework the conditional statement in this function to:

1) Not break; on success
2) Query more than one field...

Basically you'd just be writing a really complex conditional statement
(mimicking SQL) that would be applied to each record.

Another option, of course, if you have really large datasets and want to do
heavy duty SQL queries is to use PostGIS.

Chip Hankley

======================================

function zip_zoom($map, $zipcode) {
$lyr = $map->getlayerbyname('ZIP');
$src = $lyr->data;
$path = $map->shapepath;
$shp_path = $path . $src;
$db_path = $shp_path . ".dbf";

$nShpFile = ms_newShapefileObj($shp_path, -1);
$zip_fail = 1;

//open the dbase file for the shapefile
if ($dbi = dbase_open($db_path, 0)) {
$nr = dbase_numrecords ($dbi);
$q_result = array();
$x = 0;
for ($i=1; $i <= $nr; $i++) {
$cur_row = dbase_get_record_with_names ($dbi, $i);
if (trim($zipcode) == trim($cur_row[ZIP])) {
$row_id = $i;
$zip_fail = 0;
break;
}
}
}

if ($zip_fail)
echo "<script>javascript:alert('No Zip Code Match Found!');</script>";

$row_id = $row_id - 1;
$shapeExtObj = $nShpFile->getExtent($row_id);
$minx = $shapeExtObj->minx;
$miny = $shapeExtObj->miny;
$maxx = $shapeExtObj->maxx;
$maxy = $shapeExtObj->maxy;

$border = 10000;

$minx = $minx - $border;
$miny = $miny - $border;
$maxx = $maxx + $border;
$maxy = $maxy + $border;

$map->setExtent($minx, $miny, $maxx, $maxy);

}

[Armin Burger]

Chip, Puneet, Stephen,

thanks a lot for your help. I will try your examples.

I agree with you that a real database would be best. Especially with
something available like PostGIS what I appreciate very much. It's only that
sometimes it's less feasible to store all data in a database. Shapefiles are
easier to maintain, no database administrator required.

I tried attribute queries on shapefiles with Mapserver CGI and a combination
of filter and class definition using a dummy query layer. The speed was not
too bad (2 sec for a shapefile with 60000 records on a reasonable fast PC).

Armin

[Armin Burger]

After some trials I found out that MapScript supports multicolumn
attribute queries via the 'setfilter()' method. The following worked
fine

$query = "(([AREA]>500000 AND [AREA]<=1000000) AND ([CODE]=311
OR [CODE]=312))";
$layer->setFilter($query);
$layer->queryByAttributes(MS_MULTIPLE);

Its's even possible to include regular expressions in the query string.

Armin