What is the best table structure for MSSQL DataStore with GeoServer to process/analytics on billions of geometry data from Openlayer

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

What is the best table structure for MSSQL DataStore with GeoServer to process/analytics on billions of geometry data from Openlayer

C.Ram
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.





--
Sent from: http://osgeo-org.1560.x6.nabble.com/GeoServer-Dev-f3819232.html

------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-devel
Reply | Threaded
Open this post in threaded view
|

Re: What is the best table structure for MSSQL DataStore with GeoServer to process/analytics on billions of geometry data from Openlayer

Ronald Hoek - ComponentAgro B.V.
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 <[hidden email]>
Verzonden: zaterdag 2 juni 2018 17:23
Aan: [hidden email]
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.





--
Sent from: http://osgeo-org.1560.x6.nabble.com/GeoServer-Dev-f3819232.html

------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-devel

------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-devel
Reply | Threaded
Open this post in threaded view
|

Re: What is the best table structure for MSSQL DataStore with GeoServer to process/analytics on billions of geometry data from Openlayer

C.Ram
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.

Please suggest me.




--
Sent from: http://osgeo-org.1560.x6.nabble.com/GeoServer-Dev-f3819232.html

------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-devel
Reply | Threaded
Open this post in threaded view
|

Re: What is the best table structure for MSSQL DataStore with GeoServer to process/analytics on billions of geometry data from Openlayer

Ronald Hoek - ComponentAgro B.V.
Sorry C. ram,

I misunderstood you.

But I dont have much knowledge on SQL server spatial indexes.

I only found the documentatie at MS:

Spatial data in SQL server
Ronald

------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-devel