Revision 10390
Added by Aaron Marcuse-Kubitza over 11 years ago
| inputs/.geoscrub/Source/run | ||
|---|---|---|
| 1 |
#!/bin/bash -e |
|
| 2 |
. "$(dirname "${BASH_SOURCE[0]}")"/../table.run
|
|
| 0 | 3 | |
| inputs/.geoscrub/run | ||
|---|---|---|
| 1 |
#!/bin/bash -e |
|
| 2 |
. "$(dirname "${BASH_SOURCE[0]}")"/../../lib/runscripts/datasrc_dir.run
|
|
| 0 | 3 | |
| inputs/.geoscrub/geoscrub_output/postprocess.sql | ||
|---|---|---|
| 2 | 2 |
|
| 3 | 3 |
-- runtime: 40 s ("real 0m21.770s" + "Time: 17304.769 ms") @starscream
|
| 4 | 4 |
SELECT set_col_types(:table_str, ARRAY[ |
| 5 |
('decimallatitude' , 'double precision')
|
|
| 6 |
, ('decimallongitude' , 'double precision')
|
|
| 7 |
, ('latlonvalidity' , 'integer')
|
|
| 8 |
, ('countryvalidity' , 'integer')
|
|
| 9 |
, ('stateprovincevalidity', 'integer')
|
|
| 10 |
, ('countyvalidity' , 'integer')
|
|
| 5 |
('decimalLatitude' , 'double precision')
|
|
| 6 |
, ('decimalLongitude' , 'double precision')
|
|
| 7 |
, ('latLongDomainValid_verbatim' , 'integer')
|
|
| 8 |
, ('latLongInCountry_verbatim' , 'integer')
|
|
| 9 |
, ('latLongInStateProvince_verbatim', 'integer')
|
|
| 10 |
, ('latLongInCounty_verbatim' , 'integer')
|
|
| 11 | 11 |
]::col_cast[]); |
| 12 | 12 |
|
| 13 |
ALTER TABLE :table ALTER COLUMN decimallatitude SET NOT NULL;
|
|
| 14 |
ALTER TABLE :table ALTER COLUMN decimallongitude SET NOT NULL;
|
|
| 13 |
ALTER TABLE :table ALTER COLUMN "decimalLatitude" SET NOT NULL;
|
|
| 14 |
ALTER TABLE :table ALTER COLUMN "decimalLongitude" SET NOT NULL;
|
|
| 15 | 15 |
|
| 16 | 16 |
-- runtime: 55 s ("real 0m53.677s")
|
| 17 |
SELECT create_if_not_exists($$CREATE INDEX geoscrub_output_input ON geoscrub_output (decimallatitude, decimallongitude, country, stateprovince, county)$$);
|
|
| 17 |
SELECT create_if_not_exists($$CREATE INDEX geoscrub_output_input ON geoscrub_output ("decimalLatitude", "decimalLongitude", country, "stateProvince", county)$$);
|
|
| 18 | 18 |
|
| 19 | 19 |
|
| 20 | 20 |
-- map_*() derived cols |
| ... | ... | |
| 35 | 35 |
LANGUAGE sql IMMUTABLE |
| 36 | 36 |
COST 100; |
| 37 | 37 |
|
| 38 |
SELECT mk_derived_col((:table_str, 'latLongDomainValid'), $$map_coord_validity("latlonvalidity")$$); -- runtime: 25 s ("Time: 26708.932 ms") @starscream
|
|
| 38 |
SELECT mk_derived_col((:table_str, 'latLongDomainValid'), $$map_coord_validity("latLongDomainValid_verbatim")$$); -- runtime: 25 s ("Time: 26708.932 ms") @starscream
|
|
| 39 | 39 |
|
| 40 | 40 |
CREATE OR REPLACE FUNCTION map_geovalidity(value integer) |
| 41 | 41 |
RETURNS boolean AS |
| ... | ... | |
| 54 | 54 |
LANGUAGE sql IMMUTABLE |
| 55 | 55 |
COST 100; |
| 56 | 56 |
|
| 57 |
SELECT mk_derived_col((:table_str, 'latLongInCountry' ), $$map_geovalidity("countryvalidity" )$$); -- runtime: 25 s ("Time: 27884.129 ms") @starscream
|
|
| 58 |
SELECT mk_derived_col((:table_str, 'latLongInStateProvince'), $$map_geovalidity("stateprovincevalidity")$$); -- runtime: 25 s ("Time: 27228.239 ms") @starscream
|
|
| 59 |
SELECT mk_derived_col((:table_str, 'latLongInCounty' ), $$map_geovalidity("countyvalidity" )$$); -- runtime: 25 s ("Time: 27337.520 ms") @starscream
|
|
| 57 |
SELECT mk_derived_col((:table_str, 'latLongInCountry' ), $$map_geovalidity("latLongInCountry_verbatim" )$$); -- runtime: 25 s ("Time: 27884.129 ms") @starscream
|
|
| 58 |
SELECT mk_derived_col((:table_str, 'latLongInStateProvince'), $$map_geovalidity("latLongInStateProvince_verbatim")$$); -- runtime: 25 s ("Time: 27228.239 ms") @starscream
|
|
| 59 |
SELECT mk_derived_col((:table_str, 'latLongInCounty' ), $$map_geovalidity("latLongInCounty_verbatim" )$$); -- runtime: 25 s ("Time: 27337.520 ms") @starscream
|
|
| inputs/.geoscrub/geoscrub_output/run | ||
|---|---|---|
| 1 |
#!/bin/bash -e |
|
| 2 |
. "$(dirname "${BASH_SOURCE[0]}")"/../table.run
|
|
| 0 | 3 | |
| inputs/.geoscrub/geoscrub_output/map.csv | ||
|---|---|---|
| 12 | 12 |
countryvalidity,latLongInCountry_verbatim,, |
| 13 | 13 |
stateprovincevalidity,latLongInStateProvince_verbatim,, |
| 14 | 14 |
countyvalidity,latLongInCounty_verbatim,, |
| 15 |
latLongDomainValid,latLongDomainValid,, |
|
| 16 |
latLongInCountry,latLongInCountry,, |
|
| 17 |
latLongInStateProvince,latLongInStateProvince,, |
|
| 18 |
latLongInCounty,latLongInCounty,, |
|
| inputs/.geoscrub/geoscrub_output/test.xml.ref | ||
|---|---|---|
| 16 | 16 |
<_nullIf> |
| 17 | 17 |
<null>0</null> |
| 18 | 18 |
<type>float</type> |
| 19 |
<value>$decimallatitude</value>
|
|
| 19 |
<value>$decimalLatitude</value>
|
|
| 20 | 20 |
</_nullIf> |
| 21 | 21 |
</latitude_deg> |
| 22 | 22 |
<longitude_deg> |
| 23 | 23 |
<_nullIf> |
| 24 | 24 |
<null>0</null> |
| 25 | 25 |
<type>float</type> |
| 26 |
<value>$decimallongitude</value>
|
|
| 26 |
<value>$decimalLongitude</value>
|
|
| 27 | 27 |
</_nullIf> |
| 28 | 28 |
</longitude_deg> |
| 29 | 29 |
</coordinates> |
| ... | ... | |
| 40 | 40 |
<_nullIf> |
| 41 | 41 |
<null>0</null> |
| 42 | 42 |
<type>float</type> |
| 43 |
<value>$decimallatitude</value>
|
|
| 43 |
<value>$decimalLatitude</value>
|
|
| 44 | 44 |
</_nullIf> |
| 45 | 45 |
</latitude_deg> |
| 46 | 46 |
<longitude_deg> |
| 47 | 47 |
<_nullIf> |
| 48 | 48 |
<null>0</null> |
| 49 | 49 |
<type>float</type> |
| 50 |
<value>$decimallongitude</value>
|
|
| 50 |
<value>$decimalLongitude</value>
|
|
| 51 | 51 |
</_nullIf> |
| 52 | 52 |
</longitude_deg> |
| 53 | 53 |
</coordinates> |
| 54 | 54 |
</coordinates_id> |
| 55 | 55 |
<matched_place_id>0</matched_place_id> |
| 56 |
<country>$countrystd</country>
|
|
| 57 |
<county>$countystd</county>
|
|
| 58 |
<stateprovince>$stateprovincestd</stateprovince>
|
|
| 56 |
<country>$acceptedCountry</country>
|
|
| 57 |
<county>$acceptedCounty</county>
|
|
| 58 |
<stateprovince>$acceptedStateProvince</stateprovince>
|
|
| 59 | 59 |
<geovalid> |
| 60 | 60 |
<_alt> |
| 61 | 61 |
<2>false</2> |
| ... | ... | |
| 71 | 71 |
</geovalid> |
| 72 | 72 |
</place> |
| 73 | 73 |
</matched_place_id> |
| 74 |
<stateprovince>$stateprovince</stateprovince>
|
|
| 74 |
<stateprovince>$stateProvince</stateprovince>
|
|
| 75 | 75 |
</place> |
| 76 | 76 |
</place_id> |
| 77 | 77 |
</locationplace> |
| inputs/.geoscrub/geoscrub_output/VegBIEN.csv | ||
|---|---|---|
| 1 |
geoscrub,VegBIEN:/_setDefault:[source_id/source/shortname/_env:[name=source]]/path/_simplifyPath:[next=parent_id]/path,Comments |
|
| 2 |
decimallatitude,"/location/locationplace/*_id/place/*_id/coordinates/latitude_deg/_nullIf:[null=0,type=float]/value", |
|
| 3 |
decimallongitude,"/location/locationplace/*_id/place/*_id/coordinates/longitude_deg/_nullIf:[null=0,type=float]/value", |
|
| 4 |
country,/location/locationplace/*_id/place/country, |
|
| 5 |
county,/location/locationplace/*_id/place/county, |
|
| 6 |
decimallatitude,"/location/locationplace/*_id/place/matched_place_id/place:[.,source_id/source/shortname=geoscrub]/*_id/coordinates:[source_id/source/shortname=geoscrub]/_first/2/latitude_deg/_nullIf:[null=0,type=float]/value",""".,"" sorts it with other coordinates mappings" |
|
| 7 |
decimallongitude,"/location/locationplace/*_id/place/matched_place_id/place:[.,source_id/source/shortname=geoscrub]/*_id/coordinates:[source_id/source/shortname=geoscrub]/_first/2/longitude_deg/_nullIf:[null=0,type=float]/value",""".,"" sorts it with other coordinates mappings" |
|
| 8 |
countrystd,"/location/locationplace/*_id/place/matched_place_id/place:[source_id/source/shortname=geoscrub,matched_place_id=0]/country/_first/1", |
|
| 9 |
countystd,"/location/locationplace/*_id/place/matched_place_id/place:[source_id/source/shortname=geoscrub,matched_place_id=0]/county/_first/1", |
|
| 10 |
stateprovincestd,"/location/locationplace/*_id/place/matched_place_id/place:[source_id/source/shortname=geoscrub,matched_place_id=0]/stateprovince/_first/1", |
|
| 11 |
country,/location/locationplace/*_id/place/matched_place_id/place:[source_id/source/shortname=geoscrub]/country/_first/2, |
|
| 12 |
county,/location/locationplace/*_id/place/matched_place_id/place:[source_id/source/shortname=geoscrub]/county/_first/2, |
|
| 13 |
latLongDomainValid,/location/locationplace/*_id/place/matched_place_id/place:[source_id/source/shortname=geoscrub]/geovalid/_alt:[2=false]/1/_first/2/_and/1, |
|
| 14 |
latLongInCountry,/location/locationplace/*_id/place/matched_place_id/place:[source_id/source/shortname=geoscrub]/geovalid/_alt:[2=false]/1/_first/2/_and/2, |
|
| 15 |
latLongInStateProvince,/location/locationplace/*_id/place/matched_place_id/place:[source_id/source/shortname=geoscrub]/geovalid/_alt:[2=false]/1/_first/2/_and/3, |
|
| 16 |
latLongInCounty,/location/locationplace/*_id/place/matched_place_id/place:[source_id/source/shortname=geoscrub]/geovalid/_alt:[2=false]/1/_first/2/_and/4, |
|
| 17 |
stateprovince,/location/locationplace/*_id/place/matched_place_id/place:[source_id/source/shortname=geoscrub]/stateprovince/_first/2, |
|
| 18 |
stateprovince,/location/locationplace/*_id/place/stateprovince, |
|
| 19 |
countryvalidity,,** No join mapping for latLongInCountry_verbatim ** |
|
| 20 |
countyvalidity,,** No join mapping for latLongInCounty_verbatim ** |
|
| 21 |
latlonvalidity,,** No join mapping for latLongDomainValid_verbatim ** |
|
| 22 |
row_num,,** No join mapping for *row_num ** |
|
| 23 |
stateprovincevalidity,,** No join mapping for latLongInStateProvince_verbatim ** |
|
| 1 |
link ../../../mappings/VegCore-VegBIEN.csv |
|
| 24 | 2 | |
Also available in: Unified diff
inputs/.geoscrub/: switched to new-style import, using the steps at wiki.vegpath.org/Adding_new-style_import_to_a_datasource