search

aodn / content

Tracks AODN Portal content and configuration issues
0 stars 0 forks source link

SRS SAR - Broken layers srs_surface_waves_sar_fv00_trajectory_map/srs_surface_waves_sar_fv01_trajectory_map #456

Open ghost opened 4 years ago

ghost commented 4 years ago

These layers are broken due to database queries being extremely inefficient. The queries need to be refactored to use indexes properly, because these are running sequential scans on the tables to draw map tiles, which is unacceptable.

jachope commented 4 years ago

Can we remove from the Portal until resolved? What was the testing process that meant these were able to be published to Production?

ghost commented 4 years ago

I suspect the issue is the filter on url being a string match on an unindexed column. This potentially could be fixed with an index, but substring matching is probably a poor way to filter anyway, it could probably have a more appropriate column type for efficient filtering.

# explain analyze
SELECT encode(ST_AsBinary(CASE WHEN ST_HasArc("geom") THEN "geom" ELSE ST_Simplify(ST_Force2D("geom"), 0.5625, true) END),'base64') as "geom"
FROM "srs_surface_waves_sar"."srs_surface_waves_sar_fv01_trajectory_map"
WHERE "geom" && ST_GeomFromText('POLYGON ((-272.109375 -125.859375, -272.109375 110.390625, 272.109375 110.390625, 272.109375 -125.859375, -272.109375 -125.859375))', 4326);

                                                                                                                                QUERY PLAN                                                                                                                                
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on srs_surface_waves_sar_trajectory_map  (cost=0.00..2120.84 rows=2760 width=32) (actual time=0.389..700.419 rows=2744 loops=1)
   Filter: (((url)::text ~~ '%FV01%'::text) AND (geom && '0103000020E6100000010000000500000000000000C00171C00000000000775FC000000000C00171C00000000000995B4000000000C00171400000000000995B4000000000C00171400000000000775FC000000000C00171C00000000000775FC0'::geometry))
   Rows Removed by Filter: 797
 Planning time: 0.190 ms
 Execution time: 702.217 ms
(5 rows)
ggalibert commented 4 years ago

The layers are supported by two views srs_surface_waves_sar_fv00_trajectory_map and srs_surface_waves_sar_fv01_trajectory_map which are showing a different subset of the table srs_surface_waves_sar_trajectory_map. That table includes both fv00 and fv01 files and the views are generated by filtering on the url field looking at whether url includes a substring FV00 or FV01. As highlighted by @leighwgordon this is poorly efficient.

Instead the views should be filtering this table on a new field with an index. This new field could be a boolean isNRT set to true when url includes FV00 and false otherwise. This new field will have to be populated by the relevant harvester.

@lbesnard since we are also filtering on platform and WAVNUM on step 2 it is likely that we may need an index for these fields too.

lbesnard commented 4 years ago

doing the following changes on the harvester

1) update the srs_surface_waves_sar_trajectory_map MAIN table to add ais_relatime boolean based on the filename + update the existing data

    <changeSet author="lbesnard" id="add_is_realtime" runOnChange="true">
      <sql>
    ALTER TABLE srs_surface_waves_sar_trajectory_map ADD COLUMN is_realtime boolean;
        UPDATE srs_surface_waves_sar_trajectory_map
        SET is_realtime = True
        WHERE SUBSTRING( url, '[0-9]{8}T[0-9]{6}Z_.*_FV0([0-1])')  LIKE '0'::text;
    UPDATE srs_surface_waves_sar_trajectory_map
        SET is_realtime = False
        WHERE SUBSTRING( url, '[0-9]{8}T[0-9]{6}Z_.*_FV0([0-1])')  LIKE '1'::text;

      </sql>
    </changeSet>

2) update the view to match tthe point above

    <changeSet author="lbesnard" id="create_vw" runOnChange="true">
      <sql>
    DROP VIEW IF EXISTS srs_surface_waves_sar_fv00_trajectory_map;
    DROP VIEW IF EXISTS srs_surface_waves_sar_fv01_trajectory_map;

    CREATE VIEW srs_surface_waves_sar_fv00_trajectory_map AS(
            select * from srs_surface_waves_sar_trajectory_map WHERE is_realtime
    );

    CREATE VIEW srs_surface_waves_sar_fv01_trajectory_map AS(
            select * from srs_surface_waves_sar_trajectory_map WHERE NOT is_realtime
    );
      </sql>
    </changeSet>

3) update the table defintion in the harvester subjob

