opengeodb Umkreissuche

gistfile1.sql

/* http://opengeodb.org/wiki/OpenGeoDB_-_Umkreissuche */

/* create zip table */
CREATE TABLE `zip_coordinates` (
    zc_id INT NOT NULL auto_increment PRIMARY KEY,
    zc_loc_id INT NOT NULL ,
    zc_zip VARCHAR( 10 ) NOT NULL ,
    zc_location_name VARCHAR( 255 ) NOT NULL ,
    zc_lat DOUBLE NOT NULL ,
    zc_lon DOUBLE NOT NULL
)

/* fill zip table */
INSERT INTO zip_coordinates (zc_loc_id, zc_zip, zc_location_name, zc_lat, zc_lon)
SELECT gl.loc_id, plz.text_val, name.text_val, coord.lat, coord.lon
FROM geodb_textdata plz
LEFT JOIN geodb_textdata name ON name.loc_id = plz.loc_id
LEFT JOIN geodb_locations gl ON gl.loc_id = plz.loc_id
LEFT JOIN geodb_hierarchies as tier ON plz.loc_id = tier.loc_id /* localisierung */
LEFT JOIN geodb_coordinates coord ON plz.loc_id = coord.loc_id
WHERE plz.text_type =500300000/* ID für Postleitzahl */
  AND name.text_type =500100000/* ID für name */
  AND tier.id_lvl1 = 104
  AND tier.id_lvl2 = 105 /* Bundesrepublik Deutschland*/
  AND name.text_locale = "de" /* deutschsprachige Version*/
  AND (
        gl.loc_type =100600000/* ID für pol. Gliederung */
        OR 
        gl.loc_type =100700000/* ID für Ortschaft */
  );