[Geoserver-users] Geoserver and SQL performance

GeoServer GeoServer User
1

Hi,

Two alternatives:

  1. Spatial index does not exist or is broken.

  2. Geoserver can’t utilize spatial index even it exists –> bug.

I would study first alternative 1).

-Jukka Rahkonen-

···

Lähettäjä: Alvey,David (Chief Executives) [mailto:David.Alvey@anonymised.com]
Lähetetty: 16. lokakuuta 2014 11:26
Vastaanottaja: geoserver-users@lists.sourceforge.net
Aihe: [Geoserver-users] Geoserver and SQL performance

I am experiencing significant delays in rendering png8 tiles from geoserver when the data is stored in in SQL server opposed to a shapefile.

There are almost 2 million polygon objects (Ordnance Survey Vector Map Local Buildings region).

The SLD is configured only to render the buildings when zoomed right in. It can take a couple of minutes to load and sometimes needs the geoserver service to be restarted.

Any ideas?

This email or email thread section has been classified CONTROLLED - This email requires controlled access by Council personnel and / or intended recipient(s) only. This email may contain business or personal information.

Think before you print! Save energy and paper. Do you really need to print this email?

Derbyshire County Council works to improve the lives of local people by delivering high quality services. You can find out more about us by visiting ‘www.derbyshire.gov.uk’. If you want to work for us go to our job pages on ‘www.derbyshire.gov.uk/jobs’. You can register for e-mail alerts, download job packs and apply on-line.

Please Note
This email is confidential, may be legally privileged and may contain personal views that are not the views of Derbyshire County Council. It is intended solely for the addressee. If this email was sent to you in error please notify us by replying to the email. Once you have done this please delete the email and do not disclose, copy, distribute, or rely on it.
Under the Data Protection Act 1998 and the Freedom of Information Act 2000 the contents of this email may be disclosed.

Derbyshire County Council reserves the right to monitor both sent and received emails.

2

Hi David,

I’m with Jukka, it’s almost certainly a spatial-index problem.

One way to test it is to access the SQL Server database directly with QGIS (or ArcGIS). If it exhibits the same problem when zoomed in then it’s almost definitely a Spatial Index problem. If not get back to the list with details of how you’re rendering them / storing them etc. and hopefully the issue will become apparent.

Cheers,

Jonathan

···

From: Rahkonen Jukka (Tike) [mailto:jukka.rahkonen@anonymised.com]
Sent: Thursday, October 16, 2014 9:56 AM
To: ‘Alvey,David (Chief Executives)’; ‘geoserver-users@anonymised.comorge.net’
Subject: Re: [Geoserver-users] Geoserver and SQL performance

Hi,

Two alternatives:

  1. Spatial index does not exist or is broken.

  2. Geoserver can’t utilize spatial index even it exists –> bug.

I would study first alternative 1).

-Jukka Rahkonen-

Lähettäjä: Alvey,David (Chief Executives) [mailto:David.Alvey@…4560…]
Lähetetty: 16. lokakuuta 2014 11:26
Vastaanottaja: geoserver-users@lists.sourceforge.net
Aihe: [Geoserver-users] Geoserver and SQL performance

I am experiencing significant delays in rendering png8 tiles from geoserver when the data is stored in in SQL server opposed to a shapefile.

There are almost 2 million polygon objects (Ordnance Survey Vector Map Local Buildings region).

The SLD is configured only to render the buildings when zoomed right in. It can take a couple of minutes to load and sometimes needs the geoserver service to be restarted.

Any ideas?

This email or email thread section has been classified CONTROLLED - This email requires controlled access by Council personnel and / or intended recipient(s) only. This email may contain business or personal information.

Think before you print! Save energy and paper. Do you really need to print this email?

Derbyshire County Council works to improve the lives of local people by delivering high quality services. You can find out more about us by visiting ‘www.derbyshire.gov.uk’. If you want to work for us go to our job pages on ‘www.derbyshire.gov.uk/jobs’. You can register for e-mail alerts, download job packs and apply on-line.

