[mapserver-users] Problem with attribute query, multiple attributes

[Bistrais, Bob]

I tried a few things and have an interesting result-

After trying the file GDB with multiple attributes, and no success there, I tried Jeff's suggestion and used a shapefile of the parcels, and added an index.  I know that conventional wisdom says shapefile is a poor choice, but when I tried it, I got a query result within 7 or 8 seconds.  So while this might not be the recommended approach, it gets the job done.

Thanks Jeff!



-----Original Message-----
From: Lime, Steve D (DNR) [mailto:Steve.Lime at state.mn.us] 
Sent: Thursday, November 29, 2012 11:03 AM
To: Fawcett, David (MPCA); Bistrais, Bob; Jeff McKenna; mapserver-users at lists.osgeo.org
Subject: RE: [mapserver-users] Problem with attribute query, multiple attributes

Honestly I don't think an index is going to help unless the attribute filter is being passed to OGR processing. I'm not familiar enough with that driver to know if that's done or not. Usually attribute queries are written a form the driver understands. For PostGIS that means a bit of a where clause, for Shapefiles it's the normal expression syntax. Again, not sure about OGR.

With large shapefiles attribute queries are inefficient, usually requiring full table scans. Bob's example:

  queryExp = "('[TOWN]' == '007-042') AND ('[TOWN]' == 'Acton'))";

will guarantee a full scan because the expression is invalid. The same feature can't have two values for TOWN.

Steve

-----Original Message-----
From: mapserver-users-bounces at lists.osgeo.org [mailto:mapserver-users-bounces at lists.osgeo.org] On Behalf Of Fawcett, David (MPCA)
Sent: Thursday, November 29, 2012 9:10 AM
To: Bistrais, Bob; Jeff McKenna; mapserver-users at lists.osgeo.org
Subject: Re: [mapserver-users] Problem with attribute query, multiple attributes

Since OGR accesses the GDB via ESRI's API/Library, it may (should) take advantage of attribute indexes built on the data with ArcMap.  

You should make sure that the feature classes in the GDB have attribute indexes on any column that you want to use as query criteria and that they have good spatial indexes as well.  

If you don't have access to ESRI tools, you will likely want to pre-process the data by dumping it from the GDB to shapefiles or PostGIS, where you can create all of the indexes that you need.  

David.

-----Original Message-----
From: mapserver-users-bounces at lists.osgeo.org [mailto:mapserver-users-bounces at lists.osgeo.org] On Behalf Of Bistrais, Bob
Sent: Thursday, November 29, 2012 8:45 AM
To: Jeff McKenna; mapserver-users at lists.osgeo.org
Subject: Re: [mapserver-users] Problem with attribute query, multiple attributes

Thanks Jeff.  That link shows how to build an index on a shapefile.  Is there an equivalent for File GDB?  I'm looking at the GDAL document for GDB now, not seeing an index description.

-----Original Message-----
From: Jeff McKenna [mailto:jmckenna at gatewaygeomatics.com]
Sent: Wednesday, November 28, 2012 6:41 PM
To: mapserver-users at lists.osgeo.org
Subject: Re: [mapserver-users] Problem with attribute query, multiple attributes

On 12-11-28 5:43 PM, Bistrais, Bob wrote:
> I am trying to set up an attribute query for a large parcel dataset 
> (about 550,000 polygons).  I am having trouble with the query 
> exceeding the 30 second limit.
> 
>  
> 
> I am trying to query on the map_bk_lot and town attributes.  I can 
> query on either one separately and get results, but I need to query on 
> both at the same time.
> 
>  
> 
> Here is my query expression:
> 
> $queryExp = "('[TOWN]' == '007-042') AND ('[TOWN]' == 'Acton'))";     
> ---Hardcoded for testing now, will eventually use variables
> 
> $pLayer->queryByAttributes("MAP_BK_LOT",$queryExp,MS_SINGLE);   
> ----Query statement
> 
>  
> 
> The data is in an ESRI File GDB.  I am using MS4w 3.0.4m MapServer 
> 6.0.2, MapScript 6.1 for PHP
> 
>  
> 
> One other thing worth mentioning- if I use a small subset of features 
> (such as a town), the query will work without timing out.  But I'm no 
> longer convinced that dataset size is the problem since it will query 
> on one attribute.
> 
>  
> 
> Any ideas what's going wrong here?

A tip that might come in handy someday, if not in this case, could be to create an "attribute index" on your vector field (see example in http://www.gdal.org/ogr/drv_shapefile.html); I've done this before and it's improved attribute queries in MapServer significantly.  You can generate one through an ogrinfo command with the "sql" switch.

Sorry I cannot provide exact FileGDB examples at the moment.

-jeff



--
Jeff McKenna
MapServer Consulting and Training Services http://www.gatewaygeomatics.com/



_______________________________________________
mapserver-users mailing list
mapserver-users at lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users


_______________________________________________
mapserver-users mailing list
mapserver-users at lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users