We attach the Web Map service to the unsuspecting OpenSource database.
Last time, we constructed a spatial index, and now we’ll just use this skill to make a live (non-static) map service with a web interface and performance of, say, a million queries per day on a completely ordinary hardware.
The data .
- Their source will be OSM.
- The format for storing data in the DBMS is serialization from shpfile.
- We'll use dataset of Russia as test data.
- From all layers we take only three - reservoirs, forests and buildings (water-polygon, vegetation-polygon, building-polygon). This will be enough to confirm the performance of the technology and evaluate its performance.
Filling data .
- As a DBMS, we will continue to use ready-made Virtuoso V7.0.0
- Create a table of the same name for each layer of interest.
- The field of type long varbinary with geometry will be called “Shape”
- All existing fields will remain
- Add new ones
- _OBJECTID_ - record number
- minx_, miny_, maxx_, maxy_ - record extent
- Create a spatial index in the same way as we did before.
- The difference is that we now have areal, and not point objects, so one object can be indexed several times.
- We will do the rasterization of the most primitive - in the index gets the entire extent of the object, regardless of its geometry. But the real rasterization is considered to be a premature optimization.
- Another difference is in the uneven population of the map: thanks to Chukotka, the longitude varies from -180 to +180 degrees, and the latitude from ~ 40 to 80. Therefore, we will make the block index step such that on average one object falls into one index block. Why it is so will be discussed later.
- To work with the source data we will use the open library Shapefile C Library
- To work with BLOBs, you will have to add SHPSerializeObject and SHPDeserializeObject functions to it, similar to the native SHPWriteObject and SHPReadObject , but for working with memory
- To communicate with the server, we will use the native ODBC interface, but using the built-in driver, bypassing the system driver manager
- For speed, turn off autocommit connections and we will commit ourselves every 1000 objects
- In this mode
- Filling forests (~ 660 thousand objects) takes 1 min 56 seconds
- fill reservoirs (~ 380 thousand objects) - 1 min 9 seconds
- building fill (~ 5.3 million objects) - 16 min 18 seconds
Performance evaluation of the web interface .
- Yes, virtuoso has a web interface that looks like php. The vsp files (virtuoso server pages) with inserts on PL / SQL turn into stored procedures that form the html output.
We will create a simple page (test.vsp), which will only increase the counter and use it to check the rate of fire of the server.<html><body> <?vsp declare cnt integer; cnt := sequence_next ('xxx.YYY.__cnt'); ?> <table width="100%" border="1"> <tr><td align="center"><?vsp= cnt ?></td></tr> </table> </body></html> - To execute this page, we will put it in the test folder in the root ( ServerRoot parameter of the [HTTPServer] section of the virtuoso.ini file) and allow execution of files from this folder via isql (isql localhost: 1111 dba dba)
VHOST_DEFINE(lpath=>'/test/',ppath=>'/test/',vsp_user=>'DBA'); - Check that the page works through
localhost:8890/test/test.vsp - And we will massively demand it using wget
- The work of 20 parallel wget-s, each of which requested the mentioned page 10,000 times, took 28 seconds on the desktop under windows (i7, 8 cores). Consequently, the required performance of ~ 7000 requests per second and for us it is in no way (given that we aimed at a million requests per day) can not be a bottleneck.
Draw a map
- First, how do we get the data? Like that:
for select blob_to_string(Shape) as data from xxx.YYY."water-polygon" as x, xxx.YYY."v_water-polygon_spx_enum_items_in_box" as a where a.minx = cminx and a.miny = cminy and a.maxx = cmaxx and a.maxy = cmaxy and x."_OBJECTID_" = a.oid and x.maxx_ >= cminx and x.minx_ <= cmaxx and x.maxy_ >= cminy and x.miny_ <= cmaxy do { ... }xxx.YYY."water-polygon"is a table of reservoirs that we have just created and filled with data.xxx.YYY."v_water-polygon_spx_enum_items_in_box"is a procedural view on top of an auxiliary table that serves as a block spatial index. In fact, this is a coarse spatial filter that works up to the size of a spatial index block.a.minx = cminx and...- we set the call parameters for view, cminx ... - borders of the map fragment that we want to drawx."_OBJECTID_" = a.oid- join between the data table and the synthetic resultset obtained from the procedural viewx.maxx_ >= cminx and x.minx_ <= cmaxx ...intermediate spatial filter that allows you not to crawl behind blobs with geometries, if they are certainly not useful- All together, this gives us the opportunity in the body of the cycle to engage in, for example, drawing with good efficiency. At the same time, the drawing itself can be considered as a thin spatial filter.
- Once we have three layers of data, we will have to execute three subqueries for each client request.
- Now about the schedule. In order to draw directly in the process of the cursor, you have to write C - plugin . It is a dynamic library that the server loads at startup and gives you the opportunity to register new built-in functions PL / SQL.
- Actually for drawing we will use the library GD
- Add the following functions:
- img_create - creates a context for drawing a given size
- img_tostr - make gif from the current context
- img_fromptr - magic virtuoso for changing the pointer tag
- img_destroy - destroy context
- img_alloc_color - make the color identifier from RGB values
- img_draw_polyline - draw a polyline from a blob where the shape object is saved
- img_draw_polygone - the same for the polygon
- Content part of the plugin under the spoiler
#ifdef _USRDLL #include "plugin.h" #include "import_gate_virtuoso.h" #define wi_inst (wi_instance_get()[0]) #else #include <libutil.h> #include "sqlnode.h" #include "sqlbif.h" #include "wi.h" #include "Dk.h" #endif #include <stdio.h> #include <stdlib.h> #include <string.h> #include <math.h> #include "gd_utils.h" #include "gd/gd_lib.h" #include "shplib/shapefil.h" typedef struct img_ctx_s { int dx_; int dy_; double minx_; double miny_; double maxx_; double maxy_; double mulx_; double muly_; int black_; int red_; int green_; int blue_; int attr_; gdImagePtr img_; } img_ctx_t; caddr_t img_create_proc (caddr_t * qst, caddr_t * err, state_slot_t ** args) { img_ctx_t *ptr = (img_ctx_t *)dk_alloc_box (sizeof (img_ctx_t), DV_STRING); ptr->dx_ = bif_long_arg (qst, args, 0, "img_create_proc"); ptr->dy_ = bif_long_arg (qst, args, 1, "img_create_proc"); ptr->minx_ = bif_double_arg (qst, args, 2, "img_create_proc"); ptr->miny_ = bif_double_arg (qst, args, 3, "img_create_proc"); ptr->maxx_ = bif_double_arg (qst, args, 4, "img_create_proc"); ptr->maxy_ = bif_double_arg (qst, args, 5, "img_create_proc"); ptr->attr_ = bif_long_arg (qst, args, 6, "img_create_proc"); ptr->img_ = gdImageCreateTrueColor (ptr->dx_, ptr->dy_); ptr->mulx_ = ptr->dx_/fabs(ptr->maxx_ - ptr->minx_); ptr->muly_ = ptr->dy_/fabs(ptr->maxy_ - ptr->miny_); ptr->black_ = gdImageColorAllocate (ptr->img_, 0, 0, 0); ptr->blue_ = gdImageColorAllocate (ptr->img_, 0, 0, 255); ptr->red_ = gdImageColorAllocate (ptr->img_, 255, 0, 0); ptr->green_ = gdImageColorAllocate (ptr->img_, 0, 255, 0); return (caddr_t)ptr; } caddr_t img_saveas_proc (caddr_t * qst, caddr_t * err, state_slot_t ** args) { img_ctx_t *ptr = (img_ctx_t *)bif_arg (qst, args, 0, "img_saveas_proc"); gdImagePtr im = ptr->img_; caddr_t fname = bif_string_arg (qst, args, 1, "img_saveas_proc"); FILE *out = fopen (fname, "wb"); if (NULL != out) { gdImageGif (im, out); fclose (out); return (caddr_t)0; } return (caddr_t)-1; } static unsigned char clip(int value) { if (value < 0) value = 0; else if (value > 255) value = 255; return value; } caddr_t img_fromptr_proc (caddr_t * qst, caddr_t * err, state_slot_t ** args) { ptrlong addr = unbox(bif_long_arg (qst, args, 0, "img_fromptr_proc")); return addr; } caddr_t img_tostr_proc (caddr_t * qst, caddr_t * err, state_slot_t ** args) { img_ctx_t *ptr = (img_ctx_t *)bif_arg (qst, args, 0, "img_tostr_proc"); gdImagePtr im = ptr->img_; void *rv = NULL; caddr_t ret = NULL; int size = 0; gdIOCtx *out = gdNewDynamicCtx (2048, NULL); gdImageGifCtx (im, out); rv = gdDPExtractData (out, &size); if (NULL == rv || size <= 0) return 0; out->gd_free (out); ret = dk_alloc_box (size, DV_STRING); memcpy (ret, rv, size); gdFree(rv); return (caddr_t)box_num (ret); } caddr_t img_destroy_proc (caddr_t * qst, caddr_t * err, state_slot_t ** args) { img_ctx_t *ptr = (img_ctx_t *)bif_arg (qst, args, 0, "img_destroy_proc"); gdImagePtr im = ptr->img_; gdImageDestroy(im); /*dk_free_box (ptr);*/ return 0; } caddr_t img_draw_polyline_proc (caddr_t * qst, caddr_t * err, state_slot_t ** args) { img_ctx_t *ptr = (img_ctx_t *)bif_arg (qst, args, 0, "img_draw_polyline_proc"); gdImagePtr im = ptr->img_; caddr_t data = bif_string_arg (qst, args, 1, "img_draw_polyline_proc"); SHPObject *shp = SHPDeserialize (data); int nparts = shp->nParts; int npoints = shp->nVertices; double *px = shp->padfX; double *py = shp->padfY; int i, j; int color = bif_long_arg (qst, args, 2, "img_draw_polyline_proc"); gdImageSetAntiAliased (im, color); for (i = 0; i < nparts; i++) { long ps = shp->panPartStart[i]; long psz = (i==(nparts-1))? npoints-ps : shp->panPartStart[i+1]-ps; gdPointPtr points = (gdPointPtr)malloc(sizeof (gdPoint) * psz); for (j = 0; j < psz; j++) { points[j].x = (px[ps+j] - ptr->minx_) * ptr->mulx_; points[j].y = (ptr->attr_ & 1) ? ptr->dy_ - (py[ps+j] - ptr->miny_) * ptr->muly_: (py[ps+j] - ptr->miny_) * ptr->muly_; } for (j = 1; j < psz; j++) { /*if (points[j].x == points[j-1].x && points[j].y == points[j-1].y) gdImageSetPixel (im, points[j].x, points[j].y, ptr->blue_); else*/ gdImageLine (im, points[j].x, points[j].y, points[j-1].x, points[j-1].y, gdAntiAliased); } free (points); } SHPDestroyObject (shp); return 0; } caddr_t img_draw_polygone_proc (caddr_t * qst, caddr_t * err, state_slot_t ** args) { img_ctx_t *ptr = (img_ctx_t *)bif_arg (qst, args, 0, "img_draw_polygone_proc"); gdImagePtr im = ptr->img_; caddr_t data = bif_string_arg (qst, args, 1, "img_draw_polygone_proc"); SHPObject *shp = SHPDeserialize (data); int nparts = shp->nParts; int npoints = shp->nVertices; double *px = shp->padfX; double *py = shp->padfY; int i,j; int color = bif_long_arg (qst, args, 2, "img_draw_polygone_proc"); int bcolor = bif_long_arg (qst, args, 3, "img_draw_polygone_proc"); for (i = 0; i < nparts; i++) { long ps = shp->panPartStart[i]; long psz = (i==(nparts-1))? npoints-ps : shp->panPartStart[i+1]-ps; gdPointPtr points = (gdPointPtr)malloc (sizeof (gdPoint) * psz); for (j = 0; j < psz; j++) { points[j].x = (px[ps+j] - ptr->minx_) * ptr->mulx_; points[j].y = (ptr->attr_ & 1) ? ptr->dy_ - (py[ps+j] - ptr->miny_) * ptr->muly_: (py[ps+j] - ptr->miny_) * ptr->muly_; } gdImageSetAntiAliased (im, color); gdImageFilledPolygon (im, points, psz, gdAntiAliased); if (bcolor >= 0) { gdImageSetAntiAliased (im, bcolor); gdImagePolygon (im, points, psz, gdAntiAliased); } free (points); } SHPDestroyObject(shp); return 0; } caddr_t img_alloc_color_proc (caddr_t * qst, caddr_t * err, state_slot_t ** args) { img_ctx_t *ptr = (img_ctx_t *)bif_arg (qst, args, 0, "img_alloc_color_proc"); gdImagePtr im = ptr->img_; int r = bif_long_arg (qst, args, 1, "img_alloc_color_proc"); int g = bif_long_arg (qst, args, 2, "img_alloc_color_proc"); int b = bif_long_arg (qst, args, 3, "img_alloc_color_proc"); return box_num(gdImageColorAllocateAlpha (ptr->img_, r, g, b, 0)); } void init_shcall_gd_utils () { bif_define ("img_create", img_create_proc); bif_define ("img_saveas", img_saveas_proc); bif_define ("img_tostr", img_tostr_proc); bif_define ("img_fromptr", img_fromptr_proc); bif_define ("img_destroy", img_destroy_proc); bif_define ("img_alloc_color", img_alloc_color_proc); bif_define ("img_draw_polyline", img_draw_polyline_proc); bif_define ("img_draw_polygone", img_draw_polygone_proc); } - Now drawing reservoirs will look like this:
declare img any; img := img_create (512, 512, cminx, cminy, cmaxx, cmaxy, 1); declare cl integer; declare bg integer; cl := img_alloc_color (img, 0, 0, 255); bg := img_alloc_color (img, 0, 0, 200); whenever not found goto nf; for select blob_to_string(Shape) as data from xxx.YYY."water-polygon" as x, xxx.YYY."v_water-polygon_spx_enum_items_in_box" as a where a.minx = cminx and a.miny = cminy and a.maxx = cmaxx and a.maxy = cmaxy and x."_OBJECTID_" = a.oid and x.maxx_ >= cminx and x.minx_ <= cmaxx and x.maxy_ >= cminy and x.miny_ <= cmaxy do { img_draw_polygone (img, data, cl, bg); } nf:; declare ptr integer; ptr := img_tostr (img); -- gif img_destroy (img); -- declare image any; image := img_fromptr(ptr); -- http_header ('Content-type: image/gif\t\n'); -- http(image); -- - The transfer of coordinates page itself will pass through the parameters of the URL,body of the page under the spoiler
<?vsp if ({?'getfile'} <> '') { http_header ('Content-type: image/gif\t\n'); -- set the header to jpg declare s_params any; s_params := deserialize (decode_base64 (get_keyword ('params', params, ''))); -- dbg_obj_print(s_params); declare "min_x" double precision; declare "min_y" double precision; declare "max_x" double precision; declare "max_y" double precision; declare "cminx" double precision; declare "cminy" double precision; declare "cmaxx" double precision; declare "cmaxy" double precision; declare "s" varchar; s := get_keyword ('minx', s_params, ''); if (s = '') cminx := min_x; else cminx := atof(s); s := get_keyword ('miny', s_params, ''); if (s = '') cminy := min_y; else cminy := atof(s); s := get_keyword ('maxx', s_params, ''); if (s = '') cmaxx := max_x; else cmaxx := atof(s); s := get_keyword ('maxy', s_params, ''); if (s = '') cmaxy := max_y; else cmaxy := atof(s); declare img any; img := img_create (512, 512, cminx, cminy, cmaxx, cmaxy, 1); declare cl integer; declare bg integer; { cl := img_alloc_color (img, 0, 0, 255); bg := img_alloc_color (img, 0, 0, 200); whenever not found goto nf; for select blob_to_string(Shape) as data from xxx.YYY."water-polygon" as x, xxx.YYY."v_water-polygon_spx_enum_items_in_box" as a where a.minx = cminx and a.miny = cminy and a.maxx = cmaxx and a.maxy = cmaxy and x."_OBJECTID_" = a.oid and x.maxx_ >= cminx and x.minx_ <= cmaxx and x.maxy_ >= cminy and x.miny_ <= cmaxy do { img_draw_polygone (img, data, cl, bg); } nf:; cl := img_alloc_color (img, 0, 255, 0); bg := img_alloc_color (img, 0, 255, 0); whenever not found goto nf2; for select blob_to_string(Shape) as data from xxx.YYY."vegetation-polygon" as x, xxx.YYY."v_vegetation-polygon_spx_enum_items_in_box" as a where a.minx = cminx and a.miny = cminy and a.maxx = cmaxx and a.maxy = cmaxy and x."_OBJECTID_" = a.oid and x.maxx_ >= cminx and x.minx_ <= cmaxx and x.maxy_ >= cminy and x.miny_ <= cmaxy do { img_draw_polygone (img, data, cl, bg); } nf2:; cl := img_alloc_color (img, 255, 0, 0); bg := img_alloc_color (img, 255, 0, 0); whenever not found goto nf3; for select blob_to_string(Shape) as data from xxx.YYY."building-polygon" as x, xxx.YYY."v_building-polygon_spx_enum_items_in_box" as a where a.minx = cminx and a.miny = cminy and a.maxx = cmaxx and a.maxy = cmaxy and x."_OBJECTID_" = a.oid and x.maxx_ >= cminx and x.minx_ <= cmaxx and x.maxy_ >= cminy and x.miny_ <= cmaxy do { img_draw_polygone (img, data, cl, bg); } nf3:; } declare ptr integer; ptr := img_tostr (img); img_destroy (img); declare image any; image := img_fromptr(ptr); http(image); -- table and display --dbg_obj_print('boom',{?'id'}); return; } ?> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <?vsp declare "min_x" double precision; declare "min_y" double precision; declare "max_x" double precision; declare "max_y" double precision; min_x := 82.; max_x := 84.; min_y := 54.; max_y := 56.; params := vector_concat (params, vector ('minx', sprintf('%g', min_x), 'maxx', sprintf('%g', max_x), 'miny', sprintf('%g', min_y), 'maxy', sprintf('%g', max_y))); ?> <html> <body zonload="window.location.reload();"> <script language="JavaScript" type="text/javascript"> function full_extent() { document.forms['form1'].minx.value=<?vsp=min_x ?>; document.forms['form1'].miny.value=<?vsp=min_y ?>; document.forms['form1'].maxx.value=<?vsp=max_x ?>; document.forms['form1'].maxy.value=<?vsp=max_y ?>; } </script> <script language="JavaScript" type="text/javascript"> function zoom_in() { var dx = Math.abs(document.forms['form1'].maxx.value - document.forms['form1'].minx.value); dx = dx/8; var tmp = document.forms['form1'].maxx.value; tmp -= dx; document.forms['form1'].maxx.value = tmp; tmp = document.forms['form1'].minx.value; tmp -= -dx; document.forms['form1'].minx.value = tmp; var dy = Math.abs(document.forms['form1'].maxy.value - document.forms['form1'].miny.value); dy = dy/8; tmp = document.forms['form1'].maxy.value; tmp -= dy; document.forms['form1'].maxy.value = tmp; tmp = document.forms['form1'].miny.value; tmp -= -dy; document.forms['form1'].miny.value = tmp; } </script> <script language="JavaScript" type="text/javascript"> function zoom_out() { var dx = -Math.abs(document.forms['form1'].maxx.value - document.forms['form1'].minx.value); dx = dx/8; var tmp = document.forms['form1'].maxx.value; tmp -= dx; document.forms['form1'].maxx.value = tmp; tmp = document.forms['form1'].minx.value; tmp -= -dx; document.forms['form1'].minx.value = tmp; var dy = -Math.abs(document.forms['form1'].maxy.value - document.forms['form1'].miny.value); dy = dy/8; tmp = document.forms['form1'].maxy.value; tmp -= dy; document.forms['form1'].maxy.value = tmp; tmp = document.forms['form1'].miny.value; tmp -= -dy; document.forms['form1'].miny.value = tmp; } </script> <script language="JavaScript" type="text/javascript"> function pan_left() { var dx = Math.abs(document.forms['form1'].maxx.value - document.forms['form1'].minx.value); dx = dx/8; document.forms['form1'].maxx.value -= dx; document.forms['form1'].minx.value -= dx; } function pan_right() { var dx = -Math.abs(document.forms['form1'].maxx.value - document.forms['form1'].minx.value); dx = dx/8; document.forms['form1'].maxx.value -= dx; document.forms['form1'].minx.value -= dx; } function pan_up() { var dy = -Math.abs(document.forms['form1'].maxy.value - document.forms['form1'].miny.value); dy = dy/8; document.forms['form1'].maxy.value -= dy; document.forms['form1'].miny.value -= dy; } function pan_down() { var dy = Math.abs(document.forms['form1'].maxy.value - document.forms['form1'].miny.value); dy = dy/8; document.forms['form1'].maxy.value -= dy; document.forms['form1'].miny.value -= dy; } </script> <form method="GET" id="form1" name="form1"> <?vsp declare "cminx" double precision; declare "cminy" double precision; declare "cmaxx" double precision; declare "cmaxy" double precision; declare "s" varchar; s := get_keyword ('minx', params, ''); if (s = '') cminx := min_x; else cminx := atof(s); s := get_keyword ('miny', params, ''); if (s = '') cminy := min_y; else cminy := atof(s); s := get_keyword ('maxx', params, ''); if (s = '') cmaxx := max_x; else cmaxx := atof(s); s := get_keyword ('maxy', params, ''); if (s = '') cmaxy := max_y; else cmaxy := atof(s); declare mashtab double precision; mashtab := floor((cmaxx-cminx)*96./(512.*2.54)); declare cnt integer; cnt := sequence_next ('xxx.YYY.__cnt'); ?> <input type="hidden" name="minx" value='<?vsp=cminx ?>'/> <input type="hidden" name="miny" value='<?vsp=cminy ?>'/> <input type="hidden" name="maxx" value='<?vsp=cmaxx ?>'/> <input type="hidden" name="maxy" value='<?vsp=cmaxy ?>'/> <table width="100%" border="1" cellpadding="0" cellspacing="0" summary=""> <tr> <td width="20%" align="center" > </td> <td width="20%" align="center" >Min X</td> <td width="20%" align="center" >Min Y</td> <td width="20%" align="center" >Max X</td> <td width="20%" align="center" >Max Y</td> </tr> <tr> <td width="20%" align="center" >Default extent</td> <td width="20%" align="center" > <?vsp=min_x ?> </td> <td width="20%" align="center" > <?vsp=min_y ?> </td> <td width="20%" align="center" > <?vsp=max_x ?> </td> <td width="20%" align="center" > <?vsp=max_y ?> </td> </tr> <tr> <td width="20%" align="center" >Current extent</td> <td width="20%" align="center" > <?vsp=sprintf('%10.3f', cminx) ?> </td> <td width="20%" align="center" > <?vsp=sprintf('%10.3f', cminy) ?> </td> <td width="20%" align="center" > <?vsp=sprintf('%10.3f', cmaxx) ?> </td> <td width="20%" align="center" > <?vsp=sprintf('%10.3f', cmaxy) ?> </td> </tr> <tr> <td width="20%" align="center" > </td> <td width="20%" align="center" > <input type="button" onclick="javascript: full_extent(); document.forms['form1'].submit ();" value="[*]"/> </td> <td width="20%" align="center" > <input type="button" onclick="javascript: zoom_in(); document.forms['form1'].submit ();" value="+" /> <br><input type="button" onclick="javascript: zoom_out(); document.forms['form1'].submit ();" value="-" /> </td> <td width="20%" align="center" > <input type="button" onclick="javascript: pan_up(); document.forms['form1'].submit ();" value="^" /> <br> <input type="button" onclick="javascript: pan_down(); document.forms['form1'].submit ();" value="V" /> </td> <td width="20%" align="center" > <input type="button" onclick="javascript: pan_left(); document.forms['form1'].submit ();" value="<" /> <input type="button" onclick="javascript: pan_right(); document.forms['form1'].submit ();" value=">" /> </td> </tr> <tr><td colspan=5 align="center"> <p><img src=""></p> </td></tr> </table> </form></body></html> - And here is the result:
Performance evaluation.
- We will do it in about the same way as when we investigated the actual web interface.
- We will launch 4 wget in parallel, each of which will make 10,000 pairs of requests - to the page and the picture. Inside one wget, the requests are the same, different between them. This was done in view of the fact that there is not very much data, and they will somehow end up in memory, but if requests are made random, there will be a difficult estimated time to warm up. And if all requests are made the same, they will begin to be pushed by elbows. Nobody canceled the transaction requirements.
- Since after starting the processes in the background, there is a barrier (wait in bash), it will be measured at the slowest request.
- Requests will not be very large ~ 3X3 km,
- Attentive reader will say that these tests are sharpened by the index settings and there is no universality here. Yes it is. The nature of a block index is such that it effectively works only on queries that are commensurate with the size of its cell. This is on the one hand. And on the other
nobody forbids us to use several block indices simultaneously for different scales of the map. In addition, on the block indexes the light did not come together. If you consider the extent as a four-dimensional point, you can use, for example, point indices . - So, the total running time is 8 minutes 12 seconds. Or 81 requests per second.
- A million requests take 3 hours and 32 minutes, i.e. There is no significant degradation.
- The data flow through the network - 52 Mbps must be divided in half, because we have both clients and server on the same host
- CPU usage - 68%, the server takes 66 of them
- The server takes from 1.1 to 1.6 GB of RAM, there are apparently no leaks, in any case, after a million requests, 1.3 GB were used
- Database file size - ~ 3 GB
findings
- The performance goal has been achieved, experience has shown that a million requests per day are easily handled with a solid margin for peak loads.
- Given that desktop windows is not a server OS at all
- Yes, and the hardware was not server
- It should be noted that not even a prototype is presented, but a technology demonstrator. Only three layers were used, albeit fairly populated. In real life, of course, there are dozens of layers, but not all of them are needed at the same time. To make life easier, on a small scale, generalized layers are used. Etc. etc. But, once again, there was no task to repeat OSM, only to test the operation of the technology.
- If the interface seems to be ... ascetic, it is worth referring to the previous paragraph.
- Not implemented labeling ...
- Not implemented projections ...
- An important feature of this technology is the imperative description of the map project - in the form of PL / SQL text. This has its drawbacks, but also an undoubted plus - flexibility and complete freedom of action. So you can implement a system with the power of a full-fledged GIS and static tile pyramid performance. For example, start signing streets on top of traffic signs.
PS :
Whoever first correctly calls the pass at number 40 on a map fragment in the header of the article will receive thanks from the author.')
Source: https://habr.com/ru/post/200642/
All Articles