Vectortile PostgreSQL Help

List of postgres commands

# show all tables
\dt

# show all views
\dv

# show structure and definition of table/view named peaks
\d+ peaks

# quit
\q

postgis and mapbox functions

https://postgis.net/docs/index.html

-- extract x/y coordinate
ST_X
ST_Y
-- extract a coordinate field as a text
ST_AsText
-- transform from one coordinate system to another
ST_TRANSFORM
-- transforms coordinates to mapbox tile (all coordinates relative to left upper origin point of tile bounding box)
ST_AsMVTGeom
-- creates a tile bounding box of the following z,x,y values
ST_TileEnvelope

example: 
ST_X(ST_TRANSFORM(way,4674)) AS LONG,
	ST_Y(ST_TRANSFORM(way,4674)) AS LAT,
	name,
	ST_AsMVTGeom(
		ST_Transform(way, 3857),
		ST_TileEnvelope(13,4384,2878),
		4096, 64, true
	) as geom,
	ST_AsText(ST_Transform(ST_TileEnvelope(13,4384,2878), 4326))

Coordinate system EPSG:3857: uses meters as units (although inaccuracies may occur nearer to the poles)

index

https://www.postgresql.org/docs/current/indexes-types.html

SQL commands from files

For ease of development we are currently using .sql files where we are writing our queries, which will then be send to the postgres server and exectuted as a batch command:

psql alpinemaps -U alpine -f query.sql

In order to avoid having to type in your password at every terminal command you can define the environment variable PGPASSWORD and set it to your password:

export PGPASSWORD=SOMEPASSWORD

running martin with local postgres

docker run \
  --net=host \
  -e DATABASE_URL=postgresql://postgres:alpinemaps@localhost/alpinemaps \
  ghcr.io/maplibre/martin

# DATABASE_URL SYNTAX:   postgresql://USER:PASSWORD@localhost/DATABASENAME 

Leaflet

In order to visualize vector tiles in a simple 2D map you can use something like leaflet. An index.html file with leaflet code is located in the https://github.com/AlpineMapsOrg/martin_config repository. In order to specify which tables are shown you have to edit the JavaScript code in the .html file.

To start a server and serve this file run the following command:

python -m http.server 8080

Debugging

tile not loaded in qt application

there might be something wrong with the input data instead of qt code -> demand the tile directly (e.g. using wget) and check the result -> if you get a 500 server error you there most likely is something wrong with the psql data (e.g. you are converting a datafield to int but it is a string -> "500 m" instead of 500)