yukon/regress/geogridcoder/geosotgrid.sql-代码预览-yukon:基于openGauss的地理空间数据扩展项目

0c03048e创建于 2024年9月24日历史提交

--编码
SELECT ST_GeoSOTGrid(ST_GeomFromText('POINT(116.315 39.91027777777778)', 4490), 15);
SELECT ST_GeoSOTGrid(ST_GeomFromText('POINT(116.315 39.91027777777778)', 4490));
SELECT ST_GeoSOTGrid(ST_GeomFromText('POINT(116.315 39.91027777777778 12345)', 4490), 15);
SELECT count(*) FROM UNNEST(ST_GeoSOTGridAgg(ST_GeomFromText('MULTIPOLYGON (((118.35949889355601 32.809604400008496, 118.3596968618916 32.80899202183754, 118.36005349666094 32.80884561755304, 118.36015229494569 32.808626677902716, 118.36024493713795 32.80773313835468, 118.35949253782682 32.80670413084837, 118.35893288492495 32.80643149145906, 118.35751703332369 32.806579511199594, 118.35615747606215 32.8060163603961, 118.35418359442757 32.804396609811384, 118.35282058834792 32.803587297409116, 118.35244144512224 32.8035175550485, 118.35090752871746 32.8023351312754, 118.35072802536008 32.80187092749049, 118.35091050036917 32.80160357415574, 118.35142165097149 32.801358943886335, 118.35366635578647 32.80135552336618, 118.35422709418016 32.80149567982458, 118.35470444942315 32.80230636119275, 118.35545532453452 32.802615191262255, 118.35656425412338 32.80133257225893, 118.35669433503853 32.799987605568575, 118.35654785736406 32.79989209844715, 118.35726091847559 32.79896504479679, 118.35937304927371 32.798323618288386, 118.35983538405011 32.79845055675622, 118.35995818220293 32.798897115870545, 118.35918005313671 32.799819137943445, 118.35883837023526 32.800667549690495, 118.35925471580393 32.80115923666334, 118.35928027231374 32.80144182485759, 118.3590032287468 32.801715756868056, 118.35840602697569 32.80192179776719, 118.35833385597145 32.80215895070426, 118.35834077483098 32.80264669159818, 118.35878019020589 32.80293776141729, 118.35914577819572 32.802777660489134, 118.35941978011473 32.802157294376414, 118.36017094645557 32.80129915643249, 118.36016261862042 32.80115785413548, 118.35958090074342 32.800839643196376, 118.35989508324036 32.80018729345609, 118.36080400435365 32.79993518769732, 118.36148851267502 32.800148397255704, 118.361991548273 32.800115721942774, 118.36249513453646 32.79952044012142, 118.36249548189984 32.80011015001076, 118.362840008422 32.80057497170332, 118.36307405655556 32.80167398906179, 118.36340833954192 32.801722408938126, 118.36314489762128 32.8026198983897, 118.36308073989748 32.80373388338926, 118.36339701668454 32.80486181835052, 118.36388741228778 32.805765535128415, 118.3638337932425 32.806526568217286, 118.36318570790408 32.80721409672573, 118.36298784687922 32.80769134074979, 118.36190120397042 32.808175750170264, 118.36152711488313 32.80799892206737, 118.36104199655972 32.80719409713123, 118.36100342856014 32.80760130112362, 118.36123355931069 32.808266922006375, 118.36072981596779 32.80925064251398, 118.36003742358405 32.80984204416404, 118.35949889355601 32.809604400008496)))', 4490), 
21, 15));
SELECT array_length(ST_GeoSOTGridAgg(ST_GeomFromText('MULTIPOLYGON (((118.35949889355601 32.809604400008496, 118.3596968618916 32.80899202183754, 118.36005349666094 32.80884561755304, 118.36015229494569 32.808626677902716, 118.36024493713795 32.80773313835468, 118.35949253782682 32.80670413084837, 118.35893288492495 32.80643149145906, 118.35751703332369 32.806579511199594, 118.35615747606215 32.8060163603961, 118.35418359442757 32.804396609811384, 118.35282058834792 32.803587297409116, 118.35244144512224 32.8035175550485, 118.35090752871746 32.8023351312754, 118.35072802536008 32.80187092749049, 118.35091050036917 32.80160357415574, 118.35142165097149 32.801358943886335, 118.35366635578647 32.80135552336618, 118.35422709418016 32.80149567982458, 118.35470444942315 32.80230636119275, 118.35545532453452 32.802615191262255, 118.35656425412338 32.80133257225893, 118.35669433503853 32.799987605568575, 118.35654785736406 32.79989209844715, 118.35726091847559 32.79896504479679, 118.35937304927371 32.798323618288386, 118.35983538405011 32.79845055675622, 118.35995818220293 32.798897115870545, 118.35918005313671 32.799819137943445, 118.35883837023526 32.800667549690495, 118.35925471580393 32.80115923666334, 118.35928027231374 32.80144182485759, 118.3590032287468 32.801715756868056, 118.35840602697569 32.80192179776719, 118.35833385597145 32.80215895070426, 118.35834077483098 32.80264669159818, 118.35878019020589 32.80293776141729, 118.35914577819572 32.802777660489134, 118.35941978011473 32.802157294376414, 118.36017094645557 32.80129915643249, 118.36016261862042 32.80115785413548, 118.35958090074342 32.800839643196376, 118.35989508324036 32.80018729345609, 118.36080400435365 32.79993518769732, 118.36148851267502 32.800148397255704, 118.361991548273 32.800115721942774, 118.36249513453646 32.79952044012142, 118.36249548189984 32.80011015001076, 118.362840008422 32.80057497170332, 118.36307405655556 32.80167398906179, 118.36340833954192 32.801722408938126, 118.36314489762128 32.8026198983897, 118.36308073989748 32.80373388338926, 118.36339701668454 32.80486181835052, 118.36388741228778 32.805765535128415, 118.3638337932425 32.806526568217286, 118.36318570790408 32.80721409672573, 118.36298784687922 32.80769134074979, 118.36190120397042 32.808175750170264, 118.36152711488313 32.80799892206737, 118.36104199655972 32.80719409713123, 118.36100342856014 32.80760130112362, 118.36123355931069 32.808266922006375, 118.36072981596779 32.80925064251398, 118.36003742358405 32.80984204416404, 118.35949889355601 32.809604400008496)))', 4490), 
21, 20),1);
SELECT array_length(ST_GeoSOTGrid(ST_GeomFromText('MULTIPOLYGON (((118.35949889355601 32.809604400008496, 118.3596968618916 32.80899202183754, 118.36005349666094 32.80884561755304, 118.36015229494569 32.808626677902716, 118.36024493713795 32.80773313835468, 118.35949253782682 32.80670413084837, 118.35893288492495 32.80643149145906, 118.35751703332369 32.806579511199594, 118.35615747606215 32.8060163603961, 118.35418359442757 32.804396609811384, 118.35282058834792 32.803587297409116, 118.35244144512224 32.8035175550485, 118.35090752871746 32.8023351312754, 118.35072802536008 32.80187092749049, 118.35091050036917 32.80160357415574, 118.35142165097149 32.801358943886335, 118.35366635578647 32.80135552336618, 118.35422709418016 32.80149567982458, 118.35470444942315 32.80230636119275, 118.35545532453452 32.802615191262255, 118.35656425412338 32.80133257225893, 118.35669433503853 32.799987605568575, 118.35654785736406 32.79989209844715, 118.35726091847559 32.79896504479679, 118.35937304927371 32.798323618288386, 118.35983538405011 32.79845055675622, 118.35995818220293 32.798897115870545, 118.35918005313671 32.799819137943445, 118.35883837023526 32.800667549690495, 118.35925471580393 32.80115923666334, 118.35928027231374 32.80144182485759, 118.3590032287468 32.801715756868056, 118.35840602697569 32.80192179776719, 118.35833385597145 32.80215895070426, 118.35834077483098 32.80264669159818, 118.35878019020589 32.80293776141729, 118.35914577819572 32.802777660489134, 118.35941978011473 32.802157294376414, 118.36017094645557 32.80129915643249, 118.36016261862042 32.80115785413548, 118.35958090074342 32.800839643196376, 118.35989508324036 32.80018729345609, 118.36080400435365 32.79993518769732, 118.36148851267502 32.800148397255704, 118.361991548273 32.800115721942774, 118.36249513453646 32.79952044012142, 118.36249548189984 32.80011015001076, 118.362840008422 32.80057497170332, 118.36307405655556 32.80167398906179, 118.36340833954192 32.801722408938126, 118.36314489762128 32.8026198983897, 118.36308073989748 32.80373388338926, 118.36339701668454 32.80486181835052, 118.36388741228778 32.805765535128415, 118.3638337932425 32.806526568217286, 118.36318570790408 32.80721409672573, 118.36298784687922 32.80769134074979, 118.36190120397042 32.808175750170264, 118.36152711488313 32.80799892206737, 118.36104199655972 32.80719409713123, 118.36100342856014 32.80760130112362, 118.36123355931069 32.808266922006375, 118.36072981596779 32.80925064251398, 118.36003742358405 32.80984204416404, 118.35949889355601 32.809604400008496)))'
, 4490),21),1);
SELECT array_length(ST_GeoSOTGrid(ST_GeomFromText('MULTIPOLYGON (((118.35949889355601 32.809604400008496, 118.3596968618916 32.80899202183754, 118.36005349666094 32.80884561755304, 118.36015229494569 32.808626677902716, 118.36024493713795 32.80773313835468, 118.35949253782682 32.80670413084837, 118.35893288492495 32.80643149145906, 118.35751703332369 32.806579511199594, 118.35615747606215 32.8060163603961, 118.35418359442757 32.804396609811384, 118.35282058834792 32.803587297409116, 118.35244144512224 32.8035175550485, 118.35090752871746 32.8023351312754, 118.35072802536008 32.80187092749049, 118.35091050036917 32.80160357415574, 118.35142165097149 32.801358943886335, 118.35366635578647 32.80135552336618, 118.35422709418016 32.80149567982458, 118.35470444942315 32.80230636119275, 118.35545532453452 32.802615191262255, 118.35656425412338 32.80133257225893, 118.35669433503853 32.799987605568575, 118.35654785736406 32.79989209844715, 118.35726091847559 32.79896504479679, 118.35937304927371 32.798323618288386, 118.35983538405011 32.79845055675622, 118.35995818220293 32.798897115870545, 118.35918005313671 32.799819137943445, 118.35883837023526 32.800667549690495, 118.35925471580393 32.80115923666334, 118.35928027231374 32.80144182485759, 118.3590032287468 32.801715756868056, 118.35840602697569 32.80192179776719, 118.35833385597145 32.80215895070426, 118.35834077483098 32.80264669159818, 118.35878019020589 32.80293776141729, 118.35914577819572 32.802777660489134, 118.35941978011473 32.802157294376414, 118.36017094645557 32.80129915643249, 118.36016261862042 32.80115785413548, 118.35958090074342 32.800839643196376, 118.35989508324036 32.80018729345609, 118.36080400435365 32.79993518769732, 118.36148851267502 32.800148397255704, 118.361991548273 32.800115721942774, 118.36249513453646 32.79952044012142, 118.36249548189984 32.80011015001076, 118.362840008422 32.80057497170332, 118.36307405655556 32.80167398906179, 118.36340833954192 32.801722408938126, 118.36314489762128 32.8026198983897, 118.36308073989748 32.80373388338926, 118.36339701668454 32.80486181835052, 118.36388741228778 32.805765535128415, 118.3638337932425 32.806526568217286, 118.36318570790408 32.80721409672573, 118.36298784687922 32.80769134074979, 118.36190120397042 32.808175750170264, 118.36152711488313 32.80799892206737, 118.36104199655972 32.80719409713123, 118.36100342856014 32.80760130112362, 118.36123355931069 32.808266922006375, 118.36072981596779 32.80925064251398, 118.36003742358405 32.80984204416404, 118.35949889355601 32.809604400008496)))'
, 4490),20),1);
--最小外接网格支持输出三维网格
SELECT st_hasz(ST_GeoSOTGrid(ST_GeomFromText('POINT(116.315 39.91027777777778 100)', 4490)));
--网格编码正确性
SELECT array_length(ST_GeoSOTGrid(st_makeenvelope(115.605179,39.602211,116.782204,40.494509,4490),18),1); 
SELECT array_length(ST_GeoSOTGrid(st_makeenvelope(115.605179,39.602211,116.782204,40.494509,4490),22),1);
SELECT array_length(ST_GeoSOTGridAgg(st_makeenvelope(115.605179,39.602211,116.782204,40.494509,4490),22,1),1);
SELECT array_length(ST_GeoSOTGridAgg(st_makeenvelope(115.605179,39.602211,116.782204,40.494509,4490),21,1),1);

