User:FrViPofm/Rendering Dioceses
Jump to navigation
Jump to search
Warning : the codes above may contain bugs. Names of objects (sql table entities, tileMill layers...) have been changed from the original. The changes may not have been reported everywhere where it should.
Process
PostGIS
Tables
entities
CREATE TABLE entities
(
id serial NOT NULL,
"name" text,
"level" integer,
geometry geometry,
CONSTRAINT entities_pk PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
churches
CREATE TABLE churches_church
(
id serial NOT NULL,
"ref" text,
parish_id integer,
"name" text,
postal_code character varying(8),
telephone character varying(16),
email character varying(75),
url character varying(200),
locality_id character varying(5),
information text,
image character varying(100),
last_modified timestamp with time zone NOT NULL,
geometry geometry NOT NULL,
address character varying(64)
)
WITH (
OIDS=FALSE
);
Requests
A special set of requests creates or refreshes the ecclesiastic entities starting from communes boundaries forming them.
Each request, one by diocese, stores the structure of the tree : diocese > zone > parish > commune and can be modified.
The code above is used three times, one for each level: commune -> parish, parish -> zone, zone -> diocese. The specific parts are (un)commented for each step.
--reset
-- step 1/3
-- comment on steps 2&3
TRUNCATE "entities"; ALTER SEQUENCE "entities_id_seq" RESTART WITH 1;
INSERT INTO entities (name, level, geometry)
WITH
parish (id, city, name) AS (VALUES
(INSEE, Name, Parish_name),
...
),
zone (parish_name, name) AS (VALUES
(Parish_name, Zone_name),
-- insert parishes : step 1/3
-- uncomment on step 1
-- comment on steps 2&3
SELECT parish.name, 8, ST_UNION(geo.geometry) FROM commune AS geo LEFT JOIN parish ON parish.id=geo.id AND ST_ISVALID(geo.geometry) WHERE char_length(parish.name) > 2 GROUP BY parish.name ORDER BY parish.name;
-- insert zone : step 2/3
-- uncomment on step 2
-- comment on steps 1&3
-- SELECT zone.name, 7, ST_UNION(geo.geometry) FROM zone LEFT JOIN entities as geo ON zone.parish_name=geo.name AND ST_ISVALID(geo.geometry) WHERE char_length(geo.name) > 2 GROUP BY zone.name ORDER BY zone.name;
-- insert zone : step 3/3
-- uncomment on step 3
-- comment on steps 1&2
-- SELECT 'Diocèse d’Épinal', 6, ST_UNION(geo.geometry) FROM entities AS geo LEFT JOIN zone ON zone.name=geo.name AND ST_ISVALID(geo.geometry) WHERE char_length(zone.name) > 2 ;
Some parish boundaries are stored in a .osm file when they don't coincid with commune boundaries.
TileMill
layers
church-name.text
subquery :
(SELECT mysel.* FROM churches_church AS mysel WHERE mysel.name NOT LIKE 'Cathédrale %') AS church
church
subquery :
(SELECT
CASE
WHEN name LIKE 'Primatiale %' THEN 6
WHEN name LIKE 'Cathédrale %' THEN 5
WHEN name LIKE 'Basilique %' THEN 4
WHEN name LIKE 'Collégiale %' THEN 3
WHEN name LIKE 'Abbatiale %' THEN 2
WHEN name LIKE 'Chapelle %' THEN 1
ELSE 2
END AS "level",
*
FROM churches_church ORDER BY level DESC, name) as pow
cathedral-name.text
subquery :
diocese.entity
subquery :
entities
styles
style.mss
@entity6Color: #6e20f5; /* diocese */
@entity7Color:#f7484e; /* doyenne */
@entity8Color:#fa8902; /* paroisse */
@entity9Color:#fabf02;
@cathedralColor: #6e20f5;
@churchColor :#024d75;
#entity[level=6][zoom > 4]{
line-color: @entity6Color;
line-width: 1;
line-join: round;
line-opacity: .5;
}
#entity[level=6][zoom > 6]{
line-width: 2;
line-opacity: 1;
}
#entity[level=6][zoom <= 7]{
polygon-fill: @entity6Color;
polygon-opacity: .2;
}
#entity[level=7][zoom > 6]{
line-color: @entity7Color;
line-width: 1;
line-join: round;
line-opacity: .5;
}
#entity[level=7][zoom > 9]{
line-width: 2;
line-opacity: 1;
}
#entity[level=7][zoom > 10]{
line-width: 3;
line-opacity: 1;
}
#entity[level=7][zoom >= 7][zoom <= 9]{
polygon-fill: @entity7Color;
polygon-opacity: .2;
}
#entity[level=8][zoom > 8]{
line-color: @entity8Color;
line-width: 1;
line-join: round;
line-opacity: .5;
}
#entity[level=8][zoom > 9]{
line-width: 1;
line-opacity: 1;
}
#entity[level=8][zoom >= 9][zoom <= 12]{
polygon-fill: @entity8Color;
polygon-opacity: .2;
}
#entity[level=9][zoom > 9]{
line-color: @entity9Color;
line-width: 1;
line-join: round;
line-opacity: .5;
}
#entity[level=9][zoom > 11]{
line-width: 1;
line-opacity: 1;
}
#entity[level=9][zoom >= 11][zoom <= 15]{
polygon-fill: @entity9Color;
polygon-opacity: .2;
}
markers.mss
#church[level>4][zoom > 4] {
polygon-fill: #fff;
marker-height: 2;
marker-line-color: @cathedralColor;
marker-fill: @cathedralColor;
marker-allow-overlap: true; /* allow markers to overlap */
marker-opacity: .5; /* set the marker to 50% opacity */
marker-line-opacity: .5; /* set the line to 50% opacity */
}
#church[level<=4][zoom > 7] {
polygon-fill: #fff;
marker-height: 1;
marker-line-color: @churchColor;
marker-fill: @churchColor;
marker-allow-overlap: true; /* allow markers to overlap */
marker-opacity: .5; /* set the marker to 50% opacity */
marker-line-opacity: .5; /* set the line to 50% opacity */
}
#church[level>4][zoom > 6] {
marker-height: 3;
}
#church[level>=3][level<=4][zoom = 7] {
marker-line-color: @churchColor;
marker-fill: @churchColor;
marker-height: 1;
}
#church[level>=3][level<=4][zoom >= 8] {
marker-height: 2;
}
#church[level>=2][level<=3][zoom > 9] {
marker-height: 2;
}
#church[level=1][zoom > 13] {
marker-height: 2;
}
labels.mss
@dark: #222;
@purple: #6e20f5;
@black:#000;
@futura_med: "Futura Medium","Function Pro Medium","Ubuntu Regular","Trebuchet MS Regular","DejaVu Sans Book";
@futura_italic: "Futura Medium Italic","Function Pro Medium Italic","Ubuntu Italic","Trebuchet MS Italic","DejaVu Sans Oblique";
@futura_bold: "Futura Bold","Function Pro Bold","Ubuntu Bold","Trebuchet MS Bold","DejaVu Sans Bold";
@futura_bi: "Futura Bold Italic","Function Pro Bold Italic","Ubuntu Bold Italic","Trebuchet MS Bold Italic","DejaVu Sans Bold Oblique";
/*.community[LEVEL=6][zoom >= 7][zoom <= 8]{*/
#entity[level=6][zoom >= 7][zoom <= 8]{
text-face-name: @futura_bi;
text-fill:@entity6Color;
text-name:"[name]";
[zoom = 7],
[zoom = 8]{
text-size:10;
text-halo-fill:rgba(255,255,255,0.5);
}
}
#entity[level=7][zoom >= 9][zoom <= 10] {
text-face-name: @futura_bi;
text-fill:@entity7Color;
text-name:"[name]";
text-halo-radius:1;
text-halo-fill:rgba(255,255,255,0.8);
[zoom = 9]{
text-size:10;
}
[zoom = 10]{
text-size:12;
}
}
#entity[level=8][zoom >10] {
text-face-name: @futura_bi;
text-fill:@entity8Color;
text-name:"[name]";
text-halo-radius:1;
text-halo-fill:rgba(255,255,255,0.8);
[zoom = 11]{
text-size:12;
}
[zoom = 12]{
text-size:10;
}
}
#entity[level=9][zoom >11] {
text-face-name: @futura_bi;
text-fill:@entity9Color;
text-name:"[name]";
text-halo-radius:1;
text-halo-fill:rgba(255,255,255,0.8);
[zoom = 11]{
text-size:12;
}
[zoom = 12]{
text-size:10;
}
}
/* cahtédrales */
#church[level>4][zoom > 5]{
text-fill:@black;
text-size:10;
text-dy:5;
text-halo-fill:rgba(255,255,255,0.5);
text-halo-radius:1;
text-line-spacing:1;
text-wrap-width:20;
text-face-name: @futura_bold;
text-name:"[name]"; /* hackish? */
}
/* basiliques & collégiales */
#church[level>=3][level<=4][zoom >= 9]{
text-fill:@black;
text-size:9;
text-dy:5;
text-halo-fill:rgba(255,255,255,0.8);
text-halo-radius:1;
text-line-spacing:1;
text-wrap-width:20;
text-face-name: @futura_med;
text-name:"[name]"; /* hackish? */
}
#church[level<3][zoom > 11]{
text-fill:@black;
text-size:9;
text-dy:5;
text-halo-fill:rgba(255,255,255,0.8);
text-halo-radius:1;
text-line-spacing:1;
text-wrap-width:20;
text-face-name: @futura_italic;
text-name:"[name]"; /* hackish? */
}
Server
Making a tile server with tiles sored in a MBTiles database.
php
See : http://projects.bryanmcbride.com/ol_mbtiles/
<?php
$zoom = $_GET['z'];
$column = $_GET['x'];
$row = $_GET['y'];
$db = $_GET['db'];
try
{
//open the database
$conn = new PDO("sqlite:$db");
// query
$sql = "SELECT * FROM tiles WHERE zoom_level = $zoom AND tile_column = $column AND tile_row = $row";
$q = $conn->prepare($sql);
$q->execute();
$q->bindColumn(1, $zoom_level);
$q->bindColumn(2, $tile_column);
$q->bindColumn(3, $tile_row);
$q->bindColumn(4, $tile_data, PDO::PARAM_LOB);
while($q->fetch())
{
header("Content-Type: image/png");
echo $tile_data;
}
}
catch(PDOException $e)
{
print 'Exception : '.$e->getMessage();
}
?>