SQL-Karten Beipiele

Florian Zacherl






Die folgenden Beispiele beziehen sich auf die Projektdatenbank des Projekts VerbaAlpina, Version 232 sowie auf eine Datenbank zum antiken Pompeji. Die Anweisungen müssen ggf. analog auf andere Datenbankstrukturen übertragen werden.

1. Marker und Polygone mit SQL-Abfrage

SQL (Marker):

SELECT
ST_X(Georeferenz) AS lng,
ST_Y(Georeferenz) AS lat,
IF(Alpenkonvention, 'blue', 'yellow') AS color,
'hex' AS symbol,
5 AS size,
CONCAT('AIS-Informat Nr. ', Nummer, ' (', Ortsname, ')') AS description
FROM informanten
WHERE Erhebung = 'AIS'

SQL2 (Polygone):

SELECT
AsText(Geodaten) AS geo,
Name AS description,
IF(Alpenkonvention, 'blue', 'yellow') AS color
FROM Orte
WHERE Id_Kategorie = 80

SQL-Ergebnis:

SQL-Ergebnis 2:

 

Shortcode:

[map lat="43.50890510132282" lng="12.578458054884743" zoom=4 height="500px"]
[markerGroup login="va" query="SELECT ST_X(Georeferenz) AS lng, ST_Y(Georeferenz) AS lat, IF(Alpenkonvention, 'blue', 'yellow') AS color, 'hex' AS symbol, 5 AS size, CONCAT('AIS-Informat Nr. ', Nummer, ' (', Ortsname, ')') AS description FROM informanten WHERE Erhebung = 'AIS'"]

[polygonGroup login="va" query="SELECT AsText(Geodaten) as geo, Name as description, IF(Alpenkonvention, 'blue', 'yellow') AS color FROM Orte WHERE Id_Kategorie = 80"][/polygonGroup]
[/map]

 

Karte:

 

2. Gespeicherte Abfragen

Abfrage 1 (161_y)

SELECT
 ST_X(Georeferenz) AS lng,
 ST_Y(Georeferenz) AS lat,
 IPA AS description,
'text' AS symbol,
'Y' AS text,
50 AS size,
FROM Tokens
 JOIN Informanten USING (id_informant)
WHERE
 IPA != ''
 AND IPA like 'y%'
 AND LENGTH(Token) > 2

 

Abfrage 2 (161_z)

SELECT
 ST_X(Georeferenz) AS lng,
 ST_Y(Georeferenz) AS lat,
 IPA AS description,
'text' AS symbol,
'Z' AS text,
50 AS size,
'green' AS color
FROM Tokens
 JOIN Informanten USING (id_informant)
WHERE
 IPA != ''
 AND IPA like 'z%'
 AND LENGTH(Token) > 2

Shortcode:

[map lat="46.66843580134054" lng="9.67042399281278" zoom=6 height="400px"]
[markerGroup load="161_y"]
[markerGroup load="161_z"]
[/map]

 

Karte:

3. Mehrere Gruppen

[map lat="43.50890510132282" lng="12.578458054884743" zoom=4 height="400px"]
[markerGroup load="adis_001_001"]

[markerGroup load="adis_001_005"]
[/map]

SELECT ST_X(Georeferenz) AS lng, ST_Y(Georeferenz) AS lat, SUBSTRING(Nummer, 1, 3) AS text, '' AS description, 'black' as color, 'text' AS symbol, 12 AS size FROM Informanten WHERE Erhebung = 'AIS' GROUP BY SUBSTRING(Nummer, 1, 3)

 

4. Verschiedenfarbige Polygone

Shortcode

[map lat="43.50890510132282" lng="12.578458054884743" zoom=4 height="500px" background="carto-light"]
[polygonGroup login="va" query="SELECT AsText(Geodaten) as geo, Name as description, 0 as border FROM Orte WHERE Id_Kategorie = 80" color="distinct"][/polygonGroup]
[/map]

Karte

5. Verschiedenfarbige Marker

Shortcode

[map lat="43.50890510132282" lng="12.578458054884743" zoom=5 height="500px"]
[markerGroup login="va" query="SELECT ST_X(Georeferenz) AS lng, ST_Y(Georeferenz) AS lat, 'hex' as symbol, 5 AS size, CONCAT('AIS-Informat Nr. ', Nummer, ' (', Ortsname, ')') AS description FROM informanten WHERE Erhebung = 'AIS'" color="distinct"][/map]

Karte

6. Line Strings

Shortcode

[map lat="40.75122392" lng="14.48866335" zoom=16 height="500px"]

[linestringGroup login="pompeji" query="SELECT st_AsText(wkb_line) as geo, CONCAT(NAME, ' (', a.bedeutung, ')') as DESCRIPTION, case when a.bedeutung LIKE 'Maximus' then 'red' when a.bedeutung LIKE 'Hauptstraße' then 'orange' when a.bedeutung LIKE 'Nebenstraße' then 'black' END color, case when a.bedeutung LIKE 'Maximus' then 10 else 4 END width, case when a.bedeutung LIKE 'Maximus' then 'solid' when a.bedeutung LIKE 'Hauptstraße' then 'dashed' else 'dotted' END style FROM viae a"][/linestringGroup]

[polygonGroup login="pompeji" query="select st_astext(a.wkb) as geo, concat('Stadtgebiet (Fläche: ', round(ST_AREA(a.wkb)/10000,0), ' Hektar)') AS description, 'grey' as color FROM pompeji as a where a.name like 'Stadtgrenze'"][/polygonGroup]
[/map]

Karte