--高度转换
SELECT ST_GeoSOTGridZ(12345, 15);
SELECT ST_AltitudeFromGeoSOTGridZ(6, 15);

--从文本获取
SELECT ST_GeoSOTGridFromText('G001310322-230230');
SELECT ST_GeoSOTGridFromText('G001310322-230230', '110');

--转文本
SELECT ST_AsText(UNNEST(ST_GeoSOTGrid(ST_GeomFromText('POINT(116.315 39.91027777777778)', 4490), 15)));
SELECT ST_AsText(UNNEST(ST_GeoSOTGrid(ST_GeomFromText('POINT(116.315 39.91027777777778 12345)', 4490), 15)));

--转为矢量
SELECT ST_AsText(ST_GeomFromGeoSOTGrid('074EACB000000000000F0000'::geosotgrid));
SELECT ST_AsText(ST_GeomFromGeoSOTGrid('00B21A4984C0000000000120000F0001'::geosotgrid));
SELECT ST_AsText(UNNEST(ST_GeomFromGeoSOTGrid(ST_GeoSOTGrid(ST_GeomFromText('LINESTRING(116.315 39.91027777777778, 116.315 39.95027777777778)', 4490), 15))));
SELECT ST_AsText(UNNEST(ST_GeomFromGeoSOTGrid(ST_GeoSOTGrid(ST_GeomFromText('LINESTRING(116.315 39.91027777777778 12000, 116.315 39.95027777777778 12345)', 4490), 15))));

