[TinyOWS-users] TinyOWS performance problem

[Leukert, Boris]

Dear Olivier,

I have a performance problem with TinyOWS (r567).

 

The request

 

<?xml version="1.0" encoding="UTF-8"?>

<wfs:GetFeature service="WFS" version="1.0.0"
xmlns:wfs="http://www.opengis.net/wfs"
xmlns:gml="http://www.opengis.net/gml"
xmlns:ogc="http://www.opengis.net/ogc">

  <wfs:Query xmlns:lpis="http://lpis.apia.org.ro"
typeName="lpis:parcels_digitized">

    <ogc:Filter>

        <And>

          <Intersects>

              <ogc:PropertyName>lpis:the_geom</ogc:PropertyName>

                <gml:Polygon srsName='EPSG:31700'>

                    <gml:outerBoundaryIs>

                      <gml:LinearRing>

                          <gml:coordinates>499729,494786 499743,494786
499743,494786 499729,494772 499729,494786</gml:coordinates>

                        </gml:LinearRing>

                    </gml:outerBoundaryIs>

                  </gml:Polygon>

            </Intersects>

              <ogc:PropertyIsEqualTo>

                <ogc:PropertyName>lpis:status</ogc:PropertyName>

                    <ogc:Literal>1</ogc:Literal>

              </ogc:PropertyIsEqualTo>

        </And>

      </ogc:Filter>

  </wfs:Query>

</wfs:GetFeature>

 

results in the SQL statement

 

SELECT
"gid","farm_id","fiscal_year","status","sirsup_code","bloc_nr","parcel_n
r","crop_nr","cat_use","crop_code","area_declared","agro_env","parcel_no
te","inserted","updated","full_bloc",ST_AsGML(2,
ST_Transform("the_geom"::geometry,31700),0,0) AS "the_geom"
,"editor","date_confirm"

  FROM public."parcels_digitized"

WHERE (
ST_Intersects(ST_Transform("the_geom",31700),'0103000020D47B000001000000
050000000000000044801E410000000008331E41000000007C801E410000000008331E41
000000007C801E410000000008331E410000000044801E4100000000D0321E4100000000
44801E410000000008331E41') AND "status" = 1) LIMIT 100

The SQL statement causes a full table scan on the table
parcels_digitized even if the table as a geometry index.

It runs about 6 seconds on 3.9 million rows in the table what is not
very performant.

 

If I change the strings "ST_Transform("the_geom",31700)" in the WHERE
clause of the statement to "the_geom" the

geometry index is used an the statement run in about 12 ms!

 

For me there is no reason to transform the geometries in the SQL
statement because I am using only EPSG:31700.

Is it possible to prevent the transformations if only one projection
system is used in the application, TinyOWS and database?

 

Best regards,

Boris

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.maptools.org/pipermail/tinyows-users/attachments/20110607/db8b07da/attachment-0001.htm