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:[email protected]] Sent: Thursday, November 29, 2012 11:03 AM To: Fawcett, David (MPCA); Bistrais, Bob; Jeff McKenna; [email protected] 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: [email protected] [mailto:[email protected]] On Behalf Of Fawcett, David (MPCA) Sent: Thursday, November 29, 2012 9:10 AM To: Bistrais, Bob; Jeff McKenna; [email protected] 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: [email protected] [mailto:[email protected]] On Behalf Of Bistrais, Bob Sent: Thursday, November 29, 2012 8:45 AM To: Jeff McKenna; [email protected] 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:[email protected]] Sent: Wednesday, November 28, 2012 6:41 PM To: [email protected] 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 [email protected] http://lists.osgeo.org/mailman/listinfo/mapserver-users _______________________________________________ mapserver-users mailing list [email protected] http://lists.osgeo.org/mailman/listinfo/mapserver-users _______________________________________________ mapserver-users mailing list [email protected] http://lists.osgeo.org/mailman/listinfo/mapserver-users
