Thanks again and
yes I was going to do UPDATE STATISTICS but am currently installing POSTGRES
and pgAccess on my 64 bit test environment system. Also planning to look into
PostGis. Thanks for help.
thr_exit :)
/maria
________________________________
On Thu, Dec 1, 2011 at 10:46 AM, <[email protected]<mailto:[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
