[Geoserver-users] Query returns wrong answer (sqlserver)

Lukas_Bergliden · April 17, 2013, 1:38pm

Hi,
I’m calling a geoserver 2.2 with a the following filter over wfs:

<ogc:Filter xmlns:ogc=“http://www.opengis.net/ogc”>
ogc:And
ogc:DWithin
ogc:PropertyNameGeometri</ogc:PropertyName>
<gml:Point xmlns:gml=“http://www.opengis.net/gml” srsName=“EPSG:900913”>
gml:pos2006635.412538 8263825.7559183</gml:pos>
</gml:Point>
<ogc:Distance units=“m”>50</ogc:Distance>
</ogc:DWithin>
<ogc:PropertyIsLike wildCard="" singleChar=“.” escapeChar=“!”>
ogc:PropertyNameInnehavare</ogc:PropertyName>
ogc:Literalic</ogc:Literal>
</ogc:PropertyIsLike>
</ogc:And>
</ogc:Filter>

Within 50m are 3 features, two of them with Innehavare = ica and one with another name.

When I execute the query I get 0 features in the result.
If I set the Like filters literal to * all three features are returned.

I checked the log and saw that this is the sql that is executed:
SELECT * FROM (SELECT “Id”,“Innehavare”,“NuvarandeTillstaand”,“Publicerad”,“Adress”,“Postnummer”,“Ort”,“Vaaningsplan”,“Placering”,“Kommun”,“Laen”,“OevrigInfo”,“Geometri”.STAsBinary() as “Geometri”, ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) AS _GT_ROW_NUMBER FROM “HjaertstartarPositionsVy” WHERE (“Geometri”.Filter(geometry::STGeomFromText(‘POINT (2006637.8011951 8263826.9502469)’, 900913)) = 1 AND “Geometri”.STDistance(geometry::STGeomFromText(‘POINT (2006637.8011951 8263826.9502469)’, 900913))<50.0 AND “Innehavare” LIKE ‘ica%’ )) AS _GT_PAGING_SUBQUERY WHERE _GT_ROW_NUMBER <= 1000000

and after a while I figured out that I could get it to work correctly if I removed
“Geometri”.Filter(geometry::STGeomFromText(‘POINT (2006637.8011951 8263826.9502469)’, 900913)) = 1

so that the query is:
SELECT * FROM (SELECT “Id”,“Innehavare”,“NuvarandeTillstaand”,“Publicerad”,“Adress”,“Postnummer”,“Ort”,“Vaaningsplan”,“Placering”,“Kommun”,“Laen”,“OevrigInfo”,“Geometri”.STAsBinary() as “Geometri”, ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) AS _GT_ROW_NUMBER FROM “HjaertstartarPositionsVy” WHERE (“Geometri”.STDistance(geometry::STGeomFromText(‘POINT (2006637.8011951 8263826.9502469)’, 900913))<50.0 AND “Innehavare” LIKE ‘ica%’ )) AS _GT_PAGING_SUBQUERY WHERE _GT_ROW_NUMBER <= 1000000

So, basicly why does the DWitin create a Geometry.Filter()? And how can I stop it from doing so?

Thank you for your time.

Best Regards
Lukas

Lukas Bergliden
Civilingenjör

Mobil 070 644 96 61 Electrum 234, 164 40 Kista
Direkt 08 630 75 09 Kistagången 16, 7 tr

Andrea_Aime4 · April 17, 2013, 2:13pm

On Wed, Apr 17, 2013 at 3:38 PM, Lukas Bergliden <lukas.bergliden@anonymised.com

wrote:

