Created
April 11, 2018 14:14
-
-
Save tomask-de/3e7a91d1efbb9cf3dabb3b99c266eaaa to your computer and use it in GitHub Desktop.
opengeodb locations incl. location id, zip code, location name, region (federal state), lon and lat
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| CREATE TABLE locations | |
| ( | |
| id INT AUTO_INCREMENT PRIMARY KEY, | |
| loc_id INT NOT NULL, | |
| zip VARCHAR(10) NOT NULL, | |
| location_name VARCHAR(255) NOT NULL, | |
| region VARCHAR(255) NOT NULL, | |
| lat DOUBLE NOT NULL, | |
| lon DOUBLE NOT NULL | |
| ) ENGINE = InnoDB; | |
| insert into locations (loc_id, zip, location_name, region, lat, lon) | |
| SELECT | |
| dest.zc_loc_id AS ID, | |
| dest.zc_zip AS PLZ, | |
| dest.zc_location_name AS ORT, | |
| t5.text_val AS Bundesland, | |
| dest.zc_lat, dest.zc_lon | |
| FROM zip_coordinates dest, geodb_textdata t1, geodb_textdata t2, geodb_textdata t3, geodb_textdata t4, geodb_textdata t5 | |
| WHERE t1.loc_id = dest.zc_loc_id | |
| AND t2.text_val = dest.zc_zip | |
| AND t1.loc_id = t2.loc_id | |
| AND t2.text_type = '500300000' /* PLZ */ | |
| AND t3.loc_id = t1.loc_id | |
| AND t3.text_type = '500600000' /* Amtlicher Gemeindeschlüsssel */ | |
| AND t4.text_type = '500600000' | |
| AND t4.text_val = SUBSTR(t3.text_val,1,2) | |
| AND t5.loc_id = t4.loc_id | |
| AND t1.text_type = t5.text_type | |
| AND t5.text_type = '500100000' /* Name */ | |
| ORDER BY ID |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment