_______________________________________________________________________________________
Note: This e-mail is subject to the disclaimer contained at the bottom of this
message.
_______________________________________________________________________________________
Hi all,
I've successfully setup a connection to SQL Server 2012 using the MS SQL Server
JDBC Drivers 4.0 with Geoserver 2.2.1 on a Windows 2002 R2 64bit server
(running on Tomcat 7 on Java 7 if you want all the details).
After setting up a layer and trying to access it through a WMS request without
success, I traced the problem to the fact that the query being sent to SQL
Server wasn't using the spatial index on the table. E.g:
SELECT myID
,GEOM.STAsBinary() as GEOM
FROM dbo.Current_MGRS_SRID_3857 WITH(INDEX=IDX_GEOM)
WHERE GEOM.STIntersects(geometry::STGeomFromText('POLYGON ((16824688.06064964
-4008988.3687579953, 16824688.06064964 -4007727.157791289, 16825949.27161635
-4007727.157791289, 16825949.27161635 -4008988.3687579953, 16824688.06064964
-4008988.3687579953))', 3857)) = 1
GO
Creating a view of the target table with an index hint in it solves the
original issue and returns a result sub-second in testing. But it doesn't
solve the problem in Geoserver as this disables some other queries that
Geoserver needs to sends to SQL that fail if an index hint is present, such as
when it tries to determine the native SRID of the data:
SELECT geom.STSrid from dbo.View_Current_MGRS_SRID_3857 WHERE geom.STSrid IS
NOT NULL
The end result is that I can't get Geoserver to work with our 13M point SQL
Server table, even though that volume of data is a breeze to work with when the
spatial index is used.
I have Postgres setup and could use it, but the point of my testing is to prove
the superior integration properties of Geoserver over ArcGIS Server, and SQL
Server is required as well as Postgres.
So, assuming Microsoft aren't going to improve the way spatial indexes are
activated by queries: what's the status of Geoserver supporting SQL Server
spatial index hints when requesting the data? And does anyone have a
workaround, noting the comment above...
Cheers,
Hugh
_______________________________________________________________________________________
The information transmitted in this message and its attachments (if any) is
intended
only for the person or entity to which it is addressed.
The message may contain confidential and/or privileged material. Any review,
retransmission, dissemination or other use of, or taking of any action in
reliance
upon this information, by persons or entities other than the intended recipient
is
prohibited.
If you have received this in error, please contact the sender and delete this
e-mail
and associated material from any computer.
The intended recipient of this e-mail may only use, reproduce, disclose or
distribute
the information contained in this e-mail and any attached files, with the
permission
of the sender.
This message has been scanned for viruses.
_______________________________________________________________________________________
------------------------------------------------------------------------------
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
http://p.sf.net/sfu/appdyn_sfd2d_oct
_______________________________________________
Geoserver-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
