Revision 11346
Added by Paul Sarando about 11 years ago
| geonames.sh | ||
|---|---|---|
| 59 | 59 |
psql -c "COPY geonames FROM '${DATADIR}/allCountries.txt' DELIMITER E'\t' NULL ''" geoscrub
|
| 60 | 60 |
psql -c \ "SELECT setval('geonames_geonameid_seq', (SELECT max(geonameid) FROM geonames))" geoscrub
|
| 61 | 61 |
psql -c \ "CREATE INDEX geonames_countrycode_idx ON geonames (countrycode)" geoscrub |
| 62 |
psql -c "CREATE INDEX geonames_featurecode_idx ON geonames (featurecode)" geoscrub |
|
| 63 |
psql -c "CREATE INDEX geonames_name_idx ON geonames (name)" geoscrub |
|
| 62 | 64 |
|
| 63 | 65 |
# |
| 64 | 66 |
# load geonames.org alternate names |
| ... | ... | |
| 87 | 89 |
# index alternatenames |
| 88 | 90 |
psql -c 'CREATE INDEX "alternatenames_alternatename_idx" ON alternateNames (alternateName)' geoscrub |
| 89 | 91 |
# 10m42.827s |
| 92 |
psql -c 'CREATE INDEX "alternatenames_geonameid_idx" ON alternateNames (geonameid)' geoscrub |
|
| 90 | 93 |
|
| 91 | 94 |
# |
| 92 | 95 |
# load geonames.org country info |
| ... | ... | |
| 135 | 138 |
|
| 136 | 139 |
# import geonames country table |
| 137 | 140 |
psql -c "COPY hierarchy FROM '${DATADIR}/hierarchy.txt' DELIMITER E'\t' NULL ''" geoscrub
|
| 141 |
psql -c "CREATE INDEX hierarchy_parentid_idx ON hierarchy (parentId)" geoscrub |
|
| 142 |
psql -c "CREATE INDEX hierarchy_childid_idx ON hierarchy (childId)" geoscrub |
|
| 138 | 143 |
|
| 139 | 144 |
# |
| 140 | 145 |
# insert additional custom name-scrub mappings |
Also available in: Unified diff
Added indexes to speed up geonames-to-gadm.sql.
Without these indexes, these queries could take hours to complete.
With them, the times more closely matched the times Jim noted in the sql
comments.