--类型判断
SELECT ST_HasZ('074EACB000000000000F0000'::geosotgrid);
SELECT ST_HasZ('00B21A4984C0000000000120000F0001'::geosotgrid);

--查看等级
SELECT ST_GetLevel('074EACB000000000000F0000'::geosotgrid);
SELECT ST_GetLevel('00B21A4984C0000000000120000F0001'::geosotgrid);
SELECT ST_GetLevelextremum(ST_GeoSOTGridAgg(ST_GeomFromText('MULTIPOLYGON (((118.35949889355601 32.809604400008496, 118.3596968618916 32.80899202183754, 118.36005349666094 32.80884561755304, 118.36015229494569 32.808626677902716, 118.36024493713795 32.80773313835468, 118.35949253782682 32.80670413084837, 118.35893288492495 32.80643149145906, 118.35751703332369 32.806579511199594, 118.35615747606215 32.8060163603961, 118.35418359442757 32.804396609811384, 118.35282058834792 32.803587297409116, 118.35244144512224 32.8035175550485, 118.35090752871746 32.8023351312754, 118.35072802536008 32.80187092749049, 118.35091050036917 32.80160357415574, 118.35142165097149 32.801358943886335, 118.35366635578647 32.80135552336618, 118.35422709418016 32.80149567982458, 118.35470444942315 32.80230636119275, 118.35545532453452 32.802615191262255, 118.35656425412338 32.80133257225893, 118.35669433503853 32.799987605568575, 118.35654785736406 32.79989209844715, 118.35726091847559 32.79896504479679, 118.35937304927371 32.798323618288386, 118.35983538405011 32.79845055675622, 118.35995818220293 32.798897115870545, 118.35918005313671 32.799819137943445, 118.35883837023526 32.800667549690495, 118.35925471580393 32.80115923666334, 118.35928027231374 32.80144182485759, 118.3590032287468 32.801715756868056, 118.35840602697569 32.80192179776719, 118.35833385597145 32.80215895070426, 118.35834077483098 32.80264669159818, 118.35878019020589 32.80293776141729, 118.35914577819572 32.802777660489134, 118.35941978011473 32.802157294376414, 118.36017094645557 32.80129915643249, 118.36016261862042 32.80115785413548, 118.35958090074342 32.800839643196376, 118.35989508324036 32.80018729345609, 118.36080400435365 32.79993518769732, 118.36148851267502 32.800148397255704, 118.361991548273 32.800115721942774, 118.36249513453646 32.79952044012142, 118.36249548189984 32.80011015001076, 118.362840008422 32.80057497170332, 118.36307405655556 32.80167398906179, 118.36340833954192 32.801722408938126, 118.36314489762128 32.8026198983897, 118.36308073989748 32.80373388338926, 118.36339701668454 32.80486181835052, 118.36388741228778 32.805765535128415, 118.3638337932425 32.806526568217286, 118.36318570790408 32.80721409672573, 118.36298784687922 32.80769134074979, 118.36190120397042 32.808175750170264, 118.36152711488313 32.80799892206737, 118.36104199655972 32.80719409713123, 118.36100342856014 32.80760130112362, 118.36123355931069 32.808266922006375, 118.36072981596779 32.80925064251398, 118.36003742358405 32.80984204416404, 118.35949889355601 32.809604400008496)))', 4490), 21, 20));
SELECT ST_GetLevelextremum(ST_GeoSOTGridAgg(ST_GeomFromText('MULTIPOLYGON (((118.35949889355601 32.809604400008496, 118.3596968618916 32.80899202183754, 118.36005349666094 32.80884561755304, 118.36015229494569 32.808626677902716, 118.36024493713795 32.80773313835468, 118.35949253782682 32.80670413084837, 118.35893288492495 32.80643149145906, 118.35751703332369 32.806579511199594, 118.35615747606215 32.8060163603961, 118.35418359442757 32.804396609811384, 118.35282058834792 32.803587297409116, 118.35244144512224 32.8035175550485, 118.35090752871746 32.8023351312754, 118.35072802536008 32.80187092749049, 118.35091050036917 32.80160357415574, 118.35142165097149 32.801358943886335, 118.35366635578647 32.80135552336618, 118.35422709418016 32.80149567982458, 118.35470444942315 32.80230636119275, 118.35545532453452 32.802615191262255, 118.35656425412338 32.80133257225893, 118.35669433503853 32.799987605568575, 118.35654785736406 32.79989209844715, 118.35726091847559 32.79896504479679, 118.35937304927371 32.798323618288386, 118.35983538405011 32.79845055675622, 118.35995818220293 32.798897115870545, 118.35918005313671 32.799819137943445, 118.35883837023526 32.800667549690495, 118.35925471580393 32.80115923666334, 118.35928027231374 32.80144182485759, 118.3590032287468 32.801715756868056, 118.35840602697569 32.80192179776719, 118.35833385597145 32.80215895070426, 118.35834077483098 32.80264669159818, 118.35878019020589 32.80293776141729, 118.35914577819572 32.802777660489134, 118.35941978011473 32.802157294376414, 118.36017094645557 32.80129915643249, 118.36016261862042 32.80115785413548, 118.35958090074342 32.800839643196376, 118.35989508324036 32.80018729345609, 118.36080400435365 32.79993518769732, 118.36148851267502 32.800148397255704, 118.361991548273 32.800115721942774, 118.36249513453646 32.79952044012142, 118.36249548189984 32.80011015001076, 118.362840008422 32.80057497170332, 118.36307405655556 32.80167398906179, 118.36340833954192 32.801722408938126, 118.36314489762128 32.8026198983897, 118.36308073989748 32.80373388338926, 118.36339701668454 32.80486181835052, 118.36388741228778 32.805765535128415, 118.3638337932425 32.806526568217286, 118.36318570790408 32.80721409672573, 118.36298784687922 32.80769134074979, 118.36190120397042 32.808175750170264, 118.36152711488313 32.80799892206737, 118.36104199655972 32.80719409713123, 118.36100342856014 32.80760130112362, 118.36123355931069 32.808266922006375, 118.36072981596779 32.80925064251398, 118.36003742358405 32.80984204416404, 118.35949889355601 32.809604400008496)))', 4490), 21, 19));
SELECT ST_GetLevelextremum(ST_GeoSOTGridAgg(ST_GeomFromText('MULTIPOLYGON (((118.35949889355601 32.809604400008496, 118.3596968618916 32.80899202183754, 118.36005349666094 32.80884561755304, 118.36015229494569 32.808626677902716, 118.36024493713795 32.80773313835468, 118.35949253782682 32.80670413084837, 118.35893288492495 32.80643149145906, 118.35751703332369 32.806579511199594, 118.35615747606215 32.8060163603961, 118.35418359442757 32.804396609811384, 118.35282058834792 32.803587297409116, 118.35244144512224 32.8035175550485, 118.35090752871746 32.8023351312754, 118.35072802536008 32.80187092749049, 118.35091050036917 32.80160357415574, 118.35142165097149 32.801358943886335, 118.35366635578647 32.80135552336618, 118.35422709418016 32.80149567982458, 118.35470444942315 32.80230636119275, 118.35545532453452 32.802615191262255, 118.35656425412338 32.80133257225893, 118.35669433503853 32.799987605568575, 118.35654785736406 32.79989209844715, 118.35726091847559 32.79896504479679, 118.35937304927371 32.798323618288386, 118.35983538405011 32.79845055675622, 118.35995818220293 32.798897115870545, 118.35918005313671 32.799819137943445, 118.35883837023526 32.800667549690495, 118.35925471580393 32.80115923666334, 118.35928027231374 32.80144182485759, 118.3590032287468 32.801715756868056, 118.35840602697569 32.80192179776719, 118.35833385597145 32.80215895070426, 118.35834077483098 32.80264669159818, 118.35878019020589 32.80293776141729, 118.35914577819572 32.802777660489134, 118.35941978011473 32.802157294376414, 118.36017094645557 32.80129915643249, 118.36016261862042 32.80115785413548, 118.35958090074342 32.800839643196376, 118.35989508324036 32.80018729345609, 118.36080400435365 32.79993518769732, 118.36148851267502 32.800148397255704, 118.361991548273 32.800115721942774, 118.36249513453646 32.79952044012142, 118.36249548189984 32.80011015001076, 118.362840008422 32.80057497170332, 118.36307405655556 32.80167398906179, 118.36340833954192 32.801722408938126, 118.36314489762128 32.8026198983897, 118.36308073989748 32.80373388338926, 118.36339701668454 32.80486181835052, 118.36388741228778 32.805765535128415, 118.3638337932425 32.806526568217286, 118.36318570790408 32.80721409672573, 118.36298784687922 32.80769134074979, 118.36190120397042 32.808175750170264, 118.36152711488313 32.80799892206737, 118.36104199655972 32.80719409713123, 118.36100342856014 32.80760130112362, 118.36123355931069 32.808266922006375, 118.36072981596779 32.80925064251398, 118.36003742358405 32.80984204416404, 118.35949889355601 32.809604400008496)))', 4490), 21, 18));
SELECT ST_GetLevelextremum(ST_GeoSOTGridAgg(ST_GeomFromText('MULTIPOLYGON (((118.35949889355601 32.809604400008496, 118.3596968618916 32.80899202183754, 118.36005349666094 32.80884561755304, 118.36015229494569 32.808626677902716, 118.36024493713795 32.80773313835468, 118.35949253782682 32.80670413084837, 118.35893288492495 32.80643149145906, 118.35751703332369 32.806579511199594, 118.35615747606215 32.8060163603961, 118.35418359442757 32.804396609811384, 118.35282058834792 32.803587297409116, 118.35244144512224 32.8035175550485, 118.35090752871746 32.8023351312754, 118.35072802536008 32.80187092749049, 118.35091050036917 32.80160357415574, 118.35142165097149 32.801358943886335, 118.35366635578647 32.80135552336618, 118.35422709418016 32.80149567982458, 118.35470444942315 32.80230636119275, 118.35545532453452 32.802615191262255, 118.35656425412338 32.80133257225893, 118.35669433503853 32.799987605568575, 118.35654785736406 32.79989209844715, 118.35726091847559 32.79896504479679, 118.35937304927371 32.798323618288386, 118.35983538405011 32.79845055675622, 118.35995818220293 32.798897115870545, 118.35918005313671 32.799819137943445, 118.35883837023526 32.800667549690495, 118.35925471580393 32.80115923666334, 118.35928027231374 32.80144182485759, 118.3590032287468 32.801715756868056, 118.35840602697569 32.80192179776719, 118.35833385597145 32.80215895070426, 118.35834077483098 32.80264669159818, 118.35878019020589 32.80293776141729, 118.35914577819572 32.802777660489134, 118.35941978011473 32.802157294376414, 118.36017094645557 32.80129915643249, 118.36016261862042 32.80115785413548, 118.35958090074342 32.800839643196376, 118.35989508324036 32.80018729345609, 118.36080400435365 32.79993518769732, 118.36148851267502 32.800148397255704, 118.361991548273 32.800115721942774, 118.36249513453646 32.79952044012142, 118.36249548189984 32.80011015001076, 118.362840008422 32.80057497170332, 118.36307405655556 32.80167398906179, 118.36340833954192 32.801722408938126, 118.36314489762128 32.8026198983897, 118.36308073989748 32.80373388338926, 118.36339701668454 32.80486181835052, 118.36388741228778 32.805765535128415, 118.3638337932425 32.806526568217286, 118.36318570790408 32.80721409672573, 118.36298784687922 32.80769134074979, 118.36190120397042 32.808175750170264, 118.36152711488313 32.80799892206737, 118.36104199655972 32.80719409713123, 118.36100342856014 32.80760130112362, 118.36123355931069 32.808266922006375, 118.36072981596779 32.80925064251398, 118.36003742358405 32.80984204416404, 118.35949889355601 32.809604400008496)))', 4490), 21, 1));
SELECT ST_GetLevelextremum(ST_GeoSOTGrid(ST_GeomFromText('MULTIPOLYGON (((118.35949889355601 32.809604400008496, 118.3596968618916 32.80899202183754, 118.36005349666094 32.80884561755304, 118.36015229494569 32.808626677902716, 118.36024493713795 32.80773313835468, 118.35949253782682 32.80670413084837, 118.35893288492495 32.80643149145906, 118.35751703332369 32.806579511199594, 118.35615747606215 32.8060163603961, 118.35418359442757 32.804396609811384, 118.35282058834792 32.803587297409116, 118.35244144512224 32.8035175550485, 118.35090752871746 32.8023351312754, 118.35072802536008 32.80187092749049, 118.35091050036917 32.80160357415574, 118.35142165097149 32.801358943886335, 118.35366635578647 32.80135552336618, 118.35422709418016 32.80149567982458, 118.35470444942315 32.80230636119275, 118.35545532453452 32.802615191262255, 118.35656425412338 32.80133257225893, 118.35669433503853 32.799987605568575, 118.35654785736406 32.79989209844715, 118.35726091847559 32.79896504479679, 118.35937304927371 32.798323618288386, 118.35983538405011 32.79845055675622, 118.35995818220293 32.798897115870545, 118.35918005313671 32.799819137943445, 118.35883837023526 32.800667549690495, 118.35925471580393 32.80115923666334, 118.35928027231374 32.80144182485759, 118.3590032287468 32.801715756868056, 118.35840602697569 32.80192179776719, 118.35833385597145 32.80215895070426, 118.35834077483098 32.80264669159818, 118.35878019020589 32.80293776141729, 118.35914577819572 32.802777660489134, 118.35941978011473 32.802157294376414, 118.36017094645557 32.80129915643249, 118.36016261862042 32.80115785413548, 118.35958090074342 32.800839643196376, 118.35989508324036 32.80018729345609, 118.36080400435365 32.79993518769732, 118.36148851267502 32.800148397255704, 118.361991548273 32.800115721942774, 118.36249513453646 32.79952044012142, 118.36249548189984 32.80011015001076, 118.362840008422 32.80057497170332, 118.36307405655556 32.80167398906179, 118.36340833954192 32.801722408938126, 118.36314489762128 32.8026198983897, 118.36308073989748 32.80373388338926, 118.36339701668454 32.80486181835052, 118.36388741228778 32.805765535128415, 118.3638337932425 32.806526568217286, 118.36318570790408 32.80721409672573, 118.36298784687922 32.80769134074979, 118.36190120397042 32.808175750170264, 118.36152711488313 32.80799892206737, 118.36104199655972 32.80719409713123, 118.36100342856014 32.80760130112362, 118.36123355931069 32.808266922006375, 118.36072981596779 32.80925064251398, 118.36003742358405 32.80984204416404, 118.35949889355601 32.809604400008496)))', 4490), 18));

