Re: [Geoserver-users] Slow Performance on MSSQL View (Believe Caused by SQL Filter Function)

[mcharton]

After a lot of head scratching and trial and error, I believe I found a
solution (or at least workaround) for this.

 

The most obvious solution seemed to be to use a query hint to force the
spatial index in the SQL view (ie., WITH (INDEX ("MySpatialView"))).  This
seemed to work great with the SQL queries coming from the GetMap and
GetFeature requests.  However, if there were no spatial queries in the where
statement, then SQL would throw a "Query processor could not produce a query
plan because of the hints defined in this query."  But since all my queries
for my end use would involved a spatial element, I hoped it would work.

 

It did not work because Geoserver kicks off a couple queries each time the
layer's configs are reread to select the top 1 record in order to get the
dimension and srid.  I'm really not sure why it does this since this data is
already stored in the configs for the layer. but regardless, it failed in
SQL because there was no spatial query in the where statement.  I also
suspect I would have not been able to create a layer from that view for the
same reason (in this case I was modifying a view that already existed which
was already tied to an existing Geoserver layer).

 

IN THE END, I created the sql view directly through Geoserver instead of in
SQL and included the table hint there.  It looks like since both the
dimension and SRID are manually entered when creating the view in Geoserver,
those two "select top 1" queries are never fired off when the layer's
configs are reread.  I suspect that if I tried a GetFeature request without
a spatial filter, then it would error out. but that is not a need I have for
this layer.

 

Maybe this will help someone else.  I apologize if it was unclear.

 

Thanks,

Matt 

 

From: mchar...@datascoutpro.com  
Sent: Tuesday, June 29, 2021 4:49 PM
To: 'GeoServer Users' 
Subject: Slow Performance on MSSQL View (Believe Caused by SQL Filter
Function)

 

We have a MSSQL 2012 View added as a layer in Geoserver 2.17.2 that performs
well with GetMap requests but times out with GetFeature requests.

 

We have tracked it down to the following Query sent from Geoserver to MSSQL:


 

SELECT top 1000 * FROM "schema"."MyTable_view" 

