[Geoserver-devel] sqlserver BBox == Filter incorrect result returned

VT1 · February 7, 2011, 3:35am

Hi, i noticed that using

<ogc:BBOX> filter == .Filter

<ogc:Intersects> == STIntersects

The mapping can be found in SQLServerFilterToSQL.visitBinarySpatialOperator.

Because of the use of .Filter, <ogc:BBOX> seem to be returning me the wrong
error.

<ogc:BBOX>
                        <ogc:PropertyName>er:location</ogc:PropertyName>
                        <gml:Envelope srsName="EPSG:4326">
                              <gml:lowerCorner>114 30</gml:lowerCorner>
                              <gml:upperCorner>115 31</gml:upperCorner>
                        </gml:Envelope>
                  </ogc:BBOX>

From the sqlserver, running the following query generated as a result of the

above bbox using .FILTER returns me

SELECT
CAST("LOCATION".STSrid as VARCHAR) + ':' + "LOCATION".STAsText() as
"LOCATION"
FROM "ER_MININGFEATUREOCCURRENCE"
WHERE "LOCATION".Filter(geometry::STGeomFromText('POLYGON ((114 30, 114 31,
115 31, 115 30, 114 30))', 4326)) = 1

4326:POINT (117.3255 -35.03178)
4326:POINT (117.573097 -35.05859)
4326:POINT (117.791397 -35.041531)
4326:POINT (117.140676 -35.044455)
4326:POINT (117.325104 -35.03162)
4326:POINT (117.325104 -35.03162)

which is way out of range. Changing it to the use of ogc:Intersects yields
the correct result. So is it really accurate to use BBOX with .FILTER?

I post my question to the sqlserver forum and this is their reply

This is expected behaviour - you're using the Filter() method, which
performs only a primary filter of the records from the table - this is a
fast, approximate method that returns a superset of those points that
intersect the supplied polygon.

If you want only those points from the table that definitely intersect the
polygon, use STIntersects() instead of Filter()
--
View this message in context: http://old.nabble.com/sqlserver-BBox-%3D%3D-Filter-incorrect-result-returned-tp30860541p30860541.html
Sent from the GeoServer - Dev mailing list archive at Nabble.com.

Andrea_Aime4 · February 7, 2011, 7:37am

On Mon, Feb 7, 2011 at 4:35 AM, VT@anonymised.com <victor.tey@anonymised.com> wrote:

Hi, i noticed that using

<ogc:BBOX> filter == .Filter

<ogc:Intersects> == STIntersects

The mapping can be found in SQLServerFilterToSQL.visitBinarySpatialOperator.

Because of the use of .Filter, <ogc:BBOX> seem to be returning me the wrong
error.

<ogc:BBOX>
<ogc:PropertyName>er:location</ogc:PropertyName>
<gml:Envelope srsName="EPSG:4326">
<gml:lowerCorner>114 30</gml:lowerCorner>
<gml:upperCorner>115 31</gml:upperCorner>
</gml:Envelope>
</ogc:BBOX>

>From the sqlserver, running the following query generated as a result of the
above bbox using .FILTER returns me

SELECT
CAST("LOCATION".STSrid as VARCHAR) + ':' + "LOCATION".STAsText() as
"LOCATION"
FROM "ER_MININGFEATUREOCCURRENCE"
WHERE "LOCATION".Filter(geometry::STGeomFromText('POLYGON ((114 30, 114 31,
115 31, 115 30, 114 30))', 4326)) = 1

4326:POINT (117.3255 -35.03178)
4326:POINT (117.573097 -35.05859)
4326:POINT (117.791397 -35.041531)
4326:POINT (117.140676 -35.044455)
4326:POINT (117.325104 -35.03162)
4326:POINT (117.325104 -35.03162)

which is way out of range. Changing it to the use of ogc:Intersects yields
the correct result. So is it really accurate to use BBOX with .FILTER?

I post my question to the sqlserver forum and this is their reply