--网格聚合
SELECT ST_Aggregate('074EACB000000000000F0000'::geosotgrid, 9);
SELECT ST_Aggregate('00B21A4984C0000000000120000F0001'::geosotgrid, 9);

--操作符
SELECT ST_GeoSOTGrid(ST_MAKEENVELOPE(1, 1, 2, 2, 4490), 15) && ST_GeoSOTGrid(ST_MAKEENVELOPE(1.5, 1.5, 2.5, 2.5, 4490), 15);
SELECT ST_GeoSOTGrid(ST_MAKEENVELOPE(1, 1, 2, 2, 4490), 15) && ST_GeoSOTGrid(ST_MAKEENVELOPE(2, 2, 2.5, 2.5,4490), 15);
SELECT ST_GeoSOTGrid(ST_MAKEENVELOPE(1, 1, 2, 2, 4490), 15) @> ST_GeoSOTGrid(ST_MAKEENVELOPE(1.2, 1.2, 1.8, 1.8,4490), 15);
SELECT ST_GeoSOTGrid(ST_MAKEENVELOPE(1.5, 1.5, 2, 2, 4490), 15) @> ST_GeoSOTGrid(ST_MAKEENVELOPE(1, 1, 2, 2,4490), 15);
SELECT ST_GeoSOTGrid(ST_MAKEENVELOPE(1.5, 1.5, 2, 2, 4490), 15) <@ ST_GeoSOTGrid(ST_MAKEENVELOPE(1, 1, 2, 2,4490), 15);
SELECT ST_GeoSOTGrid(ST_MAKEENVELOPE(1, 1, 2, 2, 4490), 15) <@ ST_GeoSOTGrid(ST_MAKEENVELOPE(1.2, 1.2, 1.8, 1.8,4490), 15);
SELECT ST_GeoSOTGridAgg(ST_MAKEENVELOPE(0, 0, 1, 1, 4490), 15, 9) && ST_GeoSOTGridAgg(ST_MAKEENVELOPE(0.4, 0.4, 1.4, 1.4, 4490), 15, 9);
SELECT ST_GeoSOTGridAgg(ST_MAKEENVELOPE(0, 0, 1, 1, 4490), 9, 6) @> ST_GeoSOTGridAgg(ST_MAKEENVELOPE(0.4, 0.4, 1.0, 0.8, 4490), 15, 9);
SELECT ST_GeoSOTGridAgg(ST_MAKEENVELOPE(0, 0, 1, 1, 4490), 15, 9) @> ST_GeoSOTGridAgg(ST_MAKEENVELOPE(0.4, 0.4, 0.8, 0.8, 4490), 9, 6);
SELECT ST_GeoSOTGridAgg(ST_MAKEENVELOPE(0, 0, 1, 1, 4490), 9, 6) @> ST_GeoSOTGridAgg(ST_MAKEENVELOPE(0.4, 0.4, 1.1, 0.8, 4490), 15, 9);
SELECT ST_GeoSOTGridAgg(ST_MAKEENVELOPE(0, 0, 1, 1, 4490), 15, 9) @> ST_GeoSOTGridAgg(ST_MAKEENVELOPE(0.4, 0.4, 1.1, 0.8, 4490), 9, 6);
with a as (select unnest(ST_GeoSOTGrid(ST_GeomFromText('POLYGON ((115.68333333333334 39.833333333333336, 115.68333333333334 39.85, 115.7 39.85, 115.7 39.833333333333336, 115.68333333333334 39.833333333333336))',4490),18)) grid)
select count(*)  from a where array [ST_GeoSOTGridFromText('G001310233-321021')]&&array[a.grid] ;
with a as (select unnest(ST_GeoSOTGridAgg(ST_GeomFromText('POLYGON ((115.68333333333334 39.833333333333336, 115.68333333333334 39.85, 115.7 39.85, 115.7 39.833333333333336, 115.68333333333334 39.833333333333336))',4490),18,16)) grid)
select count(*)  from a where array [ST_GeoSOTGridFromText('G001310233-321021')]&&array[a.grid] ;

