Hi,
I am using GeoServer with MSSQL DataStore.
The table contains billions of record and keeps going on increasing.
Currently table Structure is
ID:nvarchar,
Name:nvarchar,
Lat:nvarchar,
Long:nvarchar,
geom:geometry
where geom column is SQL spatial_index.But still, request from Openlayer
result taking 10 to 15 second in response. Earlier it was before applied
Spatial Index on table query taking 40-50 second to process request.
Please guide me through the best approach to connect Sql Server with
billions records in one table and render with GeoServer.
Have you run the MSSQL profiler with your database?
This wil give you a good idea whether performance bottleneck is within the SQL server or in GeoServer.
Regards,
Ronald Hoek
Application Developer
ComponentAgroB.V.
Oud-Beijerland - The Netherlands
Website: http://www.componentagro.nl
KvK: H24264020
-----Oorspronkelijk bericht-----
Van: C.Ram <chhoturam.jat@anonymised.com>
Verzonden: zaterdag 2 juni 2018 17:23
Aan: geoserver-devel@lists.sourceforge.net
Onderwerp: [Geoserver-devel] What is the best table structure for MSSQL DataStore with GeoServer to process/analytics on billions of geometry data from Openlayer
Hi,
I am using GeoServer with MSSQL DataStore.
The table contains billions of record and keeps going on increasing.
Currently table Structure is
ID:nvarchar,
Name:nvarchar,
Lat:nvarchar,
Long:nvarchar,
geom:geometry
where geom column is SQL spatial_index.But still, request from Openlayer result taking 10 to 15 second in response. Earlier it was before applied Spatial Index on table query taking 40-50 second to process request.
Please guide me through the best approach to connect Sql Server with billions records in one table and render with GeoServer.
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot _______________________________________________
Geoserver-devel mailing list
Geoserver-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/geoserver-devel
Hi,
I am asking suggestion from you.
Currently, I'm taking Lat and Long column value, creating geometry(using
geometry::STLineFromText() etc.) and inserting into geom spatial index
column i.e geometry datatype.
On GeoServer as a SQL View Query using:
SELECT geom from tablename
According to you what will the best structure for the table to get good
performance with GeoServer?
for example table datatype for Lat, long, geom columns, If I'm doing an
index on the column which I will apply whether cluster, nonclustered or
spatial index etc. Any other etc.