This is expected behaviour - you're using the Filter() method, which
performs only a primary filter of the records from the table - this is a
fast, approximate method that returns a superset of those points that
intersect the supplied polygon.

If you want only those points from the table that definitely intersect the
polygon, use STIntersects() instead of Filter()

This is the usual WMS/WFS BBOX desirable behavior thing.

In GeoTools we only have one bbox filter concept.
For WMS using the secondary filter is so expensive that it would render
the WMS useless.
For WFS the spec says that bbox is just a shortcut for intersects, yet
often people just want an approximate but very fast result.

In JDBC data stores configuration there is often a "loose/approximate bbox"
flag (not sure about sql server):
- if you check it you get good WMS performance, good WFS performance,
but approximate results in the WFS case
- if you uncheck it you make the WMS visibly slow but you get
the expected WFS results

In the case of complex features the data path is long and complex
enough that probably you won't see that much of a difference, so
I suggest you just uncheck the flag, if it's there.
If it's not, I guess someone will need to add that flag.

Alternatively, but this would require some discussion, we could
apply a filter visitor in the WFS paths that replaces all BBOX occurrences
with the equivalent Intersects filter

Cheers
Andrea

--
-------------------------------------------------------
Ing. Andrea Aime
GeoSolutions S.A.S.
Tech lead

Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy

phone: +39 0584 962313
fax: +39 0584 962313
mob: +39 333 8128928

http://www.geo-solutions.it
http://geo-solutions.blogspot.com/
http://www.youtube.com/user/GeoSolutionsIT
http://www.linkedin.com/in/andreaaime
http://twitter.com/geowolf

-------------------------------------------------------

VT1 · February 7, 2011, 7:41am

but the results are way off the bbox. its not even close though. Is that the
expected behaviour as well?

Andrea Aime-5 wrote:

On Mon, Feb 7, 2011 at 4:35 AM, VT@anonymised.com <victor.tey@anonymised.com> wrote:

Hi, i noticed that using

<ogc:BBOX> filter == .Filter

<ogc:Intersects> == STIntersects

The mapping can be found in
SQLServerFilterToSQL.visitBinarySpatialOperator.

Because of the use of .Filter, <ogc:BBOX> seem to be returning me the
wrong
error.

<ogc:BBOX>
<ogc:PropertyName>er:location</ogc:PropertyName>
<gml:Envelope srsName="EPSG:4326">
<gml:lowerCorner>114 30</gml:lowerCorner>
<gml:upperCorner>115 31</gml:upperCorner>
</gml:Envelope>
</ogc:BBOX>

>From the sqlserver, running the following query generated as a result of
the
above bbox using .FILTER returns me

SELECT
CAST("LOCATION".STSrid as VARCHAR) + ':' + "LOCATION".STAsText() as
"LOCATION"
FROM "ER_MININGFEATUREOCCURRENCE"
WHERE "LOCATION".Filter(geometry::STGeomFromText('POLYGON ((114 30, 114
31,
115 31, 115 30, 114 30))', 4326)) = 1

4326:POINT (117.3255 -35.03178)
4326:POINT (117.573097 -35.05859)
4326:POINT (117.791397 -35.041531)
4326:POINT (117.140676 -35.044455)
4326:POINT (117.325104 -35.03162)
4326:POINT (117.325104 -35.03162)

which is way out of range. Changing it to the use of ogc:Intersects
yields
the correct result. So is it really accurate to use BBOX with .FILTER?

I post my question to the sqlserver forum and this is their reply

This is expected behaviour - you're using the Filter() method, which
performs only a primary filter of the records from the table - this is a
fast, approximate method that returns a superset of those points that
intersect the supplied polygon.

If you want only those points from the table that definitely intersect
the
polygon, use STIntersects() instead of Filter()

This is the usual WMS/WFS BBOX desirable behavior thing.

In GeoTools we only have one bbox filter concept.
For WMS using the secondary filter is so expensive that it would render
the WMS useless.
For WFS the spec says that bbox is just a shortcut for intersects, yet
often people just want an approximate but very fast result.

