On Thu, Dec 1, 2011 at 10:46 AM, Maria Ripa <[email protected]> wrote:
> Tanks Andrea for your input, it seems hopeful.
>
> Our goal is to make geoserver work wiith MS SQL 2008 where data is stored
> as nvarchar which has led us to use GeoServer Nightly Build. Is there any
> experiece out there of the difference between POSTGRES and MSSQL from a
> spatial point of view. I try to "GetFeature" with a bounding box and the
> question that runs on the databaseserver is:
>
> SELECT "GlobalUniqueIdentifier", ..and 20 other columns..
> ,CAST("PointCoordinate".STSrid
> as VARCHAR) + ':' + "PointCoordinate".STAsText() as "PointCoordinate"
> FROM "DataTable"
> WHERE "PointCoordinate".Filter(geometry::STGeomFromText('POLYGON ((61
> 13, 61 13.5, 61.5 13.5, 61.5 13, 61 13))', 4326)) = 1
>
Ah, MSSQL. The databases that I'd recommend for large dataset serving are
PostGIS and Oracle
(and probably DB2 as well, but I cannot speak about it since I don't have
first hand experience
with it).
The first is well maintained both because of the enthusiams of developers
and because it's used
in production settings by entities that do sponsor GeoServer development,
the latter only because
of the second reason, MSSQL... neither of them.
The sources of the MSSQL store are here, if anybody wants to improve them
the contribution
would be welcomed (commercial support is available too, so there is an
option to have that done
by a core developer as well):
http://svn.osgeo.org/geotools/trunk/modules/plugin/jdbc/jdbc-sqlserver/
>
> The method Filter is supposed to be very fast but after 1 h 40 mns i get
> all memory on the disk eaten up by swapping (MSSQL). I study the estimated
> execution plan and it turns out MSSQL does not which to use the spatial
> index I made. As far as i can see i cannot "edit" the question that comes
> from Geoserver which is MS sulotion to the problem (using the WITH
> statement).
>
May it be that sql server query planner just does not know that using the
spatial index would be beneficial?
Try the UPDATE STATISTICS command on your table
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
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
-------------------------------------------------------
------------------------------------------------------------------------------
All the data continuously generated in your IT infrastructure
contains a definitive record of customers, application performance,
security threats, fraudulent activity, and more. Splunk takes this
data and makes sense of it. IT sense. And common sense.
http://p.sf.net/sfu/splunk-novd2d
_______________________________________________
Geoserver-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