"WITH 
data AS (SELECT trajectory_id,
url,
size,
min(\"TIME\") AS time_coverage_start, 
max(\"TIME\") AS time_coverage_end,
SUBSTRING(url, '[0-9]{8}T[0-9]{6}Z_(.*)_FV0') as platform_code,
SUBSTRING(url, '[0-9]{8}T[0-9]{6}Z_.*_(K[1-2])_END-.*') as wavnum_type,
CASE WHEN SUBSTRING(url, '[0-9]{8}T[0-9]{6}Z_.*_FV0([0-1])') LIKE '0'::text 
     THEN True
     WHEN SUBSTRING(url, '[0-9]{8}T[0-9]{6}Z_.*_FV0([0-1])') LIKE '1'::text 
     THEN False
END AS is_realtime,
CASE WHEN COUNT(DISTINCT(geom)) > 1 THEN ST_SimplifyPreserveTopology(make_trajectory(geom ORDER BY \"TIME\"),0.005) ELSE ST_UNION(geom) END AS geom
FROM measurements
LEFT JOIN indexed_file ON measurements.trajectory_id = indexed_file.id
WHERE trajectory_id = " + context.fileId + "
GROUP BY trajectory_id,url,size)
SELECT DISTINCT data.trajectory_id,
url,
size,
platform_code,
wavnum_type,
time_coverage_start,
time_coverage_end,
geom
FROM data
ORDER BY trajectory_id;"
lbesnard commented 4 years ago

and 

    <changeSet author="lbesnard" id="create_idx_filters" runOnChange="true">
      <sql>
CREATE INDEX platform_idx ON srs_surface_waves_sar_trajectory_map (platform_code);
CREATE INDEX wavenum_idx ON srs_surface_waves_sar_trajectory_map (wavnum_type);
      </sql>
    </changeSet>
lbesnard commented 4 years ago

harvester PR https://github.com/aodn/harvesters/pull/824 blocked by https://github.com/aodn/issues/issues/760

ggalibert commented 4 years ago

PR above has been merged.

@leighwgordon is there anything else that could be done to improve these layers?

ghost commented 4 years ago

Unfortunately I think they are always going to provide a poor user experience unless the data are stored differently in the database.

I don't have the GIS expertise to suggest the solution, but clearly trying to draw maps "on demand" from this is not feasible.

This is an example of a single geometry from one row! I think appropriate indexes are part of the solution (particular as it grows, and scanning the table becomes prohibitively expensive), but this seems to me to be a bigger issue.

SELECT ST_AsText(geom)
FROM srs_surface_waves_sar.srs_surface_waves_sar_trajectory_map
LIMIT 1 OFFSET 1;

MULTILINESTRING((136.002593994141 2.05303716659546,137.452514648438 3.28546929359436,135.632034301758 3.82619428634644,137.086303710938 5.05519533157349,135.262420654297 5.5996036529541,136.721664428711 6.82622241973877,134.893615722656 7.37285566329956,136.360137939453 8.59667682647705,134.524871826172 9.14598560333252,136.000259399414 10.3674278259277,134.155517578125 10.918773651123,135.641662597656 12.137843132019,133.786178588867 12.6913948059082,135.283798217773 13.9077711105347,133.415405273438 14.4637308120728,134.926422119141 15.6781463623047,133.042724609375 16.235710144043,134.569030761719 17.4477996826172,132.668884277344 18.0073432922363,134.210998535156 19.2174377441406,132.291610717773 19.7785949707031,133.852111816406 20.9864349365234,131.910995483398 21.5488681793213,133.493041992188 22.7549457550049,131.526443481445 23.3186588287354,133.130905151367 24.523193359375,131.137252807617 25.0876655578613,132.766342163086 26.2906875610352,135.008895874023 -62.3880500793457,137.3662109375 -60.857048034668,133.496505737305 -60.7445106506348,135.801010131836 -59.2388610839844,132.108276367188 -59.0893173217773,134.372833251953 -57.6027793884277,130.856170654297 -57.4191093444824,133.064041137695 -55.9525337219238,129.706436157227 -55.7373504638672,131.850997924805 -54.2914047241211,128.645843505859 -54.0468711853027,130.735946655273 -52.6174964904785,127.662147521973 -52.3480377197266,129.700302124023 -50.9343872070312,126.731269836426 -50.6453247070312,128.734222412109 -49.2427291870117,125.874488830566 -48.9331207275391,127.822052001953 -47.5450859069824,125.069580078125 -47.2150688171387,126.972068786621 -45.8390159606934,124.310913085938 -45.4920272827148,126.170684814453 -44.1270980834961,123.579177856445 -43.7673072814941,125.412559509277 -42.4096298217773,122.898071289062 -42.0352973937988,124.693328857422 -40.6872978210449,122.249359130859 -40.299373626709,124.002182006836 -38.961483001709,121.629875183105 -38.5598030090332,123.349311828613 -37.229850769043,121.036544799805 -36.8169631958008,122.725028991699 -35.4950294494629,109.342399597168 -61.3531188964844,111.668807983398 -59.8389739990234,107.921501159668 -59.7004661560059,110.192489624023 -58.2089920043945,106.627296447754 -58.0345573425293,108.841735839844 -56.5636177062988,105.426055908203 -56.3601570129395,107.600227355957 -54.905200958252,104.334129333496 -54.6726341247559,106.453369140625 -53.2351455688477,103.323303222656 -52.9765281677246,105.38207244873 -51.5571174621582,102.383338928223 -51.2727203369141,104.391471862793 -49.8683662414551,101.505516052246 -49.5626335144043,103.464897155762 -48.1713409423828,100.667625427246 -47.8497619628906,102.595611572266 -46.4679260253906,99.8928985595703 -46.128719329834,101.770027160645 -44.7601890563965,99.1605606079102 -44.4025726318359,100.996520996094 -43.0444221496582,98.4664001464844 -42.6723976135254,100.263519287109 -41.3236465454102,97.7929153442383 -40.9411811828613,99.56689453125 -39.5983924865723,97.163330078125 -39.2031707763672,98.9028854370117 -37.8687782287598,96.5609741210938 -37.4619483947754,98.2621231079102 -36.1368026733398,95.9830169677734 -35.7174873352051,97.6544494628906 -34.3997230529785,95.4272918701172 -33.9705276489258,97.0710983276367 -32.6595039367676,94.8802108764648 -32.2233772277832,96.5097503662109 -30.9163436889648,94.3628311157227 -30.471321105957,95.9627456665039 -29.1712760925293,93.8618469238281 -28.7165203094482,95.4395446777344 -27.4219455718994,93.3755950927734 -26.9594955444336,94.9332504272461 -25.6707916259766,92.8933792114258 -25.2031841278076,94.4420394897461 -23.9171237945557,92.433479309082 -23.4431209564209,93.9649353027344 -22.1618270874023,91.9847946166992 -21.6816577911377,93.4962921142578 -20.4061279296875,91.5462493896484 -19.9187450408936,93.0438690185547 -18.6476039886475,91.1168594360352 -18.1543827056885,92.6020889282227 -16.8871459960938,90.6883239746094 -16.3905239105225,92.1702041625977 -15.1255254745483,90.275146484375 -14.6236705780029,91.7473068237305 -13.3625612258911,89.8687973022461 -12.8558311462402,91.3292999267578 -11.5988788604736,89.4684600830078 -11.0870037078857,90.922248840332 -9.83352947235107,89.0679702758789 -9.31836891174316,90.5220794677734 -8.06713771820068,88.6784744262695 -7.54809999465942,90.128173828125 -6.30009365081787,88.2931594848633 -5.77719306945801,89.7377166748047 -4.53293561935425,87.9116516113281 -4.00581026077271,89.354736328125 -2.76436352729797,87.5334625244141 -2.2345187664032,88.9766082763672 -0.996306359767914,87.151969909668 -0.463431566953659,88.6009750366211 0.77352637052536,86.781852722168 1.31041502952576,88.2304153442383 2.54359269142151,86.4105377197266 3.08336114883423,87.8630752563477 4.31377267837524,86.0405578613281 4.85650014877319,87.4983673095703 6.08435773849487,85.6698379516602 6.6292667388916,87.1360321044922 7.85469245910645,85.3013305664062 8.40246868133545,86.775505065918 9.62514400482178,84.9328536987305 10.1752853393555,86.416389465332 11.3954553604126,84.5637664794922 11.9480628967285,86.0583343505859 13.1658153533936,84.1937561035156 13.7204170227051,85.7007217407227 14.9362459182739,180 15.9967077794356),(-180 15.9967077794356,-106.893836975098 16.8188381195068,-108.783866882324 16.2615089416504,-107.267074584961 15.0472421646118,-109.141220092773 14.4915151596069,-107.63850402832 13.2752265930176,-109.498870849609 12.7209815979004,-108.00821685791 11.5026454925537,-109.857093811035 10.9507312774658,-108.376853942871 9.73009967803955,-110.216430664062 9.18051338195801,-108.746231079102 7.95726871490479,-110.577285766602 7.40994024276733,-109.115112304688 6.18368721008301,-110.940299987793 5.63883018493652,-109.484313964844 4.41071176528931,-111.306419372559 3.86896848678589,-109.854415893555 2.63790702819824,-111.674537658691 2.0995888710022,-110.225975036621 0.865221440792084,-112.046043395996 0.329799801111221,-110.602661132812 -0.906668782234192,-112.421401977539 -1.44000446796417,-110.978889465332 -2.67907881736755,-112.802825927734 -3.20818686485291,-111.357963562012 -4.45083951950073,-113.187187194824 -4.97667169570923,-111.740425109863 -6.22237920761108,-113.576866149902 -6.74438381195068,-112.131782531738 -7.9919319152832,-113.972190856934 -8.5109920501709,-112.52278137207 -9.7617130279541,-114.373908996582 -10.2765626907349,-112.918853759766 -11.5310478210449,-114.785995483398 -12.0411520004272,-113.320587158203 -13.2996702194214,-115.202705383301 -13.8050689697266,-113.728630065918 -15.0673894882202,-115.627883911133 -15.5679883956909,-114.151062011719 -16.8324012756348,-116.062133789062 -17.3293132781982,-114.57413482666 -18.5978202819824,-116.510711669922 -19.0883655548096,-115.005851745605 -20.3621139526367,-116.96622467041 -20.8468132019043,-115.446853637695 -22.124906539917,-117.433708190918 -22.6034355163574,-115.907615661621 -23.8841247558594,-117.914199829102 -24.3580646514893,-116.370788574219 -25.6436653137207,-118.409049987793 -26.1110210418701,-116.846694946289 -27.4018135070801,-118.924690246582 -27.8600959777832,-117.336273193359 -29.1574211120605,-119.452339172363 -29.608081817627,-117.841156005859 -30.9111289978027,-119.998634338379 -31.3536357879639,-118.374237060547 -32.6600875854492,-120.565505981445 -33.0962371826172,-118.914970397949 -34.4091873168945,-121.16096496582 -34.8342781066895,-119.47607421875 -36.1556015014648,-121.775337219238 -36.5707168579102,-120.060173034668 -37.8996200561523,-122.417449951172 -38.3041610717773,-120.682434082031 -39.6381149291992,-123.089538574219 -40.0331039428711,-121.320030212402 -41.3756523132324,-123.795211791992 -41.7575607299805,-121.988632202148 -43.1088600158691,-124.545043945312 -43.475269317627,-122.692352294922 -44.838493347168,-125.329681396484 -45.1895446777344,-123.435020446777 -46.5633735656738,-126.160797119141 -46.897834777832,-124.236320495605 -48.2802810668945,-127.044082641602 -48.5998954772949,-125.073081970215 -49.9948387145996,-127.993621826172 -50.2928276062012,-125.966102600098 -51.7033386230469,-129.002258300781 -51.979606628418,-126.923118591309 -53.4048919677734,-130.086090087891 -53.6569595336914,-127.969093322754 -55.0956954956055,-131.255706787109 -55.3240509033203,-129.083740234375 -56.7805061340332,-132.523727416992 -56.9793510437012,-130.295913696289 -58.4554061889648,-133.912673950195 -58.6192588806152,-131.621276855469 -60.1188163757324,-135.424713134766 -60.2454833984375,-133.078750610352 -61.7685089111328,-137.08869934082 -61.8533706665039,-134.707290649414 -63.3989181518555,-138.929916381836 -63.4398345947266,-136.503616333008 -65.0129547119141,-140.978729248047 -65.0003814697266,-138.519638061523 -66.6045684814453,-143.279296875 -66.5282897949219,-140.798934936523 -68.1685562133789,-145.862808227539 -68.0218200683594,-143.411010742188 -69.6957092285156,-148.790908813477 -69.4712295532227,-146.393844604492 -71.1839218139648,-152.126724243164 -70.8661193847656,-149.846038818359 -72.6194305419922,-155.948913574219 -72.191650390625,-180 -69.5031636515863),(180 -69.5031636515863,82.3546905517578 -58.5881423950195,84.5897064208984 -57.1113090515137,81.1347045898438 -56.9143409729004,83.3132095336914 -55.4564666748047,80.0130386352539 -55.230052947998,82.1364364624023 -53.7903175354004,78.9612350463867 -53.5402374267578,81.0463180541992 -52.1136093139648,77.9987564086914 -51.8387603759766,80.0324325561523 -50.4278259277344,77.1013336181641 -50.1309967041016,79.0778961181641 -48.7351913452148,76.2606964111328 -48.4171104431152,78.1902847290039 -47.03369140625,75.4558639526367 -46.7007141113281,77.3554916381836 -45.3258743286133,74.7098922729492 -44.9760398864746,76.5675277709961 -43.6121406555176,74.0035552978516 -43.2470893859863,75.8145980834961 -41.8948402404785,73.3324432373047 -41.514030456543,75.1063461303711 -40.1710433959961,72.6927108764648 -39.7772789001465,74.4318313598633 -38.4426574707031,72.0686874389648 -38.0397796630859,73.787956237793 -36.7104835510254,71.4828567504883 -36.2961387634277,73.1714248657227 -34.9740905761719,70.9197540283203 -34.548999786377,72.5740203857422 -33.236141204834,70.3776473999023 -32.8002624511719,72.0054931640625 -31.4934902191162,69.8542785644531 -31.0489482879639,71.4575729370117 -29.7481479644775,69.3369369506836 -29.2973937988281,70.928596496582 -28.0001850128174,68.8461761474609 -27.5417823791504,70.411491394043 -26.2507514953613,68.3693695068359 -25.7839603424072,69.9155502319336 -24.4982891082764,67.9052047729492 -24.024393081665,69.4339447021484 -22.7434749603271,67.4435653686523 -22.265344619751,68.9655380249023 -20.9870338439941,67.0018157958984 -20.5028305053711,68.5091018676758 -19.2287006378174,66.5695877075195 -18.7391185760498,68.0597915649414 -17.4702434539795,66.1458969116211 -16.9739627838135,67.6247787475586 -15.7090845108032,65.7300033569336 -15.2074575424194,67.1989898681641 -13.9464340209961,65.3143615722656 -13.4414253234863,66.7815704345703 -12.1823902130127,64.9121017456055 -11.6727132797241,66.3690032958984 -10.4184513092041,64.5157318115234 -9.90370082855225,65.9666900634766 -8.65248394012451,64.124397277832 -8.13429069519043,65.570930480957 -6.88627243041992,63.7325706481934 -6.36479377746582,65.1808929443359 -5.11884212493896,63.3499450683594 -4.59359312057495,64.7961654663086 -3.35071873664856,62.9706497192383 -2.82175970077515,64.4141998291016 -1.58159828186035,62.5942916870117 -1.04952824115753,64.0376281738281 0.186513125896454,62.2177200317383 0.722445011138916,63.6671829223633 1.95693123340607,61.8459434509277 2.49525761604309,63.2986640930176 3.72681999206543,61.4756507873535 4.26828765869141,62.932056427002 5.49724340438843,61.1061172485352 6.04182434082031,62.5689544677734 7.26795434951782,60.7373390197754 7.81494474411011,62.2077865600586 9.03886032104492,60.3677062988281 9.5878734588623,61.8483123779297 10.8092384338379,59.9988632202148 11.3608703613281,61.4899024963379 12.5797920227051,59.6291732788086 13.1333932876587,61.1322555541992 14.3498067855835,59.2579154968262 14.9056491851807,60.7747764587402 16.120059967041,58.8852844238281 16.6778335571289,60.4173126220703 17.8894138336182,180 30.8793270568846),(-180 30.8793270568846,-127.097816467285 36.6259346008301,-129.343322753906 36.0595626831055,-127.536689758301 34.8631324768066,-129.730209350586 34.2958374023438,-127.965042114258 33.099235534668,-130.113693237305 32.5317459106445,-128.379867553711 31.3333187103271,-130.491744995117 30.7667255401611,-128.791015625 29.5673160552979,-130.865081787109 29.0009784698486,-129.194732666016 27.8004302978516,-131.232498168945 27.2345561981201,-129.592025756836 26.0329418182373,-131.598556518555 25.4679679870605,-129.983505249023 24.2646827697754,-131.961837768555 23.700569152832,-130.367904663086 22.4951515197754,-132.322998046875 21.932279586792,-130.75065612793 20.7251300811768,-132.682418823242 20.1636810302734,-131.129821777344 18.9542579650879,-133.039962768555 18.3942089080811,-131.505737304688 17.1832752227783,-133.397567749023 16.6248359680176,-131.879211425781 15.4114818572998,-133.75505065918 14.8546543121338,-132.250701904297 13.6391458511353,-134.11262512207 13.0845441818237,-132.620788574219 11.8665361404419,-134.470764160156 11.3141889572144,-132.989730834961 10.0938005447388,-134.830032348633 9.54347038269043,-133.358245849609 8.32066059112549,-135.190643310547 7.77267122268677,-133.728103637695 6.54793691635132,-135.553131103516 6.00243854522705,-134.097229003906 4.77522134780884,-135.917785644531 4.23244524002075,-134.467269897461 3.00209331512451,-136.286422729492 2.46292567253113,-134.83869934082 1.22897744178772,-136.657501220703 0.692151367664337,-135.211807250977 -0.543566226959229,-137.031967163086 -1.07693564891815,-135.5908203125 -2.31502556800842,-137.410537719727 -2.84595537185669,-135.969451904297 -4.08706712722778,-137.796005249023 -4.61385250091553,-136.351287841797 -5.85864591598511,-138.184631347656 -6.38163614273071,-136.736770629883 -7.62963962554932,-138.57893371582 -8.14895534515381,-137.132232666016 -9.3988151550293,-137.527389526367 -11.1683921813965,-139.386871337891 -11.6802377700806,-137.928085327148 -12.937313079834,-139.805267333984 -13.4438028335571,-138.334991455078 -14.7054605484009,-140.228759765625 -15.2069759368896,-138.748611450195 -16.4723815917969,-140.661285400391 -16.969087600708,-139.177551269531 -18.2359943389893,-141.103576660156 -18.7290344238281,-139.607650756836 -20.0007801055908,-141.560958862305 -20.4865894317627,-140.046691894531 -21.7633609771729,-142.025970458984 -22.2434139251709,-140.496124267578 -23.5248870849609,-142.503875732422 -23.998722076416,-140.966247558594 -25.2826538085938,-142.995758056641 -25.7519054412842,-141.439514160156 -27.0406703948975,-143.502853393555 -27.502571105957,-141.926361083984 -28.7968139648438,-144.032348632812 -29.2499542236328,-142.428192138672 -30.5509967803955,-144.574859619141 -30.9963226318359,-142.94660949707 -32.3032493591309,-145.137466430664 -32.7393836975098,-143.494812011719 -34.0501441955566,-145.72216796875 -34.4796295166016,-144.051788330078 -35.7972602844238,-146.331146240234 -36.2164535522461,-144.631134033203 -37.5415000915527,-146.9736328125 -37.9487457275391,-145.23518371582 -39.2830238342285,-147.639312744141 -39.6783065795898,-145.879638671875 -41.0180320739746,-148.337860107422 -41.4034080505371,-146.541854858398 -42.7522354125977,-149.072967529297 -43.1240081787109,-147.238372802734 -44.4828491210938,-149.855865478516 -44.8378982543945,-147.973190307617 -46.2093658447266,-150.677474975586 -46.5480155944824,-148.750869750977 -47.9305267333984,-151.549880981445 -48.2516860961914,-149.591827392578 -49.6436309814453,-152.479553222656 -49.9480018615723,-150.472930908203 -51.3532180786133,-153.473831176758 -51.6363182067871,-151.41650390625 -53.0563278198242,-154.549499511719 -53.3141746520996,-152.431365966797 -54.7515754699707,-155.700653076172 -54.9832191467285,-153.543045043945 -56.434642791748,-156.946792602539 -56.6399040222168,-154.734268188477 -58.1114540100098,-158.303680419922 -58.2846260070801,-156.03596496582 -59.7781295776367,-159.796157836914 -59.9133033752441,-157.464889526367 -61.4305114746094,-161.427322387695 -61.5250549316406,-159.044403076172 -63.0678634643555,-163.230178833008 -63.1161270141602,-160.816787719727 -64.6830825805664,-165.234024047852 -64.6822662353516,-162.785400390625 -66.2798004150391,-167.475006103516 -66.2192077636719,-165.00651550293 -67.849250793457,-170.002182006836 -67.7185821533203,-167.534591674805 -69.3870315551758,-172.855484008789 -69.1777648925781,-170.447235107422 -70.881462097168,-176.10173034668 -70.5843124389648,-173.796173095703 -72.3286209106445,-180 -71.6265748833661),(180 -71.6265748833661,57.0069313049316 -57.7082633972168,59.2237701416016 -56.2389068603516,55.8408279418945 -56.0289573669434,58.0015068054199 -54.5779457092285,54.7653999328613 -54.3396377563477,56.8639717102051 -52.907829284668,53.7689590454102 -52.6420593261719,55.8150062561035 -51.2257461547852,52.8267135620117 -50.9402198791504,54.8373908996582 -49.5352821350098,51.9601516723633 -49.2290115356445,53.9224662780762 -47.8370704650879,51.1465606689453 -47.5117301940918,53.0561065673828 -46.1339225769043,50.3802032470703 -45.7893829345703,52.2466201782227 -44.422794342041,49.6554107666016 -44.0623359680176,51.4813842773438 -42.7063331604004,48.9544067382812 -42.3339347839355,50.7557334899902 -40.9848213195801,48.3003997802734 -40.5989570617676,50.0655517578125 -39.2585258483887,47.6761322021484 -38.8601684570312,49.400749206543 -37.5289916992188,47.078483581543 -37.1180305480957,48.7717247009277 -35.7947082519531,46.4926605224609 -35.3753128051758,48.1688003540039 -34.0568504333496,45.9411735534668 -33.6276893615723,47.5898513793945 -32.3161964416504,45.4093818664551 -31.8775787353516,47.0265464782715 -30.5732822418213,44.895076751709 -30.1247863769531,46.4889259338379 -28.8264923095703,180 9.7827305859917),(-180 9.7827305859917,-156.306518554688 16.6344985961914,-158.194000244141 16.076322555542,-156.679611206055 14.8622398376465,-158.551452636719 14.3059320449829,-157.050903320312 13.0898265838623,-158.909088134766 12.5357255935669,-157.420440673828 11.3172073364258,-159.26741027832 10.7654981613159,-157.789749145508 9.54438781738281,-159.626998901367 8.99478435516357,-158.158493041992 7.77073287963867,-159.98811340332 7.22386980056763,-158.527206420898 5.99787473678589,-160.351943969727 5.45375728607178,-158.896423339844 4.22504472732544,-160.717636108398 3.68352818489075,-159.266677856445 2.45184516906738,-161.086227416992 1.91347241401672,-159.641342163086 0.67948991060257,-161.458084106445 0.143896892666817,-160.015289306641 -1.09299349784851,-161.833755493164 -1.62537205219269,-160.391738891602 -2.86532616615295,-162.215835571289 -3.39426803588867,-160.771194458008 -4.63747644424438,-162.600677490234 -5.16243076324463,-161.158752441406 -6.40798568725586,-162.99104309082 -6.9305591583252,-161.5458984375 -8.17916202545166,-163.387145996094 -8.69764137268066,-161.937454223633 -9.94929504394531,-163.792678833008 -10.4627904891968,-162.334091186523 -11.718731880188,-164.202590942383 -12.2280864715576,-162.736373901367 -13.4871139526367,-164.620239257812 -13.9922342300415,-163.151992797852 -15.2525568008423,-165.04606628418 -15.7537832260132,-163.567962646484 -17.0183906555176,-165.481353759766 -17.5149974822998,-163.99186706543 -18.7837677001953,-165.931060791016 -19.2738494873047,-164.424438476562 -20.5477294921875,-166.387817382812 -21.0322074890137,-164.866439819336 -22.3103427886963,-166.856597900391 -22.7884502410889,-165.328353881836 -24.069522857666,-167.338684082031 -24.5433521270752,-165.792739868164 -25.8288841247559,-167.840118408203 -26.294412612915,-166.269836425781 -27.5862007141113,-168.352355957031 -28.0443019866943,-166.761016845703 -29.3420505523682,-168.88200378418 -29.7922954559326,-167.278686523438 -31.0933818817139,-169.430419921875 -31.5376853942871,-167.802673339844 -32.8448944091797,-169.999557495117 -33.2798194885254,-168.345413208008 -34.5936965942383,-170.597595214844 -35.0176391601562,-168.90885925293 -36.3400993347168,-171.214828491211 -36.7539100646973,-169.495391845703 -38.0836563110352,-171.859970092773 -38.4868087768555,-170.120101928711 -39.8207550048828,-172.535308837891 -40.2148628234863,-170.760864257812 -41.5581855773926,-173.251449584961 -41.9366989135742,-171.433029174805 -43.2913055419922,-173.998458862305 -43.6551856994629,-172.140213012695 -45.0195121765137,-174.787719726562 -45.3687591552734,-172.901611328125 -46.7409782409668,-175.624038696289 -47.0763359069824,-173.693481445312 -48.4603691101074,-176.513458251953 -48.7781562805176,-174.536087036133 -50.1749572753906,-177.469940185547 -50.4707298278809,-175.435409545898 -51.8827896118164,-178.485885620117 -52.1562232971191,-176.399780273438 -53.5838165283203,-179.578491210938 -53.832836151123,-177.45378112793 -55.2732315063477,-180 -55.4467873605632),(180 -55.4467873605632,179.242324829102 -55.4984321594238,180 -56.0056088441422),(-180 -56.0056088441422,-178.578155517578 -56.9573707580566,-180 -57.0365173700094),(180 -57.0365173700094,177.95539855957 -57.1503295898438,180 -58.4996875505994),(-180 -58.4996875505994,-179.801208496094 -58.6308822631836,-180 -58.6396184842289),(180 -58.6396184842289,176.561141967773 -58.7907447814941,178.860565185547 -60.2929458618164,175.034332275391 -60.4149322509766,177.372360229492 -61.9378318786621,173.352737426758 -62.0208549499512,175.741424560547 -63.5697059631348,171.491485595703 -63.6045150756836,173.923400878906 -65.1819534301758,169.411010742188 -65.1603393554688,171.882598876953 -66.7704086303711,167.089874267578 -66.686408996582,169.572860717773 -68.3310928344727,164.47248840332 -68.1758422851562,166.923858642578 -69.8541259765625,161.505294799805 -69.6196975708008,180 15.774800823588),(-180 15.774800823588,-172.508987426758 50.362606048584,-175.325378417969 49.8122138977051,-173.083908081055 48.6112670898438,-175.807601928711 48.057933807373,-173.639389038086 46.8596305847168,-176.271881103516 46.302417755127,-174.170989990234 45.1057815551758,-176.716613769531 44.5452613830566,-174.681549072266 43.3502502441406,-177.151718139648 42.7872619628906,-175.173675537109 41.592845916748,-177.574905395508 41.0279922485352,-175.643218994141 39.8326797485352,-177.987930297852 39.2661437988281,-176.104568481445 38.071849822998,-178.391494750977 37.5042419433594,-176.552993774414 36.3095283508301,-178.784057617188 35.741455078125,-176.989730834961 34.5464668273926,-179.172668457031 33.9775657653809,-177.411712646484 32.7810974121094,-179.555023193359 32.2134742736816,-177.829452514648 31.0155811309814,-178.239074707031 29.2493476867676,-180 28.765440257098),(180 28.765440257098,179.697463989258 28.6823024749756,180 28.4636381843949),(-180 28.4636381843949,-178.641540527344 27.481782913208,-180 27.1026516143923),(180 27.1026516143923,179.328506469727 26.9152450561523,180 26.4214887925138),(-180 26.4214887925138,-179.037658691406 25.7138690948486,-180 25.4416189129021),(180 25.4416189129021,178.962997436523 25.1482467651367,180 24.3735684465213),(-180 24.3735684465213,-179.425628662109 23.9444923400879,-180 23.780341930399),(180 23.780341930399,178.600112915039 23.3802661895752,180 22.3178596895149),(-180 22.3178596895149,-179.811676025391 22.1749362945557,-180 22.1205545104467),(180 22.1205545104467,178.239410400391 21.612154006958,179.806411743164 20.4047145843506,177.881164550781 19.8430519104004,179.427993774414 18.6337070465088,177.522979736328 18.0739707946777,179.052703857422 16.8625316619873,177.165435791016 16.3041744232178,178.680191040039 15.0904903411865,176.808044433594 14.5340976715088,178.308715820312 13.3182592391968,176.450408935547 12.7637596130371,177.938980102539 11.5455369949341,176.09211730957 10.9931364059448,177.5703125 9.77274036407471,175.732711791992 9.22251033782959,177.200942993164 7.99980688095093,175.371917724609 7.45217132568359,176.832183837891 6.22663640975952,175.008987426758 5.68129682540894,176.463012695312 4.45359802246094,174.642868041992 3.9112331867218,176.092864990234 2.68023443222046,174.274612426758 2.14099168777466,175.721282958984 0.907159090042114,175.344512939453 -0.865490198135376,173.52766418457 -1.39960491657257,174.968185424805 -2.63886117935181,173.146240234375 -3.16816759109497,174.589202880859 -4.4105658531189,172.762084960938 -4.93611574172974,174.206878662109 -6.181960105896,172.372528076172 -6.70371150970459,173.815628051758 -7.95153141021729,171.977310180664 -8.47032928466797,173.424789428711 -9.72116279602051,171.575668334961 -10.2361278533936,173.028900146484 -11.4902925491333,171.163818359375 -12.0003223419189,172.627426147461 -13.2584896087646,170.747268676758 -13.7642621994019,172.219528198242 -15.0262928009033,170.322357177734 -15.5267858505249,171.797317504883 -16.7912445068359,169.88818359375 -17.2885684967041,171.374435424805 -18.5567455291748,169.439819335938 -19.0476589202881,170.942901611328 -20.3211555480957,168.984527587891 -20.8061809539795,170.502136230469 -22.0839729309082,168.517272949219 -22.562858581543,170.041656494141 -23.8431797027588,168.036926269531 -24.3179912567139,169.578689575195 -25.6031246185303,167.542556762695 -26.070384979248,169.103271484375 -27.3606014251709,167.027191162109 -27.8197116851807,168.613876342773 -29.1168651580811,166.499847412109 -29.5679130554199,168.109359741211 -30.8706474304199,165.953872680664 -31.3137893676758,167.57666015625 -32.6198081970215,165.387435913086 -33.0564575195312,167.036361694336 -34.3690338134766,164.798675537109 -34.7957916259766,166.475784301758 -36.1154174804688,164.178695678711 -36.5308837890625,165.892303466797 -37.8593254089355,163.537521362305 -38.2635078430176,165.270629882812 -39.5980262756348,162.865798950195 -39.993350982666,162.604248046875 49.7597770690918,159.817276000977 49.206974029541,162.030807495117 48.0073394775391,159.345596313477 47.4513130187988,161.483963012695 46.2544708251953,158.88703918457 45.6956558227539,160.960144042969 44.4999237060547,158.443283081055 43.9381370544434,160.462936401367 42.7425155639648,158.012573242188 42.1795845031738,159.976715087891 40.9844932556152,157.593322753906 40.4199981689453,159.506225585938 39.2248840332031,157.187255859375 38.658618927002,159.049758911133 37.4636344909668,156.78645324707 36.8962783813477,158.605865478516 35.7016372680664,156.393508911133 35.1334800720215,158.177841186523 33.9373168945312,156.007247924805 33.3693618774414,157.754440307617 32.1723175048828,155.629364013672 31.6048202514648,157.339920043945 30.4070072174072,155.253952026367 29.8398838043213,156.933166503906 28.6408729553223,154.8828125 28.0738258361816,156.53645324707 26.8729763031006,154.515213012695 26.3067798614502,156.142211914062 25.104513168335,154.150741577148 24.5393562316895,155.753204345703 23.3353118896484,153.790145874023 22.7712497711182,155.368865966797 21.5655307769775,153.42985534668 21.0024166107178,154.988388061523 19.794885635376,153.070983886719 19.2331295013428,154.612426757812 18.0235290527344,152.713165283203 17.4638481140137,154.237762451172 16.2518997192383,152.356201171875 15.6937713623047,153.865325927734 14.4796628952026,151.998718261719 13.9237585067749,153.494552612305 12.7072706222534,151.640808105469 12.1532335281372,153.125106811523 10.9344100952148,151.282333374023 10.3827247619629,152.75634765625 9.16140747070312,150.922546386719 8.61202144622803,152.387878417969 7.38813495635986,150.560531616211 6.84148597717285,152.019134521484 5.61468744277954,150.196838378906 5.07079315185547,151.649856567383 3.84156084060669,149.830642700195 3.30050373077393,151.276809692383 2.06833529472351,149.461166381836 1.52908599376678,150.904434204102 0.295158416032791,149.087020874023 -0.239978566765785,139.997619628906 -41.2011337280273,137.531112670898 -41.5847930908203,139.33219909668 -42.9350357055664,136.785202026367 -43.302921295166,138.632263183594 -44.6648597717285,136.004852294922 -45.0178337097168,137.893737792969 -46.3901824951172,135.178649902344 -46.7265892028809,137.097106933594 -48.1077461242676,134.300750732422 -48.4294471740723,136.265609741211 -49.8229598999023,133.364837646484 -50.1249732971191,135.378631591797 -51.5319519042969,132.355972290039 -51.8103713989258,134.428451538086 -53.2342300415039,131.279968261719 -53.4888763427734,133.405822753906 -54.9288444519043,130.119445800781 -55.1570358276367,132.284912109375 -56.611400604248,128.861953735352 -56.8134384155273,131.083343505859 -58.2872085571289,127.49291229248 -58.4565467834473,129.770370483398 -59.9516677856445,125.987312316895 -60.0823440551758,128.327423095703 -61.6026229858398,124.339851379395 -61.6919746398926,126.715934753418 -63.2347221374512,122.518043518066 -63.2804870605469,100.357376098633 -5.2207465171814,98.5302658081055 -5.74575614929199,99.9733047485352 -6.99181175231934,98.1357803344727 -7.51213455200195,99.5850372314453 -8.762282371521,97.7375793457031 -9.27857208251953,99.1921310424805 -10.5319719314575,97.3327026367188 -11.0442295074463,98.7875900268555 -12.2995872497559,96.9205322265625 -12.8088693618774,98.3830413818359 -14.0677537918091,96.500373840332 -14.5723247528076,97.9720153808594 -15.8347663879395,96.0677261352539 -16.3336067199707,97.5535736083984 -17.6010112762451,95.6290435791016 -18.0943622589111,97.1271514892578 -19.3658485412598,95.1799621582031 -19.8536357879639,96.6832580566406 -21.1273212432861,94.7196350097656 -21.610876083374,96.2375869750977 -22.8894996643066,94.2466659545898 -23.3670101165771,95.7811737060547 -24.6501235961914,93.7552185058594 -25.1199836730957,95.3129043579102 -26.4090194702148,93.2538452148438 -26.8713989257812,94.8212890625 -28.1632099151611,92.7361373901367 -28.6209163665771,94.3247299194336 -29.9193019866943,92.2005920410156 -30.3681621551514,93.8125534057617 -31.6720523834229,91.6398010253906 -32.1113395690918,93.2827758789062 -33.4228439331055,91.0632400512695 -33.8530921936035,92.733642578125 -35.1707534790039,90.4633712768555 -35.5911026000977,92.1507415771484 -36.9133987426758,89.8374938964844 -37.3261299133301,91.5559768676758 -38.655948638916,89.182861328125 -39.0575218200684,90.934814453125 -40.3954811096191,88.4900360107422 -40.7827835083008,90.2844848632812 -42.1309928894043,87.7687377929688 -42.5048446655273,89.5876388549805 -43.8597068786621,87.0083618164062 -44.2220687866211,88.8674774169922 -45.5872993469238,86.2042694091797 -45.9339179992676,88.1061782836914 -47.3099479675293,85.3439407348633 -47.6379508972168,87.2985610961914 -49.0276794433594,84.4361190795898 -49.3365135192871,86.4385452270508 -50.7395401000977,83.4664688110352 -51.0275382995605,85.5039672851562 -52.4419097900391,82.4266510009766 -52.710147857666,84.5162658691406 -54.1399841308594,81.3071670532227 -54.3830184936523,83.4508972167969 -55.8299407958984,80.0887298583984 -56.0434608459473,82.2961120605469 -57.5104522705078,78.7736740112305 -57.6930274963379,81.0216445922852 -59.1771774291992,77.3383483886719 -59.3281898498535,79.6418304443359 -60.8346252441406,75.7636108398438 -60.9465637207031,78.1203079223633 -62.4777221679688,74.0187530517578 -62.5435791015625,45.187629699707 -29.1457710266113,43.0703811645508 -29.5966987609863,44.6830253601074 -30.8994979858398,42.5243835449219 -31.3420486450195,44.1501083374023 -32.648853302002,41.9576873779297 -33.0848541259766,43.6097183227539 -34.3978805541992,41.3686370849609 -34.824348449707,43.0489311218262 -36.1443519592285,40.7485008239746 -36.5590705871582,42.4652061462402 -37.8883361816406,40.1068077087402 -38.2922630310059,41.8434143066406 -39.6265411376953,39.4355049133301 -40.020092010498,41.2066764831543 -41.3629150390625,38.7303581237793 -41.744270324707,40.5384521484375 -43.0964202880859,37.9808349609375 -43.4623069763184,39.835506439209 -44.825439453125,37.1968994140625 -45.1761322021484,39.0932846069336 -46.5506591796875,36.3661460876465 -46.8848304748535,38.292537689209 -48.267692565918,35.4832611083984 -48.5873069763184,37.4564170837402 -49.9823875427246,34.5420341491699 -50.2817726135254,36.5641899108887 -51.6908683776855,33.526683807373 -51.9667587280273,35.6081161499023 -53.3923110961914,32.4436836242676 -53.6441650390625,34.5632438659668 -55.0828895568848,31.2751693725586 -55.3111190795898,-135.677658081055 -63.2441253662109,-133.2841796875 -61.6980056762695,-137.273559570312 -61.608829498291,-134.931854248047 -60.088436126709,-138.716690063477 -59.9577789306641,-136.438262939453 -58.4619903564453,-140.030471801758 -58.2923011779785,-137.807067871094 -56.8193397521973,-141.231384277344 -56.6174163818359,-139.064483642578 -55.1630554199219,-142.3525390625 -54.9345092773438,-140.225509643555 -53.4944686889648,-143.375274658203 -53.2396545410156,-141.301528930664 -51.8160667419434,-144.325485229492 -51.537239074707,-142.310623168945 -50.130313873291,-145.212432861328 -49.8281555175781,-143.24658203125 -48.4347839355469,-146.058532714844 -48.1159439086914,-144.12467956543 -46.7317123413086,-146.840591430664 -46.3951416015625,-144.95085144043 -45.0228271484375,-147.579391479492 -44.6693305969238,-145.738494873047 -43.309009552002,-148.279449462891 -42.9390106201172,-146.477508544922 -41.5889701843262,-148.944778442383 -41.2051162719727,-147.179595947266 -39.8642883300781,-149.592193603516 -39.4703903198242,-147.848541259766 -38.1351089477539,-150.198638916016 -37.7293891906738,-148.48747253418 -36.4019584655762,-150.780197143555 -35.9853935241699,-149.10546875 -34.6669006347656,-151.339279174805 -34.2384414672852,-149.692474365234 -32.9269905090332,-151.889282226562 -32.4917793273926,-150.25715637207 -31.1840972900391,-152.409286499023 -30.7401332855225,-150.801651000977 -29.4383125305176,-152.91259765625 -28.9859485626221,-151.332855224609 -27.6912994384766,-153.400894165039 -27.2297325134277,-151.841812133789 -25.9398670196533,-153.875411987305 -25.4715118408203,-152.335189819336 -24.1869430541992,-154.346817016602 -23.7137145996094,-152.814361572266 -22.4320030212402,-154.79719543457 -21.952184677124,-153.280624389648 -20.6751289367676,-155.2373046875 -20.1890754699707,-153.739349365234 -18.9172954559326,-155.668045043945 -18.4248371124268,-154.182739257812 -17.1569862365723,-156.097778320312 -16.6611213684082,-154.616027832031 -15.3958034515381,-156.512008666992 -14.8945379257202,-155.040237426758 -13.6331806182861,-156.919281005859 -13.1269884109497,-155.459289550781 -11.8703861236572,-157.320373535156 -11.3587255477905,-155.867538452148 -10.10484790802,-157.716003417969 -9.58876705169678,-156.268859863281 -8.33797073364258,-158.111968994141 -7.81921672821045,-156.663803100586 -6.57053470611572,-158.497985839844 -6.04785346984863,-157.052993774414 -4.80249738693237,-158.880065917969 -4.27616500854492,-157.438934326172 -3.03444576263428,-159.258911132812 -2.5037829875946,-157.818023681641 -1.26510322093964,-159.634872436523 -0.731236040592194,-158.194122314453 0.504069805145264,-160.011093139648 1.04091656208038,-158.563858032227 2.2740535736084,-160.382568359375 2.81372761726379,-158.93293762207 4.04395532608032,-160.752593994141 4.58652496337891,-159.2978515625 5.81362867355347,-161.12190246582 6.35963487625122,-159.660598754883 7.58447456359863,-161.491744995117 8.1320686340332,-160.021377563477 9.35495471954346,-161.860229492188 9.905348777771,-160.380981445312 11.1267356872559,-162.229309082031 11.6789484024048,-160.739059448242 12.8966703414917,-162.599243164062 13.4516983032227,-161.096771240234 14.667308807373,-162.970443725586 15.2236528396606,-161.454132080078 16.4369678497314,-163.343673706055 16.9955863952637,-161.811752319336 18.2066135406494,-163.719390869141 18.7667903900146,-162.169158935547 19.9759273529053,-164.098068237305 20.537618637085,-162.528518676758 21.7449398040771,-164.480453491211 22.3079662322998,-162.889572143555 23.5137100219727,-164.86457824707 24.0780715942383,-163.252716064453 25.2815761566162,-165.255676269531 25.847131729126,-163.616683959961 27.0490798950195,-165.652404785156 27.6154270172119,-163.985687255859 28.8157329559326,-166.055587768555 29.3826351165771,-164.358673095703 30.5818195343018,-166.461654663086 31.149486541748,-164.73616027832 32.3468284606934,-166.879821777344 32.9148101806641,-165.119140625 34.111270904541,-167.307281494141 34.6791763305664,-165.505447387695 35.8752937316895,-167.744979858398 36.4422149658203,-165.901412963867 37.6375312805176,-168.194412231445 38.2042388916016,-166.305694580078 39.3993263244629,-168.651062011719 39.9661445617676,-166.719436645508 41.1604156494141,-169.128051757812 41.7255020141602,-167.139999389648 42.9206161499023,-169.621612548828 43.4829940795898,-167.576019287109 44.6787719726562,-170.134078979492 45.2387847900391,-168.025848388672 46.4355278015137,-170.660217285156 46.9937438964844,-168.491561889648 48.1911582946777,-171.217742919922 48.7460594177246,-168.975433349609 49.9459609985352))"

ghost commented 4 years ago

That said, I wouldn't listen to me too much. because like I said, I don't enough about data. All I know is these run way too slow to be usable, and if the data volume is growing (which I guess is implied by the near real time layer?), it's only going to get worse.

jonescc commented 4 years ago

I'm not really a GIS expert either, but adding indexes is not going to help here. There's only 3719 records in the core table.

We're really packing quite a lot of geometry into those records and that must be the source of the problem.

Some possible things to look at:

ccmoloney commented 4 years ago

@ccmoloney Please put some Sumo stats in here about the health of the layer.

ocehugo commented 4 years ago

2 cents:

  1. Polygon reduction is a very common technique. I remember doing this a long time ago in QGIS and it was pretty good. Quickly googling around the qgis functionality, I arrived at the ST_SimplifyVW PostGIS function that may be worth it in this case. The algorithm used is the same used in QGIS apparently (Visvalingam).

  2. We also don't need more than 5 digits in lat/lon, unless we need sub-meter precision (unlikely).