I checked the log and saw that this is the sql that is executed:
SELECT * FROM (SELECT
"Id","Innehavare","NuvarandeTillstaand","Publicerad","Adress","Postnummer","Ort","Vaaningsplan","Placering","Kommun","Laen","OevrigInfo","Geometri".STAsBinary()
as "Geometri", ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) AS
_GT_ROW_NUMBER FROM "HjaertstartarPositionsVy" WHERE
("Geometri".Filter(geometry::STGeomFromText('POINT (2006637.8011951
8263826.9502469)', 900913)) = 1 AND
"Geometri".STDistance(geometry::STGeomFromText('POINT (2006637.8011951
8263826.9502469)', 900913))<50.0 AND "Innehavare" LIKE 'ica%' )) AS
_GT_PAGING_SUBQUERY WHERE _GT_ROW_NUMBER <= 1000000

and after a while I figured out that I could get it to work correctly if I
removed
"Geometri".Filter(geometry::STGeomFromText('POINT (2006637.8011951
8263826.9502469)', 900913)) = 1

so that the query is:
SELECT * FROM (SELECT
"Id","Innehavare","NuvarandeTillstaand","Publicerad","Adress","Postnummer","Ort","Vaaningsplan","Placering","Kommun","Laen","OevrigInfo","Geometri".STAsBinary()
as "Geometri", ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) AS
_GT_ROW_NUMBER FROM "HjaertstartarPositionsVy" WHERE
("Geometri".STDistance(geometry::STGeomFromText('POINT (2006637.8011951
8263826.9502469)', 900913))<50.0 AND "Innehavare" LIKE 'ica%' )) AS
_GT_PAGING_SUBQUERY WHERE _GT_ROW_NUMBER <= 1000000

So, basicly why does the DWitin create a Geometry.Filter()? And how can I
stop it from doing so?

Ah yes, that does not seem right. You stop it by fixing the code that
generates the sql, you can find it in geotools, in the jdbc-sqlserver
module.
Patches welcomed, if not, at least open a ticket at jira.codehaus.org (if
you don't have an account, create it on xircles.codehaus.org)

Cheers
Andrea

--

GeoServer training in Milan, 6th & 7th June 2013! Visit
http://geoserver.geo-solutions.it for more information.

Ing. Andrea Aime
@geowolf
Technical Lead

GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549

http://www.geo-solutions.it
http://twitter.com/geosolutions_it

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

Lukas_Bergliden · April 17, 2013, 7:57pm

Thank you Andrea.
I’ve created a issue and I’ll take a look on it when I get some time over.

Vänliga Hälsningar
Lukas

Lukas Bergliden
Civilingenjör

Mobil 070 644 96 61 Electrum 234, 164 40 Kista
Direkt 08 630 75 09 Kistagången 16, 7 tr

···

I checked the log and saw that this is the sql that is executed:
SELECT * FROM (SELECT “Id”,“Innehavare”,“NuvarandeTillstaand”,“Publicerad”,“Adress”,“Postnummer”,“Ort”,“Vaaningsplan”,“Placering”,“Kommun”,“Laen”,“OevrigInfo”,“Geometri”.STAsBinary() as “Geometri”, ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) AS _GT_ROW_NUMBER FROM “HjaertstartarPositionsVy” WHERE (“Geometri”.Filter(geometry::STGeomFromText(‘POINT (2006637.8011951 8263826.9502469)’, 900913)) = 1 AND “Geometri”.STDistance(geometry::STGeomFromText(‘POINT (2006637.8011951 8263826.9502469)’, 900913))<50.0 AND “Innehavare” LIKE ‘ica%’ )) AS _GT_PAGING_SUBQUERY WHERE _GT_ROW_NUMBER <= 1000000

and after a while I figured out that I could get it to work correctly if I removed
“Geometri”.Filter(geometry::STGeomFromText(‘POINT (2006637.8011951 8263826.9502469)’, 900913)) = 1

so that the query is:
SELECT * FROM (SELECT “Id”,“Innehavare”,“NuvarandeTillstaand”,“Publicerad”,“Adress”,“Postnummer”,“Ort”,“Vaaningsplan”,“Placering”,“Kommun”,“Laen”,“OevrigInfo”,“Geometri”.STAsBinary() as “Geometri”, ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) AS _GT_ROW_NUMBER FROM “HjaertstartarPositionsVy” WHERE (“Geometri”.STDistance(geometry::STGeomFromText(‘POINT (2006637.8011951 8263826.9502469)’, 900913))<50.0 AND “Innehavare” LIKE ‘ica%’ )) AS _GT_PAGING_SUBQUERY WHERE _GT_ROW_NUMBER <= 1000000

So, basicly why does the DWitin create a Geometry.Filter()? And how can I stop it from doing so?

Ah yes, that does not seem right. You stop it by fixing the code that generates the sql, you can find it in geotools, in the jdbc-sqlserver module.
Patches welcomed, if not, at least open a ticket at jira.codehaus.org (if you don’t have an account, create it on xircles.codehaus.org)