Performance is terrible because:
(1) you are asking your database to extract strings from your JSON,
concatenate them, and construct geometries, *for every feature*, *for
every request*, and
(2) because your geometries are constructed for each request, you have
no spatial indices.
This means that, for every WMS request, even the smallest tile, your
database is going to have to brute force convert every feature in your
entire table via string manipulations and geometries, and then brute
force search them for the points in the BBOX.
I expect that GeoServer SQL Views will be most effective when you have
your geometries in an indexed geometry column. Can you create a table or
materialized view with an indexed geometry column, or add such a column
to your table?
I also see that your SELECT clause has a field
JSON_VALUE(ObjJson,'$.Details.Name.Value') which varies for every row. I
do not understand why this is desirable or even works. Can you not use a
fixed field name? You also have Type but this is compared to a constant
in your WHERE clause (but I can see that this might be useful (it makes
the output self-documenting)).
On 18/03/18 03:05, Karthikgb wrote:
we are rendering many points from the SQL db. where our each row is having
one column(json) which contains Lat and long. In sql views we have given
this query to render our datasets
SELECT Id,Type,JSON_VALUE(ObjJson,'$.Details.Name.Value') AS
ObjName,geometry::STGeomFromText('POINT(' +
CAST(JSON_VALUE(ObjJson,'$.Details.Longitude.Value') AS CHAR(20))+ ' ' +
CAST(JSON_VALUE(ObjJson,'$.Details.Latitude.Value') AS CHAR(20)) + ')',4326)
AS geometria FROM dbo.Objects WHERE Type='Carr'
- when we added 300 rows means 300 points its was working fine.
- As we have added more than 80000 rows means 80000 points its getting too
much hang.
- please, can anyone tell how shall I make use of the geoserve to render
more than 300000 points and each point will be able to update and delete?
- At client side i am using openlayer to show the wms layer.
- How to pass dynamic sql query to filter the points from one layer
--
Ben Caradoc-Davies <b...@transient.nz>
Director
Transient Software Limited <https://transient.nz/>
New Zealand
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Geoserver-users mailing list
Please make sure you read the following two resources before posting to this
list:
- Earning your support instead of buying it, but Ian Turton:
http://www.ianturton.com/talks/foss4g.html#/
- The GeoServer user list posting guidelines:
http://geoserver.org/comm/userlist-guidelines.html
If you want to request a feature or an improvement, also see this:
https://github.com/geoserver/geoserver/wiki/Successfully-requesting-and-integrating-new-features-and-improvements-in-GeoServer
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users