WHERE (

"SHAPE".Filter(geometry::STGeomFromText('POLYGON (())', 4326))
= 1 AND 

  "SHAPE".STIntersects(geometry::STGeomFromText('POLYGON
(())', 4326)) = 1   AND 

  "Field1" = 1234 AND 

"Field1" IS NOT NULL

)

 

The view is just an inner join between two tables, both of which have a
variety of indexes including a proper spatial index on the "shape" field.

 

If I run this query manually against SQL, it basically runs indefinitely.
HOWEVER, 

1.  if I remove the "SHAPE".Filter() function in the where clause, it
returns results in less than a second.
2.  If I remove the subsequent "Field1" query criteria from the where
clause, it returns results in less than a second.  I assume this points to
the culprit here being that it is using the Field1 index instead of the
spatial index on the tables.

 

Is there a good solution to this problem?  We are somewhat new to Geoserver,
so we may very well be doing something wrong.  I have tried to find a way to
force the spatial query in the view with no luck.  Any help would be much
appreciated!

 

Thanks,

 

Matt

___
Geoserver-users mailing list

Please make sure you read the following two resources before posting to this 
list:
- Earning your support instead of buying it, but Ian Turton: 
http://www.ianturton.com/talks/foss4g.html#/
- The GeoServer user list posting guidelines: 
http://geoserver.org/comm/userlist-guidelines.html

If you want to request a feature or an improvement, also see this: 
https://github.com/geoserver/geoserver/wiki/Successfully-requesting-and-integrating-new-features-and-improvements-in-GeoServer


Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

[Geoserver-users] Slow Performance on MSSQL View (Believe Caused by SQL Filter Function)

[mcharton]

We have a MSSQL 2012 View added as a layer in Geoserver 2.17.2 that performs
well with GetMap requests but times out with GetFeature requests.

 

We have tracked it down to the following Query sent from Geoserver to MSSQL:


 

SELECT top 1000 * FROM "schema"."MyTable_view" 

WHERE (

"SHAPE".Filter(geometry::STGeomFromText('POLYGON (())', 4326))
= 1 AND 

  "SHAPE".STIntersects(geometry::STGeomFromText('POLYGON
(())', 4326)) = 1   AND 

  "Field1" = 1234 AND 

"Field1" IS NOT NULL

)

 

The view is just an inner join between two tables, both of which have a
variety of indexes including a proper spatial index on the "shape" field.

 

If I run this query manually against SQL, it basically runs indefinitely.
HOWEVER, 

1.  if I remove the "SHAPE".Filter() function in the where clause, it
returns results in less than a second.
2.  If I remove the subsequent "Field1" query criteria from the where
clause, it returns results in less than a second.  I assume this points to
the culprit here being that it is using the Field1 index instead of the
spatial index on the tables.

 

Is there a good solution to this problem?  We are somewhat new to Geoserver,
so we may very well be doing something wrong.  I have tried to find a way to
force the spatial query in the view with no luck.  Any help would be much
appreciated!

 

Thanks,

 

Matt

___
Geoserver-users mailing list

Please make sure you read the following two resources before posting to this 
list:
- Earning your support instead of buying it, but Ian Turton: 
http://www.ianturton.com/talks/foss4g.html#/
- The GeoServer user list posting guidelines: 
http://geoserver.org/comm/userlist-guidelines.html

If you want to request a feature or an improvement, also see this: 
https://github.com/geoserver/geoserver/wiki/Successfully-requesting-and-integrating-new-features-and-improvements-in-GeoServer


Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

Re: [Geoserver-users] PropertyIsLike Wildcard Does Not Capture Carriage Return

[mcharton]

Thank you so much, Jody, for the thorough reply.  I was initially averse to 
using a function on the adr_label since I fear it would prevent database 
indexes on the column from being used… but then I realized I’m already using an 
strToLowerCase in my real code (though not my example here), so I suppose it 
may be a moot point 😊

 

Thanks again, this is exactly what I was looking for!

 

 

 

From: Jody Garnett  
Sent: Thursday, April 1, 2021 10:50 AM
To: mchar...@datascoutpro.com
Cc: GeoServer Users 
Subject: Re: [Geoserver-users] PropertyIsLike Wildcard Does Not Capture 
Carriage Return

 

The filter is here 
https://github.com/geotools/geotools/blob/main/modules/library/main/src/main/java/org/geotools/filter/LikeFilterImpl.java

 

The eventual conversion to a java pattern is here 
https://github.com/geotools/geotools/blob/main/modules/library/main/src/main/java/org/geotools/filter/LikeToRegexConverter.java
 and there is a method to recognize and fix special characters - and newline is 
not on the list.

 

Looking a little more at java Pattern 
(https://docs.oracle.com/javase/8/docs/api/java/util/regex/Pattern.html) 
matcher used:

 

- The newline character is special to the java pattern matcher, so support 
would need to be added to LikeToRegexConverter

- The pattern matcher has a flag for MULTILINE which is not used

 

So you are not missing anything, support for multiline is not implemented / 
available.

 

As a workaround I suggest doing a string substitution using 

  function call on your adr_label and pass the result to PropertyIsLike 

 

Jody 

 

 

 

 

 

 

 

___
Geoserver-users mailing list

Please make sure you read the following two resources before posting to this 
list:
- Earning your support instead of buying it, but Ian Turton: 
http://www.ianturton.com/talks/foss4g.html#/
- The GeoServer user list posting guidelines: 
http://geoserver.org/comm/userlist-guidelines.html

If you want to request a feature or an improvement, also see this: 
https://github.com/geoserver/geoserver/wiki/Successfully-requesting-and-integrating-new-features-and-improvements-in-GeoServer


Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

[Geoserver-users] PropertyIsLike Wildcard Does Not Capture Carriage Return

[mcharton]

Hi Everyone,

 

I'm having a problem using a PropertyIsLike OGC Filter in a WFS GetFeature
request when the data includes carriage returns and linefeeds.  I would
expect that the wildcard should capture carriage returns, but that does not
appear to be the case unless I'm misunderstanding something.

 

As an example.  The following Filter returns the record I am looking for
because I explicitly search for the carriage return linefeed:



adr_label





 

But this Filter does _not_ return the record since I am only including the
wildcard character:



adr_label





 

First, am I missing something obvious here and going about this wrong?
Second, is there an easy way to accomplish this task without chaining a
bunch of PropertyIsLikes together in a long ?

 

I appreciate any insight, thanks so much!

Matt

 

___
Geoserver-users mailing list

Please make sure you read the following two resources before posting to this 
list:
- Earning your support instead of buying it, but Ian Turton: 
http://www.ianturton.com/talks/foss4g.html#/
- The GeoServer user list posting guidelines: 
http://geoserver.org/comm/userlist-guidelines.html

If you want to request a feature or an improvement, also see this: 
https://github.com/geoserver/geoserver/wiki/Successfully-requesting-and-integrating-new-features-and-improvements-in-GeoServer


Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users