---- GiST index
CREATE or replace function  testidx_grids_15_create() returns boolean
LANGUAGE 'plpgsql' AS $$
BEGIN
    create table testidx_grids_15(id serial4,grid _geosotgrid); 
	with a as (SELECT unnest(ST_GeoSOTGrid(st_makeenvelope(115.605179,39.602211,116.782204,40.494509,4490),15)) as foo)
    insert into testidx_grids_15(grid) select array[foo] as grid from a;
    --insert into testidx_grids_15(grid) SELECT array[unnest(ST_GeoSOTGrid(st_makeenvelope(115.605179,39.602211,116.782204,40.494509,4490),15))] as grid;
    create index testidx_grids_15_grididx on testidx_grids_15 using gin(grid);

    create table testidx_select(id serial4,grids _geosotgrid);
    insert into testidx_select(grids) SELECT ST_GeoSOTGrid(st_geomfromgeosotgrid(ST_GeoSOTGridFromText('G001310233-321021')),18) ;
    insert into testidx_select(grids) SELECT array[ST_GeoSOTGridFromText('G001310233-301301')];
    insert into testidx_select(grids) select  array_append (grids,ST_GeoSOTGridFromText('G001310233-301401') ) from testidx_select where id=2;
    
   return true;
END;
$$;
select testidx_grids_15_create();
select count(*) from testidx_grids_15,testidx_select a where  testidx_grids_15.grid &&a.grids;

---- BTREE index