Swiss geographic basics

We (Pro Juventute) use Swiss geographic entities for a number of analyses and reports. For this purpose we update them daily. And while we're at it, we hereby share them with you.

# Content

There are two sources for the three tables in this dataset:

1. The official directories by [SwissTopo](https://www.swisstopo.admin.ch/en/official-geographic-directories)
2. The basic geographics by the [Federal Statistics Office (BFS)](https://www.bfs.admin.ch/bfs/en/home/statistics/regional-statistics/base-maps/cartographic-bases.html)

The combined data offers a robust foundation for:

- Precise geocoding and reverse geocoding applications
- Spatial data visualization and mapping

# Data structure

The source data is provided in the Swiss [LV95](https://epsg.io/2056) map projection (both points and polygons). To enable using the global [WSG84](https://epsg.io/4326) map projection, we transform the original points and polygons and provide the second projection alongside the first.

The dataset is organized into three tables, each representing a different aspect of the geospatial information:

## Localities and municipalities

Since localities (cities and towns) and municipalities (administrative divisions) can have some overlap (a locality can contain or be contained in multiple municipalities), their polygons are different. Hence we extend the [official directory of towns and cities](https://www.swisstopo.admin.ch/en/official-directory-of-towns-and-cities) from SwissTopo with yearly updated polygons on [municipalities](https://www.bfs.admin.ch/bfs/en/home/statistics/regional-statistics/base-maps/cartographic-bases.html) from the Federal Statistical Office (BFS).

The resulting table contains those columns:

1. ID (VARCHAR(56)): Combined ID of the locality (city/town) and the municipality
2. LOCALITY_ID (VARCHAR(40)): ID of the locality (city/town)
3. LOCALITY (VARCHAR(40)): Name of the locality (city/town); multi-lingual separated by /
4. MUNICIPALITY_NR (NUMBER(16,0)): Number of the municipality
5. MUNICIPALITY (VARCHAR(40)): Name of the municipality
6. ZIP (NUMBER(16,0)): ZIP code of the locality, 4 digits
7. ZIP_ADD (NUMBER(16,0)): ZIP addition of the locality
8. CANTON (VARCHAR(2)): Canton of the municipality; one locality can have municipalities in multiple cantons
9. LANGUAGE (VARCHAR(20)): Language of the name of the locality
10. VALIDITY (DATE): Name of the locality is valid since
11. EAST_LV95 (FLOAT): East coordinate, LV95: random point inside the locality
12. NORTH_LV95 (FLOAT): North coordinate, LV95: random point inside the locality
13. LONGITUDE (FLOAT): East coordinate, WGS84: random point inside the locality
14. LATITUDE (FLOAT): North coordinate, WGS84: random point inside the locality
15. POINT_LV95 (GEOMETRY): Random point inside the locality, LV95
16. POINT_WGS84 (GEOGRAPHY): Random point inside the locality, WGS84
17. LOCALITY_POLYGON_LV95 (GEOMETRY): Polygon of the locality, LV95
18. LOCALITY_POLYGON_WGS84 (GEOGRAPHY): Polygon of the locality, WGS84
19. MUNICIPALITY_POLYGON_LV95 (GEOMETRY): Polygon of the municipality, LV95
20. MUNICIPALITY_POLYGON_WGS84 (GEOGRAPHY): Polygon of the municipality, WGS84

**Sample values** for the city of Geneva:

- LOCALITY: "Genève"
- MUNICIPALITY: "Genève", "Le Grand-Saconnex", "Meyrin", "Pregny-Chambésy", "Vernier"
- MUNICIPALITY_NR: 6621, 6623, 6630, 6634, 6643
- ZIP: 1201, 1202, 1203, 1204, 1205, 1206, 1207, 1208, 1209, 1215
- CANTON: "GE"
- LANGUAGE: "FR"

## Streets

As each street in Switzerland gets a unique identifier from the federal authorities, we utilize the [official street directory](https://www.swisstopo.admin.ch/en/official-street-directory) and simply add a street's coordinates in the WSG84 projection:

1. STREET_ID (NUMBER(16,0)): ID of the street (ESID: Eidgenössischer Strassenidentifikator)
2. STREET (VARCHAR(150)): Name of the street; multi-lingual separated by /
3. ZIP_LOCALITY (VARCHAR(500)): ZIP code and locality; multiple separated by ,
4. MUNICIPALITY_NR (NUMBER(6,0)): Number of the municipality
5. MUNICIPALITY (VARCHAR(40)): Name of the municipality
6. CANTON (VARCHAR(2)): Canton of the municipality
7. TYPE (VARCHAR(50)): Type of the street
8. STATUS (VARCHAR(50)): Realization status of the street according to GWR
9. OFFICIAL (BOOLEAN): Mandatory spelling of the street name according to GWR
10. LAST_MODIFIED (DATE): Last modification date of the street
11. EAST_LV95 (NUMBER(16,0)): East coordinate, LV95: random point on the street
12. NORTH_LV95 (NUMBER(16,0)): North coordinate, LV95: random point on the street
13. LONGITUDE (FLOAT): East coordinate, WGS84: random point on the street
14. LATITUDE (FLOAT): North coordinate, WGS84: random point on the street
15. POINT_LV95 (GEOMETRY): Random point on the street, LV95
16. POINT_WGS84 (GEOGRAPHY): Random point on the street, WGS84

**Sample values** for the most famous street in Bern:

- STREET_ID: 10006889
- STREET: "Kramgasse"
- ZIP_LOCALITY: "3011 Bern"
- MUNICIPALITY_NR: 351
- MUNICIPALITY: "Bern"
- CANTON: "BE"
- TYPE: "Street"
- STATUS: "real"
- OFFICIAL: TRUE
- LAST_MODIFIED: 2024-07-23
- EAST_LV95: 2600737
- NORTH_LV95: 1199656
- LONGITUDE: 7.448313238
- LATITUDE: 46.947988103

## Buildings

The highest level of detail available in the [official registry](https://www.swisstopo.admin.ch/en/official-directory-of-building-addresses) goes down to each officially registered building address. Noteworthy in this table are a couple of official IDs - which might be useful as reference to other geographic data provided by the Federal Statistics Office or private entities. Once again the WSG84 projection of the coordinates of a building's address is added:

1. BUILDINGADDRESS_ID (NUMBER(16,0)): ID of the building address (EGAID: Eidgenössischer Gebäudeadressidentifikator)
2. STREET_ID (NUMBER(16,0)): ID of the street (ESID: Eidgenössischer Strassenidentifikator)
3. BUILDING_ID (NUMBER(16,0)): ID of the building (EGID: Eidgenössischer Gebäudeidentifikator)
4. ENTRY_ID (NUMBER(16,0)): ID of the entry (EDID: Eidgenössischer Eingangsidentifikator)
5. STREET (VARCHAR(150)): Name of the street; multi-lingual separated by /
6. HOUSE_NUMBER (VARCHAR(12)): House number
7. BUILDING_CATEGORY (VARCHAR(50)): Category of the building
8. BUILDING_NAME (VARCHAR(50)): Name of the building; only if HOUSE_NUMBER has no value
9. ZIP_LOCALITY (VARCHAR(150)): ZIP code and locality; multiple separated by ,
10. MUNICIPALITY_NR (NUMBER(6,0)): Number of the municipality
11. CANTON (VARCHAR(2)): Canton of the municipality
12. STATUS (VARCHAR(50)): Status of the address
13. OFFICIAL (BOOLEAN): Official address
14. LAST_MODIFIED (DATE): Last modification date of the address
15. EAST_LV95 (NUMBER(16,0)): East coordinate, LV95
16. NORTH_LV95 (NUMBER(16,0)): North coordinate, LV95
17. LONGITUDE (FLOAT): East coordinate, WGS84
18. LATITUDE (FLOAT): North coordinate, WGS84
19. POINT_LV95 (GEOMETRY): Point of the address, LV95
20. POINT_WGS84 (GEOGRAPHY): Point of the address, WGS84

**Sample values** for the Zurich office of Pro Juventute:

- BUILDINGADDRESS_ID: 100178372
- STREET_ID: 10182315
- BUILDING_ID: 172869
- ENTRY_ID: 0
- STREET: "Thurgauerstrasse"
- HOUSE_NUMBER: "39"
- BUILDING_CATEGORY: "non_residential"
- BUILDING_NAME: NULL
- ZIP_LOCALITY: "8050 Zürich"
- MUNICIPALITY_NR: 261
- CANTON: "ZH"
- STATUS: "real"
- OFFICIAL: TRUE
- LAST_MODIFIED: 2024-07-23
- EAST_LV95: 2683925
- NORTH_LV95: 1252127
- LONGITUDE: 8.55071171
- LATITUDE: 47.414605366

搜集汇总

背景与挑战

背景概述

该数据集提供瑞士基础地理信息，包含地区市政边界、街道和建筑地址的详细数据，支持双坐标系统（LV95/WSG84），适用于地理编码和空间可视化分析。数据每日更新，来源为瑞士官方权威机构。