In JDBC data stores configuration there is often a "loose/approximate
bbox"
flag (not sure about sql server):
- if you check it you get good WMS performance, good WFS performance,
but approximate results in the WFS case
- if you uncheck it you make the WMS visibly slow but you get
the expected WFS results

In the case of complex features the data path is long and complex
enough that probably you won't see that much of a difference, so
I suggest you just uncheck the flag, if it's there.
If it's not, I guess someone will need to add that flag.

Alternatively, but this would require some discussion, we could
apply a filter visitor in the WFS paths that replaces all BBOX occurrences
with the equivalent Intersects filter

Cheers
Andrea

--
-------------------------------------------------------
Ing. Andrea Aime
GeoSolutions S.A.S.
Tech lead

Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy

phone: +39 0584 962313
fax: +39 0584 962313
mob: +39 333 8128928

http://www.geo-solutions.it
http://geo-solutions.blogspot.com/
http://www.youtube.com/user/GeoSolutionsIT
http://www.linkedin.com/in/andreaaime
http://twitter.com/geowolf

-------------------------------------------------------

------------------------------------------------------------------------------
The modern datacenter depends on network connectivity to access resources
and provide services. The best practices for maximizing a physical
server's
connectivity to a physical network are well understood - see how these
rules translate into the virtual world?
http://p.sf.net/sfu/oracle-sfdevnlfb
_______________________________________________
Geoserver-devel mailing list
Geoserver-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-devel

--
View this message in context: http://old.nabble.com/sqlserver-BBox-%3D%3D-Filter-incorrect-result-returned-tp30860541p30861280.html
Sent from the GeoServer - Dev mailing list archive at Nabble.com.

Andrea_Aime4 · February 7, 2011, 8:14am

On Mon, Feb 7, 2011 at 8:41 AM, VT@anonymised.com <victor.tey@anonymised.com> wrote:

but the results are way off the bbox. its not even close though. Is that the
expected behaviour as well?

If the spatial filter in sql is that lousy, yes

Cheers
Andrea

--
-------------------------------------------------------
Ing. Andrea Aime
GeoSolutions S.A.S.
Tech lead

Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy

phone: +39 0584 962313
fax: +39 0584 962313
mob: +39 333 8128928

http://www.geo-solutions.it
http://geo-solutions.blogspot.com/
http://www.youtube.com/user/GeoSolutionsIT
http://www.linkedin.com/in/andreaaime
http://twitter.com/geowolf

-------------------------------------------------------

Justin_Deoliveira · February 7, 2011, 3:16pm

Yeah, this is something i never got around to doing. The loose bbox flag is there but the sql server dialect does not check it basically always assuming “loose bbox”.

The patch should be straight forward. Just a check for the flag in SQLServerFilterToSQL. Look at PostgisFilterToSQL for an example.

On Mon, Feb 7, 2011 at 1:14 AM, Andrea Aime <andrea.aime@anonymised.com> wrote:

On Mon, Feb 7, 2011 at 8:41 AM, VT@anonymised.com victor.tey@anonymised.com wrote:

but the results are way off the bbox. its not even close though. Is that the
expected behaviour as well?

If the spatial filter in sql is that lousy, yes

Cheers
Andrea

–

Ing. Andrea Aime
GeoSolutions S.A.S.
Tech lead

Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy

phone: +39 0584 962313
fax: +39 0584 962313
mob: +39 333 8128928

http://www.geo-solutions.it
http://geo-solutions.blogspot.com/
http://www.youtube.com/user/GeoSolutionsIT
http://www.linkedin.com/in/andreaaime
http://twitter.com/geowolf

The modern datacenter depends on network connectivity to access resources
and provide services. The best practices for maximizing a physical server’s
connectivity to a physical network are well understood - see how these
rules translate into the virtual world?
http://p.sf.net/sfu/oracle-sfdevnlfb

Geoserver-devel mailing list
Geoserver-devel@anonymised.comsts.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-devel

–
Justin Deoliveira
OpenGeo - http://opengeo.org
Enterprise support for open source geospatial.