Please Note
This email is confidential, may be legally privileged and may contain personal views that are not the views of Derbyshire County Council. It is intended solely for the addressee. If this email was sent to you in error please notify us by replying to the email. Once you have done this please delete the email and do not disclose, copy, distribute, or rely on it.
Under the Data Protection Act 1998 and the Freedom of Information Act 2000 the contents of this email may be disclosed.

Derbyshire County Council reserves the right to monitor both sent and received emails.

This message has been scanned for viruses by MailControl, a service from BlackSpider Technology

Click here to report this email as spam.

3

In SQL Server you can catch the query with Activity Monitor, copy the query string and run it manually, which makes you possible to check the execuition plan if it uses the spatial index or not.

···

2014-10-16 11:12 GMT+02:00 Jonathan Moules <J.Moules@anonymised.com>:

Hi David,

I’m with Jukka, it’s almost certainly a spatial-index problem.

One way to test it is to access the SQL Server database directly with QGIS (or ArcGIS). If it exhibits the same problem when zoomed in then it’s almost definitely a Spatial Index problem. If not get back to the list with details of how you’re rendering them / storing them etc. and hopefully the issue will become apparent.

Cheers,

Jonathan

From: Rahkonen Jukka (Tike) [mailto:jukka.rahkonen@anonymised.com]
Sent: Thursday, October 16, 2014 9:56 AM
To: ‘Alvey,David (Chief Executives)’; ‘geoserver-users@anonymised.comists.sourceforge.net’
Subject: Re: [Geoserver-users] Geoserver and SQL performance

Hi,

Two alternatives:

  1. Spatial index does not exist or is broken.

  2. Geoserver can’t utilize spatial index even it exists –> bug.

I would study first alternative 1).

-Jukka Rahkonen-

Lähettäjä: Alvey,David (Chief Executives) [mailto:David.Alvey@anonymised.com]
Lähetetty: 16. lokakuuta 2014 11:26
Vastaanottaja: geoserver-users@lists.sourceforge.net
Aihe: [Geoserver-users] Geoserver and SQL performance

I am experiencing significant delays in rendering png8 tiles from geoserver when the data is stored in in SQL server opposed to a shapefile.

There are almost 2 million polygon objects (Ordnance Survey Vector Map Local Buildings region).

The SLD is configured only to render the buildings when zoomed right in. It can take a couple of minutes to load and sometimes needs the geoserver service to be restarted.

Any ideas?

This email or email thread section has been classified CONTROLLED - This email requires controlled access by Council personnel and / or intended recipient(s) only. This email may contain business or personal information.

Think before you print! Save energy and paper. Do you really need to print this email?

Derbyshire County Council works to improve the lives of local people by delivering high quality services. You can find out more about us by visiting ‘www.derbyshire.gov.uk’. If you want to work for us go to our job pages on ‘www.derbyshire.gov.uk/jobs’. You can register for e-mail alerts, download job packs and apply on-line.

Please Note
This email is confidential, may be legally privileged and may contain personal views that are not the views of Derbyshire County Council. It is intended solely for the addressee. If this email was sent to you in error please notify us by replying to the email. Once you have done this please delete the email and do not disclose, copy, distribute, or rely on it.
Under the Data Protection Act 1998 and the Freedom of Information Act 2000 the contents of this email may be disclosed.

Derbyshire County Council reserves the right to monitor both sent and received emails.

This message has been scanned for viruses by MailControl, a service from BlackSpider Technology

Click here to report this email as spam.

HR Wallingford and its subsidiaries uses faxes and emails for confidential and legally privileged business communications. They do not of themselves create legal commitments. Disclosure to parties other than addressees requires our specific consent. We are not liable for unauthorised disclosures nor reliance upon them.
If you have received this message in error please advise us immediately and destroy all copies of it.

HR Wallingford Limited
Howbery Park, Wallingford, Oxfordshire, OX10 8BA, United Kingdom
Registered in England No. 02562099

Comprehensive Server Monitoring with Site24x7.
Monitor 10 servers for $9/Month.
Get alerted through email, SMS, voice calls or mobile push notifications.
Take corrective actions from your mobile device.
http://p.sf.net/sfu/Zoho

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