Revision 11388
Added by Aaron Marcuse-Kubitza about 11 years ago
| inputs/.geoscrub/geoscrub_output/postprocess.sql | ||
|---|---|---|
| 13 | 13 |
ALTER TABLE :table ALTER COLUMN "decimalLatitude" SET NOT NULL; -- runtime: 5 s ("5248.843") @starscream
|
| 14 | 14 |
ALTER TABLE :table ALTER COLUMN "decimalLongitude" SET NOT NULL; -- runtime: 1 s ("1064.650") @starscream
|
| 15 | 15 |
|
| 16 |
-- runtime: 30 s ("29089.750 ms") @starscream
|
|
| 17 |
SELECT create_if_not_exists($$CREATE INDEX geoscrub_output_input ON geoscrub_output ("decimalLatitude", "decimalLongitude", country, "stateProvince", county)$$);
|
|
| 16 |
SELECT create_if_not_exists($$CREATE UNIQUE INDEX $$||:table_str||%'__unique'||$$ ON $$||:table_str||$$ ( |
|
| 17 |
(array["decimalLatitude"]), (array["decimalLongitude"]), (array[country]), (array["stateProvince"]), (array[county]) |
|
| 18 |
)$$); -- runtime: 2 min ("106693.873") @starscream
|
|
| 18 | 19 |
|
| 19 | 20 |
|
| 20 | 21 |
-- map_*() derived cols |
Also available in: Unified diff
inputs/.geoscrub/geoscrub_output/postprocess.sql: added nullable unique index on the inputs, for use by analytical_stem_view. note that it must be nullable in order to create a match when not all of the input fields are populated. this uses array[] to create a nullable index, which is much better than column-based import and VegBIEN's use of COALESCE because the expression is the same for every type and no NULL sentinel value is needed.