Skip to content

Instantly share code, notes, and snippets.

@tomask-de

tomask-de/place-zip-state.sql

Created April 10, 2018 16:09
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save tomask-de/0bb9c3db8bf1562d505c0a8c5ea58655 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save tomask-de/0bb9c3db8bf1562d505c0a8c5ea58655 to your computer and use it in GitHub Desktop.
Download ZIP
opengeodb select place, zip/postal code, federal state
Raw
place-zip-state.sql
SELECT
t1.loc_id, t1.text_val AS Ort, t2.text_val AS PLZ, t5.text_val AS Bundesland
FROM geodb_textdata t1, geodb_textdata t2, geodb_textdata t3, geodb_textdata t4, geodb_textdata t5
WHERE t2.text_val = '08060'
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 */
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment